Please review my Git Hub response. I have also attached my Python code to this post.
from __future__ import print_function
import time
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from google.oauth2 import service_account
# Set your credentials file path
credentials_path = '/Users/baker/Desktop/a3/credentials.json'
# Set your template document ID
template_document_id = '12g6okE4Hvr5VCMiGo6Z7szNSp_cZIRafdLHafGdEtRs'
# Set your Google Sheets document ID
spreadsheet_id = '1uzt7TxPSzJkcts6RIJv36KCD8IstYa2UAve8DKe2UZs'
# Set the sheet name in the Google Sheets document
sheet_name = 'Sheet1'
# Load credentials from the JSON file
credentials = service_account.Credentials.from_service_account_file(
credentials_path,
scopes=['https://www.googleapis.com/auth/documents', 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
)
# Fill-in IDs of your Docs template & any Sheets data source
DOCS_FILE_ID = "12g6okE4Hvr5VCMiGo6Z7szNSp_cZIRafdLHafGdEtRs"
SHEETS_FILE_ID = "1uzt7TxPSzJkcts6RIJv36KCD8IstYa2UAve8DKe2UZs"
# Set the folder ID in Google Drive to save the merged documents
folder_id = '1Wknll1g5MiJSaVKtS2e-AHlL8EzeT6UG'
# application constants
SOURCES = ('text', 'sheets')
SOURCE = 'sheets' # 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\n'
'New York, NY 10011-5201'),
('Mr. Jeff Erson', 'Googler', 'Google NYC', '76 9th Ave\n'
'New York, NY 10011-4962'),
)
# Build the Google Docs and Google Sheets services
DRIVE = build('drive', 'v2', credentials=credentials)
DOCS = build('docs', 'v1', credentials=credentials)
SHEETS = build('sheets', 'v4', credentials=credentials)
def get_data(source):
"""Gets mail merge data from chosen data source.
"""
try:
if source not in {'sheets', 'text'}:
raise ValueError(f"ERROR: unsupported source {source}; "
f"choose from {SOURCES}")
return SAFE_DISPATCH[source]()
except HttpError as error:
print(f"An error occurred: {error}")
return error
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. 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').execute().get(
'values')[1:]
# skip header row
# 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):
"""(private) Copies letter template document using Drive API then
returns file ID of (new) copy.
"""
try:
body = {'name': 'Merged form letter (%s)' % source}
return service.files().copy(body=body, fileId=tmpl_id,
fields='id').execute().get('id')
except HttpError as error:
print(f"An error occurred: {error}")
return error
def merge_template(tmpl_id, source, service):
"""Copies template document and merges data into newly-minted copy then
returns its file ID.
"""
try:
# 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
except HttpError as error:
print(f"An error occurred: {error}")
return error
if __name__ == '__main__':
# 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)))
LinkedIn
Twitter
