0

I have formulas on an SQL server, then I successfully get them from the server to my JS code, but then I try to use my add-in and most of my formulas just won't show in Excel at all.

for(var f = 0; f < formHead.length; f++){ 
    rngForm = sheetC.getRange(letterHead[f] + startCell + ":" + letterHead[f] + endCell); 
    rngForm.clear();
    rngForm.formulas = [[set[f].formula]];          
}

This is how I set the cells, I just need to set a single cell in a table column and the formula should get copied throughout the whole column, it's set up that way. Whether I assign range.formulas or range.values, with most formulas I need it just doesn't work, nothing happens and the cells stay empty.

Example of a formula that's working:

=[@[Need count]]-[@[Pack count]]

Three examples of formulas that aren't:

=IF(OR([@[Help Column]]="FIELD";LEFT([@Cat];1)="#");"";ROW()) 

=IF(AND(LEFT([@Cat];1)="#";SUMIF([Cat];r_hash([@Cat]);[Need count])>0);TEXT(ROW();"0000")&presence(r_hash([@Cat]);Box Count);IF([@[Need Count]]>0;TEXT(ROW();"0000")&",V"&presence2(ROW();Box Count);0))

=SUMIF(Data[#Headers];"Need *";Data[@])

Presence, presence2 and r_hash are custom functions. When I use them manually they work, so that is not the problem. Help column, Need count, Pack count, Cat, Box count and Need * are table column headers.

I know this isn't much to go on, but I'd be grateful for any suggestions.

tweakingmango
  • 1
  • 2
  • What do you mean by "doesn't work"? Do you get an error when inserting the formulas? Are the results wrong? ?? – Ike Jun 07 '22 at 10:31
  • I will edit the question, it means that it does nothing, the cells stay empty. – tweakingmango Jun 07 '22 at 10:47
  • Formula-parameter must be separated by comma - not by semikolon - that's why the one is working - and the others not – Ike Jun 07 '22 at 16:19
  • I have to use czech formatting, therefore commas have to be semicolons because commas are used with decimal numbers. But I tried it with one formula and it worked! I have to use US formatting in code and then it gets converted in Excel to czech. Thank you very much, I was really stuck. – tweakingmango Jun 08 '22 at 10:25

0 Answers0