I am trying to create an overflowing array in R using openxlsx. My end result will have the user change items into row 1, so I need the UNIQUE formula to dynamically add/remove values after exporting.
Here's my reprex:
library(openxlsx)
testwb <- createWorkbook()
addWorksheet(testwb, "Sheet1")
df <- data.frame(c("one"),
c("two"),
c("three"),
c("two"),
c("four"))
writeData(testwb, sheet = "Sheet1", x = df, startCol = 1, startRow = 1, colNames = FALSE)
writeFormula(testwb, sheet = "Sheet1", x = "TRANSPOSE(UNIQUE(A1:E1, TRUE))", array = FALSE,
startCol = 1, startRow = 3)
saveWorkbook(testwb, file = "test_output.xlsx", overwrite = TRUE)
openXL(testwb)
My expected output is this (works as intended with this formula):

However, the code results in errors whether I set "array" within writeFormula to both TRUE and FALSE.
Output using array = FALSE (forces @ symbols into the formula):

and output using array = TRUE (forces {} brackets):

Any ideas how I can achieve the first output? Thanks :)
openxlsx. I have implemented a solution foropenxlsx2see here