在GCP VM上运行时，使用应用程序默认凭据访问Google Sheet API，使用范围给出不足范围错误

Question

我试图从Python(在GKE中运行)访问Google Sheet(读写模式)。我尝试过outh2client以及google-auth方法，但每次都给出相同的错误:

googleapiclient.errors.HttpError: <HttpError 403 when requesting https://sheets.googleapis.com/v4/spreadsheets/1kvHv1OBCzr9GnFxRu9RTJC7jjQjc9M4rAiDnhyak2Sg/values/vm_metrics%21A10?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': {'method': 'google.apps.sheets.v4.SpreadsheetsService.GetValues', 'service': 'sheets.googleapis.com'}}]">

这是我使用outh2client的代码:

from googleapiclient.discovery import build
from oauth2client import client
creds=client.GoogleCredentials.get_application_default().create_scoped(
['https://www.googleapis.com/auth/spreadsheets'])
service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()
sheet.values().get(spreadsheetId='whatev', range='Sheet1!A:C').execute()

这是我使用google-auth的代码:

import google.auth
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
creds, project=google.auth.default(scopes=SCOPES)
service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()
sheet.values().get(spreadsheetId='XXXXXXXXXX', range='Sheet1!A:C').execute()

Answer 1

AFAICT，这是与使用应用程序默认凭证访问Google Sheet API相同的问题。解决方案(从SO帖子)是

from googleapiclient.discovery import build
from oauth2client import client
creds = client.GoogleCredentials.get_application_default().create_scoped(
['https://www.googleapis.com/auth/spreadsheets.readonly']
)
response = service.spreadsheets().values().get(
spreadsheetId='XXXXXXXXXX',
range='Sheet1!A:C'
).execute()
rows = response['values']

然而，这个将不工作与您从gcloud auth application-default login获得的通常的应用程序默认凭据。你会得到这样的错误:

Traceback (most recent call last):
File "<ipython-input-2-2a9ba7e9e38f>", line 8, in <module>
sheet.values().get(spreadsheetId='1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms', range='Class Data!A2:E''....').execute()
File "/Users/dking/miniconda3/lib/python3.7/site-packages/googleapiclient/_helpers.py", line 130, in positional_wrapper
return wrapped(*args, **kwargs)
File "/Users/dking/miniconda3/lib/python3.7/site-packages/googleapiclient/http.py", line 855, in execute
raise HttpError(resp, content, uri=self.uri)
HttpError: <HttpError 403 when requesting https://sheets.googleapis.com/v4/spreadsheets/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/values/Class%20Data%21A2%3AE....?alt=json returned "Request had insufficient authentication scopes.">

修复其实很简单!感谢James在评论中指出这一点。您必须扩展应用程序默认凭据的OAuth范围，以包含Google Sheets API，并且还必须设置Google Sheets用于速率限制的配额项目。在撰写本文时，没有使用Sheets API的文档成本，但每个项目每分钟有300个请求的限制。

gcloud auth application-default login 
--scopes=openid,https://www.googleapis.com/auth/userinfo.email,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/spreadsheets
gcloud auth application-default set-quota-project YOUR_GCP_PROJECT