Skip to content

Commit e864f3b

Browse files
wescpyasrivas
wescpy
authored and
asrivas
committed
add Docs API Mail Merge Python sample (googleworkspace#91)
* 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
1 parent 62e3801 commit e864f3b

File tree

3 files changed

+303
-0
lines changed

3 files changed

+303
-0
lines changed

‎docs/mail-merge/README.md‎

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,72 @@
1+
# Mail Merge Sample (Python) for Google Docs (REST) API
2+
3+
## Prerequisites
4+
5+
- Access to the internet and a web browser
6+
- A Google account (G Suite accounts may require administrator approval)
7+
- Basic Python skills (2.x or 3.x)
8+
- Google APIs project with the [Drive](https://developers.google.com/drive/), [Sheets](https://developers.google.com/sheets/), and [Docs](https://developers.google.com/docs/) APIs enabled
9+
10+
## Description
11+
12+
Before getting started, create a new project (or reuse an existing one) at <https://console.developers.google.com> with Google Drive, Sheets, and Docs APIs enabled. (See the videos listed below if you're new to Google APIs.) Then install the Google APIs Client Library for Python if you haven't already: `pip{,3} install -U google-api-python-client`
13+
14+
This sample app requires you to [create a new Google Docs file](https://docs.google.com). Choose the template you wish to use, but we suggest using Letter/Spearmint to keep things simple. Replace the contact information in the Doc with template variables that we can merge with desired data.
15+
16+
Here is a [sample letter template](https://drive.google.com/open?id=1Rr7eKm3tvUgRKRoOYVSMF69IVAHIOPS72-l0CzsPmfM) to get an idea of what we mean: ![sample letter template](https://user-images.githubusercontent.com/1102504/44741564-51ea2480-aab2-11e8-871c-a836626b2a0d.png "sample letter template")
17+
18+
In the document, the variable names used by the sample app are:
19+
20+
### General
21+
22+
* `{{DATE}}` — letter to be dated with this date
23+
* `{{BODY}}` — letter content
24+
25+
### Sender
26+
27+
* `{{MY_NAME}}` — sender's name
28+
* `{{MY_ADDRESS}}` — sender's address
29+
* `{{MY_EMAIL}}` — sender's email
30+
* `{{MY_PHONE}}` — sender's telephone number
31+
32+
### Recipient
33+
34+
* `{{TO_NAME}}` — recipient's name
35+
* `{{TO_TITLE}}` — recipient's job title
36+
* `{{TO_COMPANY}}` — recipient's organization
37+
* `{{TO_ADDRESS}}` — recipient's address
38+
39+
After you've templatized the Google Doc, be sure to grab its file ID — in your browser, look at the address bar, and extract the long alphanumeric string that makes up the Drive file ID from the URL: `https://docs.google.com/document/d/<DRIVE_FILE_ID>/edit`.
40+
41+
Replace `YOUR_TMPL_DOC_FILE_ID` in the `docs_mail_merge.py` source file with this file ID as the string value (in quotes) for the `DOCS_FILE_ID` variable. Run the sample app, accept the OAuth2 permissions requested, and when the script has completed, you should have a new mail-merged Google Doc named `Merged form letter` in your Google Drive folder!
42+
43+
## Data source
44+
45+
The application currently supports two different sources of data, plain text and Google Sheets. By default, the sample uses plain text via the `TARGET_TEXT` variable. A better option is to use a Google Sheet. Enable the API for your project in the developers console, and change the `source` variable at the bottom to `'sheets'`. Be sure you create a Sheet structured like the one below. Here is one [example Sheet](https://docs.google.com/spreadsheets/d/1vaoqPYGL1cJvkogV36nu3AKQ5rUacXj9TV-zqTvXuMU/edit) you can model yours with. Ensure you then set the `SHEETS_FILE_ID` variable to the file ID of your Google Sheet.
46+
47+
![sample Sheets data source](https://user-images.githubusercontent.com/1102504/54064578-62e6c180-41ca-11e9-86f6-9d147ac17200.png "sample Sheets data source")
48+
49+
## Testing
50+
51+
The unit-test script is `docs_mail_merge_test.py`; see the file for a list of the available tests.
52+
53+
## Reference
54+
55+
- Google Drive API
56+
- [API documentation](https://developers.google.com/drive)
57+
- [Support channels](https://developers.google.com/drive/api/v3/support)
58+
- Google Docs API
59+
- [API documentation](https://developers.google.com/docs)
60+
- [Python quickstart](https://developers.google.com/docs/api/quickstart/python)
61+
- [Support channels](https://developers.google.com/docs/api/support)
62+
- Google Sheets API
63+
- [API documentation](https://developers.google.com/sheets)
64+
- [Support channels](https://developers.google.com/sheets/api/support)
65+
- [Google APIs client libraries](https://developers.google.com/api-client-library)
66+
- [G Suite developer overview &amp; video](https://developers.google.com/gsuite)
67+
- [G Suite (REST) APIs intro codelab](https://g.co/codelabs/gsuite-apis-intro) (~half-hour)
68+
- Introductory API videos
69+
- [New Google APIs project setup](https://goo.gl/RbyTFD) (6:54)
70+
- [Common Python boilerplate code review](https://goo.gl/KMfbeK) (3:48)
71+
- [REST APIs intro (Drive API)](https://goo.gl/ZIgf8k) (6:20)
72+
- [Introducing the Docs API](https://youtu.be/jeU-tWKeb6g) (2:57)

‎docs/mail-merge/docs_mail_merge.py‎

Lines changed: 141 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,141 @@
1+
# -*- coding: utf-8 -*-
2+
#
3+
# Copyright ©2018-2019 Google LLC
4+
#
5+
# Licensed under the Apache License, Version 2.0 (the "License");
6+
# you may not use this file except in compliance with the License.
7+
# You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
15+
"""
16+
docs-mail-merge.py (2.x or 3.x)
17+
18+
Google Docs (REST) API mail-merge sample app
19+
"""
20+
from __future__ import print_function
21+
import time
22+
23+
from googleapiclient import discovery
24+
from httplib2 import Http
25+
from oauth2client import file, client, tools
26+
27+
# Fill-in IDs of your Docs template & any Sheets data source
28+
DOCS_FILE_ID = 'YOUR_TMPL_DOC_FILE_ID'
29+
SHEETS_FILE_ID = 'YOUR_SHEET_DATA_FILE_ID'
30+
31+
# authorization constants
32+
CLIENT_ID_FILE = 'credentials.json'
33+
TOKEN_STORE_FILE = 'token.json'
34+
SCOPES = ( # iterable or space-delimited string
35+
'https://www.googleapis.com/auth/drive',
36+
'https://www.googleapis.com/auth/documents',
37+
'https://www.googleapis.com/auth/spreadsheets.readonly',
38+
)
39+
40+
# application constants
41+
SOURCES = ('text', 'sheets')
42+
SOURCE = 'text' # Choose one of the data SOURCES
43+
COLUMNS = ['to_name', 'to_title', 'to_company', 'to_address']
44+
TEXT_SOURCE_DATA = [
45+
'Ms. Lara Brown', 'Googler', 'Google NYC',
46+
'111 8th Ave\nNew York, NY 10011-5201'
47+
]
48+
49+
def get_http_client():
50+
"""Uses project credentials in CLIENT_ID_FILE along with requested OAuth2
51+
scopes for authorization, and caches API tokens in TOKEN_STORE_FILE.
52+
"""
53+
store = file.Storage(TOKEN_STORE_FILE)
54+
creds = store.get()
55+
if not creds or creds.invalid:
56+
flow = client.flow_from_clientsecrets(CLIENT_ID_FILE, SCOPES)
57+
creds = tools.run_flow(flow, store)
58+
return creds.authorize(Http())
59+
60+
# service endpoints to Google APIs
61+
HTTP = get_http_client()
62+
DRIVE = discovery.build('drive', 'v3', http=HTTP)
63+
DOCS = discovery.build('docs', 'v1', http=HTTP)
64+
SHEETS = discovery.build('sheets', 'v4', http=HTTP)
65+
66+
# fill-in your data to merge into document template variables
67+
merge = {
68+
'my_name': 'Mr. Jeff Erson',
69+
'my_address': '76 9th Ave\nNew York, NY 10011-4962',
70+
'my_email': 'http://google.com',
71+
'my_phone': '+1-212-565-0000',
72+
# - - - - - - - - - - - - - - - - - - - - - - - - - -
73+
'date': time.ctime(),
74+
# - - - - - - - - - - - - - - - - - - - - - - - - - -
75+
'body': 'Google, headquartered in Mountain View, unveiled the new Android '
76+
'phone at the Consumer Electronics Show. CEO Sundar Pichai said '
77+
'in his keynote that users love their new Android phones.'
78+
}
79+
80+
def get_data(source):
81+
"""Gets mail merge data from chosen data source.
82+
"""
83+
if source not in {'sheets', 'text'}:
84+
raise ValueError('ERROR: unsupported source %r; choose from %r' % (
85+
source, SOURCES))
86+
func = SAFE_DISPATCH[source]
87+
return dict(zip(COLUMNS, func()))
88+
89+
def _get_text_data():
90+
"""(private) Returns plain text data; can alter to read from CSV file.
91+
"""
92+
return TEXT_SOURCE_DATA
93+
94+
def _get_sheets_data(service=SHEETS):
95+
"""(private) Returns data from Google Sheets source. NOTE: this sample
96+
code gets all cells in 'Sheet1', the first default Sheet in a
97+
spreadsheet. Use any desired data range (in standard A1 notation).
98+
This sample app is coded to return only the 2nd row (the data).
99+
"""
100+
return service.spreadsheets().values().get(spreadsheetId=SHEETS_FILE_ID,
101+
range='Sheet1!2:2').execute().get('values')[0] # 2nd row only
102+
103+
# data source dispatch table [better alternative to using eval()]
104+
SAFE_DISPATCH = {k: globals().get('_get_%s_data' % k) for k in SOURCES}
105+
106+
def _copy_template(tmpl_id, source, service):
107+
"""(private) Copies letter template document using Drive API then
108+
returns file ID of (new) copy.
109+
"""
110+
body = {'name': 'Merged form letter (%s)' % source}
111+
return service.files().copy(body=body, fileId=tmpl_id,
112+
fields='id').execute().get('id')
113+
114+
def merge_template(tmpl_id, source, service):
115+
"""Copies template document and merges data into newly-minted copy then
116+
returns its file ID.
117+
"""
118+
# copy template and set context data struct for merging template values
119+
copy_id = _copy_template(tmpl_id, source, service)
120+
context = merge.iteritems() if hasattr({}, 'iteritems') else merge.items()
121+
122+
# "search & replace" API requests for mail merge substitutions
123+
reqs = [{'replaceAllText': {
124+
'containsText': {
125+
'text': '{{%s}}' % key.upper(), # {{VARS}} are uppercase
126+
'matchCase': True,
127+
},
128+
'replaceText': value,
129+
}} for key, value in context]
130+
131+
# send requests to Docs API to do actual merge
132+
DOCS.documents().batchUpdate(body={'requests': reqs},
133+
documentId=copy_id, fields='').execute()
134+
return copy_id
135+
136+
137+
if __name__ == '__main__':
138+
if SOURCE in SOURCES:
139+
merge.update(get_data(SOURCE))
140+
fid = merge_template(DOCS_FILE_ID, SOURCE, DRIVE)
141+
print('Merged letter: docs.google.com/document/d/%s/edit' % fid)

‎docs/mail-merge/docs_mail_merge_test.py‎

Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,90 @@
1+
# Copyright 2018-2019 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
6+
#
7+
# Unless required by applicable law or agreed to in writing, software
8+
# distributed under the License is distributed on an "AS IS" BASIS,
9+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
10+
# See the License for the specific language governing permissions and
11+
# limitations under the License.
12+
13+
"""
14+
docs_mail_merge_test.py -- unit test for docs_mail_merge.py:
15+
1. test credentials file availability
16+
2. test whether project can connect to all 3 APIs
17+
3. test creation (and deletion) of Google Docs file
18+
4. test copying (and deletion) of Google Docs file
19+
5. test getting plain text data
20+
6. test getting data from Google Sheets spreadsheet
21+
"""
22+
23+
import os
24+
import unittest
25+
26+
from googleapiclient import discovery
27+
from docs_mail_merge6 import (CLIENT_ID_FILE, get_data, get_http_client,
28+
_copy_template)
29+
30+
class TestDocsMailMerge(unittest.TestCase):
31+
'Unit tests for Mail Merge sample'
32+
def test_project(self):
33+
self.assertTrue(project_test())
34+
def test_gapis(self):
35+
self.assertTrue(gapis_test())
36+
def test_create_doc(self):
37+
self.assertTrue(create_doc_test())
38+
def test_copy_doc(self):
39+
self.assertTrue(copy_doc_test())
40+
def test_get_text_data(self):
41+
self.assertTrue(bool(get_text_data_test()))
42+
def test_get_sheets_data(self):
43+
self.assertTrue(bool(get_sheets_data_test()))
44+
45+
def project_test():
46+
'Tests whether project credentials file was downloaded from project.'
47+
if os.path.exists(CLIENT_ID_FILE):
48+
return True
49+
raise IOError('''\
50+
ERROR: Must create a Google APIs project, enable both
51+
the Drive and Docs REST APIs, create and download OAuth2
52+
client credentials as %r before unit test can run.''' % CLIENT_ID_FILE)
53+
54+
def gapis_test():
55+
'Tests whether project can connect to all 3 APIs used in the sample.'
56+
HTTP = get_http_client()
57+
discovery.build('drive', 'v3', http=HTTP)
58+
discovery.build('docs', 'v1', http=HTTP)
59+
discovery.build('sheets', 'v4', http=HTTP)
60+
return True
61+
62+
def create_doc_test():
63+
'Tests whether project can create and delete a Google Docs file.'
64+
DRIVE = discovery.build('drive', 'v3', http=get_http_client())
65+
DATA = {
66+
'name': 'Test Doc',
67+
'mimeType': 'application/vnd.google-apps.document',
68+
}
69+
doc_id = DRIVE.files().create(body=DATA, fields='id').execute().get('id')
70+
DRIVE.files().delete(fileId=doc_id, fields='').execute()
71+
return True
72+
73+
def copy_doc_test():
74+
'Tests whether project can copy and delete a Google Docs file.'
75+
DRIVE = discovery.build('drive', 'v3', http=get_http_client())
76+
DOCS_FILE_ID = '1Rr7eKm3tvUgRKRoOYVSMF69IVAHIOPS72-l0CzsPmfM'
77+
doc_id = _copy_template(DOCS_FILE_ID, 'text')
78+
DRIVE.files().delete(fileId=doc_id, fields='').execute()
79+
return True
80+
81+
def get_text_data_test():
82+
'Tests reading plain text data.'
83+
return get_data()
84+
85+
def get_sheets_data_test():
86+
'Tests reading Google Sheets data.'
87+
return get_data(source='sheets')
88+
89+
if __name__ == '__main__':
90+
unittest.main()

0 commit comments

Comments
 (0)