Create serial letters with Google Docs/Suite

Be it an invitation to a birthday party, a circular letter to the club members or any other marketing activity. Serial letters enable an automated and yet personalized approach to the recipients.

In this article I would like to show how Google Sheets and Google Docs can be used to send even elaborately designed documents to a variety of recipients.

Objective of the project

This article builds on an older article of mine in which I already created documents automatically. At that time I queried the placeholders individually. But that was quite impractical, so I want to get all placeholders from a Google spreadsheet in this project.

First of all the goals I set myself for this hack.

• Creating a mail merge template with placeholders (format {{{placeholders}})
• Google Sheet worksheet with the recipients and the placeholders to replace
• Automatic creation and storage of the generated letters as PDF in a folder

Creating the letter template

I just started writing my letter in Google Docs, as I did in the previous post.

But then I got an exciting idea!

Google Slides offers a lot more formatting options and thus offers a clever alternative to creating documents. There you can work with shapes and illustrations, and it is also possible to change the placeholders.

I will simply support both as a document:

Create a spreadsheet for the placeholders

Finally we create our spreadsheets in which the individual recipients are located and the values for the placeholders.

Two of the fields will have a meaning in my logic:

• template: Document-Id of the template (so we are flexible)
• filename: Filename to be created (if empty, no file will be created)

App-Script Magic

For all non-coders among you, this section is less interesting, except for the actual script. (See also GitLab Project)

BATCH_SIZE=50;

/*
On startup new menu items are added to sheets
*/
function onOpen() {
  renderMenu();
}

/*
Refreshes the menu
*/
function renderMenu(){
  var ui = SpreadsheetApp.getUi();
  var progress=PropertiesService.getDocumentProperties().getProperty('progress');
  if(progress>0){
     ui.createMenu('Serial letter')
     .addItem('Start over', 'startOver')
     .addItem('Continue at '+parseInt(progress), 'createDocuments')
     .addToUi();
  }else{
    ui.createMenu('Serial letter')
    .addItem('Create', 'startOver')
    .addToUi();
  }
}

/*
Starts a new process
*/
function startOver(){
  PropertiesService.getDocumentProperties().setProperty('progress', 0);
  createDocuments();
}

/*
Convert the current data range into an object array and use the first line as object properties
*/
function spreadSheetToObjects(){
  //Alle gefüllten Spalten auslesen
  var range=SpreadsheetApp.getActiveSheet().getDataRange().getDisplayValues();
  //Array für alle Einträge
  var data=[];
  for(var row=1; row<range.length; row++){ //Durchläuft alle Zeilen bis auf die Überschriften (daher row=1)
    var dataObject={};
    for (var col=0; col<range[0].length; col++){ 
      var key=range[0][col]; 
      var value=range[row][col]; 
      dataObject[key]=value; 
    } 
    data.push(dataObject); 
  }
  return data; 
}

/*
Target folder for the created documents.
Is created as "gen" on the same level as the spreadsheet
*/
function getTargetFolder(){
  var folderName="gen"; //Name des Zielordners
  var folder=DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next();
  var genFolders=folder.getFoldersByName(folderName);
  if(genFolders.hasNext()){
    return genFolders.next();    
  }else{
    return folder.createFolder(folderName);
  }
}

function createDocument(dataObject, targetFolder){
  //Load Template File
  var templateFile=DriveApp.getFileById(dataObject.template);
  //Then create a copy in the "gen" folder
  var document=templateFile.makeCopy(targetFolder);
  
  //See if it is a presentation:
  if(document.getMimeType()=="application/vnd.google-apps.presentation"){
    //Then create the document from the presentation
    var slideApp=SlidesApp.openById(document.getId());
    //Scroll through all slides
    slideApp.getSlides().forEach(slide=>{
      //Replace all placeholders                          
      for(var key in dataObject){
        slide.replaceAllText('{{'+key+'}}',dataObject[key]);
      }                      
    });
    slideApp.saveAndClose();
  }else if(document.getMimeType()=="application/vnd.google-apps.document"){
    //Document is a text document -> Open and swap placeholders
    var docApp=DocumentApp.openById(document.getId());
    for(var key in dataObject){
      docApp.getBody().replaceText('{{'+key+'}}', dataObject[key]);
    }     
    docApp.saveAndClose();
  }else{
    //unknown type - Abort here
    return;
  }
  //Create pdf export
  var pdfFile=getTargetFolder().createFile(
    DriveApp.getFileById(document.getId()).getAs(MimeType.PDF),
  ).setName(dataObject.filename);
  //Originaldokument löschen
  document.setTrashed(true);
}

/*
Main function that reads all data from the spreadsheet and creates the documents
*/
function createDocuments(){
  //Convert the rows into objects
  var data=spreadSheetToObjects();
  var targetFolder=getTargetFolder();
  var start=parseInt(PropertiesService.getDocumentProperties().getProperty('progress'));
  //Run through all objects and create the documents
  for(var i=start; i<data.length && i< (start+BATCH_SIZE); i++){
    createDocument(data[i], targetFolder);    
  }
  if(i<data.length){
    SpreadsheetApp.getUi().alert("There are too many entries for one run. Use the \"Continue\" function in the menu to create the next documents");
    PropertiesService.getDocumentProperties().setProperty('progress', i);
    renderMenu();
  }
}

Typical error messages:

Exception: Es konnte kein Element mit der angegebenen ID gefunden werden. Möglicherweise haben Sie das Element nicht bearbeitet oder Sie haben nicht die erforderliche Berechtigung, um darauf zuzugreifen.	The correct document ID was not used. The ID must be from the text document or presentation. Not from the script editor.
Exception: SpreadsheetApp.getUi() kann aus diesem Kontext nicht abgerufen werden. at onOpen(Code:5:27)	The wrong code editor was used. The script must be entered in the code editor of Google Sheets.