调用Google Sheets API创建表格遇ACCESS_TOKEN_SCOPE_INSUFFICIENT错误求助

错误信息 #

return (<HttpError 403 when requesting https://sheets.googleapis.com/v4/spreadsheets?fields=spreadsheetId&alt=json returned "Request had insufficient authentication scopes.". Details: "[{'@type': 'type.googleapis.com/google.rpc.ErrorInfo', 'reason': 'ACCESS_TOKEN_SCOPE_INSUFFICIENT', 'domain': 'googleapis.com', 'metadata': {'service': 'sheets.googleapis.com', 'method': 'google.apps.sheets.v4.SpreadsheetsService.CreateSpreadsheet'}}]>
)

代码示例 #

import os.path

from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]

def create(title):
  """
  Creates the Sheet the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds = None
  # The file token.json stores the user's access and refresh tokens, and is
  # created automatically when the authorization flow completes for the first
  # time.
  if os.path.exists("token.json"):
    creds = Credentials.from_authorized_user_file("token.json", SCOPES)
  # If there are no (valid) credentials available, let the user log in.
  if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
      creds.refresh(Request())
    else:
      flow = InstalledAppFlow.from_client_secrets_file(
        "credentials.json", SCOPES
      )
      creds = flow.run_local_server(port=0)
    # Save the credentials for the next run
    with open("token.json", "w") as token:
      token.write(creds.to_json())
  # pylint: disable=maybe-no-member
  try:

    service = build("sheets", "v4", credentials=creds)
    spreadsheet = {"properties": {"title": title}}

    spreadsheet = (
        service.spreadsheets()
        .create(body=spreadsheet , fields="spreadsheetId")
        .execute()

    )

    print(f"Spreadsheet ID: {(spreadsheet.get('spreadsheetId'))}")
    return spreadsheet.get("spreadsheetId")
  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: title
  create("mysheet1")

错误原因 #

你代码里指定的SCOPES是正确的，https://www.googleapis.com/auth/spreadsheets权限足够完成创建表格的操作。问题出在之前授权生成的token.json文件使用了不包含当前所需权限的范围，而且OAuth令牌的权限范围无法通过刷新令牌来更新，必须重新授权获取新的令牌。

如果是首次配置，也有可能在Google Cloud控制台的OAuth凭据配置中，没有正确启用Google Sheets API，或者OAuth同意屏幕未添加对应的权限范围，但这种情况概率较低。

解决方法 #

• 删除旧令牌文件：找到当前项目目录下的token.json文件并删除，这个文件存储了之前授权的旧权限令牌。
• 重新运行程序：再次执行Python脚本，此时会自动弹出浏览器引导你完成新的授权流程，这次授权会使用代码里指定的正确权限范围。授权完成后，程序会生成新的token.json文件，之后就能正常创建电子表格了。
• 额外检查（若仍报错）：
  • 登录Google Cloud控制台，确认你的项目已经启用了Google Sheets API。
  • 在OAuth同意屏幕配置中，确认已将https://www.googleapis.com/auth/spreadsheets添加到已授权的范围列表中。

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