This guide explains how to use the Google Docs API to perform a mail merge.
Introduction
A mail merge takes values from rows of a spreadsheet or another data source and inserts them into a template document. This lets you create a single primary document (the template) from which you can generate many similar documents, each customized with the data being merged. The result isn't necessarily used for mail or form letters, but can be for any purpose, such as generating a batch of customer invoices.
Mail merge has been around for as long as there have been spreadsheets and word processors, and is part of many business workflows today. The convention is to organize the data as one record per row, with the columns representing fields in the data, as shown in the following table:
Name | Address | Zone | |
---|---|---|---|
1 | UrbanPq | 123 1st St. | West |
2 | Pawxana | 456 2nd St. | South |
The sample app on this page shows how you can use the Google Docs, Sheets, and Drive APIs to abstract away the details of how mail merges are performed, protecting users from implementation concerns. More information on this Python sample can be found at the sample's GitHub repo.
Sample application
This sample app copies your primary template and then merges variables from your designated data source into each of the copies. To try this sample app, first set up your template:
- Create a Docs file. Choose the template you want to use.
- Note the new file's document ID. For more information, see Document ID.
- Set the
DOCS_FILE_ID
variable to the document ID. - Replace the contact information with template placeholder variables that the app will merge with the selected data.
Here's a sample letter template with placeholders that can be merged with real data from a source such as plain text or Sheets. Here's what that template looks like:
Next, choose either plain text or Sheets as your data source
using the SOURCE
variable. The sample defaults to plain text, meaning the
sample data uses the TEXT_SOURCE_DATA
variable. To source data from
Sheets, update the SOURCE
variable to 'sheets'
and point it
to our sample
sheet
(or your own) by setting the SHEETS_FILE_ID
variable.
Here's what the sheet looks like so you can see the format:
Try the app with our sample data, then adapt it to your data and use case. The command-line application works like this:
- Setup
- Fetch the data from the data source
- Loop through each row of data
- Create a copy of the template
- Merge the copy with the data
- Output link to newly-merged document
All newly merged letters also show up in the user's My Drive. An example of a merged letter looks something like this:
Source code
Python
For more information, see the README
file and the full application source code
at the sample app's GitHub
repo.