Using GoogleSheets as a Database in R— Yeay!

4 min readNov 4, 2022

To using integration GoogleSheets as a Database in R, we can use googlesheets4 package.

One of the best ways to start collecting data with a shinyapps.io 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 Shiny. The R package googlesheets4 makes it easy to work with Google Sheets from R.

install::packages("googlesheets4")

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 — if not you'll need to log in. Then, you can read the data:

library(googlesheets4)
gs4_deauth()
url <- "https://docs.google.com/spreadsheets/d/1rJD8fVUOeQZyhsugru7kW6DgtgB82geFDwyv-kZmKpQ/edit?usp=sharing"
read_sheet(url)

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 Error: Client error: (401) UNAUTHENTICATED.

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 = gargle::gargle_oauth_email(),
  path = NULL,
  scopes = "https://www.googleapis.com/auth/spreadsheets",
  cache = gargle::gargle_oauth_cache(),
  use_oob = gargle::gargle_oob_default(),
  token = NULL
)

However, this won’t work if you want your Shiny apps to be able to access your Google Sheets. So, you need take the steps below.

1. Authorisation for Apps

First, you need to get a token and store it in a cache folder in your Shiny app directory. We’re going to call that directory .secrets. Run the following code in your console (don’t do this in Rmd). This will open up a web browser window and prompt you to choose your Google account and authorise googlesheets4. app_directory c

setwd(getwd())
gs4_auth(email = "youremail@gmail.com", cache = ".secrets")

Optionally, authorise google drive to search your drive

library(googledrive)
googledrive::drive_auth(email = "youremail@gmail.com", cache = ".secrets")

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 folder .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(email = "youremail@gmail.com", cache = ".secrets")

2. Accessing an existing sheet

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

url <- "https://docs.google.com/spreadsheets/d/1rJD8fVUOeQZyhsugru7kW6DgtgB82geFDwyv-kZmKpQ/edit?usp=sharing"
read_sheet(url)

3. Creating a new sheet

Creates an entirely new (spread)Sheet (or, in Excel-speak, workbook) using gs4_create

data <- gs4_create("name_of_your_sheets", sheets=your_data)

The sheet that you created, will automatically created on your GoogleSheet (based on you authorized account). This is the example.

```{r}
df <- data.frame(
  x = 1:3,
  y = letters[1:3]
)ss <- gs4_create("sheets-algo-1", sheets=df)
gs4_get(ss)
```

3. Checking your metadata sheet

gs4_get(ss)

You will see this output, after you run that code.

4. Writing/Appending new data

sheet_writefunction writes a data frame into a (work)sheet inside a (spread)Sheet.

If no existing Sheet is specified via ss, this function delegates to gs4_create() and the new Sheet's name is randomly generated. If that's undesirable, call gs4_create() directly to get more control.

If you don’t want to create new sheets, you can use sheet_append like this

# we will recreate the table of "other" deaths from this example Sheet
data <- gs4_example("deaths") %>%
  range_read(range = "other_data", col_types = "????DD")

# split the data into 3 pieces, which we will send separately
init_rows <- deaths[1:5, ]
second_row <- deaths[6, ]
others_row <- deaths[7:10, ]

# create a Sheet and send the first chunk of data
ss <- gs4_create("sheet-append-demo", sheets = init_rows)

# append a single row
ss %>% sheet_append(second_row)

# append multiple rows
ss %>% sheet_append(others_row)

After you run that example, you will see new sheets on your GoogleSheets

Then, you can see that the original one in your Rmd same as your data in Google Sheets

Finally, you can write and append to your GoogleSheets using R

5. Cleaning up your sheets

To cleaning up our data, we can use

gs4_find("your_sheet_name") %>%
  googledrive::drive_trash()

References

You can see how to read data with googlesheets4 in the data import cheatsheet, which also covers similar functionality in the related packages readr and readxl.