Google service account OK but sheets api throws error

I’ve been trying to connect to the our workspace account to run the sheets tutorial.

Initially I wanted to use oAuth2. But n8n is running in docker on a Linux server inside our network. When I try to use oAuth2 it complains because the callback uri http://localhost:5678/rest/oauth2-credential/callback isn’t found.

So I switched to using a service account and can connect OK in the credentials screen.

However when I come to try to read the test sheet I get a 403

{"message":"403 - {\"error\":{\"code\":403,\"message\":\"The caller does not have permission\",\"status\":\"PERMISSION_DENIED\"}}","name":"Error","stack":"Error: Request failed with status code 403\n at createError (/usr/local/lib/node_modules/n8n/node_modules/axios/lib/core/createError.js:16:15)\n at settle (/usr/local/lib/node_modules/n8n/node_modules/axios/lib/core/settle.js:17:12)\n at IncomingMessage.handleStreamEnd (/usr/local/lib/node_modules/n8n/node_modules/axios/lib/adapters/http.js:269:11)\n at IncomingMessage.emit (events.js:327:22)\n at endReadableNT (internal/streams/readable.js:1327:12)\n at processTicksAndRejections (internal/process/task_queues.js:80:21)"}

The sheet is set as shareable by all in our domain.

So I tried to change the service account credentials to impersonate my user and got a different error as follows:

Couldn’t connect with these settings

Retry

Private key validation failed: 401 - {"error":"unauthorized_client","error_description":"Client is unauthorized to retrieve access tokens using this method, or client not authorized for any of the scopes requested."}

Kinda going around in circles so hope someone can help.

TIA

M

Heey @MartynShiner,

Welcome to the community :cake:

Service accounts with Google can be a bit tricky, As long as the service account being used has access to the sheet you are using it should be all good (you need to use the IAM email account that gets created and share the sheet with it). If you have rules in place where only internal users can access a sheet from a specific drive things start to become a bit of a nightmare and you need to add the service account to the org in the Google Workplace admin console then wait up to 24 hours and even then I have found it doesn’t always work and I have had to resort to opening support cases with Google.

It might be easier to use OAuth, It would be a case of adding the WEBHOOK_URL environment variable and setting it to a URL that resolves to your Docker instance then doing a restart / rebuild of the container so it can pick up the change from there you will be good to go. The domain doesn’t need to be externally accessible so you could use an internal domain with no problems.

Jon

Thanks for the quick response.

OK - I’ll give that a go using the IAM account on the sheet. Useful to know about the service account gotchas so maybe the oAuth route will work better for us.

Jon

Success - I shared the sheet with the service account and BINGO!!! it worked OK. Thank you so much for the solution.

Is there a way of reaching out to get the documentation updated to reflect this requirement as an additional step when using service accounts?

Thanks
M

1 Like

Hey @MartynShiner,

That is not a bad idea, I will have a look later this week and get it added as I suspect it will cause confusion for others when using service accoutns as well.

:+1: thanks Jon

M

1 Like

In my case I need to create a new spreadsheet. For that one of the 3 below scopes are required.

  • https://www.googleapis.com/auth/drive
  • https://www.googleapis.com/auth/drive.file
  • https://www.googleapis.com/auth/spreadsheets

When I’m trying to authenticate, the below error pops up.

{“error”:“unauthorized_client”,“error_description”:“Client is unauthorized to retrieve access tokens using this method, or client not authorized for any of the scopes requested.”}

The error is only while using sheets API, otherwise it’s working fine with directory/users/groups/delegations etc.

Hope you can help me with this.
Thanks

Hey @Kartik_Ayikkathil,

Welcome to the community :raised_hands:

Can you create a new topic and complete the template so we can see what you are doing?

1 Like