Google Apps Script合并多维数组 保留公式替换对应空值

需求背景 #

需要将业务数据填充到表格目标区域的无公式空白位置，通过合并两个二维数组后一次性批量写入的方式提升操作效率，合并过程中必须完整保留目标区域原有公式，仅在空单元格位置写入业务数据。

数据源结构 #

• array1：待写入的业务数据数组，非业务值位置为空

const array1 = [
  ["item1","","","details1"], 
  ["item2","","","details2"],
]

• array2：从目标区域读取的原始内容数组，包含所有需要保留的单元格公式，非公式位置为空值

const array2 = [
  ["","=iferror(VLOOKUP(A63,'Client List'!$A$1:$S,19,0),\"\")","=iferror(if(B63=\"Agency\", 'Reference Info'!$C$7, VLOOKUP(A63,'Client List'!$A$1:$T,20,0)),\"\")",""], 
  ["","=iferror(VLOOKUP(A64,'Client List'!$A$1:$S,19,0),\"\")","=iferror(if(B64=\"Agency\", 'Reference Info'!$C$7, VLOOKUP(A64,'Client List'!$A$1:$T,20,0)),\"\")",""]
]

预期合并结果 #

合并后数组保留array2所有公式，空位置填充array1对应位置的业务值，结构如下：

const mergedArray = [
  ["item1","=iferror(VLOOKUP(A63,'Client List'!$A$1:$S,19,0),\"\")","=iferror(if(B63=\"Agency\", 'Reference Info'!$C$7, VLOOKUP(A63,'Client List'!$A$1:$T,20,0)),\"\")","details1"], 
  ["item2","=iferror(VLOOKUP(A64,'Client List'!$A$1:$S,19,0),\"\")","=iferror(if(B64=\"Agency\", 'Reference Info'!$C$7, VLOOKUP(A64,'Client List'!$A$1:$T,20,0)),\"\")","details2"]
]

原有逻辑错误点 #

1. 循环层级错误：嵌套遍历两个数组的所有行，会导致行交叉匹配，最终生成的数组行数为两数组行数的乘积，和目标区域行数完全不匹配。实际上两个数组行索引一一对应，同序号的行直接合并即可，无需双重遍历行。
2. 结果结构错误：将所有单元格值扁平化存入一维数组，没有保留二维行结构，无法直接用于表格批量写入。

正确实现代码 #

// 行数校验，避免索引越界
if (array1.length !== array2.length) {
  throw new Error('待合并数组行数不一致，请检查数据源');
}

const finalResult = [];
// 逐行处理，行索引一一对应
for (let rowIdx = 0; rowIdx < array2.length; rowIdx++) {
  const currentMergedRow = [];
  const dataRow = array1[rowIdx];
  const formulaRow = array2[rowIdx];

  // 逐列判断单元格值
  for (let colIdx = 0; colIdx < formulaRow.length; colIdx++) {
    const formulaVal = formulaRow[colIdx];
    const dataVal = dataRow[colIdx] ?? '';
    // 公式位置保留原值，空位置填充业务数据
    currentMergedRow.push(formulaVal === '' ? dataVal : formulaVal);
  }

  finalResult.push(currentMergedRow);
}

最终输出的finalResult为标准二维数组，结构和目标区域完全匹配，可直接调用表格批量写入接口，不会覆盖原有单元格公式。

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