Google Sheet中Apps Script的setFormulas跨表引用行异常求助

问题现象 #

用Apps Script实现插入行功能时，同表内单元格引用正常，但跨表引用出现两类异常：

• Sheet1的B2单元格引用Sheet2!B2，执行脚本插入新行后，新行的B2变为Sheet2!B3，而非预期的Sheet2!B2
• 在Sheet2插入新行时，其对Sheet1日期列A的引用变为日期字面量，而非保持Sheet1!A2格式

原代码如下：

var objRow = 2;
var numRows = 1;
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i = 0; i < sheets.length ; i++ ) {
  var sheet = sheets[i];
  var lCol = sheet.getLastColumn();                       // store the last column
  var range = sheet.getRange(objRow, 1, numRows, lCol);   // store the range to copy from
  var formulas = range.getFormulas();                     // store the formulas to copy from
  sheet.insertRowsBefore(objRow, 1);                      // insert a new row above the new row
  var newRange = sheet.getRange(objRow, 1, 1, lCol);      // store the location of the new row
  range.copyTo(newRange);                                 // copy saved row data to the new row
  newRange.setFormulas(formulas);                         // set the formulas on the new row
}

问题根源 #

1. range.copyTo(newRange)会触发Google表格的自动引用调整逻辑，导致插入行后原范围的公式行号被自动偏移
2. 插入行后再获取原范围的公式，此时原范围已下移，公式中的行号已经被修改
3. copyTo可能将公式的计算结果（而非公式本身）复制到新行，导致跨表引用变成字面量

修正方案 #

基础修正版代码 #

var objRow = 2;
var numRows = 1;
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

for (var i = 0; i < sheets.length ; i++ ) {
  var sheet = sheets[i];
  var lCol = sheet.getLastColumn();
  
  // 插入行前提前获取原行的公式和值
  var originalRange = sheet.getRange(objRow, 1, numRows, lCol);
  var originalFormulas = originalRange.getFormulas();
  var originalValues = originalRange.getValues();
  
  // 插入新行
  sheet.insertRowsBefore(objRow, 1);
  
  // 获取新行范围并直接赋值
  var newRange = sheet.getRange(objRow, 1, 1, lCol);
  newRange.setValues(originalValues);
  newRange.setFormulas(originalFormulas);
}

关键改动说明 #

• 提前获取数据：在插入行操作之前就获取原行的公式和值，避免插入行后原范围位置变化导致公式被自动调整
• 替换copyTo方法：放弃使用会触发自动引用更新的copyTo，改用setValues和setFormulas直接赋值，完整保留原始公式的引用路径

进阶精准控制版（固定跨表引用行号） #

如果需要确保新行的跨表引用始终指向固定行（比如始终指向第2行），可以手动修改公式中的行号：

var objRow = 2;
var numRows = 1;
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

for (var i = 0; i < sheets.length ; i++ ) {
  var sheet = sheets[i];
  var lCol = sheet.getLastColumn();
  
  var originalRange = sheet.getRange(objRow, 1, numRows, lCol);
  var originalFormulas = originalRange.getFormulas();
  var originalValues = originalRange.getValues();
  
  // 遍历公式，将跨表引用的行号替换为目标行（此处为objRow）
  var adjustedFormulas = originalFormulas.map(row => {
    return row.map(formula => {
      if (formula) {
        // 匹配类似Sheet2!B2或'Sheet 2'!B2的跨表引用，替换行号
        return formula.replace(/('?\w+'?!)\w+(\d+)/g, `$1${objRow}`);
      }
      return formula;
    });
  });
  
  sheet.insertRowsBefore(objRow, 1);
  
  var newRange = sheet.getRange(objRow, 1, 1, lCol);
  newRange.setValues(originalValues);
  newRange.setFormulas(adjustedFormulas);
}

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