使用ArrayFormula后脚本将新数据插入表格底部的问题如何解决

问题原因 #

Google Sheets 内置的getLastRow()方法会将ArrayFormula返回的空字符串""识别为非空内容，所以统计全表最后一行时，会直接定位到ArrayFormula覆盖范围的最后一行，而非A-E列实际有数据的最后一行。

解决方案1（优先推荐，修改脚本逻辑） #

不要直接调用工作表对象的getLastRow()，改为定向统计你实际写入数据的A-E列的最后非空行，示例代码如下：

// 传入工作表对象和要查询的列号（A列对应1，根据实际业务选有必填数据的列即可）
function getValidLastRow(sheet, checkColumn = 1) {
  const allValues = sheet.getRange(1, checkColumn, sheet.getMaxRows(), 1).getValues();
  // 倒序查找第一个非空行
  for (let rowIndex = allValues.length - 1; rowIndex >= 0; rowIndex--) {
    if (allValues[rowIndex][0] !== "") {
      return rowIndex + 1;
    }
  }
  // 全列无数据时返回第一行，可根据需求调整
  return 1;
}

后续插入数据时，用这个方法返回的行号加1作为新数据的插入位置即可。

解决方案2（无需修改脚本，调整公式写法） #

把原来的ArrayFormula改为仅处理有实际数据的行，不会生成多余的空字符串占位，示例公式：

=ARRAYFORMULA(IF(ISBLANK(C8:C),,C8:C*H8:H))

如果需要进一步缩小公式生效范围，避免占用多余行，可以用动态范围写法：

=BYROW(FILTER(C8:C, C8:C<>""), LAMBDA(val, val * OFFSET(val, 0, 5)))

内容的提问来源于stack exchange，提问作者Osman P.