Excel Office Script - setFormula and SUMIFS not working

39 views Asked by At

I am brand new to office script, I have two consecutive lines and am unclear on why I am getting an error on the SUMIFS line. The error is "Range setFormula: The argument is invalid or missing or has an incorrect format."

In the code below, clusterSummary is a reference to a worksheet, and clusterSummaryRow is 3. It doesn't change behavior if I replace the Range with a non-calculated reference ("J3").

clusterSummary.getRange("E" + clusterSummaryRow).setFormula("=COUNTIFS(sheet2!B:B, ClusterSummary!C3, sheet2!C:C, ClusterSummary!A3)");
clusterSummary.getRange("J" + clusterSummaryRow).setFormula("=SUMIFS(sheet2!L:L, sheet2!B:B, ClusterSummary!C3, sheet2!C:C, ClusterSummary!A3)");

I expected both lines to execute without error but the SUMIFS line does not.

If I modify the first COUNTIFS to SUMIFS, then I experience the same error on that line.

setFormula and setValue give the same error.

I even tried doing a string concat ("=SUMI"+"FS") in varying versions and the line still errors out.

If I modify the IFS to varying nonsense things (SUMXFX, SUMIXS, SUMIFX) the line executes without error.

0

There are 0 answers