Google Apps Script合并EE_开头工作表数据报错求助

问题背景 #

需要实现Google表格数据合并功能：

• 清空MasterEffort工作表A到H列内容
• 以列数最多的、命名以“EE_”开头的工作表表头作为模板
• 按表头合并所有EE_类工作表数据：缺列留空，不匹配模板的列跳过
• 将合并后的数据写入MasterEffort的A到H列

报错信息 #

Exception: The number of rows in the data does not match the number in the range. The data has 21, but the range has 20.
consolidateData @ Code.gs:60

原代码 #

function consolidateData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = ss.getSheetByName("MasterEffort");
  var sheets = ss.getSheets();
  var data = [];
  var headers = [];
  var sheetName;
  var sheet;
  var range;
  var values;
  var row;
  var col;
  
  // Clear content in column A to H of sheet MasterEffort.
  masterSheet.getRange("A:H").clearContent();
  
  // Identify the sheet EE_ has the most number of columns. Use the column headers of this sheet as the template.
  for (var i = 0; i < sheets.length; i++) {
    sheetName = sheets[i].getName();
    if (sheetName.indexOf("EE_") === 0) {
      sheet = sheets[i];
      range = sheet.getDataRange();
      values = range.getValues();
      
      // Treat these data as column headers of sheet MasterEffort.
      if (data.length === 0 || values[0].length > data[0].length) {
        headers = values[0];
        data = values.slice(1);
      }
    }
  }
  
  // Combine data of all sheets EE_ based on column headers. If the column does not exist then leave it blank. If the column does not match the template, skip it.
  for (var i = 0; i < sheets.length; i++) {
    sheetName = sheets[i].getName();
    if (sheetName.indexOf("EE_") === 0) {
      sheet = sheets[i];
      range = sheet.getDataRange();
      values = range.getValues();
      
      for (row = 1; row < values.length; row++) {
        data.push([]);
        for (col = 0; col < headers.length; col++) {
          if (headers.indexOf(values[0][col]) !== -1) {
            data[data.length - 1].push(values[row][headers.indexOf(values[0][col])]);
          } else {
            data[data.length - 1].push("");
          }
        }
      }
    }
  }
  
  // Write combined data to column A to H of sheet MasterEffort.
  if (data.length > 0) {
    masterSheet.getRange(1,1,data.length,data[0].length).setValues([headers].concat(data));
  }
}

错误原因分析 #

1. 数据重复与行数计算错误：初始化阶段直接将列数最多的EE_表数据行存入data，后续循环又遍历所有EE_表（包括该表）重复添加数据；同时写入时用[headers].concat(data)生成的总行数是1 + data.length，但getRange中指定的行数是data.length，导致行数不匹配。
2. 表头匹配逻辑错误：原代码中用headers.indexOf(values[0][col])匹配列，逻辑混乱，容易导致数据对应到错误的列。

修复后的代码 #

function consolidateData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName("MasterEffort");
  const sheets = ss.getSheets();
  let templateHeaders = [];
  const mergedData = [];

  // 清空MasterEffort的A-H列
  masterSheet.getRange("A:H").clearContent();

  // 第一步：找到列数最多的EE_工作表，获取其表头作为模板
  sheets.forEach(sheet => {
    const sheetName = sheet.getName();
    if (sheetName.startsWith("EE_")) {
      const values = sheet.getDataRange().getValues();
      if (values.length === 0) return;
      // 对比当前表头列数，更新模板表头
      if (values[0].length > templateHeaders.length) {
        templateHeaders = values[0];
      }
    }
  });

  // 如果没有找到EE_表，直接返回
  if (templateHeaders.length === 0) return;

  // 第二步：遍历所有EE_表，按模板表头合并数据
  sheets.forEach(sheet => {
    const sheetName = sheet.getName();
    if (sheetName.startsWith("EE_")) {
      const values = sheet.getDataRange().getValues();
      if (values.length <= 1) return; // 只有表头无数据的表跳过

      // 构建当前表表头到模板表头索引的映射
      const headerMap = new Map();
      values[0].forEach((header, idx) => {
        const templateIdx = templateHeaders.indexOf(header);
        if (templateIdx !== -1) {
          headerMap.set(templateIdx, idx);
        }
      });

      // 处理数据行
      for (let row = 1; row < values.length; row++) {
        const newRow = new Array(templateHeaders.length).fill("");
        headerMap.forEach((sourceIdx, templateIdx) => {
          newRow[templateIdx] = values[row][sourceIdx] || "";
        });
        mergedData.push(newRow);
      }
    }
  });

  // 第三步：将合并数据写入MasterEffort的A-H列（限制为8列）
  if (mergedData.length > 0) {
    // 确保表头和数据都只取前8列
    const outputHeaders = templateHeaders.slice(0, 8);
    const outputData = mergedData.map(row => row.slice(0, 8));
    const output = [outputHeaders, ...outputData];
    masterSheet.getRange(1, 1, output.length, output[0].length).setValues(output);
  }
}

修复说明 #

• 初始化阶段仅保存模板表头，不再提前加入数据行，避免重复数据
• 使用Map构建表头映射关系，确保数据列对应准确
• 写入时正确计算总行数（表头1行 + 数据行N行），避免行数不匹配
• 强制限制写入列数为8列（A-H），符合需求要求

内容的提问来源于stack exchange，提问作者Tuong Vy Tran