Google Apps Script to use mail merge the old fashioned way - to output merged Documents!

gistfile1.js

    /**
    * This script will output a mailmerge of documents.
    * All document variables are of the form <<var_name>> (spaces are ok)
    * Requires a spreadsheet with two sheets
    * The first is the data table, the second has the template url in A1
    * and the merged document's title in A2 (optional and can use data variables)
    * 
    * With thanks to:
    * drzaus: http://webapps.stackexchange.com/a/47255
    * Google Apps Script Tutorial: Simple Mail Merge
    **/


    var variable_re = new RegExp('<<[^>]+>>', 'g');

    /** run this to test
      * uses Document with id: 1SgBK3mpK4WcPH2zeHjo31pXCKo3gzF_dr1updQNiHx4
      * and sheet with id: 0Au17ekRhm0HjdHZ6QkJFTlNXSFFhZUpJOVJKWU1kbmc
    **/
    function testMergeToDocument(){
       test_sheet = SpreadsheetApp.openById("0Au17ekRhm0HjdHZ6QkJFTlNXSFFhZUpJOVJKWU1kbmc")
      SpreadsheetApp.setActiveSpreadsheet(test_sheet)
      mergeToDocument();
    }

    /**
     * This is the main entry point for the script
     */
    function mergeToDocument() {
      var mergedDoc, bodyContent, templateText, newTitle;
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      // Get the data
      var dataSheet = ss.getSheets()[0];
      var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 4);
      
      // Get the template information
      var templateSheet = ss.getSheets()[1];
      var templateString = templateSheet.getRange("A1").getValue();
      var titleString = templateSheet.getRange("A2").getValue();
      
      //Identify if template is a string or a document
      if(isUsingTemplateFile(templateString)) {
        var doc = DocumentApp.openByUrl(templateString)
        templateText = doc.getBody();
      } else {
        templateText = templateString
      }
      
      // 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 document.
        
        // Generate the title
        if(isUsingVarInTitle(titleString)) {
          newTitle = fillInTemplateStringFromObject(titleString, rowData);
        } else {
          newTitle = doc.getName() + '_' + i
        }
        // Given a template string, replace markers (for instance ${"First Name"}) with
        // the corresponding value in a row object (for instance rowData.firstName).
       if(isUsingTemplateFile(templateString)) {
         mergedDoc = copyDocument(doc, newTitle)
         bodyContent = mergedDoc.getBody();
       } else {
         mergedDoc = DocumentApp.create(newTitle);
         bodyContent = mergedDoc.getBody();
         bodyContent.setText(templateText);
       }
        output_doc = fillInTemplateDocFromObject(bodyContent, rowData);
        Logger.log(newTitle)
        mergedDoc.saveAndClose()
      }
    }
    /**
     * This will return a copy of the given document with the given name
     * 
     * @param  {Document} originalDoc The document to copy 
     * @param  {string} newName     The name of the document
     * @return {Document}             The new document
     */
    function copyDocument(originalDoc, newName) {
      // file has to be at least readable by the person running the script
      var fileId = originalDoc.getId()
      // need to open as a File to make a copy
      var newFileId = DocsList.getFileById(fileId).makeCopy(newName).getId()
      // reopen as a document and return
      return DocumentApp.openById(newFileId)
    }
    /**
     * Checks if the template string matches a document URL
     * @param  {string}  templateString 
     * @return {Boolean}                True if is a document URL
     */
    function isUsingTemplateFile(templateString){
      var re = new RegExp('/document/d/.+');
      if (re.test(templateString)){
        return true;
      } else {
        return false;
      }
    }
    /**
     * Checks if the title contains a template variable
     * @param  {string}  titleString 
     * @return {Boolean}             True if it contains a template variable
     */
    function isUsingVarInTitle(titleString){
      if(variable_re.test(titleString)){
        return true;
      } else {
        return false;
      }
    }

    //////////////////////////////////////////////////////////////////////////////////////////
    //
    // The two functions below is adapted from:
    // https://developers.google.com/apps-script/articles/mail_merge
    //
    //////////////////////////////////////////////////////////////////////////////////////////

    /**
     * Replaces markers in a template document with values defined
     * in a JavaScript data object. The replacement is done in place,
     * so nothing is returned.
     * @param  {Document} template  document containing markers, eg <<Column name>>
     * @param  {JavaScript object} data     data.columnName will replace marker <<Column name>>
     */
    function fillInTemplateDocFromObject(template, data) {
      var templateVars, matchable, output;
      output = template;
      // Search for all the variables to be replaced, for instance <<Column name>>
      
      matchable = template.getText();
      templateVars = matchable.match(variable_re);
      
      // 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])];
        output.replaceText(templateVars[i], variableData || "");
      }
    }

    /**
     * Replaces markers in a template string with values defined
     * in a JavaScript data object.
     * @param  {string} template  string containing markers, eg <<Column name>>
     * @param  {JavaScript object} data     data.columnName will replace marker <<Column name>>
     *
     * @return {string} The merged string
     */

    function fillInTemplateStringFromObject(template, data) {
      var templateVars, matchable, output;
      output = template;
      // Search for all the variables to be replaced, for instance ${"Column name"}
      matchable = template;
      
      templateVars = matchable.match(variable_re);
      
      // 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])];
        output = output.replace(templateVars[i], variableData || "");
      }
      
      return output;
    }
      
      

    //////////////////////////////////////////////////////////////////////////////////////////
    //
    // The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects'
    // tutorial.
    //
    //////////////////////////////////////////////////////////////////////////////////////////

    // 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
    //   - 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) {
      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.
    // 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.
    // Arguments:
    //   - headers: Array of Strings to normalize
    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.
    // 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';
    }