Google Sheets脚本设置R1C1公式报错，手动输入正常的解决方法

问题背景 #

搭建的Google Sheet结构：

• B列：Question
• C列：Student's Answers
• D列：Correct Answer
• E列：Correction

需求为E列自动对比C、D列内容：相等显示"Correct"，否则显示"Verify"；用户可通过下拉菜单修改"Verify"行的结果。为解决手动修改E列后公式被覆盖的问题，使用onEdit脚本在编辑C2时重置指定E列区域的公式，但脚本设置的公式显示正常却触发#ERROR! 公式解析错误，手动输入相同公式则正常运行。

问题原因 #

使用setFormulaR1C1方法时，错误嵌套INDIRECT("RC[-2]",FALSE)实现相对引用。实际上setFormulaR1C1本身就是专门处理R1C1格式公式的方法，原生支持RC[-n]这类相对引用语法，额外用INDIRECT会导致公式解析逻辑冲突。

解决方案 #

修改脚本中的公式，直接使用R1C1原生相对引用语法，移除INDIRECT包裹：

修正后的完整脚本 #

function onEdit(e) {
  var ss = e.source.getActiveSheet();
  var range = e.range.getA1Notation();
  var targetRange = ss.getRangeList(['E47:E51', 'E53:E57', 'E59:E63', 'E65:E67']);
  // 直接使用R1C1原生相对引用，替代INDIRECT写法
  var formulaR1C1 = '=IF(RC[-2]=RC[-1], "Correct", "Verify")';  
  if (ss.getName() !== 'Sheet_Name' || range !== 'C2') {
    return;
  }
  targetRange.setFormulaR1C1(formulaR1C1);
}

关键修改说明 #

• 移除INDIRECT("RC[-2]",FALSE)和INDIRECT("RC[-1]",FALSE)，直接用RC[-2]（当前单元格左移2列，对应C列）、RC[-1]（当前单元格左移1列，对应D列），逻辑和原公式完全一致
• 简化公式写法后，setFormulaR1C1能正确解析R1C1格式，避免解析错误

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