Create mail merge for Gmail using Apps Script

In this post I offer a short and sweet Google Apps Script for creating mail merge in Gmail. The script is very basic, merely meant to introduce the topic. In future posts I'll add more features and capabilities.

Mail merge is essentially the ability to send multiple emails in one operation, yet personalize the email to each recipient. It saves you the time and energy to compose multiple emails to multiple recipients that say the same thing. With mail merge, you take an email template, and a list of recipients. You then replace placeholders in the template with the data of each recipients. Like I said, pretty simple stuff.

The first thing we need is a Google Sheet with our recipients' data, like the one in the image below.

The first row in the sheet, aka the header row, contains our placeholders. We will embed these placeholders in the template and instruct our little script to replace them with each recipient's data.

You can name the headers however you want. If a header contains multiple words then you don't have to connect them into a compound word like in the example above. It's just an idea.

To write the script, open Google Apps Script from the "Extensions" menu, and add the code below to Code.gs.

const g = {
  subject: '{{firstName}}, how do you like your {{productName}}?',
  body: `Hi {{firstName}},
            
  I am following up to see how things are going with the {{productName}} that 
  you purchased on {{purchaseDate}}.
            
  {{firstName}}, please let me know if you have any questions.
             
  Thank you,
  Ben`,
};

The "g" object holds our email template. I do this in code just to make this post brief. In future posts I'll show how to store these templates in files that business users can maintain without handling code.

The "g" object has two keys for the email subject and body. Inside the template content I include the placeholders from our sheet headers, spelled exactly as they appear in the sheet. I surround them with {{}} just so that the script will know how to find them. You can identify your placeholders in any other way that works for you.

Next, we have the main function that runs our automation:

const automate = () => {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0];
  let [headers, ...data] = sh.getDataRange().getValues();
  data = data.filter((row) => row[0].toString().trim().length > 0);
};

The first thing we need to do is get our recipients' data from the sheet. "ss" points to our active spreadsheet and "sh" to its first sheet. We get the data out of the sheet and assign the first row to the "headers" variable. The rest of the rows go to the "data" variable. Finally, we filter out any empty rows that may reside in our data.

try {
  data.forEach((row) => {
    let subject = g.subject;
    let body = g.body;
    let email;
    headers.forEach((header, i) => {
      const re = new RegExp(`{{${header}}}`, 'g');
      subject = subject.replace(re, row[i]);
      body = body.replace(re, row[i]);
      if (header === 'email') {
        email = row[i];
      }
    });
    GmailApp.sendEmail(email, subject, body);
  });
  console.log('Mail merge completed');
} catch (err) {
  console.log(err.message);
  return;
}

We iterate through our recipients' data row by row. For each row we duplicate the template subject and body.

We then iterate through the headers. We create a regular expression with a global flag that wraps each header with our {{}} identifier. We then replace the placeholder with the row data in the "i" column.

As we iterate through the headers, we look for the "email" header. If we find it then we assign it to the "email" variable.

Once the header iteration is complete, we use "GmailApp.sendEmail()" to send out our emails, passing in the recipient's email and their personalized subject line and email body. GmailApp takes care of sending the email for us.

Once we're done with the data iteration, we log a message that the automation is done.

The "try/catch" enables us to catch any error and log it to the console.

We have one utility function:

function onOpen(e) {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🟢 Run').addItem('Start Mail Merge', 'start').addToUi();
}

This function will create a custom menu that we can use to run the automation directly from the sheet.

The actual email that the recipient receives looks like this:

The first time you run this script Google will ask you to authorize it. It's best to test the mail merge with your own email address before you send out emails to your recipients.