Google Apps Script Patterns: Celebrating 10 years by revisiting mail merge/bulk emailing

TL;DR A new solution for creating and distribute visually rich bulk emails with Gmail and Google Sheets.

On 19 August 2019 Google Apps Script celebrated 10 years of general access. Apps Script has come a long way in the 10 years as you can see from the announcement blog post and launch video also embedded below:

One of the use cases covered in the video is performing a mail merge from data in Google Sheets. Over the years this topic has been covered multiple times and you access one of the first Apps Script tutorials Simple Mail Merge. The topic was revisited in 2011 on the G Suite Developers Blog in 4 ways to do Mail Merge using Google Apps Script. This post features contributions from Google Apps Script stalwards, James Ferreira, Steve Webster and Romain Vialard.

As part of Romain’s contribution he shared a very early version of “Yet another Mail Merge” to the now deprecated Script Gallery. YAMM has gone on to become one of the most popular Google Sheets Add-ons [see YAMM listing]. Whilst I’m not aware of the original code being available anywhere Romain still regularly helps out other developers with solving problems related to this solution (a recent example).

In the ‘4 ways’ post Romain highlights the benefits of using the Gmail Service to access a draft email composed by the user enabling them to use the built-in Gmail formatting options. Manually formatting emails can sometimes be a tricky business because whilst most email clients are happy rendering HTML all the styling needs to be inline for it to render correctly and by using the Gmail message composer this is done for you.

When I was recently asked if I’d like to contribute an example to the new G Suite Solutions Gallery my immediate thought given Apps Script was celebrating 10 years was to revisit the mail merge use case using the Gmail draft solution used by Romain. That solution is now published here and the code included in the Solutions Github repo.

Mail Merge

The code for this solution is built on a previous Google Apps Script Pattern on Conditionally updating rows of Google Sheet data by reading and writing data once. To complete the circle it also uses a modified version of the fillInTemplateFromObject() function used in the original Mail Merge tutorial.

So here is to many more years of Google Apps Script and mail merge solutions …

If you have an idea for your own solution for the gallery you can submit it on the GitHub issue tracker.