Just discovered the googlesheets package and find it very helpful thus far. I would now like to be able to replace all or a subset of the contents in an existing sheet.
Example:
> library(googlesheets)
> set.seed(10)
> test1 <- data.frame(matrix(rnorm(10),nrow = 5))
> test1
X1 X2
1 0.01874617 0.3897943
2 -0.18425254 -1.2080762
3 -1.37133055 -0.3636760
4 -0.59916772 -1.6266727
5 0.29454513 -0.2564784
> gs_new("foo_sheet", input = test1, trim = TRUE)
This creates a new sheet as expected. Let's say that we then need to update the sheet (this data is used for a shinyapps.io hosted shiny app, and I would prefer to not have to redeploy the app in order to change sheet references).
> test1$X2 <- NULL
> test1
X1
1 0.01874617
2 -0.18425254
3 -1.37133055
4 -0.59916772
5 0.29454513
I tried to simply overwrite with gs_new() but run into the following warning message:
> gs_new("foo_sheet", input = test1, trim = TRUE)
Warning message:
At least one sheet matching "foo_sheet" already exists, so you may
need to identify by key, not title, in future.
This results in a new sheet foo_sheet being created with a new key, but does not replace the existing sheet and will therefore produce a key error if we try to register the updated sheet with
gs_title("foo_sheet")
Error in gs_lookup(., "sheet_title", verbose) :
"foo_sheet" matches sheet_title for multiple sheets returned by gs_ls() (which should reflect user's Google Sheets home screen). Suggest you identify this sheet by unique key instead.
This means that if we later try to access the new sheet foo_sheet with gs_read("foo_sheet"), the API will return the original sheet, rather than the new one.
> df <- gs_read("foo_sheet")
> df
X1 X2
1 0.01874617 0.3897943
2 -0.18425254 -1.2080762
3 -1.37133055 -0.3636760
4 -0.59916772 -1.6266727
5 0.29454513 -0.2564784
It is my understanding that one possible solution could be to first delete the sheet with gs_delete("test1") and then create a new one. Alternatively one could perhaps empty cells with gs_edit_cells(), but was hoping for some form of overwrite function.
Thanks in advance!
I find that the edit cells function is a good workaround:
gs_edit_cells(ss = "foo_sheet", ws = "worksheet name", input = test1, anchor = "A1" trim = TRUE, col_names = TRUE)
By anchoring the data to the upper left corner, you can effectively overwrite all other data. The trim function will eliminate all cells that are not to be updated.