Skip to content

Class for Google Sheets formulas

Source: R/gs4_formula.R
gs4_formula.Rd

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.

Usage

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

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

ss <- gs4_create("gs4-formula-demo", sheets = dat)
#> ✔ Creating new Sheet: gs4-formula-demo.
ss
#> 
#> ── <googlesheets4_spreadsheet> ───────────────────────────────────────────
#> Spreadsheet name: gs4-formula-demo                            
#>               ID: 13ZWDvjoCtLz1lauqq2eZQBTXOpWXo5YTSVFEPaHsdsw
#>           Locale: en_US                                       
#>        Time zone: Etc/GMT                                     
#>      # of sheets: 1                                           
#> 
#> ── <sheets> ──────────────────────────────────────────────────────────────
#> (Sheet name): (Nominal extent in rows x columns)
#>          dat: 7 x 1

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
#> # A tibble: 4 × 2
#>   desc      summaries                         
#>   <chr>     <fmla>                            
#> 1 max       =max(A:A)                         
#> 2 sum       =sum(A:A)                         
#> 3 min       =min(A:A)                         
#> 4 sparkline =SPARKLINE(A:A, {"color", "blue"})

range_write(ss, data = summaries, range = "C1", reformat = FALSE)
#> ✔ Editing gs4-formula-demo.
#> ✔ Writing to sheet dat.
#> ✔ Changing dims: (7 x 1) --> (7 x 4).

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
#> # A tibble: 2 × 2
#>   desc      example                                                
#>   <chr>     <fmla>                                                 
#> 1 hyperlink =HYPERLINK("http://www.google.com/","Google")          
#> 2 image     =IMAGE("https://www.google.com/images/srpr/logo3w.png")

sheet_write(miscellany, ss = ss)
#> ✔ Writing to gs4-formula-demo.
#> ✔ Writing to sheet miscellany.

# clean up
gs4_find("gs4-formula-demo") %>%
  googledrive::drive_trash()
#> File trashed:
#> • gs4-formula-demo <id: 13ZWDvjoCtLz1lauqq2eZQBTXOpWXo5YTSVFEPaHsdsw>