(Over)write new data into a range
Writes a data frame into a range of cells. Main differences from
sheet_write() (a.k.a. write_sheet()):
Narrower scope. 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 data must
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
data to the user-specified range.
If you just want to add rows to an existing table, the function you probably
want is sheet_append().
range_write( ss, data, sheet = NULL, range = NULL, col_names = TRUE, reformat = TRUE )
ss |
Something that identifies a Google Sheet: its file ID, a URL from
which we can recover the ID, an instance of |
data |
A data frame. |
sheet |
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 |
Where to write. This
|
col_names |
Logical, indicates whether to send the column names of
|
reformat |
Logical, indicates whether to reformat the affected cells.
Currently googlesheets4 provides no real support for formatting, so
|
The input ss, as an instance of sheets_id
The range argument of range_write() is special, because the Sheets API
can implement it in 2 different ways:
If range represents 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 data we are writing. This
frees you from doing fiddly range computations based on the dimensions of
the data.
If range describes 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
data object 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 UpdateSheetPropertiesRequest:
The main data write is done via an UpdateCellsRequest:
Other write functions:
gs4_create(),
gs4_formula(),
range_delete(),
range_flood(),
sheet_append(),
sheet_write()
if (gs4_has_token()) {
# create a Sheet with some initial, empty (work)sheets
(ss <- gs4_create("range-write-demo", sheets = c("alpha", "beta")))
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")
# 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)
# 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)
# clean up
gs4_find("range-write-demo") %>%
googledrive::drive_trash()
}Please choose more modern alternatives, such as Google Chrome or Mozilla Firefox.