Connecting Shiny with Google Drive & Sheets

Setting up an Application

As a sample application, consider the use of my package, shinysurveys, which provides easy-to-use, minimalistic code for creating and deploying surveys with shiny. The following code will generate a shiny application that asks for your favorite food and your name.

Those who have used shinysurveys before know that a “Submit” button is built in to the package, but no actions are defined. So, when a user completes the survey, the app designer can specify what happens upon submission. In this case, we want to save the data to Google Drive. First, we will store it as a data frame response_data. This can be done with the getSurveyData() function from shinysurveys, which aggregates all responses in a tidy format. The code for this is below:

# Load packages
library(shiny)
library(shinysurveys)

# Define questions in the format of a shinysurvey
survey_questions <- data.frame(
  question = c("What is your favorite food?",
               "What's your name?"),
  option = NA,
  input_type = "text",
  input_id = c("favorite_food", "name"),
  dependence = NA,
  dependence_value = NA,
  required = c(TRUE, FALSE)
)

# Define shiny UI
ui <- fluidPage(
  surveyOutput(survey_questions,
               survey_title = "Hello, World!",
               survey_description = "A demo survey")
)

# Define shiny server
server <- function(input, output, session) {
  renderSurvey()
  
  observeEvent(input$submit, {
    response_data <- getSurveyData()
  })
}

# Run the shiny application
shinyApp(ui, server)

When run in the browser, this looks like:

Authenticating Google Drive

In order to save the data to Google Drive, we must use the package googledrive. In this example, I will save the data to Google Sheets, specifically, with the package googlesheets4. Under the hood, these packages use gargle to handle user authentication. As mentioned, we want to use a cached, non-interactive token for shiny applications to connect to our Google Drive account. With the goal of deploying this on shinyapps.io, we need to set some gargle options. I would recommend running this in your “app.R” file.

options(
  # whenever there is one account token found, use the cached token
  gargle_oauth_email = TRUE,
  # specify auth tokens should be stored in a hidden directory ".secrets"
  gargle_oauth_cache = "your-app-folder-name/.secrets"
)

With these options set, run the commands googledrive::drive_auth() and googlesheets4::gs4_auth() in order to login and cache your auth token for use with your deployed shiny application. And voila! You should have a hidden folder “.secrets” that contains your token file. To check, you can use list.files("your-app-folder-name/.secrets/").

Note that the “.secrets” folder contains sensitive information and should NOT be committed to version control systems such as GitHub. To avoid this, you can add it to your .gitignore file manually or as follows: use_git_ignore("your-app-folder-name/.secrets/").

For more detailed documentation on caching auth tokens, please see this vignette.

Setting up a Google Sheet

In order to save the user data to Google Sheets, we must first create a sheet. This can be done manually in the browser or programmatically as follows:

# Run once on set up
googlesheets4::gs4_create(name = "your-sheet-name", 
           # Create a sheet called main for all data to 
           # go to the same place
           sheets = "main")

Writing to your Google Sheet

We already created a Google Sheet and now we need to write our survey data to it. We need a sheet id to do this, accessible as follows:

# Get the ID of the sheet for writing programmatically
# This should be placed at the top of your shiny app
sheet_id <- googledrive::drive_get("your-sheet-name")$id

We now modify our server to read our sheet data and check to see whether it contains responses. If not, we will write the first entry to set up the column names appropriately. If it has data, we will simply append responses to it.

# Define shiny server
server <- function(input, output, session) {
  renderSurvey()
  
  observeEvent(input$submit, {
    response_data <- getSurveyData()
    
    # Read our sheet
    values <- read_sheet(ss = sheet_id, 
                         sheet = "main")
    
    # Check to see if our sheet has any existing data.
    # If not, let's write to it and set up column names. 
    # Otherwise, let's append to it.
    
    if (nrow(values) == 0) {
      sheet_write(data = response_data,
                  ss = sheet_id,
                  sheet = "main")
    } else {
      sheet_append(data = response_data,
                   ss = sheet_id,
                   sheet = "main")
    }
    
  })
  
}

Excluding the sheet creation function, which is only run once, our full shiny application is as follows:

# Load packages
library(shiny)
library(shinysurveys)
library(googledrive)
library(googlesheets4)

options(
  # whenever there is one account token found, use the cached token
  gargle_oauth_email = TRUE,
  # specify auth tokens should be stored in a hidden directory ".secrets"
  gargle_oauth_cache = "your-app-folder-name/.secrets"
)

# Get the ID of the sheet for writing programmatically
# This should be placed at the top of your shiny app
sheet_id <- drive_get("your-sheet-name")$id

# Define questions in the format of a shinysurvey
survey_questions <- data.frame(
  question = c("What is your favorite food?",
               "What's your name?"),
  option = NA,
  input_type = "text",
  input_id = c("favorite_food", "name"),
  dependence = NA,
  dependence_value = NA,
  required = c(TRUE, FALSE)
)

# Define shiny UI
ui <- fluidPage(
  surveyOutput(survey_questions,
               survey_title = "Hello, World!",
               survey_description = "A demo survey")
)

# Define shiny server
server <- function(input, output, session) {
  renderSurvey()
  
  observeEvent(input$submit, {
    response_data <- getSurveyData()
    
    # Read our sheet
    values <- read_sheet(ss = sheet_id, 
                         sheet = "main")
    
    # Check to see if our sheet has any existing data.
    # If not, let's write to it and set up column names. 
    # Otherwise, let's append to it.
    
    if (nrow(values) == 0) {
      sheet_write(data = response_data,
                  ss = sheet_id,
                  sheet = "main")
    } else {
      sheet_append(data = response_data,
                   ss = sheet_id,
                   sheet = "main")
    }
    
  })
  
}

# Run the shiny application
shinyApp(ui, server)