Automating newsletters with Google Apps Script

In the month since my last post on Setting up a Gmail recruiter response template, I’ve been exploring other ways to automate tedious tasks in G Suite. At first, I just wanted to create a basic timestamp in Google Sheets. When I couldn’t find a built-in formula, I Googled around and learned I could create one easily by writing a Google Apps Script.

Around the same time, I also started having conversations with folks to learn about design ops. Over the course of those conversations, one phrase really stuck with me: “process over people.”

In the abstract, it might sound just like another piece of robotic tech jargon. But in practice, I think it encourages healthy team dynamics:

• Teams are never fixed
• In any given week, someone might be out sick, on vacation or on leave
• Less frequently, someone might transfer to a new team, get laid off or leave the company
• If someone is absent, the rest of the teammates should be able run the team normally

If a team comes grinding to a halt when someone is out or leaves, the unhealthy dynamics quickly become apparent:

• People are discouraged from taking time off and burn out
• Information gets used as currency
• Growth opportunities aren’t evenly distributed

To me, “process over people” means “prioritize the knowledge of a group of people over the knowledge of a single person.” I think I like this idea so much because it refutes my least favorite design systems trope: the design systems hero. But it also aligns well with my team’s longterm goal, which is to turn the Karma Product Language into a self-service function.

To be honest, we’re still a far cry from running a self-service design system. But we’ve taken the first step by starting to document and automate our processes.

Opportunity for automation

When I first dug into the Google Apps Script docs, I compiled a list of tasks that took up too much of my time. Writing and sending out our design systems newsletter was at the top of the list. I had experimented with Mailchimp and other newsletter services in the past, but creating a highly produced newsletter ended up being really time-consuming.

I also realized a formal newsletter service wouldn’t be collaborative enough. Since there are now 4 designers and 10 engineers working on design systems, collating and publishing the newsletter is more complex than it was before. On one hand, this is a good thing, because it means system knowledge is shared across our group. But it’s also tough, because there’s not yet a clear process in place for drafting the newsletter as a team.

To make it clearer, I needed to define:

• Where does the newsletter get written?
• How often does it get sent out?
• Who does it get sent out to?
• What information should be included?
• How do people contribute to it?

Getting ready

Tool-wise, Google Docs was an easy replacement for Mailchimp, since everyone at the company already has the right license and permissions. As someone new to Google Apps Script, I also thought it would be easier to stay within the G Suite family. I cleaned up the Newsletter folder of our KPL team drive and created a fresh template file.

Next, I wrote a quick template draft.

Writing the script

When the template was in a good place, I opened up Google Apps Script and created a new project called “Generate KPL Newsletter Draft.”

Piecing together snippets from StackOverflow, I wrote a script to:

1. Add the file ID for the KPL Newsletter Template Google Doc
2. Request the file from Drive
3. Get the current date
4. Using the current date, find the Mondays of the month
5. Create a new copy of the template, to be sent 14 days after the first Monday of the month
6. Create a second copy of the template, to be sent 14 days after the third Monday of the month

function generateDoc() {
 // Get document information
  var templateFileId = ADD FILE ID HERE;
  var file = DriveApp.getFileById(templateFileId);

 //  Get today's date
  var d = new Date(),
    month = d.getMonth(),
    mondays = [];

  d.setDate(1);

  // Get the first Monday in the month
  while (d.getDay() !== 1) {
    d.setDate(d.getDate() + 1);
  }

  // Get all the other Mondays in the month
  while (d.getMonth() === month) {
    mondays.push(new Date(d.getTime()));
    d.setDate(d.getDate() + 7);
  }

  var firstMonday = mondays[0];
  var thirdMonday = mondays[2];
  var daysToChange = 14;
  var firstMondaySendDate = new Date(
    firstMonday.getFullYear(),
    firstMonday.getMonth(),
    firstMonday.getDate() + daysToChange
  );
  var firstMondayFormattedDate = Utilities.formatDate(
    firstMondaySendDate,
    "GMT+7",
    "MM/dd/yyyy"
  );
  var thirdMondaySendDate = new Date(
    thirdMonday.getFullYear(),
    thirdMonday.getMonth(),
    thirdMonday.getDate() + daysToChange
  );
  var thirdMondayFormattedDate = Utilities.formatDate(
    thirdMondaySendDate,
    "GMT+7",
    "MM/dd/yyyy"
  );

  file.makeCopy(firstMondayFormattedDate + " Newsletter Draft");
  file.makeCopy(thirdMondayFormattedDate + " Newsletter Draft");
}

To test the script, I pressed play and checked the Newsletter folder.

Is it the most efficient script in the world? Probably not. But it works and that’s good enough for me.

Adding a trigger

Once I confirmed that the script had created the copies correctly, I added a built-in project trigger.

In this case, using a time-based trigger to run the script on the first of the month made the most sense.

Now, thanks to the script, each newsletter draft will be ready to go, even if folks on the team are out. Once I finish the rest of the documentation on how to put the non-automated parts of the newsletter together, we’ll be one step closer to self-service.

On another note

I only need to write two more posts this year to beat my 2017 record of…5 whole posts. Maybe I’ll keep the automation theme going and write about how to autogenerate a Figma component changelog from the “Publish component” dialog next.