Google Sheets中App Script自动填充公式失效问题求助

错误点分析 #

• 固定单元格引用问题：你公式里写的B2356是固定单元格，下拉填充时不会自动对应每行的B列数据，应该使用相对引用（比如B2，对应第2行的B列，下拉时会自动变成B3、B4...）。
• 目标范围行数计算错误：sheet.getRange(2, 9, lastRow)的第三个参数是行数，从第2行开始的话，实际需要填充的行数应该是lastRow - 1（减去表头所在的第1行），否则会生成远超数据范围的空行公式。
• 冗余操作：先用setFormula再autoFill属于多余步骤，直接给整个目标范围批量设置公式更高效。

修复后的代码 #

function autofill() {
  // 获取当前活动工作表
  var sheet = SpreadsheetApp.getActiveSheet();
  // 获取数据的最后一行
  var lastRow = sheet.getLastRow();
  
  // 修正公式：使用相对引用B2，下拉时会自动递增
  var formula = "=IF(ISNUMBER(MATCH(B2,'Name of the spreadsheet'!A:A,0)),\"Referral program\",\"No referral\")";
  
  // 直接获取需要填充的整个范围：第2行到最后一行，第9列（I列）
  var targetRange = sheet.getRange(2, 9, lastRow - 1);
  // 给整个范围批量设置公式，自动处理相对引用的递增
  targetRange.setFormula(formula);
}

补充说明（如果坚持用autoFill方法） #

function autofillWithAutoFill() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  var formula = "=IF(ISNUMBER(MATCH(B2,'Name of the spreadsheet'!A:A,0)),\"Referral program\",\"No referral\")";
  var sourceRange = sheet.getRange(2, 9);
  sourceRange.setFormula(formula);
  
  // 修正目标范围的行数：lastRow -1（从第2行到最后一行共lastRow-1行）
  var destinationRange = sheet.getRange(2, 9, lastRow - 1);
  sourceRange.autoFill(destinationRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
}

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