Google Sheets API服务账号认证：读取成功但更新单元格返回403错误

问题现象 #

• 读取单元格操作可正常执行，能获取到目标单元格内容
• 更新单元格操作无控制台显式报错，但目标单元格未发生变更；查看请求对象后发现返回403权限错误，提示信息为"The request is missing a valid API key."

相关代码 #

from apiclient import discovery
from google.oauth2 import service_account
import os

credentials = service_account.Credentials.from_service_account_file(
  os.path.join(os.getcwd(), 'app3-c1824-91dfa420b4a8.json'), 
  scopes=['https://www.googleapis.com/auth/spreadsheets']
)
apiService = discovery.build('sheets', 'v4', credentials=credentials)

# 读取操作（执行成功）
values = apiService.spreadsheets().values().get(
  spreadsheetId='1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok',
  range='A1'
).execute()

print(f"READ response: {values}")

# 更新操作（执行失败）
res = apiService.spreadsheets().values().update(
  spreadsheetId='1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok',
  range='Sheet1!A2',
  valueInputOption='RAW',
  body={
    'values': [['test']]
  }
)

print(f"UPDATE response: {res}")

读取请求日志输出 #

2023-09-28 17:45:28 [googleapiclient.discovery_cache] INFO: file_cache is only supported with oauth2client<4.0.0
2023-09-28 17:45:28 [googleapiclient.discovery_cache] INFO: file_cache is only supported with oauth2client<4.0.0
2023-09-28 17:45:28 [googleapiclient.discovery] DEBUG: URL being requested: GET https://sheets.googleapis.com/v4/spreadsheets/1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok/values/A1?alt=json
2023-09-28 17:45:28 [google_auth_httplib2] DEBUG: Making request: POST https://oauth2.googleapis.com/token
READ response: {'range': 'Sheet1!A1', 'majorDimension': 'ROWS', 'values': [['prashan']]}

更新请求日志输出 #

2023-09-28 17:45:28 [googleapiclient.discovery] DEBUG: URL being requested: PUT https://sheets.googleapis.com/v4/spreadsheets/1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok/values/Sheet1%21A2?valueInputOption=RAW&alt=json
        UPDATE response: <googleapiclient.http.HttpRequest object at 0x106c1ef50>

更新请求对象详情 #

res
<googleapiclient.http.HttpRequest object at 0x106c1ef50>
special variables:
function variables:
body: '{"values": [["test"]]}'
body_size: 22
headers: {'accept': 'application/json', 'accept-encoding': 'gzip, deflate', 'user-agent': '(gzip)', 'x-goog-api-client': 'gdcl/2.96.0 gl-python/3.11.3', 'content-type': 'application/json'}
http: <google_auth_httplib2.AuthorizedHttp object at 0x108628590>
method: 'PUT'
methodId: 'sheets.spreadsheets.values.update'
response_callbacks: []
resumable: None
resumable_progress: 0
resumable_uri: None
uri: 'https://sheets.googleapis.com/v4/spreadsheets/1fV0fLiLidUEfOQsmZuytRPW6FapkYkFN-NOiZOftLok/values/Sheet1%21A2?valueInputOption=RAW&alt=json'
_in_error_state: False
_process_response: <bound method HttpRequest._process_response of <googleapiclient.http.HttpRequest object at 0x106c1ef50>>
_rand: <built-in method random of Random object at 0x14e0a9c20>
_sleep: <built-in function sleep>

错误详情 #

直接访问更新请求的URI，返回如下JSON：

{
  "error": {
    "code": 403,
    "message": "The request is missing a valid API key.",
    "status": "PERMISSION_DENIED"
  }
}

已完成的排查配置 #

• 服务账号所属的app3项目已启用Google Sheets API
• 该服务账号已被授予目标表格的Editor权限
• 确认Google Sheets API不支持通过API密钥执行写入操作，当前采用的是服务账号认证方式

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