Granting a service account access to my Google Sheet for googlesheets4 access

tidyverse
, ,
1

Hi all, I'm using the googledrive package by way of the googlesheets4 package, and I'd like to grant non-interactive access to a Google Sheet (e.g. so a cron job can make updates to a sheet on my behalf).
The steps I've taken, I think should work, but clearly I'm missing something here :-/

  1. In my GCS account, I created a Service Account. This returned a JSON token file, which I stored.

  2. In my Google Sheet, I granted edit permissions to the email address associated with the Service Account (which is one of the fields in the token JSON file).

  3. Now my R program looks like so:

     library(googledrive)
 library(googlesheets5)
 drive_auth(path = "/path/to/my/service/account/token/file.json")
 sheets_sheet_add("id-of-my-sheet", "new worksheet name")

  4. That last call starts the OAuth2.0 dance, which is exactly what I'm trying to avoid by using the service account's token.

Any ideas for how to allow a background R process to access my Google Sheet without the OAuth2.0 dance?

2

I don't know much about this specific auth dance, but I think that these gargle vignettes might help; specifically, it sounds like you need to rig a service account token as Application Default Credentials.

https://gargle.r-lib.org/articles/non-interactive-auth.html#provide-a-service-account-token-directly (this one also kicks to the Service Account Token vignette, but it sounds like you've done that bit)

https://gargle.r-lib.org/articles/gargle-auth-in-client-package.html

3

You might need to add another line for sheets authorization as well

sheets_auth(path = "/path/to/my/service/account/token/file.json")

googlesheets4 is probably starting an OAuth2.0 dance because the sheets package doesn't see the service account token credentials, and instead is trying to create a new OAuth token for you. I have been able to use both packages successfully using this method of directly calling the service account location for both drive_auth and sheets_auth.

edited to add more detail and hopefully be more helpful.

4

Hi!
I was struggling with this issue also. I reviewed all the suggestions in Non-interactive auth, but with no luck. I tried also the Service Account Token, it did not work. This was frustrating taking into account the simple process I wanted to do (modify a googlesheet through a ShinyApp). Here is the solution I have found, it might be useful for you:

5

There's an open issue in gargle that might be relevant/you may want to weigh in on re. extending support of application default credentials

6

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.