3

I've got googlesheets4 working in a shinyapps.io with the following code:

  gs4_auth(
email = "me@email.com",
path = NULL,
scopes = "https://www.googleapis.com/auth/drive",
cache = "path_to_cache",
use_oob = FALSE,
token = NULL)

I run this locally, which requires initial browser authentication and downloads a file of some sort.

As long as I upload that file with my app to shinyapps.io, then it works (i.e. refreshes the token whenever it needs).

However, as I understand it, this is using googlesheets4 own Google API settings, which were set up to make it easy for everyone to use.

The disadvantage is that, since a lot of people are sharing this API, they sometimes (myself included) hit the data limits and get a 429 RESOURCE EXHAUSTED error. This is discussed here.

OK, so I've followed the instructions here and here and added the following code BEFORE the auth chunk already provided:

if (interactive()){
  # Desktop Client ID
  google_app <- httr::oauth_app(
    "my-awesome-google-api-wrapping-package",
    key = "mykey_for_desktop_app",
    secret = "mysecret"
  )
}else{
  # Web Client ID
  google_app <- httr::oauth_app(
    "my-awesome-google-api-wrapping-package",
    key = "mykey_for_web_app",
    secret = "mysecret"
  )
}

# API key
google_key <- "My-API-KEY"
gs4_auth_configure(app = google_app, api_key = google_key)
# Also configure google drive to use my API
drive_auth_configure(app = google_app, api_key = google_key)

So this seems to work locally (e.g. in RStudio) and I can see activity on my Google Cloud API dashboard.

However, whilst this works for a short period of time (e.g. 10 mins), even when uploaded to shinyapps.io, the auto-refresh seems to fail because I soon get the dreaded:

"Can't get Google credentials. Are you running googlesheets4 in a non-interactive session?"

Is anyone able to point me towards what I'm doing wrong?

Again - it works fine as long as I'm not trying to use my own API settings (the second code chunk).

Jimbo Mahoney
  • 220
  • 2
  • 9
  • OK, as I try and fix this, I'm going to make some notes about others who have solved this issue: This was reported to help: https://gargle.r-lib.org/articles/non-interactive-auth.html#project-level-oauth-cache - i.e. specifically the project-level section. – Jimbo Mahoney Nov 29 '20 at 16:32
  • ... and this... https://community.rstudio.com/t/shinyapps-io-googlesheet4-cant-get-google-credentials/59079 – Jimbo Mahoney Nov 29 '20 at 22:47

2 Answers2

2

OK, pretty sure I've got this working...

It was the YouTube video here that really helped, and made this more clear.

All I need is a Service Account, which seems to generate a json file that I can upload with my app.

i.e. at around 1:03 in the video shows the creation of this service account, then adding that e-mail address (of the Service Account) to the Google Sheet(s) I want to access, this means I can download (using GoogleDrive) and write (using GoogleSheets).

The crazy part is that all I need to put in my code is the following:

drive_auth(path = ".secrets/client_secret.json")
gs4_auth(path = ".secrets/client_secret.json")

i.e. those two lines (plus the downloaded json file for the Service Account) replace ALL the code I posted in my OP!

Jimbo Mahoney
  • 220
  • 2
  • 9
0

If anyone is reading this, I was struggling with the last steps of Jimbo's (excellent) answer, i.e. how to upload the local json file to shinyapps.io.

My working solution : I created a subfolder inside the shiny app folder, next to the app.r file, called "secrets". I placed the json file there. I made sure to set my working directory to the shiny app when testing everything locally. (Note : don't include the setwd() code in your shiny app code). I'm not sure if this exposes the json file somehow, but it'll have to do.

When publishing to shinyapps, I checked all boxes suggested by Rstudio to upload the whole contents of the folder (app.r file, subfolder + json file in subfolder). I used the following path in the app.r file:

drive_auth(path = "secret/clientsecret.json")
gs4_auth(path = "secret/clientsecret.json")
malcolmSH
  • 3
  • 3