Set it and forget it: Keeping your site’s content fresh through automation

I currently work on an academic website that gets a lot of traffic. In addition to several thousand visitors per day, the site has numerous content editors from various organizations within the school who maintain their own separate areas of the site. In order to keep the site’s content fresh and relevant to the particular phase of the academic year, we have a fairly elaborate content schedule that we follow.

Our team uses Trello to organize and keep track of our content schedule. In case you’re not familiar with Trello, it’s a collaboration tool that provides boards, lists, and cards in which to organize your projects. We set up a board called “Content Updates” that contains twelve lists – one for each month. Each month’s list contains cards that describe a content update task that should be completed that month.

The tasks are of two types: (1) content updates that are completed by our team (e.g., updating the main message on the homepage) and (2) content notifications that are sent to various content editors reminding them to update their areas of the site. Below is what a portion of the Content Updates board looks like. I’ve edited the image for simplicity. The actual board that we use contains a few hundred cards.

Trello content updates board

While it’s possible to manage the Content Updates board manually and use Trello’s built-in due date alerts as reminders to check the board, it requires a lot of attention and repetitive tasks, such as copying cards to our team’s Current Projects board, sending reminder emails to content editors, and updating due dates. My goal was to automate the management of the board to the point where we could “set it and forget it.”

Before I get into the automation process, I’ll explain a little more about our Trello setup.

  • We maintain a Trello board for Content Updates that is separate from the Current Projects board that we work from every day. As mentioned above, the Content Updates board contains one list for each month.
  • Each month’s list is populated with cards – one card per content update task. Each card is labeled for the type of task: content update (orange) or notification (blue). Additionally, each card has a due date and is assigned to at least one team member.
  • We use Trello’s Custom Fields Power-Up to include fields for notification email addresses and subject lines.
  • We use the main “description” field of each card to compose the email notification message.

A content update card would look like this. Note the use of Trello’s checklist feature to break down the task into small and manageable steps.

Content update Trello card

A content notification card would look like this. The main description area contains the HTML email to be sent, and custom fields specify the email address and subject line for the outbound reminder email.

Content notification Trello card

We opted for HTML emails rather than just plain text so that we could include formatting, such as bold text to draw the reader’s attention to our request. We found this HTML editor tool helpful to compose the formatted emails.

The Automation Process

Automation isn’t magical; it’s simply the process of telling the computer to go through the steps that you would have otherwise done manually. With that in mind, it’s important to start by clearly defining your workflow.

The basic idea behind our workflow is to check the Content Updates board daily for tasks that are coming due. Different actions are taken depending on whether the task is a content update, which our team completes, or a content notification, which is a reminder sent to an external group.

We found it useful to receive alerts for cards due 7 days in advance for a couple of reasons. First, it gives our team time to work the internal tasks into our schedules. Second, we get a chance to review outgoing emails to ensure that they are formatted correctly and the recipient list is up to date.

Having clarified our process, below is an outline of the daily workflow that we wanted to automate:

  1. Find any cards on the Content Updates board that are due in 7 days. Depending on the type of task, complete one of the actions below:
    • Content update task: Copy the card to our team’s Current Projects board (Upcoming Projects list), and send an email reminder to the internal team.
    • Content notification task: Send an email to the internal team with a reminder to check the message, email addresses, subject line, and due date.
  2. Find any cards on the Content Updates board that are due today. Depending on the type of task, complete one of the actions below:
    • Content update task: Update the original card’s due date to next year. (Note that the card that was copied to the Current Projects board 7 days ago is completely separate from this original card.)
    • Content notification task: Copy the card to our team’s Current Projects board (Waiting for Client list), send the notification to the specified email address(es), and update the original card’s due date to next year.

Fortunately, Trello has a REST API that makes it possible to automate the process described above. My go-to tool for automation via APIs is Google Apps Script. It’s easy to set up and has a number of built-in services for interacting with external APIs, sending emails, and scheduling timed triggers. Google Apps Script is based on JavaScript.

To set up a new project with Google Apps Script, you simply create a new Google Apps file, such as a sheet or doc, and select Tools > Script Editor. From there, you can write a script that is attached to that file. You can also create a standalone script if it doesn’t make sense to attach your script to a Google Apps file.

Below are a few of the helper functions that I wrote to manage the Trello workflow automation via Google Apps Script. You can find the entire script on GitHub.

Make a call to the Trello API

The UrlFetchApp class makes it possible to communicate with external APIs. Below is the helper function that I use to make Trello API calls.

function trelloAPICall(url, options) {
  
  var appKey = 'YOUR-APP-KEY-HERE';
  var appToken = 'YOUR-APP-TOKEN-HERE';
  
  var urlWithKey = url + 'key=' + appKey + '&token=' + appToken;

  return UrlFetchApp.fetch(urlWithKey, options);
}

Note that urlWithKey doesn’t include a ‘?’ before ‘key=’. This makes it possible to include additional query args in the url that you feed into the trelloAPICall helper function. However, you need to remember to include the ‘?’ in the url string.

Send an email notification

The MailApp class enables you to send emails. We use the version of sendEmail with optional arguments to specify the replyTo address and to include HTML tags in the body of the message.

function sendNotification(sendTo, subjectLine, message, useHtml) {
  
  var replyTo = 'ourteam@test.com';
  
  if (useHtml !== true) {  

    MailApp.sendEmail(sendTo, replyTo, subjectLine, message);
  
  } else {
    // remove html tags for plain mail
    var msgPlain = message.replace(/(<([^>]+)>)/ig, ""); 
    
    MailApp.sendEmail(sendTo, subjectLine, msgPlain, {
      name: 'Our Team Name',
      replyTo: replyTo,
      cc: 'ourteam@test.com',
      htmlBody: message
    });
  }
}

Update a Trello card’s due date

Updating a Trello card’s due date is a simple API request, where the new due date is included in the options as the payload.

function updateDueDate(cardID) {

  var nextYear = yearFromToday();
  var payload = {'due' : nextYear};
  var url = 'https://api.trello.com/1/cards/' + cardID + '?';
  var options = {'method' : 'put',
                 'payload' : payload};

  trelloAPICall(url, options);
}

Read custom fields from a Trello card

We use Trello’s Custom Fields Power-Up to maintain additional structured data for each card. The helper function below enables us to retrieve the email address and subject line from a content notification card.

function retrieveCustomFields(cardID) {

  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;
  
  // TODO: Add your IDs here
  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;
    
    }
  }
  
  return customFieldObject;
}

Copy a Trello card to a different list or board

The Trello API allows us to copy cards from the Content Updates board into our Current Projects board, which is the board we work from on a daily basis. You just need to know the ID of the card and the ID of the list to which the card should be copied.

function copyCardToList(cardID, listID) {
  
  var payload = {'idList' : listID,
                 'idCardSource' : cardID,
                 'pos' : 'top'
                };
           
  var url = 'https://api.trello.com/1/cards?';
  var options = {'method' : 'post',
                 'payload' : payload};

  trelloAPICall(url, options);
}

Putting it all together

I set up a time-driven trigger to run the script that checks for content update tasks that are coming due daily. Because the script updates the due date for each task to the next year after it’s completed, it truly creates a “set it and forget it” situation. Granted, the team still needs to be attentive to the reminder emails and make adjustments as content and content owners change. Additionally, the script is integrated with a Google Sheet, which I use to log the actions taken. That way, I can review the log to ensure that the script is working as expected.

There are several benefits that come from automating a workflow. The first is that it forces the team to define a process where one was previously only loosely defined. The second and more obvious benefit is that it makes it easier for a small team to manage a large website. By automating the workflow for our content updates, our team has been able to keep our site fresh and up to date with much less effort.

Leave a Reply

Your email address will not be published. Required fields are marked *