GAS操作Google表单响应表setFormula写入公式行号自动偏移问题

问题现象 #

通过GAS为绑定Google Form的响应表写入数组公式，用于同步A列提交时间戳到E列，写入代码如下：

ss.getSheets()[0].getRange("E1").setFormula("={\"Timestamp Moved\"; ARRAYFORMULA(IF($A$2:$A<>\"\",$A$2:$A,\"\"))}");

公式写入初始状态完全正常，单元格内公式内容为：

={"Timestamp Moved"; ARRAYFORMULA(IF($A$2:$A<>"",$A$2:$A,""))}

但每次收到新的表单提交，公式内锁定的$A$2:$A引用就会自动向下偏移1行，首次提交后公式变为：

={"Timestamp Moved"; ARRAYFORMULA(IF($A$3:$A<>"",$A$3:$A,""))}

后续每次提交都会持续触发偏移，最终导致时间戳同步失效。
对照测试发现：如果手动在单元格输入完全一致的公式，无论多少次表单提交，$A$2的引用都保持固定，不会偏移。已确认setFormula方法仅执行一次，不存在脚本重复运行修改公式的情况。

最小复现步骤 #

• 新建Google表格，打开GAS编辑器，添加如下测试脚本：

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets()[0].getRange("C1").setFormula("={\"Timestamp Moved\"; ARRAYFORMULA(IF($A$2:$A<>\"\",$A$2:$A,\"\"))}");
}

• 在表格菜单栏选择「工具」-「创建新表单」，新建仅包含1个问题的表单，自动生成响应表
• 运行上述脚本写入公式，此时公式状态正常
• 提交1次表单，即可观察到公式引用偏移、时间戳复制失效的现象
  
  

根本原因 #

该问题由Google表单响应表的新行插入机制触发：
表单收到新提交时，不会直接在现有数据下方的空行写入内容，而是会在数据区域末尾插入一行全新的单元格存储响应内容。通过GASsetFormula写入的公式会被Sheets标记为脚本生成的非用户原生公式，插入新行时会自动调整公式内的引用范围——即便引用加了绝对引用符号$也不会阻止该调整。
而手动输入的公式会被判定为用户主动创建的原生公式，插入新行时不会修改这类公式的引用范围，因此不会出现偏移。

解决方法 #

方法1：用INDIRECT函数硬锁定引用范围 #

将需要固定的引用用INDIRECT包裹，直接以文本形式解析单元格范围，完全不受行插入、引用偏移的影响，修改后的写入代码如下：

ss.getSheets()[0].getRange("E1").setFormula("={\"Timestamp Moved\"; ARRAYFORMULA(IF(INDIRECT(\"A2:A\")<>\"\",INDIRECT(\"A2:A\"),\"\"))}");

该方法改动最小，适配所有版本的Sheets公式逻辑，是最稳定的解决方案。

方法2：改用R1C1引用格式写入公式 #

使用setFormulaR1C1替代普通setFormula写入公式，让Sheets将公式识别为原生配置的公式，避免触发自动偏移：

ss.getSheets()[0].getRange("E1").setFormulaR1C1("={\"Timestamp Moved\"; ARRAYFORMULA(IF(R2C1:C<>\"\",R2C1:C,\"\"))}");

方法3：避免脚本直接写入响应表公式 #

在表单绑定完成、响应表自动生成后，提前手动在目标单元格输入公式，脚本仅负责表单创建、触发配置等逻辑，从根源上避开脚本写入公式的识别标记问题。

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