如何在Google Script中添加带相对引用的公式

要在指定空白单元格中插入带相对引用的公式，需注意公式行号要匹配目标表的实际插入位置，不能固定使用B2。以下是修改后的完整脚本：

var sourceSpreadsheetId = "SOURCE_SPREADSHEET_ID";
var targetSpreadsheetId = "TARGET_SPREADSHEET_ID";

var sourceSheetName = "SourceSheetName";
var targetSheetName = "TargetSheetName"

var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
var sourceSheet = sourceSpreadsheet.getSheetByName(sourceSheetName);

var targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId);
var targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);

var selection = sourceSheet.getSelection();
var selectedRanges = selection.getActiveRangeList().getRanges();

var targetData = [];

selectedRanges.forEach(function (range) {
  var startRow = range.getRow();
  var numRows = range.getNumRows();
  var sourceRange = sourceSheet.getRange(startRow, 2, numRows, 4);
  var sourceValues = sourceRange.getValues();
  
  sourceValues.forEach(function (row) {
    // 保留原数据结构，空白项后续填充公式
    targetData.push([row[0], "", "", row[1], row[2], row[3]]);
  });
});

var startTargetRow = targetSheet.getLastRow() + 1;
var targetRange = targetSheet.getRange(startTargetRow, 1, targetData.length, targetData[0].length);
// 先写入所有静态值
targetRange.setValues(targetData);

// 生成并设置公式：针对B列（第2列）的空白单元格
var formulaRows = targetData.length;
var bFormulaRange = targetSheet.getRange(startTargetRow, 2, formulaRows, 1);
var bFormulas = [];
for(var i=0; i<formulaRows; i++){
  var currentRow = startTargetRow + i;
  bFormulas.push([`=IF(B${currentRow}="","--",COUNTIF($B$2:B${currentRow},B${currentRow}))`]);
}
bFormulaRange.setFormulas(bFormulas);

// 如果C列也需要填充相同公式，取消下面注释
// var cFormulaRange = targetSheet.getRange(startTargetRow, 3, formulaRows, 1);
// var cFormulas = [];
// for(var i=0; i<formulaRows; i++){
//   var currentRow = startTargetRow + i;
//   cFormulas.push([`=IF(B${currentRow}="","--",COUNTIF($B$2:B${currentRow},B${currentRow}))`]);
// }
// cFormulaRange.setFormulas(cFormulas);

关键说明 #

• 动态行号匹配：通过startTargetRow + i计算当前单元格的实际行号，确保公式的相对引用与插入位置完全对应。
• 值与公式分离处理：先用setValues写入静态数据，再用setFormulas单独设置公式区域，避免静态值被当作公式解析引发错误。
• 灵活适配需求：如果仅需填充其中一个空白列，保留对应代码即可；若两个空白列都需要公式，取消对应注释即可。

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