request for new function gs_replace()

[klittle314]

function would allow replacing contents of a worksheet while retaining the name and position in the larger sheet. Useful if updating a Google sheet in some kind of loop or repeated calls to the API.

thanks

[jennybc]

Came from discussion in #258

https://developers.google.com/drive/v2/reference/files/update

I might be able to wrap something that replaces the guts of the document on Drive but keeps existing filename, URL, etc. I suspect this will always be faster than any approach that uses a cell-aware API, i.e. Google Sheets API, no matter the version.

[diegocgaona]

Hi @jennybc ,

Thanks for your incredible package. I think I also need a function like this. I want to import a XML to a Google Spreadsheet (too big for IMPORTXML). With a script I will download the XML, transform in a CSV, and then replace the sheet in Google Drive. I will try this with Shiny, but maybe is much advanced to me.

Another doubt, It would be good to do this in a specific folder of Google Drive.

Thanks!!

[jennybc]

gs_upload() now has an overwrite argument that will allow you to overwrite an entire Sheet. It does not offer worksheet-level control. But it might help in some cases.

[dsdaveh]

Thanks for the gs_upload tip (and the awesome library). Although gs_upload does work, it wipes out any pivots, charts or auxillary sheets, which would be a primary use case for the replace. I found a workaround by creating a cloned sheet for the pivots etc, and importing from the original sheet. The clone updates automatically when the original is updated with gs_upload. I've published an R Notebook with the steps in case anybody else is interested.

[jennybc]

I'm in maintenance mode here and admit that extending this in situ replacement work is more likely to happen in the reboot of this package that wraps the newer and nicer Sheets v4 API (https://github.com/tidyverse/googlesheets4). If you still care about this, please do open an issue there, so I can build it in from the very start!