- Access to the internet and a web browser
- A Google or Gmail account (G Suite accounts may require administrator approval)
- Basic Python skills (2.x or 3.x)
- Google APIs project with the Drive, Sheets, and Docs APIs enabled
Create a new project (or reuse an existing one) from your developer console with the three Google APIs above enabled. (See the videos listed at the bottom if you're new to Google APIs.) Ensure you have the Google APIs Client Library for Python installed; the fastest way of doing this is to use pip install -U google-api-python-client (or with pip3 if you have both Python 2 and 3 on your computer).
This sample app requires you to create a new Google Docs file to serve as the letter template with variable placeholders. Choose the template you wish to use, but we recommend Letter/Spearmint to keep things simple. Replace the contact information in the Doc with template variables that we can merge with desired data. Here are the variables we're using:
{{DATE}}— letter to be dated with this date{{BODY}}— letter content
{{MY_NAME}}— sender's name{{MY_ADDRESS}}— sender's address{{MY_EMAIL}}— sender's email{{MY_PHONE}}— sender's telephone number
{{TO_NAME}}— recipient's name{{TO_TITLE}}— recipient's job title{{TO_COMPANY}}— recipient's organization{{TO_ADDRESS}}— recipient's address
Here's one sample letter template to get an idea of what we mean:
Once you're done, grab your template's file ID — go to the URL in the address bar of your browser and extract the long alphanumeric string representing the Drive file ID: https://docs.google.com/document/d/DRIVE_FILE_ID/edit. Replace YOUR_TMPL_DOC_FILE_ID with this ID as the DOCS_FILE_ID string variable (keep in quotes).
Next, decide on the data source. This sample app currently supports plain text and Google Sheets. By default, the TARGET_TEXT variable is set to 'text' but change to 'sheets' once you have a Google Sheet with the data. The code supports a Sheet structured like this:
Here is one example Sheet you can model yours with. Ensure you then set the SHEETS_FILE_ID variable to its file ID (get it the same way as your Google Doc).
The application script (docs_mail_merge.py) supplies a data structure (merge) with the sender info, date, body of the letter, and placeholders for values that will be replaced by data from the selected data source. The data is fetched and merged into form letters in a loop, displaying links to each of the merged letters. One function is used to fetch the data, supported by private function "shims" for each data source. The other pair of functions: a private function to copy the template, and one more for merging the form data into a copy of the template.
If you run the sample app as written (with all real variables and data documents) and accept the OAuth2 permissions. You'll see one line of output per letter merged. Those letters, named Merged form letter, will also be found in your Google Drive. If you run the app with our data featured here, your merged letter should look like this:
The unit-test script is docs_mail_merge_test.py; see the file for a list of the available tests.
- Google Drive API
- Google Docs API
- Google Sheets API
- G Suite APIs
- Introductory videos
- New Google APIs project setup (6:54)
- Common OAuth2 boilerplate code (Python) (3:48)
- REST APIs intro (Drive API) (6:20)
- Introducing the Docs API (2:57)
Here are some recommendations on how you can enhance this application:
- Adapt this sample for your mail merge use case
- Support exporting merged letters as PDF (HINT: Drive API)
- Support importing data from other data sources, i.e., Cloud SQL, Salesforce, etc.
- Augment the plain text data support to include processing data from CSV files