Overwrite worksheet using googlesheets in R

Question

I have 2 questions.

1. How do you overwrite a worksheet in an existing spreadsheet using the googlesheets package in R?

2. How do you create a new worksheet in an existing spreadsheet using the googlesheets package in R?

I could not find anything in the documentation.

Answer 1

You can overwrite data in a worksheet directly using gs_edit_cells() using the trim = TRUE option to erase whatever extra was in the sheet. As the documentation points out, use of this function and, therefore, any functions that rely on it (including gs_ws_new() if input is not NULL) will be extremely slow.

The only other option available is to construct a complete file with all the worksheets of interest (e.g. .xlsx) and use gs_upload(), which will overwrite your entire file.

Answer 2

To add a new worksheet in an existing spreadsheet:

require(googlesheets)
#first get the existing spreadsheet
existing_spreadsheet <- gs_title("title")  
#Then add the new worksheet to the existing sheet 
gs_ws_new(existing_spreadsheet
       , ws_title = "worksheet title"  #make sure it doesn't exist already
       , input = your_input #data.frame or data.table
       , trim = TRUE  #optional if you want your worksheet trimed
              )

I haven't been able to find a direct way to overwrite a worksheet in an existing spreadsheet myself. So i've had to delete the existing worksheet and add it again as a new worksheet.

#first delete the existing worksheet
existing_spreadsheet <- gs_ws_delete(existing_spreadsheet, "work sheet title you want updated")
# Then add the newworksheet with new data
gs_ws_new(existing_spreadsheet
        , ws_title = "worksheet title" 
        , input = your_new_data #data.frame or data.table
        , trim = TRUE  #optional if you want your worksheet trimed
      )