Log output is one row off

Question

I am trying to put a formula based on the background color of a cell in row C.
It kinda works, but when I search and find a cell (say C7), my log finds it at C6. So when I use the putformula, it gets in the wrong cell (the cell above it).

Then even when I find the right cell, how do I pass this info to the putformula?

The code is a mixture of a lot of examples found on the Internet.

function testv1(){
  var sourceSheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("overzicht");
  var lastSourceRow = sourceSheet.getLastRow();
  var lastSourceCol = sourceSheet.getLastColumn();

  var sourceRange = sourceSheet.getRange(1, 2, lastSourceRow, lastSourceCol);  //6,2 / de 1e is row, de 2 is colum, (colum c)
  var sourceData = sourceRange.getBackgrounds();
    
 var activeRow = 2;

  for (row in sourceData) {
      if (sourceData[row][1] === '#efefef') {     

              Logger.log(row)
     var formulaCell = sourceSheet.getRange([row + [0]],3,lastSourceRow,1)  
     formulaCell.setFormula('=IFERROR(SUM(filter(Transacties!$C:C;TEXT(Transacties!$A:A;\"yyyyMMMM\") = $D$3 & $C$3;regexmatch (Transacties!$F:F; A' + (row) + ')));\"Geen gegevens test test 5x\")');   
           }
  }

}

score 1 · Answer 1 · answered Dec 12 '22 at 22:12

A for...in statement is usually not the best choice for iterating an array. Further, JavaScript arrays are zero-indexed. In your for...in loop, the row variable will get incremented as in 0, 1, 2, 3...

To make it work, use Array.forEach(), like this:

function testv1() {
  const setFormula_ = (cell, rowNumber) =>
    cell.setFormula(`=iferror( sum( filter( Transacties!C:C; text(Transacties!A:A; "yyyyMMMM") = D3 & C3; regexmatch(Transacties!F:F; A${rowNumber}) ) ); "Geen gegevens test test 5x" )`);
  const range = SpreadsheetApp.getActive().getRange('overzicht!B1:B');
  const rowStart = range.getRow();
  const fillColors = range.getBackgrounds().flat();
  fillColors.forEach((color, rowIndex) => {
    if (color === '#efefef') {
      const formulaCell = range.offset(rowIndex, 1, 1, 1);
      setFormula_(formulaCell, rowStart + rowIndex);
    }
  });
}

score 1 · Accepted Answer · answered Dec 13 '22 at 02:40

1

Try this:

function testv1() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("overzicht");
  const rg = sh.getRange(1, 2, sh.getLastRow(), sh.getLastColumn());
  const vs = rg.getBackgrounds();
  vs.forEach((r, i) => {
    if (r[1] == "#efefef") {
      Logger.log(i + 1);
      sh.getRange(i + 1, 3).setFormula('=IFERROR(SUM(filter(Transacties!$C:C;TEXT(Transacties!$A:A;\"yyyyMMMM\") = $D$3 & $C$3;regexmatch (Transacties!$F:F; A' + (i + 1) + ')));\"Geen gegevens test test 5x\")');
    }
  })
}

answered Dec 13 '22 at 02:40

Cooper

59,616
6
23
54

Ok, but let say I want to search from row 10. I would change the getrange from 1 to 10 like this right? "const rg = sh.getRange(10, 2, sh.getLastRow(), sh.getLastColumn());". But that does not work. The logger still starts at 1?. The first nr is row, the second is column. What is it I dont understand? – wjp79 Dec 13 '22 at 21:14
Yes and the row is then i + 10. And the number of rows is sh.getLastRow() - 9 – Cooper Dec 13 '22 at 22:04

Log output is one row off

2 Answers2