Google Workspace邮件合并生成文档权限访问问题求助

问题背景 #

使用ServiceAccount凭据运行邮件合并代码后，出现两个问题：

• 生成的文档链接需要申请权限才能访问（即使是创建凭据的Gmail账号）
• 合并后的文档未存入指定的Google Drive文件夹

运行代码 #

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)))

预期行为 #

• 生成的文档链接可直接访问，无需权限申请
• 合并后的文档自动存入指定的Google Drive文件夹

实际行为 #

• 代码执行成功，输出文档链接：

Merged letter 1: docs.google.com/document/d/11ePx7cP-M49rBVtF4gvaMBA02ZH92W5xOK5DXOzdH9Q/edit
Merged letter 2: docs.google.com/document/d/1to3zGwJYsBflIeiOm9pydNOdmQ9IJDXxbtNADEsylkA/edit

• 使用创建凭据的Gmail账号访问链接时，仍需申请权限
• 文档未存入指定Drive文件夹

解决方案 #

1. 解决权限访问问题 #

生成的文档归属Service Account独立账号，而非你的个人Gmail账号，因此需要主动为个人账号添加访问权限。修改merge_template函数，加入权限设置逻辑：

更新后的merge_template函数

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)
        
        # 为个人Gmail账号添加编辑权限
        permission = {
            'type': 'user',
            'role': 'writer',
            'emailAddress': '你的Gmail账号邮箱'  # 替换为实际邮箱
        }
        service.permissions().create(
            fileId=copy_id,
            body=permission,
            sendNotificationEmail=False  # 可选：是否发送权限通知邮件
        ).execute()
        
        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

2. 将文档存入指定Drive文件夹 #

当前代码复制模板时未指定目标文件夹，需修改_copy_template函数的请求体，加入parents参数绑定目标文件夹ID：

更新后的_copy_template函数

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,
            'parents': [folder_id]  # 指定目标文件夹ID
        }
        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

关键注意事项 #

• 确保你的个人Gmail账号有权限访问指定的Drive文件夹，否则文档无法存入该目录
• 替换代码中的你的Gmail账号邮箱为实际使用的邮箱地址
• 若不需要发送权限通知邮件，保持sendNotificationEmail=False即可

内容的提问来源于stack exchange，提问作者Baker Ssebandeke