Persistent Data Storage Solutions for R Shiny Applications
Using the Google Method
Shiny apps are very flexible tools. Sometimes however, we want our Shiny apps to store data in a Structured format for survey/questionnaire purposes. Common solutions to writing data in a structured way will not work in a Shiny app because they will only store data locally - think write.csv() etc.
We already have hosting for our apps in Shinyapps.io, but that means our app is now no longer stored locally, it is instead stored on a remote server. If you are a single researcher and are meeting participants face to face (think on street surveys) a local hosting route may be sufficient. If however you are part of a team, or want more flexibility in data storage, remote is the way to go.
This post will cover the most straightforward and popular method of writing remote structured data from a Shiny app to a remote server. This method will use free software and free* hosting services. The software used to store our structured data is Google Sheets (make sure you have an account and are signed up to use it). The R package that we shall be using to store our data to Sheets is called [googlesheets4] (again make sure it is installed). To connect the R code to the app to the Sheets we need to use another Google service called Google Cloud Console.
This solution is popular because we don’t need to deal with the issues of dealing with formal database systems like SQL. Google forms can easily be converted to .csv non-proprietary software for ease of sharing and uploading to our personal favourite analysis software.
First we will start with a super basic Shiny app. Remember to save your app in an appropriate directory where your master folder is named “your_app”, and save your app.R script in here! Our test app will look like this: library(shiny)
# Shiny app with fields for user input
shinyApp(
ui = fluidPage(
DT::dataTableOutput("responses", width = 300), tags$hr(),
textInput("name", "Name", ""),
checkboxInput("used_shiny", "I've built a Shiny app in R before", FALSE),
sliderInput("r_num_years", "Number of years using R", 0, 25, 2, ticks = FALSE),
actionButton("submit", "Submit")
),
server = function(input, output, session) {
}
)
At the moment this is only the shell or UI, the server logic which is where the magic happens will come later.
Setup:
First things first, we need to set up our Google Sheet. Simply create a new sheet, name it whatever suits your project and then make sure to label the headings of your given user input.
Now we have done that we want to return to our simple Shiny app. We need to add a couple of functions to our app in order for it to understand we want all user input to be sent to (write) our Google Sheet when the user hits the Submit button. This is going to require a few functions placed in the server side of the Shiny app (in reality we can remove most of these functions in a real world setting - we are adding a couple more just so you can see in real time what is happening).
# Function to save data to Google Sheets
save_data_gsheets <- function(data) {
data <- data %>% as.list() %>% data.frame() # Convert input to a data frame
sheet_append(SHEET_ID, data) # Append data to Google Sheet
}
# Load existing data from Google Sheets
load_data_gsheets <- function() {
read_sheet(SHEET_ID)
}
# Aggregate form data
formData <- reactive({
data <- sapply(fields, function(x) input[[x]])
data
})
# When submit button is clicked, save form data
observeEvent(input$submit, {
save_data_gsheets(formData())
})
# Show the previous responses in the DataTable
output$responses <- DT::renderDataTable({
input$submit
load_data_gsheets()
})
Each function will now be dissected and discussed. Firstly, our save_data_gsheets function is created to save the data input by users to our Google Sheet document. It is telling R to convert the inputs into a data frame (structured data) and then appending that dataframe to our Google Sheets. We tell it to append because we want to add a row to our existing dataframe we don’t want to re-write other rows. You may notice that sheet_append is appending something called SHEET_ID. This is something we define outside the Shiny app that is the url for our Google Sheets. We do NOT want others to know this id, this is where all our user data is stored.
Our formData function works in tandem with the prior function by providing our data frame with form or structure. It applies the ‘fields’ that we define outside the shiny app (this tells us what our variable names are):
fields <- c("name", "used_shiny", "r_num_years")
Combined these two functions are used in this:
# When submit button is clicked, save form data
observeEvent(input$submit, {
save_data_gsheets(formData())
})
This piece of code is telling our app that when the submit button is pressed by a user, the save_data_gsheets function is run using the logic formed by our formData function. At its core this is all we need to store data in a Shiny app.
However for this first run through we want to make sure what we are doing is right. We want to see if user data is being stored. For that we use the function:
# Load existing data from Google Sheets
load_data_gsheets <- function() {
read_sheet(SHEET_ID)
}
This function loads the Google Sheet in the Shiny app. Combined with this:
# Show the previous responses in the DataTable
output$responses <- DT::renderDataTable({
input$submit
load_data_gsheets()
})
Set of R code, the Google Sheet is rendered appropriately within the Shiny app environment.
This is unfortunately not the last step. If we were to run this app we would quickly run into errors. Trying to edit a Google Form from someone’s account (even if it is your own) is not something we can just do. We require some form of authentication. This would be really easy if only we wanted to edit the sheet but we want all users to ‘edit’.
To authenticate all user data being edited in our constructed Google Sheet we need to create an account on Google Cloud Console. The account can be created easily enough, though you will need to provide credit card authentication (this is a free service - you will not be charged!). After you have done this, go to the navigation menu, go to ‘IAM and Admin’ then Service Account.
You need to now create a Service Account and assign it a name (this is so Google Sheets has perms). You can leave the Service Account access blank. Under keys for your new account, you need to create a new key. Make sure this key is in JSON format! Download this JSON file.
Open the JSON file - if you don’t have a dedicated reader, Rstudio works fine. You want to copy the client email from this document and then head back over to your Google Sheet tab. From here we want to do two things, first go to Share and change general access to anyone with the link to editor. Secondly, add people by pasting that email you copied and send. After this head back over to your Google Cloud tab and enable Sheet perms.
Now we want to head over to the directory where our Shiny app is stored. We want to create a folder called “.secrets”. We name it that because no one except you should ever see the details inside! Open that folder and then paste your JSON file into it. Rename that file something simple like “service-account.json”. We do this because we need to paste this in our Shiny file and a long filename just doesn’t look nice.
This should be us all done, and our updated Shiny code should look like this:library(shiny)
library(googlesheets4)
library(DT)
# Authenticate using the service account JSON key
gs4_auth(path = ".secrets/service-account.json")
# Define the fields to save from the form
fields <- c("name", "used_shiny", "r_num_years")
SHEET_ID <- "https://docs.google.com/spreadsheets/inserthere"
# Shiny app with fields for user input
shinyApp(
ui = fluidPage(
DT::dataTableOutput("responses", width = 300), tags$hr(),
textInput("name", "Name", ""),
checkboxInput("used_shiny", "I've built a Shiny app in R before", FALSE),
sliderInput("r_num_years", "Number of years using R", 0, 25, 2, ticks = FALSE),
actionButton("submit", "Submit")
),
server = function(input, output, session) {
# Function to save data to Google Sheets
save_data_gsheets <- function(data) {
data <- data %>% as.list() %>% data.frame() # Convert input to a data frame
sheet_append(SHEET_ID, data) # Append data to Google Sheet
}
# Load existing data from Google Sheets
load_data_gsheets <- function() {
read_sheet(SHEET_ID)
}
# Aggregate form data
formData <- reactive({
data <- sapply(fields, function(x) input[[x]])
data
})
# When submit button is clicked, save form data
observeEvent(input$submit, {
save_data_gsheets(formData())
})
# Show the previous responses in the DataTable
output$responses <- DT::renderDataTable({
input$submit
load_data_gsheets()
})
}
)
The last thing we need to do is deploy our app. We do that through the publish button when we locally test run our app or by pasting the following code in the console (only use this method if you run into issues with publish button. This sometimes occurs because Shinyapps.io things you are trying to upload a quatro doc).
rsconnect::deployApp( appDir = "directory", appPrimaryDoc = "appname.R", appFiles = c("testapp.R", ".secrets/") )