Scrape_sheet2

library(googledrive)
library(googlesheets)
library(writexl)
library(stringr)
library(dplyr)

gs_auth(token = NULL, new_user = FALSE,
key = getOption("googlesheets.client_id"),
secret = getOption("googlesheets.client_secret"),
cache = getOption("googlesheets.httr_oauth_cache"), verbose = TRUE)

mysheet <- drive_ls(type ="spreadsheet")%>%drive_reveal("path")
list_sheet <- data.frame ( sheet = character(), path = character(), id = character(), sheetname = character())
for (i in 1 : length(mysheet[[1]])){
tryCatch({
sheet <- mysheet$name[i] %>% as.character()
path <- mysheet$path[i] %>% as.character()
id <- mysheet$id[i] %>% as.character()
ss <- gs_key(mysheet$id[i])
wslist <-try(gs_ws_ls(ss))
for (j in 1 : length(wslist)){
sheetname <- wslist[j]
temp <- data.frame(sheet,path,id,sheetname)
list_sheet <- rbind(list_sheet, temp)
}
}, error = function(e) NA)
}