从 Google Compute Engine (python) 访问 Google Sheets API [英] Accessing Google Sheets API from Google Compute Engine (python)

问题描述

我正在尝试从 Compute Engine 实例中执行以下操作:

from googleapiclient.discovery 导入构建service = build('sheets', 'v4')sheet = service.spreadsheets()结果 = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()

根据这篇文章

如果我在计算引擎中运行应用程序，则不需要明确传递 API 密钥.但是，我收到以下错误:

 googleapiclient.errors.HttpError: <HttpError 403 请求时https://sheets.googleapis.com/v4/spreadsheets/[...]?alt=json返回请求的身份验证范围不足.".详细信息:请求的身份验证范围不足.">

我错过了什么?我是否需要向我的实例服务帐户授予对 Sheets API 的访问权限?如果是这样，我该怎么做?我在云控制台中找不到任何有意义的东西.在

再次运行代码，会成功:

{'properties': {'autoRecalc': 'ON_CHANGE','defaultFormat': {'backgroundColor': {'blue': 1,'绿色':1，'红色':1}，'背景颜色样式':{...'属性':{'索引':1，'sheetId': [[已编辑]],'sheetType': 'OBJECT','title': '图表'}}],'spreadsheetId': '[[SPREADSHEET_ID]]','spreadsheetUrl': 'https://docs.google.com/spreadsheets/d/[[SPREADSHEET_ID]]/edit'}

201124 使用 Compute Engine 进行更新

当您创建 Compute Engine 实例时，您必须为其授予访问表格的范围:

# 默认范围SCOPES="https://www.googleapis.com/auth/devstorage.read_only,\https://www.googleapis.com/auth/logging.write,\https://www.googleapis.com/auth/monitoring.write,\https://www.googleapis.com/auth/servicecontrol,\https://www.googleapis.com/auth/service.management.readonly,\https://www.googleapis.com/auth/trace.append"# 添加表格SCOPES=${SCOPES},https://www.googleapis.com/auth/spreadsheets.readonly"；gcloud beta 计算实例创建 sheeter \--project=${PROJECT} \--zone=${ZONE} \--machine-type=f1-micro \--image-family=debian-10 \--image-project=debian-cloud \--scopes=${SCOPES}

您也可以在您选择的服务帐户下运行实例，例如:

gcloud beta 计算实例创建 sheeter \--project=${PROJECT} \--zone=${ZONE} \--machine-type=f1-micro \--image-family=debian-10 \--image-project=debian-cloud \--scopes=${SCOPES} \--service-account=${EMAIL}

创建实例后，重复安装步骤.无需在实例上安装密钥，也无需在 GOOGLE_APPLICATION_CREDENTIALS 上安装密钥，因为它们均由 Compute Engine 提供.

检查实例在其下运行的服务帐户:

curl \--header 元数据风格:谷歌"\http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/

应该屈服，例如:

${PROJECT-NUM}[e-mail]/默认/

这是您的代码将在其下运行的服务帐户(在本例中为 Compute Engine 的默认帐户).现在，运行代码.

python3 main.py

I am trying to do the following from within the Compute Engine Instance:

from googleapiclient.discovery import build

service = build('sheets', 'v4')
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute()

According to this article https://cloud.google.com/docs/authentication/production and also this page in Cloud Console

I don't need to explicitly pass an API key if I am running the app inside compute engine. Yet, I am getting the following error:

  googleapiclient.errors.HttpError: <HttpError 403 when requesting 
  https://sheets.googleapis.com/v4/spreadsheets/[...]?alt=json
  returned "Request had insufficient authentication scopes.".
  Details: "Request had insufficient authentication scopes.">

What am I missing? Do I need to give access to the Sheets API to my instance service account? If so, how do I do it? I can't find anything meaningful in the cloud console. In the Google Example, they are passing the scopes explicitly SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'] when creating creds, but I don't see how I can pass them here.

解决方案

I've not tried this but saw your question unanswered...

One issue using Google Apps services is that the data is accessed by named user accounts (e.g. [e-mail]) and so you'll either need to use one of the enumerated accounts or make the sheet public (so that anything can access it; you probably don't want to do this).

There's a wrinkle between Cloud and Apps (yes, I know the difference no longer exists in the Google marketing, but...) and, while Cloud services now prefer IAM, the other Google services still use scopes (not IAM). So, munging the service account's IAM permissions won't help.

Your scope is correct.

I think there are (at least) 2 options:

• Add the service account to the sheet's permissions;
• Grant the service account delegated auth (link);

All service accounts have an email address.... you should (!?) be able to add your service account's email address to the sheet.

You can find this:

Either gcloud iam service-accounts list --project=${PROJECT}

Or gcloud projects get-iam-policy ${PROJECT}

Then add the email address to the sheet using the sheet's sharing option.

201124 Update w/ example

Pick any Sheets (sheet) and grab its ID from the URL.

Confirm that you're able to access it (using your user credentials) using the (excellent) APIs Explorer specificially for the Sheets v4 API GET method here:

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get

Create a Google Cloud Platform Project, enable sheets and create a service account and a key.

NOTE Because Sheets does not use IAM, we don't need to assign any permissions to the Service Account:

PROJECT=[[YOUR-PROJECT-ID]]
BILLING=$(gcloud alpha billing accounts list --format="value(name)")

gcloud projects create ${PROJECT}
gcloud beta billing projects link ${PROJECT} --billing-account=${BILLING}

# Enable Sheets API
gcloud services enable sheets.googleapis.com --project=${PROJECT}

# Create Service Account & Key
ROBOT="sheeter"

gcloud iam service-accounts create ${ROBOT} \
--project=${PROJECT}

EMAIL=${ROBOT}@${PROJECT}.iam.gserviceaccount.com

gcloud iam service-accounts keys create  ./${ROBOT}.json \
--iam-account=${EMAIL} \
--project=${PROJECT}

# Use Application Default Credentials
export GOOGLE_APPLICATION_CREDENTIALS=./${ROBOT}.json

# Create Python virtualenv and add Google APIs SDK
python3 -m venv venv
source venv/bin/activate
python3 -m pip install --upgrade google-api-python-client
mkdir python
cd python
touch main.py

Use the following example drawn from Google's example

import google.auth

from pprint import pprint

from googleapiclient import discovery

credentials, project = google.auth.default()

service = discovery.build("sheets", "v4", credentials=credentials)

spreadsheet_id = "[[SPREADSHEET_ID]]"

ranges = []
include_grid_data = False

request = service.spreadsheets().get(spreadsheetId=spreadsheet_id,
                                     ranges=ranges,
                                     includeGridData=include_grid_data)
response = request.execute()

pprint(response)

Replace [[SPREADSHEET_ID]] with the ID of the Sheet you'd like to use

Try running the code, it will 403:

python3 main.py

Add the value of ${EMAIL} to the Sheet's Sharing permissions

Run the code again, it will succeed:

{'properties': {'autoRecalc': 'ON_CHANGE',
                'defaultFormat': {'backgroundColor': {'blue': 1,
                                                      'green': 1,
                                                      'red': 1},
                                  'backgroundColorStyle': {...
             'properties': {'index': 1,
                            'sheetId': [[REDACTED]],
                            'sheetType': 'OBJECT',
                            'title': 'Chart'}}],
 'spreadsheetId': '[[SPREADSHEET_ID]]',
 'spreadsheetUrl': 'https://docs.google.com/spreadsheets/d/[[SPREADSHEET_ID]]/edit'}

201124 Update using Compute Engine

When you create a Compute Engine instance, you must give it the scope to access Sheets:

# Default Scopes
SCOPES="https://www.googleapis.com/auth/devstorage.read_only,\
https://www.googleapis.com/auth/logging.write,\
https://www.googleapis.com/auth/monitoring.write,\
https://www.googleapis.com/auth/servicecontrol,\
https://www.googleapis.com/auth/service.management.readonly,\
https://www.googleapis.com/auth/trace.append"

# Add Sheets
SCOPES="${SCOPES},https://www.googleapis.com/auth/spreadsheets.readonly"

gcloud beta compute instances create sheeter \
--project=${PROJECT} \
--zone=${ZONE} \
--machine-type=f1-micro \
--image-family=debian-10 \
--image-project=debian-cloud \
--scopes=${SCOPES}

You may also run the Instance under a service account of your choosing, e.g.:

gcloud beta compute instances create sheeter \
--project=${PROJECT} \
--zone=${ZONE} \
--machine-type=f1-micro \
--image-family=debian-10 \
--image-project=debian-cloud \
--scopes=${SCOPES} \
--service-account=${EMAIL}

Once the instance is created, repeat the install steps. There's no need to install a key on the instance nor GOOGLE_APPLICATION_CREDENTIALS as these are both provided by Compute Engine.

Check the service account that the Instance runs under:

curl \
--header "Metadata-Flavor: Google" \
http://metadata.google.internal/computeMetadata/v1/instance/service-accounts/

Should yield, e.g.:

${PROJECT-NUM}[e-mail]/
default/

This is the Service Account (in this case Compute Engine's default) under which your code will run. Now, run the code.

python3 main.py

这篇关于从 Google Compute Engine (python) 访问 Google Sheets API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！