Use Google App Engine to automate a website's content update workflow in Trello

trello_automation.js

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;
}