googlesheets4: range_flood – R documentation

Become an expert in R — Interactive courses, Cheat Sheets, certificates and more!

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, a URL from which we can recover the ID, an instance of `googlesheets4_spreadsheet` (returned by `gs4_get()`), or a `dribble`, which is how googledrive represents Drive files. 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

Examples

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()
}

googlesheets4

Access Google Sheets using the Sheets API V4

v0.3.0

MIT + file LICENSE

Authors

Jennifer Bryan [cre, aut] (<https://orcid.org/0000-0002-6983-2759>), RStudio [cph, fnd]

Initial release