如何限制Google Apps Script自动填充Q列的终止行（适配On Edit触发器）

问题1：基于其他单元格内容停止填充 #

核心思路是找到目标数据列（比如你用来判断的A列）的最后一个非空行，只填充到该行的Q列，而非整个工作表的最后一行。这样只要A列没有新增非空内容，就不会继续往下填充。

修改后的脚本如下：

function FillFormulas() {   
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');   
  // 获取A列所有值，从下往上找最后一个非空行的行号
  var columnAValues = spreadsheet.getRange("A:A").getValues();
  var targetLastRow = 1;
  for (var i = columnAValues.length - 1; i >= 0; i--) {
    if (columnAValues[i][0] !== "") {
      targetLastRow = i + 1; // 数组索引从0开始，行号需+1
      break;
    }
  }
  // 最少从第2行开始填充，无有效数据则直接退出
  if (targetLastRow < 2) return;
  spreadsheet.getRange("Q2").setFormula("=IF(ISBLANK(A2),0,1)");   
  var fillDownRange = spreadsheet.getRange(2, 17, targetLastRow - 1);   
  spreadsheet.getRange("Q2").copyTo(fillDownRange); 
}

问题2：固定终止行且插入行时自动偏移 #

如果不想依赖数据列判断，可以用一个单元格存储终止行号（比如Sheet1的Z1单元格，初始值设为70），插入行时自动更新这个值，脚本读取该值作为填充的终止行。

步骤1：初始化存储单元格 #

在Sheet1的Z1单元格输入70，作为初始终止行号。

步骤2：修改脚本（含填充逻辑与插入行更新逻辑） #

function FillFormulas() {   
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');   
  // 读取Z1单元格的终止行号
  var stopRow = spreadsheet.getRange("Z1").getValue();
  // 避免无效填充
  if (stopRow < 2) return;
  spreadsheet.getRange("Q2").setFormula("=IF(ISBLANK(A2),0,1)");   
  var fillDownRange = spreadsheet.getRange(2, 17, stopRow - 1);   
  spreadsheet.getRange("Q2").copyTo(fillDownRange); 
}

// 修改On Edit触发器，插入行时自动更新终止行号
function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== 'Sheet1') return;
  var stopRow = sheet.getRange("Z1").getValue();
  // 若插入行位置在终止行之前，更新终止行号
  if (e.range.getRow() <= stopRow) {
    sheet.getRange("Z1").setValue(stopRow + 1);
    // 触发填充脚本，确保新行Q列有公式
    FillFormulas();
  }
}

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