Google Sheets in R Shiny — a happy maRriage?

Google Sheets in R Shiny — a happy maRriage?

Google Sheets with Rshiny as an alternative to rhandsontable

The perfect couple could look like this: Google Sheets with great UX on the one hand and RStudio’s Shiny with its analytics flexibility on the other hand.

Are Google Sheets a viable alternative to rhandsontables?

Why bother you might ask? Why not just use DataTable or rhandsontable?

The incumbents rhandsontable and DataTables

Let’s take a step back, prior to looking at our initial question and examine our use case. Our start-up builds analytical apps for people analytics and digital marketing use cases. One of our main apps allows users to analyze, run models, and change bids to optimize their Google Ads performance.

Search Engine use case: User can input target values per product category in a table

When it comes to displaying information in tables, we are using:

1. rh andsontable , which are editable and their editability can be restricted, e.g. to a single column. Our customer can basically click a cell and directly edit it. Unfortunately, once a rhandsontable has to deal with several hundreds of rows scrolling and editing become fairly slow.
2. DataTable, which can take up much more data and handle it with ease, even when filtering a feature. However, they are not well-editable in Shiny, let alone being restrictedly editable.

So summarizing our challenge: DataTables are highly performant, but are not editable. Whereas rhandsontables are editable, but not as performant.

Let’s embed Google Sheets in Rshiny

Eureka! Why not just use an embedded Google Sheet in Rshiny.

Here were our assumptions:

1. Sheets are Google’s online spreadsheet solution, which are performant tables that can be fed with large input from our database.
2. Sheets are editable and, therefore, similar to rhandsontable
3. Sheets are user friendly, in terms of formating, operations, as well as acceptance by non-technical users
4. Sheets can also be manipulated outside of R/Rshiny

We quickly found the R package googlesheets which, as the name suggests ,enables you to work with Google Sheets in R. In essence, the packages gives you the possibility to manipulate, read and (re)write Google Sheets data in the R backend. It is well documented and there are plenty of resources to get you start working with googlesheets, such as a vignette , documentation and inspirational Shiny example apps .

Our assessment of googlesheets in Shiny

Let’s break down our assessment and lessons learnt, point by point:

1. Embedding a Google Sheet in Shiny

The process of embedding a Google Sheet in an app is fairly easy. First, we need to find the URL of our (private) Google Sheet that we want to embed. In the server section of our RShiny app, we simply embed the Sheet with an HTML Iframe tag and render it in our ui.R file.

Result:The Google Sheet renders reasonably fast.

Code snippet to embed Google Sheets:

# ====================== #
# server.R

# link your Sheet's URL string here
googleSheet_embed_link <- ["URL"]

shinyServer(function(input, output, session) {
  output$googleSheet <- renderUI({
    tags$iframe(id = "googleSheet",
            src = googleSheet_embed_link,
            width = 1024,
            height = 768,
            frameborder = 0,
            marginheight = 0)
 })
})

# ====================== #
# ui.R

# simply render your embedded sheet 
ui <- fluidPage(
  titlePanel("Embedded Google Sheet"),
    htmlOutput("googleSheet"))

2. Authentication and Authorization

To embed a Google Sheet, no authentication is required (generally). However, if we want to use the R backend to read, write and manipulate Sheet data, we need to authenticate as a permitted Google account first. This is done by using the library’s gs_auth() function.

It is also convenient to create a permanent .rds-Token and leave it on the Shiny app’s server to authenticate with. More info on managing OAuth tokens can be found here .

Now we can have a look at the available Sheets in our Google Drive with the function gs_ls() and drive_find() .

Result:At the time of writing this article, we encountered several authentication errors and bugs working with gs_auth() and gs_ls() respectively. Luckily, we were able to resolve the authentication issues with the help of others, who faced similar obstacles .

Jenny Bryan , the maintainer of the googlesheets package, explained that these problems occur because

“googlesheets has grown very long in the tooth and I am focusing attention on its successor googledrive and googlesheets4 […]”

She suggests using the googledrive package and its function drive_find() instead of gs_ls() . With this alternative, authentification worked!

# create an .rds-Token once

# out of band (oob) option enables correctly loading auth page
options(httr_oob_default=TRUE)
token <- gs_auth (cache = FALSE)
saveRDS(token, file = "[PATH]/gdrive_token.rds")

# authenticate via Token
gs_auth(token = "[PATH]/gdrive_token.rds")
# check if correct user account
gs_user()

# browse Google Sheets

#leads to errors
#gs_ls()

# workaround with googledrive package
library("googledrive")
options(httr_oob_default=TRUE)
drive_auth()
drive_find()

3. Registering and reading from Google Sheets

A Google Sheet needs to be registered first, before we can read from or write to it. Sheet files in our own Gdrive can be easily registered by using the gs_title() function or by registering the Sheet via its unique key that can be easily extracted from the URL.

Result:Once the sheet is registered, reading the data from it is simple and fast.

# Option 1:
# register a sheet in your own drive via its title
gsheet <- gs_title("my_gsheet")

# read data
load_gsheet <- gs_read(gsheet, ws="Sheet1") # as tibble

# Option 2:
# register a sheet(also on Team Drives)via its unique key
gsheet <- gs_key("[KEYSTRING]", lookup=FALSE, visibility = "private")

# read data
load_gsheet <- gs_read(gsheet, ws="Sheet1"

4. Writing to Google Sheets

There are currently two options in googlesheets to write data to a Google Sheet: gs_edit_cells() and gs_upload() .

gs_edit_cells() keeps the formatting of a Google Sheet intact, such as restricted columns, colors, cell references, etc. This would serve our use case well, given that we only want one column in the sheet to be editable and keep our other sheet settings in place, e.g. an editable column. However, gs_edit_cells() is extremely slow and we sometimes encountered errors when trying to write to a larger range of cells.

gs_upload() , on the other hand, runs very fast but it is a whole file operation. This means any formatting that we had setup in our sheet was overwritten as a consequence. It also does not seem to work with sheets located on Team Drives, since you can only target a sheet file by its title.

Result:Writing data to Google Sheets by editing cells is extremly slow and full file replace does not meet the requirements of our use case.

# write mtcars dataset to a Google Sheet

# keeps the formatting of a sheet, but it is very slow
gs_edit_cells(gsheet, ws = "Sheet1", input = mtcars, trim = FALSE, anchor = "A1")

# fast, but it overwrites all settings and formatting of a sheet
gs_upload(mtcars, sheet_title="my_gsheet")

Summary & temporary divorce from googlesheets

We see that embedding a Google Sheet in RShiny is fairly easy and so is authenticating and browsing sheet files (with a few work arounds). Registering a file was also a straightforward process and the speed at which data is read from a Google Sheet is great.

For our specific use case, however, we were not able to write large data to a Google Sheet in an acceptable time frame, while also maintaining the formatting (in particular restricting editability to one column). In addition, we would have wanted more customization options from Google’s side, for example permanently hiding the toolbar. We therefore decided to stick with rhandsontables for the time being.

This being said, we still see much opportunity in utilizing Google Sheet’s functionality and features embedded in Shiny. Most of our encountered problems can be attributed to the googlesheets package being based on an older Google Sheets API version. A new version of the package is currently in development, but it will always depend on the release and features of the current Google Sheets API.

If this new version can solve the performance issue for writing larger data sets, we would happily be the first to retry googlesheets.