Non-interactive user tokens with googlesheets4

This repository contains an example of an R Markdown document that uses googlesheets4 to read from a private Google Sheet and is deployed to RStudio Connect.

The path of least resistance for Google auth is to sit back and respond to some interactive prompts, but this won’t work for something that is deployed to a headless machine. You have to do some advance planning to provide your deployed product with a token.

The gargle vignette Non-interactive auth is the definitive document for how to do this. The gargle package handles auth for several packages, such as bigrquery, googledrive, gmailr, and googlesheets4.

This repo provides a detailed example for the scenario where you are using an OAuth2 user token for a product deployed on RStudio Connect (see vignette section Project-level OAuth cache from which this was adapted). Note that service account tokens are the preferred strategy for a deployed product, but sometimes there are reasons to use a user token.

Authenticating

Load the googlesheets4 package.

library(googlesheets4)

By default, gargle uses a central token store, outside of the project, which isn’t going to work for us. Instead we specify a project level directory .secrets which will contain our Google token. We will set the gargle_oauth_cache option to refer to this .secrets directory. We can check where the token will be cached with gargle::gargle_oauth_cache().

# designate project-specific cache
options(gargle_oauth_cache = ".secrets")

# check the value of the option, if you like
gargle::gargle_oauth_cache()

Next we will have to perform the interactive authentication just once. Doing this will generate the token and store it for us. You will be required to select an email account to authenticate with.

# trigger auth on purpose --> store a token in the specified cache
# a broswer will be opened
googlesheets4::sheets_auth()

Now that you have completed the authentication and returned to R, we can double check that the token was cached in .secrets.

# see your token file in the cache, if you like
list.files(".secrets/")

Voila! Let’s deauthorize in our session so we can try authenticating once more, but this time without interactivity.

# deauth
sheets_deauth()

In sheets_auth() we can specify where the token is cached and which email we used to authenticate.

# sheets reauth with specified token and email address
sheets_auth(
  cache = ".secrets",
  email = "josiah@email.com"
  )

Alternatively, we can specify these in the options() and run the authentication without an arguments supplied. Let’s first deauth in our session to try authenticating again.

# deauth again
sheets_deauth()

# set values in options
options(
  gargle_oauth_cache = ".secrets",
  gargle_oauth_email = "josiah@email.com"
)

# run sheets auth
sheets_auth()

Now that we are sure that authorization works without an interactive browser session, we should migrate the options into an .Rprofile file. This way, when an R session is spun up the options will be set from session start. Meaning, if you use sheets_auth() within your R Markdown document it will knit without having to open the browser.

Deploying to Connect

In order for the deployment to RStudio Connect to work, the .secrets directory and .Rprofile files need to be in the bundle. Be sure to do this from the Add Files button. If you cannot see the files because they are hidden from Finder you cran press cmnd + shift + .. Then publish!

Other Google Platforms

This same process can be replicated for other packages that use gargle authentication. By virtue of having gargle as the central auth package for Google APIs, the workflow outlined here, and the others in the non-interactive auth vignette, can can be utilized for other google API packages (i.e. googledrive).

# authenticate with googledrive and create a token
googledrive::drive_auth()