Accessing Google Sheets file shared with service account yields 403 Forbidden error

Question

I'm having trouble processing Google Spreadsheet with a Powershell script.

I've created a Google Sheet, created a Google Cloud Project, enabled the Sheets & Drive APIs, created a service account, and added it to Google Sheet (shared).

Installed Google Cloud SDK:

Install-Module GoogleCloud
gcloud init
gcloud auth activate-service-account --key-file .\Documents-eb5947bf353a.json

but requesting a value range throws a "403 (Forbidden)" error:

$sheetidDocuments = "XXXXXXXXXXXXXXXXXXXXXXXXX"
$rangeDocuments = "Sheet1!XXX:XXX"
$requestUri = "https://sheets.googleapis.com/v4/spreadsheets/$sheetidDocuments/values/$rangeDocuments"
$result = Invoke-RestMethod -Uri $requestUri -Method Get -ContentType "application/json; charset=utf-8"

and I have no clue why. I lost hours trying to fix it, no success. Is that how service accounts work, or I have to use OAuth2 with access tokens for this?
I just want to read/write to a sheet.

Other projects that use user accounts instead of service accounts work just fine.

How are you applying the access token to the request? Forbidden normally means you are trying to access a method that requires authentication without actually being authenticated. — DaImTo
Well, that's part of my question - do I need to handle accesstokens with service accounts as well? I was thinking I only need accesstokens when using Oauth2 and not json keys. If that's the case, then no wonder the thing is not working ;) — Mara
you will always need an access token to make the request to the API. So your going to have to figure out how to do Sevice account authentication with powershell. If you do figure out how to do that please answer your question I would love to see the code for that. I have never seen anyone get that working. — DaImTo
developers.google.com/identity/protocols/OAuth2ServiceAccount — DaImTo

Edo Akse Edo Akse · Accepted Answer · 2019-04-19T09:59:34

As stated by DalmTo in the comments, you need to pass an access token to the API. You can use the command gcloud auth print-access-token to get a valid access token from the gcloud CLI.

Be sure to get a new token before doing the actual request as they have a ~60m lifespan and you can get expired token responses if they're not fresh.

$token = gcloud auth print-access-token
$headers = @{'Authorization' = 'Bearer '+$token}

$result = Invoke-RestMethod -Uri $requestUri -Method Get -ContentType "application/json; charset=utf-8" -Headers $headers

if you don't want to use the token from gcloud, you'll have to get a token yourself but this takes a bit of work. See the HTTP/REST section here for a very detailed explanation

Accessing Google Sheets file shared with service account yields 403 Forbidden error

1 Answers