this is probably a really simple question, but I can't seem to figure it out. We recently moved a project from a shared folder on Google Drive to a Team Drive. I used to directly access the spreadsheet from R with this code:
library(googlesheets)
mydir <- gs_ls() #authenticate to google list sheets
mydir$sheet_title
mygs <- gs_title(x = "FileName", verbose = F) #read file
mygs <- gs_read(ss = mygs, ws = "SheetName", skip=1) # get specific worksheet
But now that we moved the project, it doesn't work anymore, as the googlesheets package only works on my Google Drive directory. I've found a package for accessing my Team Drive (googledrive) and I've managed to find the file within the directory but I can't seem to figure out how to READ my specific worksheet anymore:
library(googledrive)
td <- team_drive_find()
myfile <- drive_find(team_drive = as_id(td$id[td$name=="TDriveName"]),
pattern = "FileName")
myfile <- drive_get(id = as_id(myfile$id), verbose = TRUE)
This gives me a tibble with the metadata of the spreadsheet but not the actual spreadsheet like gs_title() does. There has to be a function to do this right?
This is currently a work in progress for a new reboot of the googlesheets package, called googlesheets4, which is not yet released on CRAN.
However, you can currently achieve what you want with a combination of the googlesheets package and the googledrive package, as you eluded to above.
First, you have to get the metadata for a particular spreadsheet using drive_get, and specify the team drive, either by name or ID or URL. Then, register the spreadsheet with any of the functions for this from the googlesheets package, i.e. gs_title, gs_key, or gs_url.
The workaround here is to set the arguments lookup = FALSE and visibility = "private" in gs_key.
I prefer to use team drive IDs and spreadsheets keys, as opposed to names. You can use team_drive_find to get the IDs of your team drives.
See below:
library(dplyr) # For pipe operator, %>%
library(googledrive)
library(googlesheets)
data <-
drive_get(path = "example-googlesheet-name",
team_drive = as_id("0ABCDefGH2jK_Lm3NPA")) %>%
select(id) %>%
combine() %>%
gs_key(lookup = FALSE,
visibility = "private") %>%
gs_read_csv()
The above code reads in the example spreadsheet from a team drive, via the team drive's ID. From the metadata returned by drive_get, it selects the ID, combines it into a vector, and passes the ID to gs_key to register the spreadsheet. gs_read_csv then reads in the register spreadsheet and assigns the result to data.
See these Github issues for more information: