Sheets API: reading data from spreadsheet with Python

Question

I am trying to retrieve data from a Google spreadsheet that is shared to my personal email address. I have a service account set up in a json file which looks like this:

{
  "type": "service_account",
  "project_id": "my-project-name",
  "private_key_id": "012345678901234567890123456789",
  "private_key": "-----BEGIN PRIVATE KEY-----\xxxxx\n-----END PRIVATE KEY-----\n",
  "client_email": "[email protected]",
  "client_id": "9876543210",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/my-name%40my-project-name.iam.gserviceaccount.com"
}

The rationale of my code (see below) is this: given a spreadsheet with an url that is shared to me (--> spreadsheet_id, sheet_id), it finds the tab name (name), and uses it to retrieve the data (data_values) which is eventually converted to pd.DataFrame.

Now my problem is, when I run this function on a publicly available online spreadsheet, it works fine, but whenever I attempt running it on a spreadsheet that I have access to, yet it is not publicly available, it fails with the following error:

Error code: 403, PERMISSION_DENIED: The request is missing a valid API key

It is as if the service account (which is linked to my Google account, hence to my personal email), doesn't have access to content that is shared to my personal account. Is this the case by design? How can I overcome this? I would highly prefer an automatic solution, rather than having to manually set the access right of each of the spreadsheets separately, to share it with my service account address, instead of my personal one.

Edit: Since then I manually added the email address of my service account to one of the files and it worked. So it seems, the service account has no access to my personal email's files, only to those that are specifically shared with the service account? Should I then always share each of the files with my service account email address too?

Code:

def get_spreadsheet_data(name, spreadsheet_id, sheet_id, service_account_json_path, scope):

    creds = ServiceAccountCredentials.from_json_keyfile_name(service_account_json_path, scope)
    service = build('sheets', 'v4', credentials=creds)
    sheets = service.spreadsheets()

    # If name is not provided, generate it from spreadsheet_id
    if not name:
        a = sheets.get(
            spreadsheetId=spreadsheet_id,
            fields='sheets(properties(index,sheetId,title))'
        ).execute()
        name = [sheet['properties']['title'] for sheet in a['sheets'] \
                if int(sheet['properties']['sheetId']) == int(sheet_id)][0]
    
    data_table = sheets.values().get(spreadsheetId=spreadsheet_id, range=name).execute()
    data_values = data_table.get('values', [])
        
    df = pd.DataFrame(data_values)
    return df

I have to apologize for my poor English skill. I cannot understand but whenever I attempt running it on a spreadsheet that I have access to, yet it is not publicly available, it fails with the following error:. Can I ask you about the detail of it? — Tanaike
– Tanaike, Commented Jan 30, 2022 at 1:21
@Tanaike the code execution stops at the line sheets.get(spreadsheetId = spreadsheet_id, fields='sheets(properties(index,sheetId,title))') and raises the above mentioned error. — lazarea
– lazarea, Commented Jan 30, 2022 at 8:51
I added an edit. I retried the above code but this time, sharing the file with my service account email. And it worked! But I don't understand why the service account cannot view my personal account's files. Isn't that supposed to be the point of setting up service accounts? — lazarea
– lazarea, Commented Jan 30, 2022 at 8:55
Thank you for replying. About I don't understand why the service account cannot view my personal account's files., the service account is a different account from your own account. I think that this is the reason for your issue. In that case, when your want to use the service account to impersonate your account, how about checking Delegating domain-wide authority to the service account. But in this case, it is required to have a Google Workspace account. If this was not useful, I apologize. — Tanaike
– Tanaike, Commented Jan 30, 2022 at 9:02

Iamblichus · Accepted Answer · 2022-01-31 08:39:47Z

2

Posting this for documentation purposes.

As Tanaike mentioned, a service account and your regular account are completely different accounts.

The service account, by itself, will only be able to access the files that have been shared with it (or created by it), not the ones shared with your regular account.

As explained in the official docs:

Typically, an application uses a service account when the application uses Google APIs to work with its own data rather than a user's data.

If you have a Workspace account, you could use the service account to act on behalf of other users in your domain (e.g. your regular account): see Delegating domain-wide authority to the service account for that. Otherwise, there's no way to access user files with your service account.

Reference:

Using OAuth 2.0 for Server to Server Applications

answered Jan 31, 2022 at 8:39

community wiki

Iamblichus

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sheets API: reading data from spreadsheet with Python

1 Answer 1

Reference:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Reference:

Comments

Your Answer

Sign up or log in

Post as a guest

Related