Skip to content
Branch: master
Find file History
wescpy and asrivas add support for >1 row of data (#95)
* add Docs API Mail Merge Python sample

* pylint & eval fixes

* lint fixes

* final(?) lint fixes

* PR fixes and code cleanup

* more cleanup & fixed brokenness from last commit

* unit test fixes

* README missing one more reference to Sheets API

* oops

* added Docs API video to README

* updated app to spuport >1 row of data; README cleanup

* update unit tests to support new functionality; README cleanup too

* README tweaks

* pylint fixes
Latest commit c17d2c9 Mar 17, 2019
Permalink
Type Name Latest commit message Commit time
..
Failed to load latest commit information.
README.md add support for >1 row of data (#95) Mar 17, 2019
docs_mail_merge.py add support for >1 row of data (#95) Mar 17, 2019
docs_mail_merge_test.py add support for >1 row of data (#95) Mar 17, 2019

README.md

Mail Merge Sample (Python) for Google Docs (REST) API

Prerequisites

  • 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

Description

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:

General

  • {{DATE}} β€” letter to be dated with this date
  • {{BODY}} β€” letter content

Sender

  • {{MY_NAME}} β€” sender's name
  • {{MY_ADDRESS}} β€” sender's address
  • {{MY_EMAIL}} β€” sender's email
  • {{MY_PHONE}} β€” sender's telephone number

Recipient

  • {{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:

sample letter template

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

Data source

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:

sample Sheets data source

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

Application code

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:

sample merged letter

Testing

The unit-test script is docs_mail_merge_test.py; see the file for a list of the available tests.

Reference

Enhancements

Here are some recommendations on how you can enhance this application:

  1. Adapt this sample for your mail merge use case
  2. Support exporting merged letters as PDF (HINT: Drive API)
  3. Support importing data from other data sources, i.e., Cloud SQL, Salesforce, etc.
  4. Augment the plain text data support to include processing data from CSV files
You can’t perform that action at this time.