Connect to googlesheets via shiny in R with googlesheets4

Question

I'm trying to use an updated version of this example to connect to a private googlesheet via shiny, and deploy this app on the shinyapps.io server. The user is not required to authenticate to a google account as the app uses a specified pre-existing googlesheet.

I've followed this example (partly copied here), attempting to save the token to my shiny app:

# previous googlesheets package version:
shiny_token <- gs_auth() # authenticate w/ your desired Google identity here
saveRDS(shiny_token, "shiny_app_token.rds")

but tried to update it to googlesheets4, like this:

ss <- gs4_get("MY GOOGLE DOC URL") # do the authentication once, manually.
ss
gs4_has_token() # check that the token exists

# get token
ss_token <- gs4_token()
# save the token
save(ss_token, file = "APP PATH ... /data/tk.rdata")

Then in the app, I have placed this code outside the shinyApp() function.

load("data/tk.rdata")

googlesheets4::gs4_auth(token = ss_token, use_oob = T)

In the app, I connect to a google doc from the app, using a hardcoded id obtained from ss$spreadsheet_id above. The app works locally.

After attempting to deploy the app to the server I get the error "...Can't get google credentials. Are you running googlesheets4 in a non-interactive session?... etc" I thought that the token would contain sufficient information for this.

I'd be grateful if anyone can point me to a guide to setting this up, and also comment on whether this approach (saving a token on the shinyapps.io) is safe?

I've looked at other examples, but it seems most are for the previous version of googlesheets

Answer 1

Just follow the instructions in this link:

# designate project-specific cache
options(gargle_oauth_cache = ".secrets")
# check the value of the option, if you like
gargle::gargle_oauth_cache()
# trigger auth on purpose to store a token in the specified cache
# a broswer will be opened
googlesheets4::sheets_auth()
# see your token file in the cache, if you like
list.files(".secrets/")
# sheets reauth with specified token and email address
sheets_auth(
 cache = ".secrets",
 email = "youremail"
 )

Answer 2

On 21-Jul-2021 googlesheets4 deprecated some of its function when releasing v1.0.0.

I have updated volfi's answer to work with googlesheets4 v1.0.0.
It also works when deploying to shinyapps.io.

Set up non-interactive authentication

library(googlesheets4)
    
# Set authentication token to be stored in a folder called `.secrets`
options(gargle_oauth_cache = ".secrets")

# Authenticate manually
gs4_auth()

# If successful, the previous step stores a token file.
# Check that a file has been created with:
list.files(".secrets/")

# Check that the non-interactive authentication works by first deauthorizing:
gs4_deauth()

# Authenticate using token. If no browser opens, the authentication works.
gs4_auth(cache = ".secrets", email = "[email protected]")

Example - add data to Gooogle Sheet

Create a Google Sheet on Google Sheets and copy the sheet's url.

library(googlesheets4)
gs4_auth(cache=".secrets", email="[email protected]")

ss <- gs4_get("https://docs.google.com/path/to/your/sheet")
sheet_append(ss, data.frame(time=Sys.time()))

If deploying your app to shinyapps.io make sure to deploy the file in the .secrets folder.

Answer 3

I am posting here because I started from this thread on this journey, and want to share what finally worked after many hours of having a go, reading gargle, googledrive, and googlesheets4 documentation and oh so many other posts on this issue.

1. I first used the googlesheets4 method gs4_auth() to obtain a credential and stored it in a .secrets folder. As described in this thread and here. This worked on my desktop and I was excited. It did not work on shinyapps.io or on my Ubuntu 18.4 instance of shiny-server that I have on an AWS EC2 instance. The error was something like this:

"Error in ... : Can't get Google credentials.Are you running googledrive in a non-interactive session? Consider: drive_deauth() to prevent the attempt to get credentials. Call drive_auth() directly with all necessary specifics."

2. Then I tried an approach starting from here and taking me to here Somehow this did work on shinyapps.io but still not on my Ubuntu shiny server.

3. This worked: I pursued a Google service account approach as described here and created a project, then a service account for the project, added Google Sheets API to the project, then downloaded a key as a JSON file. I then used at the top of my app_server.R file googlesheets4::gs4_auth(path = './<path to hidden JSON file folder I called .token>/.token/<JSON key file>.json'). This still did not work until the final step that is not clearly explained almost anywhere I looked which is to go to the Google sheet in question, and "share" it with the client_email email address from the JSON key file, giving it editor permissions, in my case. This was finally well explained in this random article: https://robocorp.com/docs/development-guide/google-sheets/interacting-with-google-sheets

Finally read and write access for my app from shiny server on my AWS server instance. I really hope someone finds this useful.