使用Google Apps Script的setFormulaR1C1时如何解决公式解析错误？

问题核心 #

需要使用getFormulasR1C1/setFormulasR1C1实现相对单元格引用，但源区域存在无公式单元格时，getFormulasR1C1返回的数组中对应位置为null，直接替换为空字符串后调用setFormulasR1C1仍会触发公式解析错误。

原因分析 #

你之前的代码存在两个关键问题：

1. 使用for...in遍历数组时，可能会遍历到数组的非索引属性（比如原型链上的方法），导致处理逻辑混乱；
2. 使用== null判断虽然能匹配null和undefined，但存在类型转换的潜在误判，不如严格相等判断精准。

正确解决方案 #

方法1：使用map遍历处理（推荐）

通过map方法遍历二维数组，将每个null严格替换为空字符串，逻辑简洁且可靠：

var formulasAndNull = sh.getRange(headingRow+1, firstCol, 1, sh.getLastColumn() - firstCol + 1).getFormulasR1C1();
// 遍历每一行和每个单元格，替换null为空字符串
var formulas = formulasAndNull.map(row => row.map(cell => cell === null ? '' : cell));
// 应用处理后的公式数组
sh.getRange(targetRow, firstCol, formulas.length, formulas[0].length).setFormulasR1C1(formulas);

方法2：使用普通for循环遍历

如果不习惯箭头函数，也可以用传统的for循环按索引遍历，避免for...in的潜在问题：

var formulasAndNull = sh.getRange(headingRow+1, firstCol, 1, sh.getLastColumn() - firstCol + 1).getFormulasR1C1();
var formulas = [];
// 遍历行
for (var rowIdx = 0; rowIdx < formulasAndNull.length; rowIdx++) {
  var currentRow = [];
  // 遍历列
  for (var colIdx = 0; colIdx < formulasAndNull[rowIdx].length; colIdx++) {
    var cellValue = formulasAndNull[rowIdx][colIdx];
    currentRow.push(cellValue === null ? '' : cellValue);
  }
  formulas.push(currentRow);
}
// 应用公式
sh.getRange(targetRow, firstCol, formulas.length, formulas[0].length).setFormulasR1C1(formulas);

验证效果 #

处理后的数组中，原无公式单元格的位置被替换为空字符串，调用setFormulasR1C1时会正常清除对应单元格内容，不会触发公式解析错误，同时保留有公式单元格的R1C1格式相对引用。

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