Google Drive - Bulk letter generation

After having found no solution on the web to generate bulk letters, I decided to write a little script (MS-Word style but using Google Drive). Also a good start for me to get in touch with Google Apps Scripts, the requirements are simple:

• Google spreadsheet with address data
• Google document, used as template containing placeholder for replacement:
• ###LABEL###  (address label)
• ###SALUTATION### (personalized start of the letter, e.g "Dear Mr. / Mrs.")
• Result: Google document containing all letters based on the replacements from above (easy printing)

 Note: Error handling and i18n were ignored for this first prototype.

1. Create standalone app in Google Drive
New -> Script (if script is not available you haveto connect the api to your drive by clicking more...)

2. Insert webapp code.
The doGet() method is the entry point, if the script is loaded. It just opens a file picker dialog where the base template document can be selected. Afterwards again the picker is called but to select the spreadsheet containing the series data. At the end the generation is triggered and a link is provided.

Code.gs

/**
 * Init
 */
function doGet(){
  var app = UiApp.createApplication();
  var templateId = app.createHidden("templateId").setId("templateId");
  app.add(templateId);
  
  showPicker(app, UiApp.FileType.DOCUMENTS);  
  return app;
}

/**
 * Handler for selected document
 */
function listSelectedDoc_(e){
  
  var app = UiApp.getActiveApplication(); 
  app.getElementById('templateId').setValue(e.parameter.items[0].id);
  showPicker(app, UiApp.FileType.SPREADSHEETS);
  
  return app;
}

/**
 * Handler for selting spreadsheet
 */
function listSelectedSpreadsheet_(e) {
  
  var app = UiApp.getActiveApplication();     
  var spreadsheetId = e.parameter.items[0].id; 
  var templateId = e.parameter.templateId;
  var folderId = DocsList.getFileById(templateId).getParents()[0].getId();
  
  var bl = new BulkLetterGenerator(folderId, spreadsheetId, templateId);
  var generated = bl.generate();
  if(generated) {        
    var anchor = app.createAnchor("Open bulk letter", generated);    
    app.add(anchor);    
  }
  
  return app.close();
}

/**
 * Show a picker: folder, docs or tables
 */
function showPicker(app, type) {

  switch(type) {
     
    case UiApp.FileType.DOCUMENTS:
      var title = "Select a template document";
      var handler = app.createServerHandler('listSelectedDoc_');
      break;      
    case UiApp.FileType.SPREADSHEETS:
      var title = "Select a spreadsheet with addresses";
      var handler = app.createServerHandler('listSelectedSpreadsheet_');      
      handler.addCallbackElement(app.getElementById("templateId"));
      break;    
  }
  
  var dialog = app.createDocsListDialog();
  dialog.addSelectionHandler(handler);  
  dialog.setDialogTitle(title);
  dialog.setInitialView(type);
  dialog.setMultiSelectEnabled(false);
  dialog.showDocsPicker();
}

Notes: Because it is not possible to use the handler results in callback functions (asynchronous sever call), I used a hidden element an pass it to addCallbackElement. Maybe anyone more experienced can provide a better solution... it just was my first script (try)...

3. BulkLetterGenerator Class
As the name implies, it provides the needed method to generate the bulk letter (in the same folder as the template lives in). It has only only one public method: generate() which returns the URL to the generated document. Inside some reflection is used to copy data from template document.

Notes: For other languages than English, the block below the comment "prepare spreadsheet members & replace" should be adopted. Also German address labels standard is used in this case.

BulkLetterGenerator.gs

function BulkLetterGenerator(targetId, spreadsheetId, templateId) {
  
  var that = this;
  this.targetId = targetId;
  this.spreadsheetId = spreadsheetId;
  this.templateId = templateId;
  
 /**
  * Generating bulk letter
  */
  this.generate = function () {        
    
    var membersTable = SpreadsheetApp.openById(that.spreadsheetId);
    var members = membersTable.getDataRange().getValues();
    
    var template = DocumentApp.openById(that.templateId);
    var templateBody = template.getActiveSection();
    
    // create new document
    var bulk = getBulkDocument(template); 
    
    // interate over recipients (starting from 1, zero is headline usually)
    for (var i = 1; i < members.length; i++) {
      
      // copy elements
      for (var j = 0; j < template.getNumChildren(); j++) {
        bulk = appendElementToDoc(bulk, template.getChild(j).copy());
      }
      
      // prepare spreadsheet members & replace
      var label = members[i][0] +"\n"+ members[i][1] +" "+ members[i][2] +"\n"+ members[i][3] + "\n" + members[i][4];    

      var salutation += " " + members[i][0] +" "+ members[i][2];    
      bulk = bulk.replaceText('###LABEL###', label).replaceText('###SALUTATION###', salutation);  
      bulk.appendPageBreak();
    }  
    
    bulk.saveAndClose();
    return bulk.getUrl();
  }  
  
 /**
  * Copy elements to new doc
  */
  function appendElementToDoc(bulk, element) {
    
    var tName = underscoreToCamelCase(element.getType() + "");  
    try {
      bulk["append" + tName](element);
    }
    catch(err) {    
      Logger.log(err + "");
    }   
    return bulk;
  }
  
 /**
  * Transform typename to function name
  */
  function underscoreToCamelCase(type) {
    
    type = type.toLowerCase();
    var tName = type.charAt(0).toUpperCase() + type.slice(1);
    
    var parts = tName.split("_");
    if(parts.length == 2) {
      tName = parts[0] + parts[1].charAt(0).toUpperCase() + parts[1].slice(1);
    }
    
    return tName;
  }
  
  /**
  * Getting bulk document to insert content
  *
  * @param Document
  * @return Document
  */
  function getBulkDocument(template) {
    
    var bulk = DocumentApp.create("Bulk Letter");        
    bulk.setMarginBottom(template.getMarginBottom());
    bulk.setMarginLeft(template.getMarginLeft());
    bulk.setMarginRight(template.getMarginRight());
    bulk.setMarginTop(template.getMarginTop());  
    bulk.setPageHeight(template.getPageHeight());
    bulk.setPageWidth(template.getPageWidth());
    
    // Header & footer
    if(!bulk.getHeader()) {
      var header = bulk.addHeader(); 
    } else {
      var header = bulk.getHeader();
    }  
    for (var i = 0; i < template.getHeader().getNumChildren(); i++) {
      appendElementToDoc(header, template.getHeader().getChild(i).copy());
    }
    
    if(!bulk.getFooter()) {
      var footer = bulk.addFooter();
    } else {
      var footer = bulk.getFooter();
    }  
    for (var i = 0; i < template.getFooter().getNumChildren(); i++) {
      appendElementToDoc(footer, template.getFooter().getChild(i).copy());
    } 
    
    // Editors & viewer
    var editors = template.getEditors();
    for (var i = 0; i < editors.length; i++) {
      bulk.addEditor(editors[i])
    }
    
    var viewers = template.getViewers();
    for (var i = 0; i < editors.length; i++) {
      bulk.addViewer(viewers[i])
    }
    
    //move to folder  
    DocsList.getFileById(bulk.getId()).removeFromFolder(DocsList.getRootFolder());
    DocsList.getFileById(bulk.getId()).addToFolder(DocsList.getFolderById(that.targetId));
    
    return bulk;
  }
}

5. Create template document
Just write a letter containing the string ###LABEL### for the address block and ###SALUTATION### for personalized beginning.

6. Create address spreadsheet
The spreadsheet's first line is ignores (headlines are assumed). The columns have to be in the correct order:

• Salutation
• Surname
• Name
• Street
• Zip / City

7. Run bulk letter generation

Open the script again and set a version (File -> Manage Versions) and start the version as webapp. After setting some permissions, an URL is provided to call the script directly... happy generating...