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:

Sections

This tutorial is divided into the following sections:

Section 1: Preparing a spreadsheet and running the example

  1. Open our Spreadsheet template for this tutorial.

  2. Create a personal copy of the spreadsheet by clicking on 'Make a Copy'. You can now edit the spreadsheet and start writing scripts.

  3. Submit data to the spreadsheet by clicking on the 'Form' menu, then 'Go to live form'. Make sure you enter your own email address.

  4. If you go back to the spreadsheet, you should see a new row with the data you entered in the form

  5. Select the menu item Tools > Script editor.

  6. 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.

  7. 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.

  1. Function sendEmails

    gmail/mailmerge/mailmerge.gs
    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.

    gmail/mailmerge/mailmerge.gs
    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.

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

    Reads all the data in a given range of spreadsheet cells.

    gmail/mailmerge/mailmerge.gs
    // 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.

    gmail/mailmerge/mailmerge.gs
    MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);

    Sends the actual email.

  2. Function fillInTemplateFromObject

    gmail/mailmerge/mailmerge.gs
    // 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).

    gmail/mailmerge/mailmerge.gs
    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.

    gmail/mailmerge/mailmerge.gs
    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.

  1. Click on the 'Form' menu, then 'Edit Form' and add a new question to retrieve the name of the department.
  2. 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).
  3. Notice how a new column has been created in the Spreadsheet that corresponds to the new question in the Form.
  4. 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.

  5. 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);

  6. Run the function sendEmails and check your email.

  7. 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
/**
 * Iterates row by row in the input range and returns an array of objects.
 * Each object contains all the data for a given row, indexed by its normalized column name.
 * @param {Sheet} sheet The sheet object that contains the data to be processed
 * @param {Range} range The exact range of cells where the data is stored
 * @param {number} columnHeadersRowIndex Specifies the row number where the column names are stored.
 *   This argument is optional and it defaults to the row immediately above range;
 * @return {object[]} An array of objects.
 */
function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), normalizeHeaders(headers));
}

/**
 * For every row of data in data, generates an object that contains the data. Names of
 * object fields are defined in keys.
 * @param {object} data JavaScript 2d array
 * @param {object} keys Array of Strings that define the property names for the objects to create
 * @return {object[]} A list of objects.
 */
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}

/**
 * Returns an array of normalized Strings.
 * @param {string[]} headers Array of strings to normalize
 * @return {string[]} An array of normalized strings.
 */
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

/**
 * Normalizes a string, by removing all alphanumeric characters and using mixed case
 * to separate words. The output will always start with a lower case letter.
 * This function is designed to produce JavaScript object property names.
 * @param {string} header The header to normalize.
 * @return {string} The normalized header.
 * @example "First Name" -> "firstName"
 * @example "Market Cap (millions) -> "marketCapMillions
 * @example "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
 */
function normalizeHeader(header) {
  var key = '';
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == ' ' && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

/**
 * Returns true if the cell where cellData was read from is empty.
 * @param {string} cellData Cell data
 * @return {boolean} True if the cell is empty.
 */
function isCellEmpty(cellData) {
  return typeof(cellData) == 'string' && cellData == '';
}

/**
 * Returns true if the character char is alphabetical, false otherwise.
 * @param {string} char The character.
 * @return {boolean} True if the char is a number.
 */
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}

/**
 * Returns true if the character char is a digit, false otherwise.
 * @param {string} char The character.
 * @return {boolean} True if the char is a digit.
 */
function isDigit(char) {
  return char >= '0' && char <= '9';
}

/**
 * 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; templateVars && 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!