Why not just use an embedded Google Sheet in Rshiny.
Here were our assumptions:Sheets are Google’s online spreadsheet solution, which are performant tables that can be fed with large input from our database.
Sheets are editable and, therefore, similar to rhandsontableSheets are user friendly, in terms of formating, operations, as well as acceptance by non-technical usersSheets can also be manipulated outside of R/RshinyWe 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 ShinyLet’s break down our assessment and lessons learnt, point by point:Embedding a Google Sheet in ShinyThe 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 heregoogleSheet_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 AuthorizationTo 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 pageoptions(httr_oob_default=TRUE)token <- gs_auth (cache = FALSE)saveRDS(token, file = "[PATH]/gdrive_token.
rds")# authenticate via Tokengs_auth(token = "[PATH]/gdrive_token.
rds")# check if correct user accountgs_user()# browse Google Sheets#leads to errors#gs_ls()# workaround with googledrive packagelibrary("googledrive")options(httr_oob_default=TRUE)drive_auth()drive_find()3.
Registering and reading from Google SheetsA 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 titlegsheet <- gs_title("my_gsheet")# read dataload_gsheet <- gs_read(gsheet, ws="Sheet1") # as tibble# Option 2:# register a sheet(also on Team Drives)via its unique keygsheet <- gs_key("[KEYSTRING]", lookup=FALSE, visibility = "private")# read dataload_gsheet <- gs_read(gsheet, ws="Sheet1"4.
Writing to Google SheetsThere 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 slowgs_edit_cells(gsheet, ws = "Sheet1", input = mtcars, trim = FALSE, anchor = "A1")# fast, but it overwrites all settings and formatting of a sheetgs_upload(mtcars, sheet_title="my_gsheet")Summary & temporary divorce from googlesheetsWe 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.
In the meantime, we love to receive your comments, corrections, and suggestion for alternatives to work with tables in Shiny.
Henry, Johannes, Thomas from Predict42.