Authentication issue accessing Google Sheets Python API via cloud functions

Question

I am trying to access a Google sheet using a cloud function and the sheets API in python, but get a 403 permissions error. I have created a service account, exported the key (included in the cloud function zip I am uploading) and have given this service account the API scopes necessary to access sheets (I've tried both https://www.googleapis.com/auth/spreadsheets and https://www.googleapis.com/auth/drive). Here is the code:

import httplib2
from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials

def main(request):
  scope = ['https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/drive']

  credentials = ServiceAccountCredentials.from_json_keyfile_name('client_secrets.json', scope)

  service = build('sheets', 'v4', http=credentials.authorize(httplib2.Http()))

  spreadsheet_id = 'id-of-the-google-sheet'

  range = 'Sheet1!A:D'

  response = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range).execute()

  cols = response.get('values', [])[0]
  return cols[0]

requirements.txt:

google-api-python-client
oauth2client
google-auth-httplib2
google-auth

If I explicitly share the Google sheet with the service account, the authentication does work and I can access the sheet data. The question is why doesn't this work by applying the proper API scopes as the Google accounts admin?

Where is client_secrets.json coming from? The credentials used here should be derived from the credentials.json file you get when enabling the Google Sheets API for your project. See developers.google.com/sheets/api/quickstart/python for an example. — Dustin Ingram

Edo Akse Edo Akse · Accepted Answer · 2019-04-19T09:17:22

Google Drive permissions are on top of scopes. Scopes are needed for authentication of the request, but Drive permissions (and Sheets by extension) are what determines who has access to which file. If the calling account (the serviceaccount in this case) doesn't have access to the Sheet in question you'll get the 403 you've been getting.

Setting up the scopes in the Admin Console doesn't have direct correlation to the permissions.

One thing to note as well, if you have documents shared to everyone in the G Suite account by default, you could use Domain Wide Delegation (1) and using the serviceaccount impersonate a user (like the superadmin) inside the G Suite domain. You wouldn't need to share each document with the serviceaccount itself.

(1) This one is for the Admin SDK, but this is the article that contains the best example. You're interested in the credentials object there. You can also create the object with a .json IIRC.

Authentication issue accessing Google Sheets Python API via cloud functions

1 Answers