Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Use Google App Engine to automate a website's content update workflow in Trello
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};
return todaysDate;
}
function getMonthListID(month) {
var listIDs = [];
listIDs['September'] = ''; // TODO: Fill in your list IDs
listIDs['October'] = '';
listIDs['November'] = '';
listIDs['December'] = '';
listIDs['January'] = '';
listIDs['February'] = '';
listIDs['March'] = '';
listIDs['April'] = '';
listIDs['May'] = '';
listIDs['June'] = '';
listIDs['July'] = '';
listIDs['August'] = '';
return listIDs[month];
}
function trelloAPICall(url, options) {
var appKey = ''; // TODO: Fill in your key and token here
var appToken = '';
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'] : 'ourteam@test.com');
var subjectLine = (customFields['subjectline'] ? customFields['subjectline'] : null);
var message = (results[result].desc ? results[result].desc : null);
if (sendTo !== 'ourteam@test.com') { // External
if (subjectLine && message) {
var awaitingResponseListID = ''; // Waiting for Feedback // TODO: Fill in your ID
sendNotification(sendTo, subjectLine, message, true);
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('ourteam@test.com', 'Message failed to send', exceptionMessage, false);
}
}
// 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;
var messagePreview = (results[result].desc ? results[result].desc : null);
sevenDaysPrior(cardID, results[result].name, dueDate, messagePreview);
}
}
}
// 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;
var messagePreview = (results2[result].desc ? results2[result].desc : null);
sevenDaysPrior(cardID, results2[result].name, dueDate, messagePreview);
}
}
}
}
function sevenDaysPrior(cardID, cardName, dueDate, messagePreview) {
// 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'] : 'ourteam@test.com');
var subjectLine = (customFields['subjectline'] ? customFields['subjectline'] : cardName);
if (sendTo == 'ourteam@test.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, false);
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.<br><br>';
message = message + '<b>Due Date:</b> ' + dueDate + '<br>';
message = message + '<b>Subject Line:</b> ' + subjectLine + '<br>';
message = message + '<b>Send To:</b> ' + sendTo + '<br><br>';
message = message + '<b>Message:</b>' + '<br><br>';
message = message + messagePreview;
sendNotification('ourteam@test.com', subjectLine, message, true);
}
// 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 // TODO: Fill in your IDs
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, useHtml) {
var replyTo = 'ourteam@test.com';
if (useHtml !== true) {
MailApp.sendEmail(sendTo, replyTo, subjectLine, message);
} else {
var msgPlain = message.replace(/(<([^>]+)>)/ig, ""); // clear html tags for plain mail
MailApp.sendEmail(sendTo, subjectLine, msgPlain, {
name: 'Our Team',
replyTo: replyTo,
cc: 'ourteam@test.com',
htmlBody: message
});
}
}
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);
}
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);
}
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
You can’t perform that action at this time.