How can I read a Google spreadsheet in a Team Driv

Question

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?

Answer 1

It's been issued and currently there's no solution for this problem yet. Workaround work : move back to your own drive or create one shared gmail account that accessible for all of your team, then work collaborately with that account.

Answer 2

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:

https://github.com/jennybc/googlesheets/issues/327

https://github.com/jennybc/googlesheets/pull/318