Google Sheets deleteRows触发器修改公式引用范围 如何规避？

问题原因 #

Google Sheets存在自动引用调整机制：当公式引用范围内的行被删除时，系统会自动偏移公式内的行号避免引用失效，因此你删除第2行及以下的内容后，公式里的起始行号会自动跳转到剩下的第一行的行号。

解决方案 #

1. 公式层面修改（无需调整触发器，操作最简单）

使用INDIRECT函数把引用范围写成字符串格式，字符串内容不会被系统自动调整，修改后的公式如下：

={"TRIP 1 TIME";ARRAYFORMULA(IF(ISBLANK(INDIRECT("D2:D")),"",INDIRECT("D2:D") - INDIRECT("C2:C")))}

也可以用INDEX函数锁定起始行，效果完全一致：

={"TRIP 1 TIME";ARRAYFORMULA(IF(ISBLANK(INDEX(D:D,2):D),"",INDEX(D:D,2):D - INDEX(C:C,2):C))}

如果需要保留删除冗余空行的逻辑，优先选择该方案。

2. 触发器脚本层面修改（无需调整现有公式）

把删除行的逻辑替换为清空指定范围的内容，只有删除行操作会触发引用偏移，清空内容不会触发该机制，修改后的代码如下：

function deleteRows() {
   var sheet = SpreadsheetApp.getActive().getSheetByName('Daily Attendance - inAcc'); 
   var start, howManyToClear; 
   start = 2;
   howManyToClear = sheet.getLastRow() - start + 1;
   // 替换删除行为清空内容
   sheet.getRange(start, 1, howManyToClear, sheet.getLastColumn()).clearContent();
   // 如果需要同时清空格式，把上一行的clearContent()替换为clear()即可
}

该方案更适合表格内有大量同类引用公式的场景，不用逐一修改公式。

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