googleworkspace · asrivas · Mar 15, 2019 · Mar 13, 2019 · Mar 13, 2019 · Mar 13, 2019
diff --git a/docs/mail-merge/README.md b/docs/mail-merge/README.md
@@ -0,0 +1,72 @@
+# Mail Merge Sample (Python) for Google Docs (REST) API
+
+## Prerequisites
+
+- Access to the internet and a web browser
+- A Google account (G Suite accounts may require administrator approval)
+- Basic Python skills (2.x or 3.x)
+- 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
+
+## Description
+
+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`
+
+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.
+
+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")
+
+In the document, the variable names used by the sample app are:
+
+### 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
+
+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`.
+
+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!
+
+## Data source
+
+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.
+
+![sample Sheets data source](https://user-images.githubusercontent.com/1102504/54064578-62e6c180-41ca-11e9-86f6-9d147ac17200.png "sample Sheets data source")
+
+## Testing
+
+The unit-test script is `docs_mail_merge_test.py`; see the file for a list of the available tests.
+
+## Reference
+
+- Google Drive API
+    - [API documentation](https://developers.google.com/drive)
+    - [Support channels](https://developers.google.com/drive/api/v3/support)
+- Google Docs API
+    - [API documentation](https://developers.google.com/docs)
+    - [Python quickstart](https://developers.google.com/docs/api/quickstart/python)
+    - [Support channels](https://developers.google.com/docs/api/support)
+- Google Sheets API
+    - [API documentation](https://developers.google.com/sheets)
+    - [Support channels](https://developers.google.com/sheets/api/support)
+- [Google APIs client libraries](https://developers.google.com/api-client-library)
+- [G Suite developer overview &amp; video](https://developers.google.com/gsuite)
+- [G Suite (REST) APIs intro codelab](https://g.co/codelabs/gsuite-apis-intro) (~half-hour)
+- Introductory API videos
+    - [New Google APIs project setup](https://goo.gl/RbyTFD) (6:54)
+    - [Common Python boilerplate code review](https://goo.gl/KMfbeK) (3:48)
+    - [REST APIs intro (Drive API)](https://goo.gl/ZIgf8k) (6:20)
+    - [Introducing the Docs API](https://youtu.be/jeU-tWKeb6g) (2:57)
diff --git a/docs/mail-merge/docs_mail_merge.py b/docs/mail-merge/docs_mail_merge.py
@@ -0,0 +1,141 @@
+# -*- coding: utf-8 -*-
+#
+# Copyright ©2018-2019 Google LLC
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+
+"""
+docs-mail-merge.py (2.x or 3.x)
+
+Google Docs (REST) API mail-merge sample app
+"""
+from __future__ import print_function
+import time
+
+from googleapiclient import discovery
+from httplib2 import Http
+from oauth2client import file, client, tools
+
+# Fill-in IDs of your Docs template & any Sheets data source
+DOCS_FILE_ID = 'YOUR_TMPL_DOC_FILE_ID'
+SHEETS_FILE_ID = 'YOUR_SHEET_DATA_FILE_ID'
+
+# authorization constants
+CLIENT_ID_FILE = 'credentials.json'
+TOKEN_STORE_FILE = 'token.json'
+SCOPES = (  # iterable or space-delimited string
+    'https://www.googleapis.com/auth/drive',
+    'https://www.googleapis.com/auth/documents',
+    'https://www.googleapis.com/auth/spreadsheets.readonly',
+)
+
+# application constants
+SOURCES = ('text', 'sheets')
+SOURCE = 'text' # Choose one of the data SOURCES
+COLUMNS = ['to_name', 'to_title', 'to_company', 'to_address']
+TEXT_SOURCE_DATA = [
+    'Ms. Lara Brown', 'Googler', 'Google NYC',
+    '111 8th Ave\nNew York, NY  10011-5201'
+]
+
+def get_http_client():
+    """Uses project credentials in CLIENT_ID_FILE along with requested OAuth2
+        scopes for authorization, and caches API tokens in TOKEN_STORE_FILE.
+    """
+    store = file.Storage(TOKEN_STORE_FILE)
+    creds = store.get()
+    if not creds or creds.invalid:
+        flow = client.flow_from_clientsecrets(CLIENT_ID_FILE, SCOPES)
+        creds = tools.run_flow(flow, store)
+    return creds.authorize(Http())
+
+# service endpoints to Google APIs
+HTTP = get_http_client()
+DRIVE = discovery.build('drive', 'v3', http=HTTP)
+DOCS = discovery.build('docs', 'v1', http=HTTP)
+SHEETS = discovery.build('sheets', 'v4', http=HTTP)
+
+# fill-in your data to merge into document template variables
+merge = {
+    'my_name': 'Mr. Jeff Erson',
+    'my_address': '76 9th Ave\nNew York, NY  10011-4962',
+    'my_email': 'http://google.com',
+    'my_phone': '+1-212-565-0000',
+    # - - - - - - - - - - - - - - - - - - - - - - - - - -
+    'date': time.ctime(),
+    # - - - - - - - - - - - - - - - - - - - - - - - - - -
+    'body': 'Google, headquartered in Mountain View, unveiled the new Android '
+            'phone at the Consumer Electronics Show. CEO Sundar Pichai said '
+            'in his keynote that users love their new Android phones.'
+}
+
+def get_data(source):
+    """Gets mail merge data from chosen data source.
+    """
+    if source not in {'sheets', 'text'}:
+        raise ValueError('ERROR: unsupported source %r; choose from %r' % (
+            source, SOURCES))
+    func = SAFE_DISPATCH[source]
+    return dict(zip(COLUMNS, func()))
+
+def _get_text_data():
+    """(private) Returns plain text data; can alter to read from CSV file.
+    """
+    return TEXT_SOURCE_DATA
+
+def _get_sheets_data(service=SHEETS):
+    """(private) Returns data from Google Sheets source. NOTE: this sample
+        code gets all cells in 'Sheet1', the first default Sheet in a
+        spreadsheet. Use any desired data range (in standard A1 notation).
+        This sample app is coded to return only the 2nd row (the data).
+    """
+    return service.spreadsheets().values().get(spreadsheetId=SHEETS_FILE_ID,
+            range='Sheet1!2:2').execute().get('values')[0] # 2nd row only
+
+# data source dispatch table [better alternative to using eval()]
+SAFE_DISPATCH = {k: globals().get('_get_%s_data' % k) for k in SOURCES}
+
+def _copy_template(tmpl_id, source, service):
+    """(private) Copies letter template document using Drive API then
+        returns file ID of (new) copy.
+    """
+    body = {'name': 'Merged form letter (%s)' % source}
+    return service.files().copy(body=body, fileId=tmpl_id,
+            fields='id').execute().get('id')
+
+def merge_template(tmpl_id, source, service):
+    """Copies template document and merges data into newly-minted copy then
+        returns its file ID.
+    """
+    # copy template and set context data struct for merging template values
+    copy_id = _copy_template(tmpl_id, source, service)
+    context = merge.iteritems() if hasattr({}, 'iteritems') else merge.items()
+
+    # "search & replace" API requests for mail merge substitutions
+    reqs = [{'replaceAllText': {
+                'containsText': {
+                    'text': '{{%s}}' % key.upper(), # {{VARS}} are uppercase
+                    'matchCase': True,
+                },
+                'replaceText': value,
+            }} for key, value in context]
+
+    # send requests to Docs API to do actual merge
+    DOCS.documents().batchUpdate(body={'requests': reqs},
+            documentId=copy_id, fields='').execute()
+    return copy_id
+
+
+if __name__ == '__main__':
+    if SOURCE in SOURCES:
+        merge.update(get_data(SOURCE))
+        fid = merge_template(DOCS_FILE_ID, SOURCE, DRIVE)
+        print('Merged letter: docs.google.com/document/d/%s/edit' % fid)
diff --git a/docs/mail-merge/docs_mail_merge_test.py b/docs/mail-merge/docs_mail_merge_test.py
@@ -0,0 +1,90 @@
+# Copyright 2018-2019 Google LLC
+#
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+
+"""
+docs_mail_merge_test.py -- unit test for docs_mail_merge.py:
+    1. test credentials file availability
+    2. test whether project can connect to all 3 APIs
+    3. test creation (and deletion) of Google Docs file
+    4. test copying (and deletion) of Google Docs file
+    5. test getting plain text data
+    6. test getting data from Google Sheets spreadsheet
+"""
+
+import os
+import unittest
+
+from googleapiclient import discovery
+from docs_mail_merge6 import (CLIENT_ID_FILE, get_data, get_http_client,
+        _copy_template)
+
+class TestDocsMailMerge(unittest.TestCase):
+    'Unit tests for Mail Merge sample'
+    def test_project(self):
+        self.assertTrue(project_test())
+    def test_gapis(self):
+        self.assertTrue(gapis_test())
+    def test_create_doc(self):
+        self.assertTrue(create_doc_test())
+    def test_copy_doc(self):
+        self.assertTrue(copy_doc_test())
+    def test_get_text_data(self):
+        self.assertTrue(bool(get_text_data_test()))
+    def test_get_sheets_data(self):
+        self.assertTrue(bool(get_sheets_data_test()))
+
+def project_test():
+    'Tests whether project credentials file was downloaded from project.'
+    if os.path.exists(CLIENT_ID_FILE):
+        return True
+    raise IOError('''\
+        ERROR: Must create a Google APIs project, enable both
+        the Drive and Docs REST APIs, create and download OAuth2
+        client credentials as %r before unit test can run.''' % CLIENT_ID_FILE)
+
+def gapis_test():
+    'Tests whether project can connect to all 3 APIs used in the sample.'
+    HTTP = get_http_client()
+    discovery.build('drive', 'v3', http=HTTP)
+    discovery.build('docs', 'v1', http=HTTP)
+    discovery.build('sheets', 'v4', http=HTTP)
+    return True
+
+def create_doc_test():
+    'Tests whether project can create and delete a Google Docs file.'
+    DRIVE = discovery.build('drive', 'v3', http=get_http_client())
+    DATA = {
+        'name': 'Test Doc',
+        'mimeType': 'application/vnd.google-apps.document',
+    }
+    doc_id = DRIVE.files().create(body=DATA, fields='id').execute().get('id')
+    DRIVE.files().delete(fileId=doc_id, fields='').execute()
+    return True
+
+def copy_doc_test():
+    'Tests whether project can copy and delete a Google Docs file.'
+    DRIVE = discovery.build('drive', 'v3', http=get_http_client())
+    DOCS_FILE_ID = '1Rr7eKm3tvUgRKRoOYVSMF69IVAHIOPS72-l0CzsPmfM'
+    doc_id = _copy_template(DOCS_FILE_ID, 'text')
+    DRIVE.files().delete(fileId=doc_id, fields='').execute()
+    return True
+
+def get_text_data_test():
+    'Tests reading plain text data.'
+    return get_data()
+
+def get_sheets_data_test():
+    'Tests reading Google Sheets data.'
+    return get_data(source='sheets')
+
+if __name__ == '__main__':
+    unittest.main()