(Over)write new data into a rangeSource:
range_write()literally targets some cells, not a whole (work)sheet.
The edited rectangle is not explicitly styled as a table. Nothing special is done re: formatting a header row or freezing rows.
Column names can be suppressed. This means that, although
datamust be a data frame (at least for now),
range_write()can actually be used to write arbitrary data.
The target (spread)Sheet and (work)sheet must already exist. There is no ability to create a Sheet or add a worksheet.
The target sheet dimensions are not "trimmed" to shrink-wrap the
data. However, the sheet might gain rows and/or columns, in order to write
datato the user-specified
If you just want to add rows to an existing table, the function you probably
Something that identifies a Google Sheet:
its file id as a string or
a URL from which we can recover the id
dribble, which is how googledrive represents Drive files
an instance of
googlesheets4_spreadsheet, which is what
A data frame.
Sheet to write into, in the sense of "worksheet" or "tab". You can identify a sheet by name, with a string, or by position, with a number. Ignored if the sheet is specified via
range. If neither argument specifies the sheet, defaults to the first visible sheet.
Where to write. This
rangeargument has important similarities and differences to
Similarities: Can be a cell range, using A1 notation ("A1:D3") or using the helpers in
cell-specification. Can combine sheet name and cell range ("Sheet1!A5:A") or refer to a sheet by name (
range = "Sheet1", although
sheet = "Sheet1"is preferred for clarity).
Difference: Can NOT be a named range.
rangecan be interpreted as the start of the target rectangle (the upper left corner) or, more literally, as the actual target rectangle. See the "Range specification" section for details.
Logical, indicates whether to send the column names of
Logical, indicates whether to reformat the affected cells. Currently googlesheets4 provides no real support for formatting, so
reformat = TRUEeffectively means that edited cells become unformatted.
ss, as an instance of
range argument of
range_write() is special, because the Sheets API
can implement it in 2 different ways:
rangerepresents exactly 1 cell, like "B3", it is taken as the start (or upper left corner) of the targeted cell rectangle. The edited cells are determined implicitly by the extent of the
datawe are writing. This frees you from doing fiddly range computations based on the dimensions of the
rangedescribes a rectangle with multiple cells, it is interpreted as the actual rectangle to edit. It is possible to describe a rectangle that is unbounded on the right (e.g. "B2:4"), on the bottom (e.g. "A4:C"), or on both the right and the bottom (e.g.
cell_limits(c(2, 3), c(NA, NA)). Note that all cells inside the rectangle receive updated data and format. Important implication: if the
dataobject isn't big enough to fill the target rectangle, the cells that don't receive new data are effectively cleared, i.e. the existing value and format are deleted.
If sheet size needs to change, makes an
The main data write is done via an
# create a Sheet with some initial, empty (work)sheets (ss <- gs4_create("range-write-demo", sheets = c("alpha", "beta"))) #> ✔ Creating new Sheet: range-write-demo. #> Spreadsheet name: range-write-demo #> ID: 1uuyIH-EtOGAnGNDe8gA4kp1jUCEk_fQRTKTsCOS8Zk0 #> Locale: en_US #> Time zone: Etc/GMT #> # of sheets: 2 #> #> (Sheet name): (Nominal extent in rows x columns) #> alpha: 1000 x 26 #> beta: 1000 x 26 df <- data.frame( x = 1:3, y = letters[1:3] ) # write df somewhere other than the "upper left corner" range_write(ss, data = df, range = "D6") #> ✔ Editing range-write-demo. #> ✔ Writing to sheet alpha. # view your magnificent creation in the browser gs4_browse(ss) # send data of disparate types to a 1-row rectangle dat <- tibble::tibble( string = "string", logical = TRUE, datetime = Sys.time() ) range_write(ss, data = dat, sheet = "beta", col_names = FALSE) #> ✔ Editing range-write-demo. #> ✔ Writing to sheet beta. # send data of disparate types to a 1-column rectangle dat <- tibble::tibble( x = list(Sys.time(), FALSE, "string") ) range_write(ss, data = dat, range = "beta!C5", col_names = FALSE) #> ✔ Editing range-write-demo. #> ✔ Writing to sheet beta. # clean up gs4_find("range-write-demo") %>% googledrive::drive_trash() #> File trashed: #> • range-write-demo <id: 1uuyIH-EtOGAnGNDe8gA4kp1jUCEk_fQRTKTsCOS8Zk0>