range_flood function

Flood or clear a range of cells

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.

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.

Returns

The input ss, as an instance of sheets_id

Examples

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

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