range_write.Rd

% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/range_write.R
\name{range_write}
\alias{range_write}
\title{(Over)write new data into a range}
\usage{
range_write(
  ss,
  data,
  sheet = NULL,
  range = NULL,
  col_names = TRUE,
  reformat = TRUE
)
}
\arguments{
\item{ss}{Something that identifies a Google Sheet:
\itemize{
\item its file id as a string or \code{\link[googledrive:drive_id]{drive_id}}
\item a URL from which we can recover the id
\item a one-row \code{\link[googledrive:dribble]{dribble}}, which is how googledrive
represents Drive files
\item an instance of \code{googlesheets4_spreadsheet}, which is what \code{\link[=gs4_get]{gs4_get()}}
returns
}

Processed through \code{\link[=as_sheets_id]{as_sheets_id()}}.}

\item{data}{A data frame.}

\item{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 \code{range}. If neither argument specifies the sheet, defaults to the first visible sheet.}

\item{range}{Where to write. This \code{range} argument has important similarities
and differences to \code{range} elsewhere (e.g. \code{\link[=range_read]{range_read()}}):
\itemize{
\item Similarities: Can be a cell range, using A1 notation ("A1:D3") or using
the helpers in \code{\link{cell-specification}}. Can combine sheet name and cell
range ("Sheet1!A5:A") or refer to a sheet by name (\code{range = "Sheet1"},
although \code{sheet = "Sheet1"} is preferred for clarity).
\item Difference: Can NOT be a named range.
\item Difference: \code{range} can be interpreted as the \emph{start} of the target
rectangle (the upper left corner) or, more literally, as the actual
target rectangle. See the "Range specification" section for details.
}}

\item{col_names}{Logical, indicates whether to send the column names of
\code{data}.}

\item{reformat}{Logical, indicates whether to reformat the affected cells.
Currently googlesheets4 provides no real support for formatting, so
\code{reformat = TRUE} effectively means that edited cells become unformatted.}
}
\value{
The input \code{ss}, as an instance of \code{\link{sheets_id}}
}
\description{
Writes a data frame into a range of cells. Main differences from
\code{\link[=sheet_write]{sheet_write()}} (a.k.a. \code{\link[=write_sheet]{write_sheet()}}):
\itemize{
\item Narrower scope. \code{range_write()} literally targets some cells, not a whole
(work)sheet.
\item The edited rectangle is not explicitly styled as a table. Nothing special
is done re: formatting a header row or freezing rows.
\item Column names can be suppressed. This means that, although \code{data} must
be a data frame (at least for now), \code{range_write()} can actually be used
to write arbitrary data.
\item The target (spread)Sheet and (work)sheet must already exist. There is no
ability to create a Sheet or add a worksheet.
\item The target sheet dimensions are not "trimmed" to shrink-wrap the \code{data}.
However, the sheet might gain rows and/or columns, in order to write
\code{data} to the user-specified \code{range}.
}

If you just want to add rows to an existing table, the function you probably
want is \code{\link[=sheet_append]{sheet_append()}}.
}
\section{Range specification}{

The \code{range} argument of \code{range_write()} is special, because the Sheets API
can implement it in 2 different ways:
\itemize{
\item If \code{range} represents exactly 1 cell, like "B3", it is taken as the \emph{start}
(or upper left corner) of the targeted cell rectangle. The edited cells are
determined implicitly by the extent of the \code{data} we are writing. This
frees you from doing fiddly range computations based on the dimensions of
the \code{data}.
\item If \code{range} describes a rectangle with multiple cells, it is interpreted
as the \emph{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.
\code{cell_limits(c(2, 3), c(NA, NA))}. Note that \strong{all cells} inside the
rectangle receive updated data and format. Important implication: if the
\code{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.
}
}

\examples{
\dontshow{if (gs4_has_token()) (if (getRversion() >= "3.4") withAutoprint else force)(\{ # examplesIf}
# 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()
\dontshow{\}) # examplesIf}
}
\seealso{
If sheet size needs to change, makes an \code{UpdateSheetPropertiesRequest}:
\itemize{
\item \url{https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#UpdateSheetPropertiesRequest}
}

The main data write is done via an \code{UpdateCellsRequest}:
\itemize{
\item \url{https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatecellsrequest}
}

Other write functions: 
\code{\link{gs4_create}()},
\code{\link{gs4_formula}()},
\code{\link{range_delete}()},
\code{\link{range_flood}()},
\code{\link{sheet_append}()},
\code{\link{sheet_write}()}
}
\concept{write functions}