4

I am trying to upload hundreds of googlesheets using the new R googlesheets4 package using the function gs4_create. I can successfully upload files in the root of the google drive but fail to see how I can send it inside a pre existing folder on google drive.

See the following reprex:

df <- data.frame(a=1:10,b=letters[1:10])
googlesheets4:: gs4_create(name="TEST_FOLDER/testsheet",sheets=df)

It creates a file named : "TEST_FOLDER/testsheet in the root folder.

While I want to create the file inside the TEST_FOLDER.

I know I can use write_sheet() on files pre existing inside a folder but I want to create new files, not write in pre existing files. I also know the googledrive::drive_upload() will allow me to upload csv files but I do not like the format of the csv files when they are uploaded, as they go as plain text sheets with no frozen first row. This is possible only through the googlesheets4 package. So back to my question:

How do I create a googlesheet files (in bulk) inside the TEST_FOLDER?

zx8754
  • 52,746
  • 12
  • 114
  • 209
Lazarus Thurston
  • 1,197
  • 15
  • 33

1 Answers1

2

First, you have to create a folder with drive_mkdir(name = "TEST_FOLDER") from the googledrive package. Once you created it, I would recommend you to work with the ids of the folder and the files. So, the next step to find the id would be:

folder_id <- drive_find(n_max = 10, pattern = "TEST_FOLDER")$id

*This works if you have only one folder called TEST_FOLDER in your Google Drive. If you have more than one, i would recommend you to copy/paste the id directly, or identifying the id you want before assigning to the "folder_id" object.

*If you don't want to do this step, you can also copy/paste the id directly from the Google Drive url

Once you have it, you can program a for loop in order to upload all files. For example, supposing your sheets are called sheet1, sheet2... sheet10:

a <- rep("sheet",10)
b <- 1:10
names <- paste0(a,b)

for(x in names){
   
   gs4_create(name = x, sheets = list(sheet1 = get(x)))
   
   sheet_id <- drive_find(type = "spreadsheet", n_max = 10, 
                          pattern = x)$id

   sheet_id <- drive_find(type = "spreadsheet", n_max = 10, 
                          pattern = x)$id

   drive_mv(file = as_id(sheet_id), path = as_id(folder_id))

}

NOTE: If you have too many files in your root folder of Google Drive, the mkdir function will take too much time. That's why I recommend working with ids. If you have this problem, you could create this folder manually, copy the id and assign it to the "folder_id" object.

Francesc Pons Álvarez
  • 131
  • 13