Google Script实现跨工作表匹配姓名填充值并补0

我尝试编写Google Apps Script实现以下需求，但运行报错：
在Google Sheets中，「data」工作表存储完整人员列表，「values」工作表包含部分相同人员及对应的B列数值。运行脚本时，需要将「values」中对应人员的B列数值填充到「data」的下一个空白列；若人员未在「values」中出现，则填充0，且每次运行都要使用新的空白列。

values工作表示例： #

data工作表预期效果： #

我编写的代码如下：

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sh1 = ss.getSheetByName('values');
  var sh1lastRow = sh1.getLastRow();
  var sh1AValues = sh1.getRange(1, 1, sh1lastRow, 1).getValues().flat();
  var sh1BValues = sh1.getRange(1, 2, sh1lastRow, 1).getValues().flat();

  var sh2 = ss.getSheetByName('data');
  var sh2lastRow = sh2.getLastRow();
  var sh2lastCol = sh2.getLastColumn();
  var sh2AValues = sh2.getRange(1, 1, sh2lastRow, 1).getValues().flat();
  var sh2xRange = sh2.getRange(1, sh2lastCol, sh2lastRow, 1);


  var output = sh1AValues.map(row => {
    var index = sh1BValues.indexOf(row);
    if(index >= 0)
      return [sh1BValues[index]];
    return [];
  });

  sh2xRange.setValues(output);
}

运行后报错：Exception: The number of rows in the data does not match the number of rows in the range. The data has 25 but the range has 31.

错误原因分析 #

1. 行数不匹配：原代码遍历的是values表的人员列表，生成的结果行数等于values表的行数，但data表的行数更多，导致写入时行数不匹配报错。
2. 列选择错误：原代码使用sh2.getLastColumn()作为目标列，会覆盖data表最后一列的已有数据，而非填充到新的空白列。
3. 默认值错误：未找到匹配人员时返回空数组，不符合需求中填充0的要求。

修正后的代码 #

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sh1 = ss.getSheetByName('values');
  var sh1lastRow = sh1.getLastRow();
  var sh1AValues = sh1.getRange(1, 1, sh1lastRow, 1).getValues().flat();
  var sh1BValues = sh1.getRange(1, 2, sh1lastRow, 1).getValues().flat();

  var sh2 = ss.getSheetByName('data');
  var sh2lastRow = sh2.getLastRow();
  var sh2lastCol = sh2.getLastColumn() + 1; // 选择下一个空白列
  var sh2AValues = sh2.getRange(1, 1, sh2lastRow, 1).getValues().flat();
  var sh2xRange = sh2.getRange(1, sh2lastCol, sh2lastRow, 1);

  // 遍历data表的人员列表，匹配values表的数据
  var output = sh2AValues.map(row => {
    var index = sh1AValues.indexOf(row);
    if(index >= 0)
      return [sh1BValues[index]];
    return [0]; // 未匹配到则返回0
  });

  sh2xRange.setValues(output);
}

内容的提问来源于Stack Exchange，提问作者AlxMrx