Mail Merge for Documents using Google Sheets, Docs, & Scripts

Manually modifying numerous documents can be tedious. Google’s G Suite can help you make this process easier.

I like to think of it as mail merge for documents. Instead of sending out custom emails to a group of recipients, we create a custom document or PDF in the same fashion.

There are many ways to set this up and many ways to expand. In this post, I’ll cover the basics and let you take it from there.

The Hypothetical Situation

Say you have 50 shareholders, each of whom needs an individualized quarterly statement printed and mailed. We can leverage Google Docs, Scripts, and Sheets to accomplish this task efficiently.

Set It Up

Using G Suite, create four new documents (you can see my examples in the links):

• A Google Sheet with the shareholder names and number of shares
• A Google Doc that houses the template of the document to be sent out to shareholders
• An empty Google Doc that will act as our output document and be mailed to each shareholder
• A Google Script that will have the code to manage the data manipulation

Write the Script

A Google Script will control all the data flow. It will pull in data from the spreadsheet to access the shareholder names and number of shares for each shareholder, then lay out the shareholder statement. This layout will be used for all shareholder letters. Custom variables or placeholders will be added where the script should insert the content from the spreadsheet.

Each variable will be defined in the template and referenced in the script. For example, creating a variable like {{insertNameHere}} may be a good choice, because the script will perform a find-and-replace for the variable. I suggest creating a variable that is unlikely to have multiple instances in the content.

The script will then copy the body of the template once for each shareholder and paste it into the empty output document. Before iterating to the next shareholder, the script will replace any custom variables created in the template. It will add a page break so the next letter starts on the top of the next page.

Google’s model of referencing elements in Google Docs along with the limited logging/debugging made this step the most challenging. To make things a little easier, you can put the content into tables. For this example, we created four tables (with no borders) to represent the four sections of the statement. Each table is a child of the document body.

You can also try using their logging command Logger.log() to help output data. It’s not a great solution, but it may help.

One nice addition is to add a call to clear the data currently in the output document. If you do this, you don’t have to select all and delete every time the script runs. Otherwise, the new data will append the existing content.

Pull It All Together

To create a Google Script, go to the menu > tools > script editor in Google Docs, or visit script.google.com. You will want to turn on access to the Sheets & Docs APIs so data can flow via Google Menu bar > Resources > Advanced Google Services.

Check out Google’s example for pulling out data from a Google Sheet via a Google Script. Once you’re used to accessing data, you can reference the code sample provided to see how the content from the template is accessed, copied, and appended to the output document.

Let It Run

To run the script, choose Run from the Google Script menu, then select the generateShareholderPDF function. It will take a few moments, but if you head over to your output document, you will soon see the 50 pages of content appear. Each page will show the shareholder’s name and number of shares printed out, with all the content from the template document ready for printing and distribution.

There are so many ways to extend this concept. Let us know how you use it to solve a problem…