Accessing Google Sheets API from Google Compute Engine (python)

Question

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.

DazWilkin DazWilkin · Accepted Answer · 2020-11-23T20:51:21

~~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. [email protected]) 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}[email protected]/
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

Accessing Google Sheets API from Google Compute Engine (python)

1 Answers

201124 Update w/ example

201124 Update using Compute Engine