Using Google Apps Script to streamline our editorial process

We're always on the lookout for new and interesting tools and beta releases to consider for inclusion in the weekly Console newsletter. When we find something that might fit, we log it in Google Sheets.

We have one Google Sheet for beta programs and one for interesting tools. Every Wednesday, we review the sheets against our selection criteria and then pick the 4-6 items for inclusion in newsletter. This is managed in Mailchimp, so we have to copy the chosen content over to the email template. The email is sent on Thursday morning and we also tweet out the links over the following week.

Although this may change in the future, our goal as we launch in 2021 is to keep our tech stack as simple as possible. It's almost a cliche that a spreadsheet (often Excel) is the most common database / UI / management system / CRM / ERP, but relying on Google Sheets means we can focus on collecting the content rather than building an elaborate Content Management System (CMS). The main downside is that a spreadsheet isn't designed for writing. We're not writing essays but even so, writing inside a small cell isn't the best UX. It does make us think about keeping things short though.

Another downside is the disconnect between where we manage the content (Google Sheets) and where we distribute it (Mailchimp and Twitter). One solution to this is to manually copy the items to Mailchimp and Twitter. There isn't too much content, but this copy/paste could easily take 30-60 minutes and there is room for human error.

A better solution is to automate as much as possible. Mailchimp doesn't allow you to create email content via their API but our template is only HTML. This means if we can generate that HTML somewhere, we will only need a human to paste it into the editor. Same with Twitter. We can just create a one click Tweet link which can then be scheduled via the Twitter web UI.

I would like to have a complete API integration to automate everything, but this was a quick way to solve most of the problem - it is better than pasting content into different sections and dealing with formatting. It cuts down time spent on boring process from an hour (with multiple opportunities for human error), to several seconds (and just one opportunity for error). And if the copy/paste has a problem, it will be obvious.

Using Google Apps Script

Google Sheets does a good job at hiding what is actually a very complex product. Like Office has VBA and Macros (now Office Scripts), Google has built Apps Script which allows you to use Google Workspace (Sheets, Docs, Slides, etc) as an application platform.

I built a simple app which adds a new menu item to the Sheets UI that will read the data in the sheet, find the items selected for the next newsletter, then output the HTML ready to be pasted into Mailchimp. Another option automatically generates links to compose the Tweets.

The JS code to create this new menu is very simple:

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  // Newsletter meny
  ui.createMenu('Newsletter')
    .addItem('Get MailChimp code', 'getMCCode')
    .addItem('Get Tweets', 'getTweets')
    .addToUi();
}

Clicking the menu option shows a modal dialog generated from an HTML template that loops through the selected content, generating the output that we need for the Mailchimp newsletter.

Both sheets have a similar structure so we take advantage of the ability to create and share Apps Script libraries. This packages common code in a library that is called by Apps Script bound to each Sheet. It includes a common method to get the date of next Thursday (for querying the content for inclusion in the next newsletter), and to get the data from the sheet itself.

Querying data from the sheet is core Apps Script functionality but the array is indexed by column position. We don't change the order of the columns often but there was an early bug because the fields were hard coded based on their expected position. I added a quick hack to loop through and find the right field index based on the heading column to protect against future order changes.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangea1notation
var range = spreadsheet.getRange(range);
var values = range.getValues();

var data = [];

// Get the index for the field we want
// Assumes the first row is a header row
for (const [key, value] of Object.entries(values[0])) {
    if (value == scheduledForField) {
        var scheduledForIndex = key;
    }
}

for (var row in values) {
  var scheduledFor = new Date(values[row][scheduledForIndex]);
  data.push(values[row])
}

The online script editor has recently been significantly improved, however Google provides a command line tool called clasp which means I can sync the code locally and do the development using my preferred IDE.

Conclusions

This is how we have built our own simple tooling around Google Sheets as a platform for managing our content. The code is minimal and we can easily extend it if our requirements become more complex. Eventually, we may build a custom system but I expect this to be a key part of the editorial workflow for a while.

This works well because it allows anyone in our team to build the weekly Mailchimp email template with little effort. It avoids us having to build a custom CMS and instead use something reliable and robust we are already used to: Google Sheets.

The Apps Script code we use is available on our GitHub account:

• Interesting Tools Sheet Apps Script.
• Shared Mailchimp library.