googlesheets4 (version 1.0.0)

range_flood: Flood or clear a range of cells

Description

range_flood() "floods" a range of cells with the same content. range_clear() is a wrapper that handles the common special case of clearing the cell value. Both functions, by default, also clear the format, but this can be specified via reformat.

Usage

range_flood(ss, sheet = NULL, range = NULL, cell = NULL, reformat = TRUE)
range_clear(ss, sheet = NULL, range = NULL, reformat = TRUE)

Arguments

ss

Something that identifies a Google Sheet:

• its file id as a string or drive_id

• a URL from which we can recover the id

• a one-row dribble, which is how googledrive represents Drive files

• an instance of googlesheets4_spreadsheet, which is what gs4_get() returns

Processed through as_sheets_id().

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.

range

A cell range to read from. If NULL, all non-empty cells are read. Otherwise specify range as described in Sheets A1 notation or using the helpers documented in cell-specification. Sheets uses fairly standard spreadsheet range notation, although a bit different from Excel. Examples of valid ranges: "Sheet1!A1:B2", "Sheet1!A:A", "Sheet1!1:2", "Sheet1!A5:A", "A1:B2", "Sheet1". Interpreted strictly, even if the range forces the inclusion of leading, trailing, or embedded empty rows or columns. Takes precedence over skip, n_max and sheet. Note range can be a named range, like "sales_data", without any cell reference.

cell

The value to fill the cells in the range with. If unspecified, the default of NULL results in clearing the existing value.

reformat

Logical, indicates whether to reformat the affected cells. Currently googlesheets4 provides no real support for formatting, so reformat = TRUE effectively means that edited cells become unformatted.

Value

The input ss, as an instance of sheets_id

See Also

Makes a RepeatCellRequest:

• https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#repeatcellrequest

Other write functions: gs4_create(), gs4_formula(), range_delete(), range_write(), sheet_append(), sheet_write()

Examples

# NOT RUN {
if (gs4_has_token()) {
  # create a data frame to use as initial data
  df <- gs4_fodder(10)

  # create Sheet
  ss <- gs4_create("range-flood-demo", sheets = list(df))

  # default behavior (`cell = NULL`): clear value and format
  range_flood(ss, range = "A1:B3")

  # clear value but preserve format
  range_flood(ss, range = "C1:D3", reformat = FALSE)

  # send new value
  range_flood(ss, range = "4:5", cell = ";-)")

  # send formatting
  # WARNING: use these unexported, internal functions at your own risk!
  # This not (yet) officially supported, but it's possible.
  blue_background <- googlesheets4:::CellData(
    userEnteredFormat = googlesheets4:::new(
      "CellFormat",
      backgroundColor = googlesheets4:::new(
        "Color",
        red = 159 / 255, green = 183 / 255, blue = 196 / 255
      )
    )
  )
  range_flood(ss, range = "I:J", cell = blue_background)

  # range_clear() is a shortcut where `cell = NULL` always
  range_clear(ss, range = "9:9")
  range_clear(ss, range = "10:10", reformat = FALSE)

  # clean up
  gs4_find("range-flood-demo") %>%
    googledrive::drive_trash()
}
# }