如何避免Google Sheets与Forms新增行破坏QUERY公式？

问题原因 #

当你通过脚本删除Test表第2行及以后的数据后，Google Forms新增响应时会自动在Test表插入新行，此时Google Sheets的公式自动调整机制会把Pedidos表B2的=QUERY(Test!A2:X100)修改为=QUERY(Test!A3:X100)，导致公式无法正确读取新数据。

解决方案 #

方案1：用INDIRECT函数固定引用（无需修改脚本） #

把Pedidos表B2的公式替换为：

=QUERY(INDIRECT("Test!A2:X100"))

INDIRECT函数会直接解析字符串形式的单元格引用，不受行插入/删除操作的影响，能始终保持引用范围为Test!A2:X100。

方案2：在现有脚本中添加重置公式的逻辑 #

如果需要保留原公式写法，可在脚本删除Test表行的操作后，强制重置Pedidos表B2的公式。修改后的完整脚本如下：

function sendExcelAndDeleteRows() {
  var spreadsheetId = "###"; // 替换为你的表格ID
  var emailAddress = "###"; // 替换为收件邮箱
  var subject = "Excel Sheet - Pedidos";
  var message = "Please find attached the Excel sheet for Pedidos.";

  // 导出Pedidos表为Excel格式
  var pedidosSheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Pedidos");
  var pedidosDataRange = pedidosSheet.getDataRange();
  var pedidosValues = pedidosDataRange.getValues();
  var pedidosHeader = pedidosValues[0];
  var pedidosNewRange = pedidosSheet.getRange(1, 2, 1, pedidosHeader.length - 1);
  pedidosNewRange.setValues([pedidosHeader.slice(1)]);

  var dateToday = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd-MM-yyyy");
  var pedidosFileName = "Pedidos_" + dateToday + ".xlsx";
  var pedidosFile = DriveApp.createFile(pedidosFileName, pedidosDataRange.getValues(), MimeType.MICROSOFT_EXCEL);
  MailApp.sendEmail(emailAddress, subject, message, {attachments: [pedidosFile]});

  // 删除Test表第2行及以后的数据
  var testSheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Test");
  testSheet.deleteRows(2, testSheet.getLastRow() - 1);

  // 新增：重置Pedidos表B2单元格的QUERY公式
  pedidosSheet.getRange("B2").setFormula('=QUERY(Test!A2:X100)');

  // 清除Pedidos表指定范围内容
  var lastColumn = pedidosDataRange.getLastColumn();
  var lastRow = pedidosDataRange.getLastRow();
  pedidosSheet.getRange(2,3,lastRow,lastColumn).clearContent()
  
  // 删除临时Excel文件
  pedidosFile.setTrashed(true);
}

function createTrigger() {
  ScriptApp.newTrigger('sendExcelAndDeleteRows')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay.TUESDAY, ScriptApp.WeekDay.WEDNESDAY, ScriptApp.WeekDay.THURSDAY, ScriptApp.WeekDay.FRIDAY)
    .atHour(20)
    .nearMinute(30)
    .create();
}

关键修改点：在删除Test表行的代码后，添加pedidosSheet.getRange("B2").setFormula('=QUERY(Test!A2:X100)');，每次脚本执行后都会强制恢复B2的公式，避免Forms联动修改。

注意事项 #

• 方案1更轻量化，适合不需要频繁调整脚本的场景；方案2适合需要完全自动化维护公式的场景。
• 运行脚本前需确保其拥有修改表格内容的权限，可手动执行一次验证效果。

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