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. |