#' (Over)write new data into a Sheet
#'
#' @description
#'
#' This is one of the main ways to write data with googlesheets4. This function
#' writes a data frame into a (work)sheet inside a (spread)Sheet. The target
#' sheet is styled as a table:
#' * Special formatting is applied to the header row, which holds column
#' names.
#' * The first row (header row) is frozen.
#' * The sheet's dimensions are set to "shrink wrap" the `data`.
#'
#' If no existing Sheet is specified via `ss`, this function delegates to
#' [`gs4_create()`] and the new Sheet's name is randomly generated. If that's
#' undesirable, call [`gs4_create()`] directly to get more control.
#'
#' If no `sheet` is specified or if `sheet` doesn't identify an existing sheet,
#' a new sheet is added to receive the `data`. If `sheet` specifies an existing
#' sheet, it is effectively overwritten! All pre-existing values, formats, and
#' dimensions are cleared and the targeted sheet gets new values and dimensions
#' from `data`.
#'
#' This function goes by two names, because we want it to make sense in two
#' contexts:
#' * `write_sheet()` evokes other table-writing functions, like
#' `readr::write_csv()`. The `sheet` here technically refers to an individual
#' (work)sheet (but also sort of refers to the associated Google
#' (spread)Sheet).
#' * `sheet_write()` is the right name according to the naming convention used
#' throughout the googlesheets4 package.
#'
#' `write_sheet()` and `sheet_write()` are equivalent and you can use either one.
#'
#' @param data A data frame. If it has zero rows, we send one empty pseudo-row
#' of data, so that we can apply the usual table styling. This empty row goes
#' away (gets filled, actually) the first time you send more data with
#' [sheet_append()].
#' @eval param_ss()
#' @eval param_sheet(action = "write into")
#'
#' @template ss-return
#' @export
#' @family write functions
#' @family worksheet functions
#'
#' @examplesIf gs4_has_token()
#' df <- data.frame(
#' x = 1:3,
#' y = letters[1:3]
#' )
#'
#' # specify only a data frame, get a new Sheet, with a random name
#' ss <- write_sheet(df)
#' read_sheet(ss)
#'
#' # clean up
#' googledrive::drive_trash(ss)
#'
#' # create a Sheet with some initial, placeholder data
#' ss <- gs4_create(
#' "sheet-write-demo",
#' sheets = list(alpha = data.frame(x = 1), omega = data.frame(x = 1))
#' )
#'
#' # write df into its own, new sheet
#' sheet_write(df, ss = ss)
#'
#' # write mtcars into the sheet named "omega"
#' sheet_write(mtcars, ss = ss, sheet = "omega")
#'
#' # get an overview of the sheets
#' sheet_properties(ss)
#'
#' # view your magnificent creation in the browser
#' gs4_browse(ss)
#'
#' # clean up
#' gs4_find("sheet-write-demo") %>%
#' googledrive::drive_trash()
sheet_write <- function(data,
ss = NULL,
sheet = NULL) {
data_quo <- enquo(data)
data <- eval_tidy(data_quo)
check_data_frame(data)
# no Sheet provided --> call gs4_create() ---------------------------------
if (is.null(ss)) {
if (quo_is_symbol(data_quo)) {
sheet <- sheet %||% as_name(data_quo)
}
if (is.null(sheet)) {
return(gs4_create(sheets = data))
} else {
check_string(sheet)
return(gs4_create(sheets = list2(!!sheet := data)))
}
}
# finish checking inputs -----------------------------------------------------
ssid <- as_sheets_id(ss)
maybe_sheet(sheet)
# retrieve spreadsheet metadata ----------------------------------------------
x <- gs4_get(ssid)
gs4_bullets(c(v = "Writing to {.s_sheet {x$name}}."))
# no `sheet` ... but maybe we can name the sheet after the data --------------
if (is.null(sheet) && quo_is_symbol(data_quo)) {
candidate <- as_name(data_quo)
# accept proposed name iff it does not overwrite existing sheet
if (!is.null(candidate)) {
m <- match(candidate, x$sheets$name)
sheet <- if (is.na(m)) candidate else NULL
}
}
# initialize the batch update requests and the target sheet s ----------------
requests <- list()
s <- NULL
# ensure there's a target sheet, ready to receive data -----------------------
if (!is.null(sheet)) {
s <- tryCatch(
lookup_sheet(sheet, sheets_df = x$sheets),
googlesheets4_error_sheet_not_found = function(cnd) NULL
)
}
if (is.null(s)) {
x <- sheet_add_impl_(ssid, sheet_name = sheet)
s <- lookup_sheet(nrow(x$sheets), sheets_df = x$sheets)
} else {
# create request to clear the data and formatting in pre-existing sheet
requests <- c(
requests,
list(bureq_clear_sheet(s$id))
)
}
gs4_bullets(c(v = "Writing to sheet {.w_sheet {s$name}}."))
# create request to write data frame into sheet ------------------------------
requests <- c(
requests,
prepare_df(s$id, data)
)
# do it ----------------------------------------------------------------------
req <- request_generate(
"sheets.spreadsheets.batchUpdate",
params = list(
spreadsheetId = ssid,
requests = requests,
responseIncludeGridData = FALSE
)
)
resp_raw <- request_make(req)
gargle::response_process(resp_raw)
invisible(ssid)
}
#' @rdname sheet_write
#' @export
write_sheet <- sheet_write
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.