range_write: (Over)write new data into a range

Description Usage Arguments Value Range specification See Also Examples

View source: R/range_write.R

Description

Writes a data frame into a range of cells. Main differences from sheet_write() (a.k.a. write_sheet()):

If you just want to add rows to an existing table, the function you probably want is sheet_append().

Usage

1
2
3
4
5
6
7
8
range_write(
  ss,
  data,
  sheet = NULL,
  range = NULL,
  col_names = TRUE,
  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().

data

A data frame.

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. Ignored if the sheet is specified via range. If neither argument specifies the sheet, defaults to the first visible sheet.

range

Where to write. This range argument has important similarities and differences to range elsewhere (e.g. range_read()):

  • Similarities: Can be a cell range, using A1 notation ("A1:D3") or using the helpers in cell-specification. Can combine sheet name and cell range ("Sheet1!A5:A") or refer to a sheet by name (range = "Sheet1", although sheet = "Sheet1" is preferred for clarity).

  • Difference: Can NOT be a named range.

  • Difference: range can be interpreted as the start of the target rectangle (the upper left corner) or, more literally, as the actual target rectangle. See the "Range specification" section for details.

col_names

Logical, indicates whether to send the column names of data.

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

Range specification

The range argument of range_write() is special, because the Sheets API can implement it in 2 different ways:

See Also

If sheet size needs to change, makes an UpdateSheetPropertiesRequest:

The main data write is done via an UpdateCellsRequest:

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

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
if (gs4_has_token()) {
  # create a Sheet with some initial, empty (work)sheets
  (ss <- gs4_create("range-write-demo", sheets = c("alpha", "beta")))

  df <- data.frame(
    x = 1:3,
    y = letters[1:3]
  )

  #  write df somewhere other than the "upper left corner"
  range_write(ss, data = df, range = "D6")

  # view your magnificent creation in the browser
  gs4_browse(ss)

  # send data of disparate types to a 1-row rectangle
  dat <- tibble::tibble(
    string = "string",
    logical = TRUE,
    datetime = Sys.time()
  )
  range_write(ss, data = dat, sheet = "beta", col_names = FALSE)

  # send data of disparate types to a 1-column rectangle
  dat <- tibble::tibble(
    x = list(Sys.time(), FALSE, "string")
  )
  range_write(ss, data = dat, range = "beta!C5", col_names = FALSE)

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

Example output



googlesheets4 documentation built on July 22, 2021, 1:07 a.m.