googlesheets R package, troubleshooting oauth authentication and token storage

Some troubleshooting tips for using the googlesheets R package to pull data from a work-hosted Google drive for a web application that will be run in a non-interactive computing environment follow.

Issue: after attempting to authorize the connection to Google sheets with the googlesheets R package, the following error is returned:

Error in function_list[[k]](value) : Forbidden (HTTP 403).

One theory behind the error is that the Drive API must be enabled in addition to the sheets API (https://github.com/jennybc/googlesheets/issue/288).

Unfortunately, for users attempting to access a spreadsheet at work, requesting a change to these settings may not be possible.

The googlesheets package is still usable to retrieve spreadsheet data, however.

You’ll need to authorize googlesheets, most likely by creating a new client_key and secret in the google developers console, and then referencing those values in the authentication call within R. I wrapped the call with a try statement, but you could also more elegantly handle this specific (and expected) 403 error. Left as an exercise for the reader.

try(gs_auth(new_user = TRUE,
  key = "your_key",
  secret = "not_telling ;-)"
))

Then, invisibly retrieve the token in force using the gd_token function and write to the file of your choice for later use.

This will allow you to avoid the OAuth authentication dance (which is rather annoying) and could be difficult to work around if attempting to use this package in a non-interactive environment.

gd_token() %>% saveRDS(., file="myRDSToken.rds")

You should only need to run the lines above once. Then in future usage of the googlesheets package (make sure the RDSToken file is saved in the same directory as your project!), you’ll be able to authenticate with the following:

try(gs_auth(token = "myRDSToken.rds"))

Again, error handling can be improved to specifically catch the 403 error.