二维数组使用string replace()时整列同步更新问题排查

问题描述 #

我需要从表格中提取一行公式，复制到剩余行并更新公式中对应行的A列单元格引用。步骤为：构建待复制的公式字符串数组、复制为与目标行数一致的二维数组、遍历每行替换对应行号、将更新后的数组写入目标区域。但执行代码时出现异常：每次替换操作会同步更新整列的所有行。例如预期二维数组从[[2,2,2],[2,2,2],[2,2,2]]变为[[3,3,3],[4,4,4],[5,5,5]]，实际在第一次行迭代后所有行就变为[[3,3,3],[3,3,3],[3,3,3]]，最终全部变为[[5,5,5],[5,5,5],[5,5,5]]。即使将fill()替换为for循环初始化二维数组，问题依然存在，怀疑是内存引用导致。

原代码 #

//sync the formulas in rows with entries in column A
const lastColumn = auctionListSheet.getLastColumn(); 
const lastRow2 = auctionListSheet.getLastRow(); //TODO: doesn't only look in col A
// row to copy across other rows
const masterRow = auctionListSheet.getRange("B2:"+String.fromCharCode(lastColumn + 64)+"2").getFormulas(); 
//range to update with properly referrenced formulas to col A of that row
const changeRange = auctionListSheet.getRange("B3:"+String.fromCharCode(lastColumn + 64)+lastRow2.toString()); 
// Initialize the formulas array with the same number of rows as the changeRange
//var formulas = new Array(changeRange.getNumRows()).fill().map(() => masterRow[0]);
// * tried replacing ^ line with this for loop
var formulas = new Array(changeRange.getNumRows());

// Iterate over the rows of the formulas array
for (var i = 0; i < formulas.length; i++) {
  // Set the value of the current row to the value of the first element in the masterRow array
  formulas[i] = masterRow[0];
}

const numColumns = changeRange.getNumColumns();
const numRows = changeRange.getNumRows();

//iterate and copy formulas cell by cell
for (var i = 0; i < formulas.length; i++) {
  var refRow = "A" + (3 + i);
  for (var j = 0; j < formulas[i].length; j++) {
    var element = formulas[i][j].toString();
    element = element.replace(/A\d/g, refRow);
    formulas[i][j] = element; // should only replace one cell, but updates entire column
  }
}

changeRange.setFormulas(formulas);

问题原因 #

核心问题是数组引用共享：masterRow[0]是一个数组对象，当执行formulas[i] = masterRow[0]时，并没有复制这个数组的内容，而是将formulas的每一行都指向了同一个数组引用。所以无论修改哪一行的元素，本质都是在修改同一个底层数组，导致所有行同步变化。

解决方案 #

需要对masterRow[0]进行浅拷贝，确保每一行都是独立的数组副本。可以使用展开运算符[...arr]、Array.from()或map方法实现拷贝。

修改后的代码 #

//sync the formulas in rows with entries in column A
const lastColumn = auctionListSheet.getLastColumn(); 
const lastRow2 = auctionListSheet.getLastRow(); //TODO: doesn't only look in col A
// row to copy across other rows
const masterRow = auctionListSheet.getRange("B2:"+String.fromCharCode(lastColumn + 64)+"2").getFormulas(); 
//range to update with properly referrenced formulas to col A of that row
const changeRange = auctionListSheet.getRange("B3:"+String.fromCharCode(lastColumn + 64)+lastRow2.toString()); 
// Initialize the formulas array with the same number of rows as the changeRange
var formulas = new Array(changeRange.getNumRows());

// 初始化时为每行创建独立的数组副本，避免引用共享
for (var i = 0; i < formulas.length; i++) {
  formulas[i] = [...masterRow[0]];
  // 也可以用 Array.from(masterRow[0]) 或 masterRow[0].map(item => item)
}

const numColumns = changeRange.getNumColumns();
const numRows = changeRange.getNumRows();

//iterate and copy formulas cell by cell
for (var i = 0; i < formulas.length; i++) {
  var refRow = "A" + (3 + i);
  for (var j = 0; j < formulas[i].length; j++) {
    var element = formulas[i][j].toString();
    element = element.replace(/A\d/g, refRow);
    formulas[i][j] = element;
  }
}

changeRange.setFormulas(formulas);

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