2

I am writing a Python 3.7 script that needs to read data from a Google Spreadsheet.

The spreadsheet in question belongs to an Organization that my work Google Account is part of: let's just call it "Organization". The spreadsheet permissions are set as "Anyone at Organization with the link can view". This detail has been preventing my application from working.

I went to the credentials dashboard at https://console.cloud.google.com/apis/credentials while being authenticated with my account in Organization, and created a Service Account Key. Domain Wide Delegation is allowed, as per Using New Google API Console project getting unauthorized_client ,Client is unauthorized to retrieve access tokens using this method. I downloaded the JSON keyfile to /path/to/service_account_key.json.

Below I document my attempt with the gspread client library - https://github.com/burnash/gspread - however I had the exact same problem using google-api-python-client 1.7.4:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly']
credentials = ServiceAccountCredentials.from_json_keyfile_name('/path/to/service_account_key.json', scopes)
gc = gspread.authorize(credentials)
# spreadhseet ID below obfuscated, it's actually the one you get from its URL
sheet = gc.open_by_key('12345-abcdef')

gspread's response has the same HTTP code and message as the plain Google API v4:

gspread.exceptions.APIError: {
  "error": {
    "code": 403,
    "message": "The caller does not have permission",
    "status": "PERMISSION_DENIED"
  }
}

However if I change the permissions on the spreadsheet (which I won't be allowed to do on the actual one) to "Anyone with the link can view", thus removing Organization", everything works!

<Spreadsheet 'Your spreadsheet' id:12345-abcdef>

My rough guess was that the service account I created did not inherit from me the membership in Organization. However I found no option for ensuring that. I even asked the domain Administrator to create the service account for me from his Admin accounts (also with Domain-wide Delegation on): nothing.

It has been said at Google service account and sheets permissions that I must explicitly share the sheet with the email address of the service account. When I did that it worked, but I also got a warning message claiming that account was not in the G Suite domain of Organization (again, how could it not be?).

Many Thanks

alexdma
  • 41
  • 1
  • 1
  • 4

1 Answers1

2

Try delegating that account to a specific user of your organization.

it goes as

from oauth2client.service_account import ServiceAccountCredentials
from httplib2 import Http

scope = [
            'https://www.googleapis.com/auth/drive',
            'https://www.googleapis.com/auth/spreadsheets.readonly',
            'https://www.googleapis.com/auth/spreadsheets'
        ]

creds = ServiceAccountCredentials.from_json_keyfile_dict('/path_to_service_account_json_key', scope)

delegated = creds.create_delegated('anyuser@org_domain.com')
delegated_http = delegated.authorize(Http())
spreadsheetservice = gspread.authorize(delegated)

This would make the service account delegated as the user. now you may simply share that very sheet to the above mentioned user (anyuser@org_domain.com) or pass the sheet's owner email dynamically.

prince
  • 143
  • 7