Google Sheets指定列更新时如何自动添加行匹配表单响应表行数？

问题背景 #

我有两个工作表（Form Responses 1和Sheet 2），其中Form Responses 1接收Google表单提交的数据，Sheet 2引用它做计算。我在Sheet 2中使用公式：
=SUMIFS('Form responses 1'!C2:C;M2:M;month(A9))
对指定月份的数值求和。M列是数组公式，会根据Form Responses 1的每条记录自动更新月份信息。

每当有新表单提交时，Form Responses 1会新增一行数据，一旦两个工作表行数不一致，SUMIFS函数就无法正常工作。我需要实现：让Sheet 2的行数始终和新增数据后的Form Responses 1保持一致，当Sheet 2的M列更新时自动补全行数。

我尝试了带onChange触发器的Google脚本：

function addRowOnEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  
  // 定义触发范围
  var triggerRange = sheet.getRange('M2:M');
  
  // 检查编辑的单元格是否在触发范围内
  if (range.getColumn() === triggerRange.getColumn() && range.getRow() >= triggerRange.getRow() && range.getRow() <= triggerRange.getLastRow()) {
    // 获取工作表的最后一行
    var lastRow = sheet.getLastRow();
    
    // 在最后一行后插入新行
    sheet.insertRowAfter(lastRow);
    
    // 如有需要，可向新增行添加数据
    var newRow = sheet.getRange(lastRow + 1, 1); // 可根据需要调整列号
    newRow.setValue("New Data"); // 可将"New Data"改为所需值
  }
}

原脚本的问题 #

• 依赖onEdit触发器，但表单提交导致的数组公式更新不一定会触发普通单元格编辑事件
• 逻辑上仅在编辑M列单元格时新增一行，无法保证两行工作表的行数完全匹配，极端情况会出现行数差越来越大

修正后的解决方案 #

核心思路 #

直接对比两个工作表的总行数，当Form Responses 1的行数超过Sheet 2时，批量在Sheet 2插入缺失的行数，确保两者行数一致。

完整脚本 #

function syncSheetRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSheet = ss.getSheetByName('Form Responses 1');
  var calcSheet = ss.getSheetByName('Sheet 2');
  
  // 防止工作表不存在导致报错
  if (!formSheet || !calcSheet) return;
  
  var formLastRow = formSheet.getLastRow();
  var calcLastRow = calcSheet.getLastRow();
  
  // 计算需要补充的行数
  var rowsToAdd = formLastRow - calcLastRow;
  
  if (rowsToAdd > 0) {
    // 批量插入缺失的行，比逐行插入效率更高
    calcSheet.insertRowsAfter(calcLastRow, rowsToAdd);
    
    // 若M列的数组公式需要扩展，自动重新应用（根据实际情况调整）
    var mColFormula = calcSheet.getRange('M2').getFormula();
    if (mColFormula.startsWith('=ARRAYFORMULA')) {
      calcSheet.getRange('M2:M' + (calcLastRow + rowsToAdd)).setFormula(mColFormula);
    }
  }
}

触发器设置步骤 #

1. 打开Google表格的脚本编辑器（点击「扩展程序」→「Apps脚本」）
2. 粘贴上述代码，保存项目
3. 点击左侧菜单栏的「触发器」图标，添加新触发器：
   • 选择执行函数：syncSheetRows
   • 选择事件源：「从电子表格」
   • 选择事件类型：「更改」
   • 保存触发器

补充说明 #

• 若你的M列数组公式已经设置为自动扩展（比如写成=ARRAYFORMULA(IF('Form Responses 1'!A2:A="",,MONTH('Form Responses 1'!A2:A)))），可以删除脚本中扩展公式的部分
• 批量插入行的方式更适合表单提交量大的场景，避免频繁触发脚本导致性能问题

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