add support for >1 row of data (googleworkspace#95)

wescpy · asrivas · commit c17d2c9074ba · 2019-03-17T11:22:10.000-04:00
* add Docs API Mail Merge Python sample

* pylint &amp; eval fixes

* lint fixes

* final(?) lint fixes

* PR fixes and code cleanup

* more cleanup &amp; 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 &gt;1 row of data; README cleanup

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

* README tweaks

* pylint fixes
diff --git a/docs/mail-merge/README.md b/docs/mail-merge/README.md
@@ -3,19 +3,15 @@
 ## Prerequisites
 
 - Access to the internet and a web browser
-- A Google account (G Suite accounts may require administrator approval)
+- 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](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`
+Create a new project (or reuse an existing one) from [your developer console](https://console.developers.google.com) 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](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:
+This sample app requires you to [create a new Google Docs file](https://docs.google.com) 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
 
@@ -36,15 +32,27 @@ In the document, the variable names used by the sample app are:
 * `{{TO_COMPANY}}` &mdash; recipient's organization
 * `{{TO_ADDRESS}}` &mdash; recipient's address
 
-After you've templatized the Google Doc, be sure to grab its file ID &mdash; 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`.
+Here's one [sample letter template](https://drive.google.com/open?id=1Xycxuuv7OhEQUuzbt_Mw0TPMq02MseSD1vZdBJ3nLjk) to get an idea of what we mean:
+
+![sample letter template](https://user-images.githubusercontent.com/1102504/54470461-6b5c7080-4765-11e9-9912-01b44c734118.png "sample letter template")
 
-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!
+Once you're done, grab your template's file ID &mdash; 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
 
-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.
+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](https://user-images.githubusercontent.com/1102504/54470464-731c1500-4765-11e9-9110-986519502cdf.png "sample Sheets data source")
+
+Here is [one example Sheet](https://drive.google.com/open?id=18yqXLEMx6l__VAIN-Zo52pL18F3rXn0_-K6gZ-vwPcc) 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
 
-![sample Sheets data source](https://user-images.githubusercontent.com/1102504/54064578-62e6c180-41ca-11e9-86f6-9d147ac17200.png "sample Sheets data source")
+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](https://user-images.githubusercontent.com/1102504/54470465-731c1500-4765-11e9-8a0a-93a3bb445d6e.png "sample merged letter")
 
 ## Testing
 
@@ -53,20 +61,29 @@ The unit-test script is `docs_mail_merge_test.py`; see the file for a list of th
 ## Reference
 
 - Google Drive API
-    - [API documentation](https://developers.google.com/drive)
-    - [Support channels](https://developers.google.com/drive/api/v3/support)
+    - [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)
+    - [API documentation](https://developers.google.com/docs/)
+    - [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
+    - [API documentation](https://developers.google.com/sheets/)
+    - [Support channels](https://developers.google.com/sheets/api/support/)
+- G Suite APIs
+    - [G Suite developer overview &amp; video](https://developers.google.com/gsuite/)
+    - [G Suite (REST) APIs intro codelab](https://g.co/codelabs/gsuite-apis-intro/) (30 mins)
+    - [Google APIs client libraries](https://developers.google.com/api-client-library/)
+- Introductory videos
     - [New Google APIs project setup](https://goo.gl/RbyTFD) (6:54)
-    - [Common Python boilerplate code review](https://goo.gl/KMfbeK) (3:48)
+    - [Common OAuth2 boilerplate code (Python)](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)
+
+## Enhancements
+
+Here are some recommendations on how you can enhance this application:
+
+1. Adapt this sample for your mail merge use case
+1. Support exporting merged letters as PDF (HINT: Drive API)
+1. Support importing data from other data sources, i.e., [Cloud SQL](https://cloud.google.com/sql/), Salesforce, etc.
+1. Augment the plain text data support to include processing data from CSV files
diff --git a/docs/mail-merge/docs_mail_merge.py b/docs/mail-merge/docs_mail_merge.py
@@ -13,10 +13,11 @@
 # limitations under the License.
 
 """
-docs-mail-merge.py (2.x or 3.x)
+docs-mail-merge.py (Python 2.x or 3.x)
 
 Google Docs (REST) API mail-merge sample app
 """
+# [START mail_merge_python]
 from __future__ import print_function
 import time
 
@@ -41,10 +42,12 @@
 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'
-]
+TEXT_SOURCE_DATA = (
+    ('Ms. Lara Brown', 'Googler', 'Google NYC', '111 8th Ave\n'
+                                                'New York, NY  10011-5201'),
+    ('Mr. Jeff Erson', 'Googler', 'Google NYC', '76 9th Ave\n'
+                                                'New York, NY  10011-4962'),
+)
 
 def get_http_client():
     """Uses project credentials in CLIENT_ID_FILE along with requested OAuth2
@@ -63,44 +66,28 @@ def get_http_client():
 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()))
+    return SAFE_DISPATCH[source]()
 
 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).
+    """(private) Returns data from Google Sheets source. It gets all rows of
+        'Sheet1' (the default Sheet in a new spreadsheet), but drops the first
+        (header) row. Use any desired data range (in standard A1 notation).
     """
     return service.spreadsheets().values().get(spreadsheetId=SHEETS_FILE_ID,
-            range='Sheet1!2:2').execute().get('values')[0] # 2nd row only
+            range='Sheet1').execute().get('values')[1:] # skip header row
 
-# data source dispatch table [better alternative to using eval()]
+# data source dispatch table [better alternative vs. eval()]
 SAFE_DISPATCH = {k: globals().get('_get_%s_data' % k) for k in SOURCES}
 
 def _copy_template(tmpl_id, source, service):
@@ -135,7 +122,32 @@ def merge_template(tmpl_id, source, service):
 
 
 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)
+    # fill-in your data to merge into document template variables
+    merge = {
+        # sender data
+        'my_name': 'Ayme A. Coder',
+        'my_address': '1600 Amphitheatre Pkwy\n'
+                      'Mountain View, CA  94043-1351',
+        'my_email': 'http://google.com',
+        'my_phone': '+1-650-253-0000',
+        # - - - - - - - - - - - - - - - - - - - - - - - - - -
+        # recipient data (supplied by 'text' or 'sheets' data source)
+        'to_name': None,
+        'to_title': None,
+        'to_company': None,
+        'to_address': None,
+        # - - - - - - - - - - - - - - - - - - - - - - - - - -
+        'date': time.strftime('%Y %B %d'),
+        # - - - - - - - - - - - - - - - - - - - - - - - - - -
+        '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 phones.'
+    }
+
+    # get row data, then loop through & process each form letter
+    data = get_data(SOURCE) # get data from data source
+    for i, row in enumerate(data):
+        merge.update(dict(zip(COLUMNS, row)))
+        print('Merged letter %d: docs.google.com/document/d/%s/edit' % (
+                i+1, merge_template(DOCS_FILE_ID, SOURCE, DRIVE)))
+# [END mail_merge_python]
diff --git a/docs/mail-merge/docs_mail_merge_test.py b/docs/mail-merge/docs_mail_merge_test.py
@@ -1,4 +1,6 @@
-# Copyright 2018-2019 Google LLC
+# -*- coding: utf-8 -*-
+#
+# Copyright &copy;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.
@@ -24,7 +26,7 @@
 import unittest
 
 from googleapiclient import discovery
-from docs_mail_merge6 import (CLIENT_ID_FILE, get_data, get_http_client,
+from docs_mail_merge import (CLIENT_ID_FILE, get_data, get_http_client,
         _copy_template)
 
 class TestDocsMailMerge(unittest.TestCase):
@@ -73,18 +75,18 @@ def create_doc_test():
 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')
+    DOCS_FILE_ID = '1Xycxuuv7OhEQUuzbt_Mw0TPMq02MseSD1vZdBJ3nLjk'
+    doc_id = _copy_template(DOCS_FILE_ID, 'text', DRIVE)
     DRIVE.files().delete(fileId=doc_id, fields='').execute()
     return True
 
 def get_text_data_test():
     'Tests reading plain text data.'
-    return get_data()
+    return get_data('text')
 
 def get_sheets_data_test():
     'Tests reading Google Sheets data.'
-    return get_data(source='sheets')
+    return get_data('sheets')
 
 if __name__ == '__main__':
     unittest.main()
