6 Reading and saving data

Figure 6.1: RadioTables Demo App. You can also access this app with shinyintro::app("radiotables")or view it in a separate tab with the showcase interface.

6.1 Local Data

You can read and write data from a Shiny app the same way you do from any R script. We will focus on reading data, since writing data locally can cause problems and is better done with Google Sheets.

The working directory for a Shiny app is the directory that app.R is in. I recommend keeping your data in a directory called data to keep things tidy.

# read local data
my_data <- readxl::read_xls("data/my_data.xls")

# read data on the web
countries <- readr::read_csv("https://datahub.io/core/country-list/r/data.csv")
languages <- jsonlite::read_json("https://datahub.io/core/language-codes/r/language-codes.json")

6.2 Google Sheets

One of the best ways to start collecting data with a Shiny app is with Google Sheets. This allows you to collect data to the same place from multiple servers, which might happen if you're running the app locally on more than one computer or through a service like shinyapps.io. The R package googlesheets4 makes it easy to work with Google Sheets from R.

If you just want to read data from a public Google Sheet, you don't need any authorisation. Just start your code with gs4_deauth() after you load googlesheets4 (otherwise you'll be prompted to log in). Then you can read data like this:

library(googlesheets4)
gs4_deauth()
sheet_id <- "https://docs.google.com/spreadsheets/d/1tQCYQrI4xITlPyxb9dQ-JpMDYeADovIeiZZRNHkctGA/"
read_sheet(sheet_id)

number	letter
1	A
2	B
3	C

However, even if a Google Sheet is publicly editable, you can't add data to it without authorising your account. If you try, you'll get the error below.

data <- data.frame(number = 4, letter = "D")
sheet_append(sheet_id, data)

## Error: Client error: (401) UNAUTHENTICATED
## • Request not authenticated due to missing, invalid, or expired
##   OAuth token.
## • API keys are not supported by this API. Expected OAuth2 access
##   token or other authentication credentials that assert a
##   principal. See https://cloud.google.com/docs/authentication
## 
## Error details:
## • reason: CREDENTIALS_MISSING
## • domain: googleapis.com
## • metadata.method:
##   google.apps.sheets.v4.SpreadsheetsService.BatchUpdateSpreadsheet
## • metadata.service: sheets.googleapis.com

You can authorise interactively using the following code (and your own email), which will prompt you to authorise "Tidyverse API Packages" the first time you do this.

gs4_auth(email = "debruine@gmail.com")

However, this won't work if you want your Shiny apps to be able to access your Google Sheets.

6.2.1 Authorisation for Apps

First, you need to get a token and store it in a cache folder in your app directory. We're going to call that directory .secrets. Run the following code in your console (NOT in an Rmd file). This will open up a web browser window and prompt you to choose your Google account and authorise "Tidyverse API Packages".

setwd(app_directory)
gs4_auth(email = "debruine@gmail.com", cache = ".secrets")

# optionally, authorise google drive to search your drive
# googledrive::drive_auth(email = "debruine@gmail.com", cache = ".secrets")

Prompts to choose an account, grant permissions, and confirm.

Figure 6.2: Prompts to choose an account, grant permissions, and confirm.

When you have finished, you will see a page that says something like, "Authentication complete. Please close this page and return to R." In the file pane in RStudio, you should now see a directory called .secrets in the app directory.

If you are using GitHub, you don't want to save your secret info to a public repository, so run the following code to ignore any directories called .secrets (so they will only exist on your computer and not on GitHub).

usethis::use_git_ignore(".secrets")
usethis::use_git_ignore("*/.secrets")

Now, you can include the following code at the top of your app.R script to authorise the app to read from and write to your files.

gs4_auth(cache = ".secrets", email = "debruine@gmail.com")

6.2.2 Accessing an existing sheet

If you have an existing Google Sheet, you can access it by URL.

sheet_id <- "https://docs.google.com/spreadsheets/d/1tQCYQrI4xITlPyxb9dQ-JpMDYeADovIeiZZRNHkctGA/"
data <- data.frame(number = 4, letter = "D")
sheet_append(sheet_id, data)
read_sheet(sheet_id)

number	letter
1	A
2	B
3	C
4	D

6.2.3 Make a new sheet

You can set up a new Google Sheet with code. You only need to do this once for a sheet that you will use with a Shiny app, and you will need to save the sheet ID. If you don't specify the tab name(s), the sheet will be created with one tab called "Sheet1". I recommend making only one sheet per app and saving each table in a separate tab.

id <- gs4_create("demo2", sheets = c("demographics", "questionnaire"))
id

## Spreadsheet name: demo2
##               ID: 12xl_l9Rbm09JKJE-jCfYYjuQP9CCqyoPE5vQe_8QxPg
##           Locale: en_US
##        Time zone: Europe/London
##      # of sheets: 2
## 
##  (Sheet name): (Nominal extent in rows x columns)
##  demographics: 1000 x 26
## questionnaire: 1000 x 26

Include the ID at the top of your app like this:

SHEET_ID

6.2.4 Add data

You can add an empty data structure to your sheet by specifying the data types of each column like this:

data <- data.frame(
  name = character(0),
  birthyear = integer(0),
  R_user = logical(0),
  score = double(0)
)

write_sheet(data, SHEET_ID, "demographics")
read_sheet(SHEET_ID, "demographics") %>% names()

## [1] "name"      "birthyear" "R_user"    "score"

Or you can populate the table with starting data.

data <- data.frame(
  name = "Lisa",
  birthyear = 1976L,
  R_user = TRUE,
  score = 10.2
)

write_sheet(data, SHEET_ID, "demographics")
read_sheet(SHEET_ID, "demographics")

name	birthyear	R_user	score
Lisa	1976	TRUE	10.2

Notice that birthyear is a double, not an integer. Google Sheets only have one numeric type, so both doubles and integers are coerced to doubles.

6.2.5 Appending data

Then you can append new rows of data to the sheet.

data <- data.frame(
  name = "Robbie",
  birthyear = 2007,
  R_user = FALSE,
  score = 12.1
)

sheet_append(SHEET_ID, data, "demographics")
read_sheet(SHEET_ID, "demographics")

name	birthyear	R_user	score
Lisa	1976	TRUE	10.2
Robbie	2007	FALSE	12.1

If you try to append data of a different data type, some weird things can happen. Logical values added to a numeric column are cast as 0 (1) and 1 (TRUE), while numeric values added to a logical column change the column to numeric. If you mix character and numeric values in a column, the resulting column is a column of one-item lists so that each list can have the appropriate data type. (Data frames in R cannot mix data types in the same column.)

data <- data.frame(
  name = 1,
  birthyear = FALSE,
  R_user = 0,
  score = "No"
)

sheet_append(SHEET_ID, data, "demographics")
read_sheet(SHEET_ID, "demographics")

name	birthyear	R_user	score
Lisa	1976	1	10.2
Robbie	2007	0	12.1
1	0	0	No

You must append data that has the same number and order of columns as the Google Sheet. If you send columns out of order, they will be recorded in the order you sent them, not in the order of the column names. If you send extra columns, the append will fail.

The demo app "radiotables" has a safer version of sheet_append() that you can use when you're developing on your machine. It's defined in scripts/gs4.R. This version gracefully handles data with new columns, missing columns, columns in a different order, and columns with a different data type. However, it reads the whole data sheet before deciding whether to append or overwrite the data, which can slow down your app, so is best used only during development when you're changing things a lot. If it's not running locally, it uses the original googlesheets4::sheet_append() function instead.

6.2.6 Saving data

If you mix data types in a column, the data frame returned by read_sheet() has list columns for any mixed columns. Dates can also get written in different ways that look the same when you print to the console, but are a mix of characters and doubles, so you have to convert them to strings like this before you can save as CSV.

string_data <- lapply(data, sapply, toString) %>% as.data.frame()
readr::write_csv(string_data, "data.csv")

The demo app "radiotables" has a custom function gs4_write_csv() defined in scripts/gs4.R that does the above for you.

6.3 Exercises

Read others' data

Read in data from the public google sheet at https://docs.google.com/spreadsheets/d/1QjpRZPNOOL0pfRO6IVT5WiafnyNdahsch1A03iHdv7s/. Find the sheet ID and figure out which sheet has data on US states (assign this to the object states) and which has data on Eutherian mammals (assign this to mammals).

library(googlesheets4)

gs4_deauth()

sheet_url <- "https://docs.google.com/spreadsheets/d/1QjpRZPNOOL0pfRO6IVT5WiafnyNdahsch1A03iHdv7s/"
sheet_id <- as_sheets_id(sheet_url)

states <- read_sheet(sheet_id, 1)
mammals <- read_sheet(sheet_id, 2)

Read your own data

Create a google sheet online and read its contents in R. You will need to either make it public first (click on the green Share icon in the upper right) or authorise googlesheets to access your account.

gs4_auth()
my_sheet_url <- ""
mydata <- read_sheet(my_sheet_url)

Write data

Append some data to your google sheet.

6.4 Your App

In the app you're developing, determine what data need to be saved and set up a google sheet (or local data if you're having trouble with google). Add the authorisation code to your app (see scripts/gs4.R in the radiotables demo). Write the server function to save data from your app when an action button is pressed.

5 Reactive functions

7 HTML, CSS, and JavaScript