Mail Merge with a Google Document Template

Let’s say Jane, an HR expert for Acme Inc. has a weekly task to email employees who have earned the Employee of the Week award.

To accomplish this, Jane created a nice Google Docs document that has drawings and other cool popping features. She treats this document as a template where she copies it for each deserving employee. Next, she takes the customized document and downloads it in PDF format. Then, she composes an email with the congratulations text and attaches the PDF. Since she no longer needs the customized Employee of the Week award version, she deletes it.

Oh, how Jane wishes there was a better way. Well, there is with Google Apps Script!

Bob from IT creates a simple spreadsheet form that prompts for the employee’s email and the customized fields in her Google document template. He creates an Apps Script within the spreadsheet (see Tools > Script Editor) and adds OnSubmit trigger so when the forms submit button is clicked, it runs the script. Finally, he updates the template document with key place holders. For example, the employees name portion is replaced with keyFullName.

As the code sample below demonstrates, the Apps Script gets the document template, copies it in a new temporary document, opens the temp document, converts it to PDF format, composes the email, sends the email with the attached PDF and deletes the temp document. Whew! Jane gives Bob an Employee of the YEAR award!

Here is a code snippet example to get you started.

// Global variables
docTemplate = “enter document ID here”;
docName = “enter document name here”;

function sendDocument() {
// Full name and email address values come from the spreadsheet form
var full_name = from-spreadsheet-form
var email_address = from-spreadsheet-form
// Get document template, copy it as a new temp doc, and save the Doc’s id
var copyId   = DocsList.getFileById(docTemplate)
                .makeCopy(docName+’ for ‘+full_name)
                .getId();
// Open the temporary document
var copyDoc = DocumentApp.openById(copyId);
// Get the document’s body section
var copyBody = copyDoc.getActiveSection();
// Replace place holder keys/tags,
copyBody.replaceText(‘keyFullName’, full_name);
var todaysDate = Utilities.formatDate(new Date(), “GMT”, “MM/dd/yyyy”);
copyBody.replaceText(‘keyTodaysDate’, todaysDate);
// Save and close the temporary document
copyDoc.saveAndClose();
// Convert temporary document to PDF by using the getAs blob conversion
var pdf = DocsList.getFileById(copyId).getAs(“application/pdf”);
// Attach PDF and send the email
MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});
// Delete temp file
DocsList.getFileById(copyId).setTrashed(true);
}