setFormula question. Howto get input from script itself inside the formula-CodePudding

I have a formula that I want to write in specific cells (based on background color). The code below kinda works, but the formula itself stays static. (A41 stays A41 in every cell I put this formula in)

I want the formula to take into account in which row it is being placed. I have to break the formula up with { or & or , I tried multiple times, without success.

Anyone have a hint?

        var gray2 = "#efefef";
  //var nocolor = "#ffffff";
  var rangeList = colors.reduce((ar, [a], i) => {
  var row = i   1;
    if (a == gray2) ar.push(`C${row}:C${row}`);
    return ar;
  }, []);
  false);  

sheetbalk.getRangeList(rangeList).setFormula("=IFERROR(SUM(filter(Transacties!$C:C;TEXT(Transacties!$A:A;\"yyyyMMMM\") = $D$3 & $C$3;regexmatch(Transacties!$F:F; A41)));\"Geen gegevens\")");

From the the following part

Transacties!$F:F; A41)

A41 needs to be in the same row, for example A42.

CodePudding user response：

Does ... A" (41 row) ")... or similar work?

CodePudding user response：

I think I found it

sheetbalk.getRangeList(rangeList).setFormula('=IFERROR(SUM(filter(Transacties!$C:C;TEXT(Transacties!$A:A;\"yyyyMMMM\") = $D$3 & $C$3;regexmatch (Transacties!$F:F; A'   (row)   ')));\"Geen //gegevens\")');