RFUNCTION()

[JanMarvin]

Guess adding python code via =PY() that has to be evaluated remotely is "cool". What about some R code?

Don't be afraid of circular reference warnings ...

library(openxlsx2)
# add a custom formula, first define it, then use it
wb <- wb_workbook()$add_worksheet()

wb$add_formula(x = c(RFUNCTION = "_xlfn.LAMBDA(_xlpm.cell, _xlpm.text, _xlpm.cell)"))
#> formula registered to the workbook
wb$add_formula(x = "=RFUNCTION(B2, \"1+1\")", dims = "B2", cm = TRUE)
#> Warning in write_data2(wb = wb, sheet = sheet, data = x, name = name, colNames
#> = colNames, : modifications with cm formulas are experimental. use at own risk
wb$add_formula(x = "=RFUNCTION(C3, \"sum(1:10)\")", dims = "C3", cm = TRUE)
#> Warning in write_data2(wb = wb, sheet = sheet, data = x, name = name, colNames
#> = colNames, : modifications with cm formulas are experimental. use at own risk

wb$save("/tmp/test.xlsx")

wb <- wb_load("/tmp/test.xlsx")
cc <- wb$worksheets[[1]]$sheet_data$cc
if (length(fmls <- which(grepl("^=?RFUNCTION\\(", cc$f)))) {
  
  for (i in fmls) {
    message(cc$f[i])
    fml <- gsub("^=?RFUNCTION\\([A-Za-z0-9,\\s]*,\\s*\"([^\"]+)\"\\)$", "\\1", cc$f[i])
    message(fml)
    cc$v[i] <- as.character(eval(parse(text = fml)))
  }
  
  wb$worksheets[[1]]$sheet_data$cc <- cc
}
#> =RFUNCTION(B2, "1+1")
#> 1+1
#> =RFUNCTION(C3, "sum(1:10)")
#> sum(1:10)

wb_to_df(wb, col_names = FALSE)
#>    B  C
#> 2  2 NA
#> 3 NA 55

wb$save("/tmp/test.xlsx")
xl_open("/tmp/test.xlsx")
#> Warning: will not open file when not interactive
#> NULL