Non-interactive user tokens with googlesheets4

Deploying to RStudio Connect


Josiah Parry


January 13, 2020

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.


Load the googlesheets4 package.

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

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

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

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

# 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
  cache = ".secrets",
  email = ""

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

# set values in options
  gargle_oauth_cache = ".secrets",
  gargle_oauth_email = ""

# run 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

Thank you to Jenny Bryan for her help editing this!