Mail Merge

readme.md

Data preparation

Usage: Create a spreadsheed with two sheets.

1. data
2. template

Order is important.

In the data sheet:

sent      name          Email Address	          title
<empty>	  Ran Tavory    rantav@appsflyer.com    data 1
<empty>	  Ran Tavory    ran@reversim.com        data 2

In the template sheet: A1: Email tempalte (HTML supported) A2: Subject tempalte A3: CC email address

Use ${"var"}for templating. For ecample:

Update regarding ${"title"} ...

Send emails

1. Go to the spreadsheet's Tools -> Script Editor
2. Pasete the contents of mailmerge.gs
3. Run the funcation mailmerge

After sending each row the sent column gets filled. If you resend, only rows that had not been marked as EMAIL_SENT would get sent (to prevent dounble sending in case you stopped and resumed).

mailmerge.gs

var EMAIL_SENT = 'EMAIL_SENT';

/**
 * Sends emails from spreadsheet rows.
 */
function mailmerge() {
  Logger.log('Starting mail merge...');
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheets()[0];
  var startRow = 2; // First row of data to process
  var dataWidth = 4;
  var dataRange = dataSheet.getRange(startRow, 1, dataSheet.getMaxRows() - 1, dataWidth);
  var sent = 0;
  var skipped = 0;

  var templateSheet = ss.getSheets()[1];
  var emailTemplate = templateSheet.getRange('A1').getValue();
  var emailSubjectTemplate = templateSheet.getRange('B1').getValue();
  var cc = templateSheet.getRange('C1').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];
    var emailSent = rowData.sent;
    if (emailSent == EMAIL_SENT) { // Prevents sending duplicates
      skipped++;
    } else {
      // 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 = fillInTemplateFromObject(emailSubjectTemplate, rowData);
      
      emailText = insertNl(emailText);
      Logger.log("Sending email to %s", rowData.emailAddress)
      MailApp.sendEmail({
        to: rowData.emailAddress,
        cc: cc, 
        subject: emailSubject,
        htmlBody: emailText,
      });
      
      dataSheet.getRange(startRow + i, 1).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
      sent++
    }
  }
  Logger.log('Mail merge done! Sent %s emails, (%s skipped)', sent, skipped);
}

// Insert HTML <br> elements for every newline in the text
function insertNl(text) {
  return text.split("\n").join("<br>");
}
  
/**
 * 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;
}

// getRowsData 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.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//       This argument is optional and it defaults to all the cells except those in the first row
//       or all the cells below columnHeadersRowIndex (if defined).
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range; 
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
  var dataRange = range || 
    sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
  var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
  var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(dataRange.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.
// Arguments:
//   - data: JavaScript 2d array
//   - keys: Array of Strings that define the property names for the objects to create
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. 
// Empty Strings are returned for all Strings that could not be successfully normalized.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    keys.push(normalizeHeader(headers[i]));
  }
  return keys;
}

// Normalizes a string, by removing all non 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.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "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.
// Arguments:
//   - cellData: string
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

// Returns true if the character char is alphabetical, false otherwise.
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.
function isDigit(char) {
  return char >= '0' && char <= '9';
}