Source Code: Automate Mail Merge with Google Docs using Python (with Google Sheets as data source)

import os
import datetime
import time
import io
from googleapiclient.http import MediaIoBaseUpload
from Google import Create_Service

CLIENT_SECRET_FILE = r'<client secret file>'
API_SERVICE_NAME = 'docs'
API_VERSION = 'v1'
SCOPES = ['https://www.googleapis.com/auth/documents',
          'https://www.googleapis.com/auth/drive']

"""
Step 1. Create Google API Service Instances
"""
# Google Docs instance
service_docs = Create_Service(
    CLIENT_SECRET_FILE, 
    'docs', 'v1', 
    ['https://www.googleapis.com/auth/documents',
    'https://www.googleapis.com/auth/drive']
)
time.sleep(2)

# Google Drive instance
service_drive = Create_Service(
    CLIENT_SECRET_FILE,
    'drive',
    'v3',
    ['https://www.googleapis.com/auth/drive']
)
time.sleep(2)

# Google Sheets instance
service_sheets = Create_Service(
    CLIENT_SECRET_FILE,
    'sheets',
    'v4',
    ['https://www.googleapis.com/auth/spreadsheets']
)
time.sleep(2)


template_document_id = '<Google Docs Template Document Id'
google_sheets_id = 'Google Sheets Id'
folder_id = 'Google Drive Folder Id' # None --> save in the parent folder

responses = {}

"""
Step 2. Load Records from Google Sheets
"""
worksheet_name = 'Projects'
responses['sheets'] = service_sheets.spreadsheets().values().get(
    spreadsheetId=google_sheets_id,
    range=worksheet_name,
    majorDimension='ROWS',
).execute()

columns = responses['sheets']['values'][0]
records = responses['sheets']['values'][1:]


"""
Step 3. Iterate Each Record and Perform Mail Merge
"""

def mapping(merge_field, value=''):
    json_representation = {
        'replaceAllText': {
            'replaceText': value,
            'containsText': {
                'matchCase': 'true',
                'text': '{{{{{0}}}}}'.format(merge_field)
            }
        }
    }
    return json_representation

for record in records:
    print('Processing record {0}...'.format(record[2]))
    # Copy template doc file as new doc file
    document_title = 'SOW for {0}'.format(record[2])

    responses['docs'] = service_drive.files().copy(
        fileId=template_document_id,
        body={
            'parents': [folder_id],
            'name': document_title
        }
        
    ).execute()
    document_id = responses['docs']['id']
    
    # Update Google Docs document (not template file)
    merge_fields_information = [mapping(columns[indx], value) for indx, value in enumerate(record)]

    service_docs.documents().batchUpdate(
        documentId=document_id,
        body={
            'requests': merge_fields_information
        }
    ).execute()

    """
    Export Document as PDF
    """
    PDF_MIME_TYPE = 'application/pdf'
    byteString = service_drive.files().export(
        fileId=document_id,
        mimeType=PDF_MIME_TYPE
    ).execute()

    media_object = MediaIoBaseUpload(io.BytesIO(byteString), mimetype=PDF_MIME_TYPE)
    service_drive.files().create(
        media_body=media_object,
        body={
            'parents': [folder_id],
            'name': '{0} (PDF).pdf'.format(document_title)
        }
    ).execute()

print('Mail Merge Complete.')