Tutorial: Simple Mail Merge

Hugo Fierro, Google Apps Script team
Ikai Lan, Google Apps Script team
Originally published March 2009, updated December 2012

Goal

This tutorial shows an easy way to collect information from different users in a spreadsheet using Google Forms, then leverage it to generate and distribute personalized emails.

Time to Complete

Approximately 15 minutes

Prerequisites

Before you begin this tutorial, you should already be familiar with:

the getRowsData function described in the Reading spreadsheet data using JavaScript Objects tutorial
Google Forms

Sections

This tutorial is divided into the following sections:

Section 1: Preparing a spreadsheet and running the example
Section 2: Understanding the code
Section 3: Exercise: modifying the form and the personalized email
Section 4: More powerful templates
Section 5: Full code
Summary

Section 1: Preparing a spreadsheet and running the example

Open our Spreadsheet template for this tutorial.
Create a personal copy of the spreadsheet by clicking on 'Make a Copy'. You can now edit the spreadsheet and start writing scripts.
Submit data to the spreadsheet by clicking on the 'Form' menu, then 'Go to live form'. Make sure you enter your own email address.
If you go back to the spreadsheet, you should see a new row with the data you entered in the form
Select the menu item Tools > Script editor.
In the script editor, select the menu item Run > sendEmails. After you authorize the script, as many emails as rows in the spreadsheet will be sent.
Finally, check your email. It may take a few seconds for the message to be delivered, so you may want to refresh.

Continue reading Section 2 to learn more about how this example's script works.

Section 2: Understanding the code

In this section, we will explain relevant parts of the script. The full code is available in Section 5.

Function sendEmails
```
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 4);
```
The code above gets a range that contains all the data in the spreadsheet. Note that unbounded ranges are not supported so we compute the range using the actual total number of rows in the Sheet.
```
  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange("A1").getValue();
```
The code above retrieves the string template that will be used to generate personalized emails. It is defined in the "A1" cell in the second sheet called 'Email Template'.

Note the four template markers, like ${"First Name"} . They correspond to column names in the data Sheet and specify where the data from a given row should be displayed.
```
  // Create one JavaScript object per row of data.
  var objects = getRowsData(dataSheet, dataRange);
```
Reads all the data in a given range of spreadsheet cells. See Reading spreadsheet data using JavaScript Objects tutorial
```
  // Generate a personalized email.
  // Given a template string, replace markers (for instance ${"First Name"}) with
  // the corresponding value in a row object (for instance rowData.firstName).
  var emailText = fillInTemplateFromObject(emailTemplate, rowData);
```
Given the template string described above, prepares the text of an email that contains the information submitted by a user using a form.
```
  MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);
```
Sends the actual email.
Function fillInTemplateFromObject
```
  // Search for all the variables to be replaced, for instance ${"Column name"}
  var templateVars = template.match(/\${\"[^\"]+\"}/g);
```
The JavaScript function match find instances of a given pattern of text (See Regular Expressions).
```
  var variableData = data[normalizeHeader(templateVars[i])];
```
Tries to retrieve the value in a data object corresponding to a marker. This is done by normalizing the marker name and checking if the data object has a value associated with the normalized name.
```
  email = email.replace(templateVars[i], variableData || "");
```
Replaces a marker in the email template string with a value or simply remove the marker if no value for it has been found.

Section 3: Exercise: modifying the form and the personalized email

At this point you should be able to modify the form and personalized emails.

Click on the 'Form' menu, then 'Edit Form' and add a new question to retrieve the name of the department.
Open the live form and submit some data (once again, make sure you put your own email address to be able to see the results).
Notice how a new column has been created in the Spreadsheet that corresponds to the new question in the Form.
Now, modify the template email (cell "A1" in the second sheet) and add a marker to display the department name somewhere in the email. Remember that the marker name has to match the column name in the data Sheet.
Open the Script Editor. Update the variable dataRange in the sendEmails function to read the new department column:
```
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 5);
```
Run the function sendEmails and check your email.
Check that the department names appear in the email. If it does not, verify the marker name in the template string.

Section 4: More powerful templates

Are you interested in using a more advanced template library?

Check out open source JavaScript template libraries, for instance EJS. You can quickly copy & paste the code in your script and start using it.

Also, if you would like to store your templates somewhere else (e.g. a web server), you will be very soon able to download the template using our URLFetch service.

Section 5: Full code

gmail/mailmerge/mailmerge.gs

View on GitHub

/**
 * Sends emails from spreadsheet rows.
 */
function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 4);

  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange('A1').getValue();

  // Create one JavaScript object per row of data.
  var objects = getRowsData(dataSheet, dataRange);

  // For every row object, create a personalized email from a template and send
  // it to the appropriate person.
  for (var i = 0; i < objects.length; ++i) {
    // Get a row object
    var rowData = objects[i];

    // Generate a personalized email.
    // Given a template string, replace markers (for instance ${"First Name"}) with
    // the corresponding value in a row object (for instance rowData.firstName).
    var emailText = fillInTemplateFromObject(emailTemplate, rowData);
    var emailSubject = 'Tutorial: Simple Mail Merge';

    MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);
  }
}

/**
 * Replaces markers in a template string with values define in a JavaScript data object.
 * @param {string} template Contains markers, for instance ${"Column name"}
 * @param {object} data values to that will replace markers.
 *   For instance data.columnName will replace marker ${"Column name"}
 * @return {string} A string without markers. If no data is found to replace a marker,
 *   it is simply removed.
 */
function fillInTemplateFromObject(template, data) {
  var email = template;
  // Search for all the variables to be replaced, for instance ${"Column name"}
  var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);

  // Replace variables from the template with the actual values from the data object.
  // If no value is available, replace with the empty string.
  for (var i = 0; i < templateVars.length; ++i) {
    // normalizeHeader ignores ${"} so we can call it directly here.
    var variableData = data[normalizeHeader(templateVars[i])];
    email = email.replace(templateVars[i], variableData || '');
  }

  return email;
}

Summary

Congratulations, you have completed this tutorial and have created your own simple Mail Merge application leveraging Google Forms. It should allow you to create new Forms and send personalized emails without having to modify any code. Feel free to reuse the script and adjust it to your needs!