Google Apps Script中setFormula实现单元格引用随行动态变化

• 问题核心：通过背景色判断给特定C列单元格写入公式时，公式中的A41为静态引用，无法随公式所在行自动变化（如写入C42时仍为A41），需改为引用同行的A列单元格。
• 修改思路：放弃批量设置固定公式的方式，改为逐行判断并动态生成对应公式，确保A列引用与公式所在行一致。
• 修改后代码：

  var gray2 = "#efefef";
  // 替换为你的目标工作表名称
  var sheetbalk = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("目标工作表名");
  // 获取所有单元格背景色（原代码可能省略了这部分初始化逻辑）
  var colors = sheetbalk.getDataRange().getBackgrounds();
  
  // 遍历每一行，判断背景色并设置动态公式
  colors.forEach(([a], i) => {
    var row = i + 1;
    if (a == gray2) {
      // 用A${row}动态生成同行A列引用
      var dynamicFormula = `=IFERROR(SUM(filter(Transacties!$C:C;TEXT(Transacties!$A:A;"yyyyMMMM") = $D$3 & $C$3;regexmatch(Transacties!$F:F; A${row})));"Geen gegevens")`;
      sheetbalk.getRange(`C${row}`).setFormula(dynamicFormula);
    }
  });
  

• 关键改动说明：
  • 用forEach遍历背景色数组，对符合条件的行单独处理，避免批量公式的静态引用问题
  • 利用模板字符串的变量插值A${row}，自动生成当前行的A列引用（如行42对应A42）
  • 替换原getRangeList().setFormula()方法，改为逐行设置公式，实现动态引用

内容的提问来源于stack exchange，提问作者wjp79