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)



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 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.


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.


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


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.


The input ss, as an instance of sheets_id

See also


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() }
#> Creating new Sheet: "range-flood-demo"
#> Request failed [429]. Retry 1 happens in 1 seconds ...
#> Request failed [429]. Retry 2 happens in 1.5 seconds ...
#> Request failed [429]. Retry 3 happens in 13.3 seconds ...
#> Request failed [429]. Retry 4 happens in 15.5 seconds ...
#> Error: Client error: (429) RESOURCE_EXHAUSTED #> * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information. #> * Quota exceeded for quota metric 'Write requests' and limit 'Write requests per minute per user' of service 'sheets.googleapis.com' for consumer 'project_number:250395471985'. #> #> Error details: #> * Error details of type 'google.rpc.ErrorInfo' may not be fully revealed. #> * Workaround: use `tryCatch()` and inspect error payload yourself. #> * Consider opening an issue at https://github.com/r-lib/gargle/issues.