Google Sheets多维数组脚本问题：补全Sheet1缺失数据

问题背景 #

• Sheet1（A:L列，首行为表头）：A列是唯一文本值，B-L列存储各类数据，部分行存在空白
• Sheet2结构与Sheet1完全一致，A列同样为唯一文本值
• 两表A列存在部分相同值，但行位置不固定；相同A列值对应的B-L列数据可能存在差异，Sheet1的空白需由Sheet2对应数据补全

现有问题 #

已完成两表数据存入数组的基础步骤，但无法实现相同A列值的行数据对比，以及将Sheet2缺失数据补回Sheet1对应行的逻辑。现有脚本尝试合并数组去重，但未达到补全目标。

核心思路是先将Sheet2数据转换为以A列值为键的映射对象，实现快速查找对应行数据，再遍历Sheet1数组逐一补全空白单元格。

步骤1：修正数据读取逻辑 #

先修复原脚本中数据读取的错误（如变量名笔误），正确获取两表有效数据：

var spreadsheet = SpreadsheetApp.getActive();
var sheet1 = spreadsheet.getSheetByName('Sheet1');
var sheet2 = spreadsheet.getSheetByName('Sheet2');

// 获取Sheet1有效数据（A2到最后一行）
var sheet1LastRow = sheet1.getLastRow();
var sheet1Values = sheet1.getRange(2, 1, sheet1LastRow - 1, 12).getValues(); // 行从2开始，共sheet1LastRow-1行，12列

// 获取Sheet2有效数据
var sheet2LastRow = sheet2.getLastRow();
var sheet2Values = sheet2.getRange(2, 1, sheet2LastRow - 1, 12).getValues();

步骤2：构建Sheet2数据映射对象 #

将Sheet2的A列值作为键，整行数据作为值，后续可通过A列值直接定位对应行：

// 创建Sheet2数据映射：key=A列值，value=整行数据
var sheet2Map = {};
sheet2Values.forEach(row => {
  var key = row[0]; // A列对应数组索引0
  if (key) { // 跳过空行
    sheet2Map[key] = row;
  }
});

步骤3：遍历Sheet1数组补全缺失数据 #

遍历Sheet1每一行，通过A列值从映射中找到Sheet2对应行，对比B-L列（数组索引1到11），若Sheet1单元格为空则用Sheet2数据替换：

// 遍历Sheet1数据，补全缺失内容
var updatedSheet1Values = sheet1Values.map(row => {
  var key = row[0];
  // 如果Sheet2存在对应A列值的行
  if (sheet2Map[key]) {
    var sheet2Row = sheet2Map[key];
    // 遍历B-L列（索引1至11）
    for (let col = 1; col < 12; col++) {
      // 仅补全Sheet1中的空白单元格
      if (!row[col]) {
        row[col] = sheet2Row[col];
      }
    }
  }
  return row;
});

步骤4：将补全后的数据写回Sheet1 #

把更新后的数组覆盖写入Sheet1的对应区域：

// 将更新后的数据写入Sheet1（A2:L区域）
sheet1.getRange(2, 1, updatedSheet1Values.length, 12).setValues(updatedSheet1Values);

完整可运行脚本 #

function fillMissingDataFromSheet2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet1 = spreadsheet.getSheetByName('Sheet1');
  var sheet2 = spreadsheet.getSheetByName('Sheet2');

  // 获取Sheet1有效数据
  var sheet1LastRow = sheet1.getLastRow();
  var sheet1Values = sheet1.getRange(2, 1, sheet1LastRow - 1, 12).getValues();

  // 获取Sheet2有效数据
  var sheet2LastRow = sheet2.getLastRow();
  var sheet2Values = sheet2.getRange(2, 1, sheet2LastRow - 1, 12).getValues();

  // 构建Sheet2数据映射
  var sheet2Map = {};
  sheet2Values.forEach(row => {
    var key = row[0];
    if (key) {
      sheet2Map[key] = row;
    }
  });

  // 补全Sheet1缺失数据
  var updatedSheet1Values = sheet1Values.map(row => {
    var key = row[0];
    if (sheet2Map[key]) {
      var sheet2Row = sheet2Map[key];
      for (let col = 1; col < 12; col++) {
        if (!row[col]) {
          row[col] = sheet2Row[col];
        }
      }
    }
    return row;
  });

  // 写入Sheet1
  sheet1.getRange(2, 1, updatedSheet1Values.length, 12).setValues(updatedSheet1Values);
}

关键说明 #

• 使用对象映射替代数组遍历查找，大幅提升大数据量下的执行效率
• 默认仅补全Sheet1的空白单元格，保留已有数据；若需覆盖所有不一致数据，可将判断条件改为row[col] !== sheet2Row[col]

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