Created
March 1, 2019 05:57
-
-
Save jamiebergen/c4c0d6cce07b6d3118914a74c733e87a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function dailyCheck() { | |
// Get today's date | |
var todaysDate = getTodaysDate(); | |
var todaysMonth = todaysDate['month']; | |
var dayOfMonth = todaysDate['day']; | |
var thisYear = todaysDate['year']; | |
var nextMonth = todaysDate['next']; | |
// Check cards and run tasks if found | |
var monthListID = getMonthListID(todaysMonth); | |
var nextMonthListID = getMonthListID(nextMonth); | |
checkCards(monthListID, nextMonthListID, dayOfMonth, thisYear); | |
} | |
function getTodaysDate() { | |
var now = new Date(); | |
var months = []; | |
months[0] = "January"; | |
months[1] = "February"; | |
months[2] = "March"; | |
months[3] = "April"; | |
months[4] = "May"; | |
months[5] = "June"; | |
months[6] = "July"; | |
months[7] = "August"; | |
months[8] = "September"; | |
months[9] = "October"; | |
months[10] = "November"; | |
months[11] = "December"; | |
var currentMonth = months[now.getMonth()]; | |
var nextMonth = months[(now.getMonth()+1)%12]; // Mod accounts for Dec -> Jan | |
var currentDate = now.getDate(); | |
var currentYear = now.getFullYear(); | |
var todaysDate = {month:currentMonth, next:nextMonth, day:currentDate, year:currentYear}; | |
//Logger.log(todaysDate); | |
return todaysDate; | |
} | |
function getMonthListID(month) { | |
var listIDs = []; | |
listIDs['September'] = ''; // !!! Fill in actual list IDs | |
listIDs['October'] = ''; | |
listIDs['November'] = ''; | |
listIDs['December'] = ''; | |
listIDs['January'] = ''; | |
listIDs['February'] = ''; | |
listIDs['March'] = ''; | |
listIDs['April'] = ''; | |
listIDs['May'] = ''; | |
listIDs['June'] = ''; | |
listIDs['July'] = ''; | |
listIDs['August'] = ''; | |
//Logger.log(listIDs[month]); | |
return listIDs[month]; | |
} | |
function trelloAPICall(url, options) { | |
var appKey = 'your_app_key'; // !!! Your app key goes here | |
var appToken = 'your_app_token'; // !!! Your app token goes here | |
var urlWithKey = url + 'key=' + appKey + '&token=' + appToken; | |
return UrlFetchApp.fetch(urlWithKey, options); | |
} | |
function checkCards(monthListID, nextMonthListID, dayOfMonth, thisYear) { | |
// Get 7 days from today | |
var ref = new Date(); | |
ref.setDate(ref.getDate()+7); | |
var dayIn7 = ref.getDate(); | |
var monthIn7 = ref.getMonth(); | |
var yearIn7 = ref.getFullYear(); | |
// Determine whether 7 days from today is next month | |
var today = new Date(); | |
var currentMonth = today.getMonth(); | |
var needNextMonth = true; | |
if (currentMonth == monthIn7) { | |
needNextMonth = false; | |
} | |
// Get all cards for this month | |
var url = 'https://api.trello.com/1/lists/' + monthListID + '/cards?'; | |
var options = {'method' : 'get'}; | |
var response = trelloAPICall(url, options); | |
var results = JSON.parse(response.getContentText()); | |
for (result in results){ | |
// Get due date (if assigned) | |
if (results[result].due !== null) { | |
var dueDate = new Date(results[result].due); | |
var dayDue = dueDate.getDate(); | |
var yearDue = dueDate.getFullYear(); | |
} else { | |
var dayDue = null; | |
} | |
// If due today: | |
// Our Team: Update due date for next year | |
// External: Copy card to waiting list, send notification, update due date | |
if (dayDue == dayOfMonth && yearDue == thisYear) { | |
var cardID = results[result].id; | |
var customFields = retrieveCustomFields(cardID); | |
var sendTo = (customFields['sendto'] ? customFields['sendto'] : 'test@email.com'); | |
var subjectLine = (customFields['subjectline'] ? customFields['subjectline'] : null); | |
var message = (results[result].desc ? results[result].desc : null); | |
if (sendTo !== 'test@email.com') { // External | |
if (subjectLine && message) { | |
var awaitingResponseListID = '5bb3a0a979a2d134d45c075e'; // Waiting for Feedback | |
sendNotification(sendTo, subjectLine, message); | |
copyCardToList(cardID, awaitingResponseListID); | |
} else { | |
// Send email to Our Team to notify them that it didn't send | |
var exceptionMessage = 'Just a heads up! The message to ' + sendTo + ' failed to send due to a missing subject line and/or message.'; | |
sendNotification('test@email.com', 'Message failed to send', exceptionMessage); | |
} | |
} | |
// Update due date (for both Our Team and External) | |
updateDueDate(cardID); | |
// Write to log | |
logCardAction(results[result].name, sendTo, dueDate); | |
} | |
// If due in 7 days (and 7 days from now is still this month): | |
if (needNextMonth === false) { | |
if (dayDue == dayIn7 && yearDue == yearIn7) { | |
var cardID = results[result].id; | |
sevenDaysPrior(cardID, results[result].name, dueDate); | |
} | |
} | |
} | |
// If due in 7 days (and 7 days from now is next month): | |
if (needNextMonth === true) { | |
// Get all cards for next month | |
var url2 = 'https://api.trello.com/1/lists/' + nextMonthListID + '/cards?'; | |
var options2 = {'method' : 'get'}; | |
var response2 = trelloAPICall(url2, options2); | |
var results2 = JSON.parse(response2.getContentText()); | |
for (result in results2){ | |
// Get due date (if assigned) | |
if (results2[result].due !== null) { | |
var dueDate = new Date(results2[result].due); | |
var dayDue = dueDate.getDate(); | |
var yearDue = dueDate.getFullYear(); | |
} else { | |
var dayDue = null; | |
} | |
// If due in 7 days: | |
if (dayDue == dayIn7 && yearDue == yearIn7) { | |
var cardID = results2[result].id; | |
sevenDaysPrior(cardID, results2[result].name, dueDate); | |
} | |
} | |
} | |
} | |
function sevenDaysPrior(cardID, cardName, dueDate) { | |
// Our Team: Copy to upcoming (top of list) and send email reminder | |
// External: Send email to Our Team with reminder to check message, email addresses, and due date | |
var customFields = retrieveCustomFields(cardID); | |
// Send email and copy card to Our Team board | |
var sendTo = (customFields['sendto'] ? customFields['sendto'] : 'test@email.com'); | |
var subjectLine = (customFields['subjectline'] ? customFields['subjectline'] : cardName); | |
if (sendTo == 'test@email.com') { // Our Team: copy to upcoming (top of list), send email reminder | |
var message = 'This is a notification that a content update task is due and has been moved to the Upcoming Projects list.'; | |
sendNotification(sendTo, subjectLine, message); | |
var upcomingProjectsListID = ''; // Upcoming Projects | |
copyCardToList(cardID, upcomingProjectsListID); | |
} else { // External: Send email to Our Team with reminder to check message, email addresses, and due date | |
var message = 'This is a reminder that an external notification is scheduled to be sent in 7 days. Please check the message, email address, and due date in advance.'; | |
sendNotification('test@email.com', subjectLine, message); | |
} | |
// Write to log | |
logCardAction(cardName, sendTo, dueDate); | |
} | |
function retrieveCustomFields(cardID) { | |
// Retrieve custom fields for card | |
var options = {'method' : 'get'}; | |
var customFieldUrl = 'https://api.trello.com/1/cards/' + cardID + '/?fields=name&customFieldItems=true&'; | |
var customFieldResponse = trelloAPICall(customFieldUrl, options); | |
var customFieldResults = JSON.parse(customFieldResponse.getContentText()); | |
customFieldResults = customFieldResults.customFieldItems; | |
//Logger.log(customFieldResults); | |
var emailSubjectLineId = ''; // idCustomField | |
var emailNotificationId = ''; // idCustomField | |
var customFieldObject = {}; | |
for (customField in customFieldResults){ | |
if (customFieldResults[customField].idCustomField == emailSubjectLineId) { | |
customFieldObject['subjectline'] = customFieldResults[customField].value.text; | |
} else if (customFieldResults[customField].idCustomField == emailNotificationId) { | |
customFieldObject['sendto'] = customFieldResults[customField].value.text; | |
} | |
} | |
//Logger.log(customFieldObject); | |
return customFieldObject; | |
} | |
function sendNotification(sendTo, subjectLine, message) { | |
var replyTo = 'test@email.com'; | |
if (sendTo == 'test@email.com') { | |
MailApp.sendEmail(sendTo, replyTo, subjectLine, message); | |
} else { | |
var msgPlain = message.replace(/(<([^>]+)>)/ig, ""); // clear html tags for plain mail | |
MailApp.sendEmail(sendTo, subjectLine, msgPlain, { | |
name: 'SLS Our Team', | |
replyTo: replyTo, | |
cc: 'test@email.com', | |
htmlBody: message | |
}); | |
} | |
//Logger.log('Mail sent to: ' + sendTo); | |
} | |
function copyCardToList(cardID, listID) { | |
var payload = {'idList' : listID, // (required) The id of the list that the card should be added to | |
'idCardSource' : cardID, // (optional) The id of the card to copy into a new card. | |
'pos' : 'top' // (optional) The position to place the copied card | |
}; | |
var url = 'https://api.trello.com/1/cards?'; | |
var options = {'method' : 'post', | |
'payload' : payload}; | |
trelloAPICall(url, options); | |
//Logger.log('Copy card to board'); | |
} | |
function updateDueDate(cardID) { | |
nextYear = yearFromToday(); | |
var payload = {'due' : nextYear, | |
//'labels': ''//(optional) | |
}; | |
var url = 'https://api.trello.com/1/cards/' + cardID + '?'; | |
var options = {'method' : 'put', | |
'payload' : payload}; | |
trelloAPICall(url, options); | |
//Logger.log('Update due date'); | |
} | |
function logCardAction(name, sendTo, dueDate) { | |
var d = new Date(); | |
var currentDate = d.toLocaleDateString(); | |
var sheet = SpreadsheetApp.getActiveSheet(); | |
sheet.appendRow([name, sendTo, dueDate, currentDate]); | |
} | |
function yearFromToday() { | |
var nextYear = new Date(new Date().setFullYear(new Date().getFullYear() + 1)).toISOString(); | |
return nextYear; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment