日志输出少一行

Question

我试图把一个公式基于一个单元格的背景颜色在行c
它有点工作，但当我搜索并找到一个单元格(说C7)，我的日志发现它在C6。所以当我使用putformula时，它会进入错误的单元格(它上面的单元格)。

然后，即使我找到正确的单元格，我如何将此信息传递给putformula?

该代码是在互联网上找到的许多示例的混合物。

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")');   
}
}
}

Answer 1

试试这个:

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")');
}
})
}

Answer 2

A for…In语句通常不是迭代数组的最佳选择。此外，JavaScript数组是零索引的。在for...in循环中，row变量将按0,1,2,3…递增

使用Array.forEach()，像这样:

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);
}
});
}