使用Sheets API更新Sheet2后Sheet1的公式引用异常变动如何解决

触发原因 #

这个问题是Google Sheets的相对引用自动偏移机制和API操作触发的单元格重定位规则共同导致的，常见触发场景如下：

• 调用API时执行的是插入行/删除行操作而非直接更新指定单元格数据：即便你操作的是Sheet2中A5行以下的位置，只要你插入数据时用的是AppendCellsRequest这类插入行的接口，或者在A1:A5范围的上方/内部插入了新行，Sheets会自动调整所有引用该范围的公式，避免引用失效，和手动在Sheet里插入行时的公式自动调整逻辑完全一致。比如你在Sheet2的第1行上方插入1行，原本引用的A1:A5会自动变成A2:A6。
• 公式未使用绝对引用锁定范围：你当前写的AVERAGE('Sheet2'!A1:A5)属于半相对引用，行号没有加$锁定，只要API操作触发了Sheet2的单元格位置整体偏移，引用的行范围就会同步变动。
• API请求开启了自动调整引用的参数：部分Sheets API的更新请求支持传入adjustFormulaReferences参数，默认值为True，开启后会自动调整所有关联公式的引用范围匹配数据位置的变动。

修复方案 #

• 把公式修改为绝对引用锁定行范围：调整为AVERAGE('Sheet2'!$A$1:$A$5)，即可避免任何数据操作导致的引用偏移。
• 调用API更新/追加数据时优先使用values.update方法直接写入指定范围的单元格，不要用插入行的操作方式，同时在请求参数中设置adjustFormulaReferences=False。

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