1

I want to print out tests for a class I teach, but I want each to be identical except for the name of the student at the top. I want this to be drawn from a Google spreadsheet that I've created that has the first names listed in one column and surnames in the next.

How do I do this?

1

I have made a script with document and spreadsheet for you to test...

script is currently being submitted to the gallery, but here's the code:

(or see it as a Gist here)

/**
* 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 (link removed here due to insufficient status)
* 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';
}
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.