In order to write a formula into Google Sheets, you need to store it as an object of class googlesheets4_formula. This is how we distinguish a "regular" character string from a string that should be interpreted as a formula. googlesheets4_formula is an S3 class implemented using the vctrs package.

gs4_formula(x = character())

Arguments

x

Character.

Value

An S3 vector of class googlesheets4_formula.

See also

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

Examples

if (gs4_has_token()) {
  dat <- data.frame(x = c(1, 5, 3, 2, 4, 6))

  ss <- gs4_create("gs4-formula-demo", sheets = dat)
  ss

  summaries <- tibble::tribble(
    ~desc, ~summaries,
    "max", "=max(A:A)",
    "sum", "=sum(A:A)",
    "min", "=min(A:A)",
    "sparkline", "=SPARKLINE(A:A, {\"color\", \"blue\"})"
  )

  # explicitly declare a column as `googlesheets4_formula`
  summaries$summaries <- gs4_formula(summaries$summaries)
  summaries

  range_write(ss, data = summaries, range = "C1", reformat = FALSE)

  miscellany <- tibble::tribble(
    ~desc, ~example,
    "hyperlink", "=HYPERLINK(\"http://www.google.com/\",\"Google\")",
    "image", "=IMAGE(\"https://www.google.com/images/srpr/logo3w.png\")"
  )
  miscellany$example <- gs4_formula(miscellany$example)
  miscellany

  sheet_write(miscellany, ss = ss)

  # clean up
  gs4_find("gs4-formula-demo") %>%
    googledrive::drive_trash()
}
#>  Creating new Sheet: gs4-formula-demo.
#>  Editing gs4-formula-demo.
#>  Writing to sheet dat.
#>  Changing dims: (7 x 1) --> (7 x 4).
#>  Writing to gs4-formula-demo.
#>  Writing to sheet miscellany.
#> File trashed:
#>gs4-formula-demo <id: 1jc0C3BoRnQyArjrLdqwzlq983Ixrtt4_7Qa2E-1WFg0>