Server-to-server auth for google sheets API

0
votes

I have a little program running on a bunch of raspberry Pi's that I want to upload values to a google sheet. These things will run headless and without UI. The data is not sensitive. I chose google sheets because it should be easy for me to play with the data and save time. However, I'm having trouble with auth.

If you follow this quickstart it includes auth examples: https://developers.google.com/sheets/api/quickstart/python it takes you to an auth dialog.

auth options

However, all of the options seem to require a 'head' or UI interaction. Even Web Server assumes you can show an oauth flow to the user in the browser. I cannot do this, it's a script running on a pi with no monitor.

Is there a way to setup a true, long-term oauth flow for a headless server to authenticate and write to one of my sheets?

1
authenticationgoogle-sheetsoauthgoogle-apigoogle-oauth
In your situation, how about using the service account? When the service account is used, it is not required to authorize using the browser. So I thought that this might be suitable. Ref For example, a sample script of python using the service account can be seen at this thread. If this was not the direction you expect, I apologize. – Tanaike
This sounds like the right direction... any idea what 'role' I should pick for thus service account? ¯_(ツ)_/¯ – ConfusedNoob
Tried this setup with no roles but get "403 The caller does not have permission" - cant see any roles that seem to map to gsheets? – ConfusedNoob
Thank you for replying. I apologize for my poor English skill. Unfortunately, I cannot replicate your situation from our replying. For example, as a test, when only the service account name is set at "Service account details" and push "Done" button and create new key as JSON, the sample script works. I apologize for this. But I would like to support you. So can you provide the detail flow for replicating your issue and your current script? By this, I would like to try to replicate your issue. – Tanaike

1 Answers

0
votes

This post walked me through the key steps successfully: https://denisluiz.medium.com/python-with-google-sheets-service-account-step-by-step-8f74c26ed28e

1/ Create a service account 2/ Share the spreadsheet with the service account e-mail (a long generated e-mail address you can access in the credentials.json file)