Python通过服务账号创建Google Sheets后如何开放全员访问权限

我有一个示例dataframe：

df = pd.DataFrame(
[["a", "b"], ["c", "d"]],
index=["row 1", "row 2"],
columns=["col 1", "col 2"],
)

我需要将该dataframe写入Google Sheets，为此我已经创建了谷歌服务账号，配置如下：

FOLDER_PATH = "/home/"
CLIENT_SECRET_FILE = os.path.join(FOLDER_PATH, 'auth.json')
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

我的Google.py代码如下：

import pickle
import os
import datetime
from google_auth_oauthlib.flow import Flow, InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.http import MediaFileUpload, MediaIoBaseDownload
from google.auth.transport.requests import Request


def Create_Service(client_secret_file, api_name, api_version, *scopes):
   print(client_secret_file, api_name, api_version, scopes, sep='-')
   CLIENT_SECRET_FILE = client_secret_file
   API_SERVICE_NAME = api_name
   API_VERSION = api_version
   SCOPES = [scope for scope in scopes[0]]
   print(SCOPES)

   cred = None

   pickle_file = f'token_{API_SERVICE_NAME}_{API_VERSION}.pickle'
   # print(pickle_file)

   if os.path.exists(pickle_file):
      with open(pickle_file, 'rb') as token:
          cred = pickle.load(token)

   if not cred or not cred.valid:
       if cred and cred.expired and cred.refresh_token:
        cred.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE, SCOPES)
        cred = flow.run_local_server()

    with open(pickle_file, 'wb') as token:
        pickle.dump(cred, token)

try:
    service = build(API_SERVICE_NAME, API_VERSION, credentials=cred)
    print(API_SERVICE_NAME, 'service created successfully')
    return service
except Exception as e:
    print('Unable to connect.')
    print(e)
    return None

def convert_to_RFC_datetime(year=1900, month=1, day=1, hour=0, minute=0):
  dt = datetime.datetime(year, month, day, hour, minute, 0).isoformat() + 'Z'
  return dt

我的gsheet.py代码如下：

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from Google import Create_Service
import os
from google.oauth2 import service_account

FOLDER_PATH = "/home/"
CLIENT_SECRET_FILE = os.path.join(FOLDER_PATH, 'auth.json')
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

service = Create_Service(CLIENT_SECRET_FILE, API_SERVICE_NAME, API_VERSION, SCOPES)
sheets_file1 = service.spreadsheets().create(body={}).execute()

sheets_file1中包含了创建的表格链接，目前仅我的服务账号有权限访问该表格，请问如何设置权限让所有人都可以访问该链接？

第一步：调整权限范围 #

Google Sheets的文件权限由Google Drive API管理，因此需要新增Drive接口的权限，修改SCOPES配置如下：

SCOPES = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
]

修改完成后删除本地生成的token_sheets_v4.pickle文件，避免程序复用旧的低权限凭证。

第二步：创建Google Drive服务实例 #

复用现有Create_Service方法创建Drive服务，用于操作文件权限：

# 在gsheet.py中新增以下代码
DRIVE_API_NAME = 'drive'
DRIVE_API_VERSION = 'v3'
drive_service = Create_Service(CLIENT_SECRET_FILE, DRIVE_API_NAME, DRIVE_API_VERSION, SCOPES)

第三步：配置公开访问权限 #

提取创建好的表格ID，调用Drive权限接口设置公开规则：

spreadsheet_id = sheets_file1['spreadsheetId']

# 权限配置：type=anyone代表所有人可访问，role设为reader是仅查看，设为writer是可编辑
permission_body = {
    'type': 'anyone',
    'role': 'reader'
}

# 执行权限配置
drive_service.permissions().create(
    fileId=spreadsheet_id,
    body=permission_body
).execute()

（可选）写入DataFrame到表格 #

# 转换DataFrame为符合接口要求的二维数组
values = [df.columns.values.tolist()] + df.values.tolist()

# 从表格A1位置开始写入数据
body = {'values': values}
service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id,
    range='A1',
    valueInputOption='RAW',
    body=body
).execute()

# 拼接公开访问链接
public_url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit"
print(f"公开表格链接：{public_url}")

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