Google Apps Script问题：主表行数据匹配更新子表出错

问题描述 #

有一个Google主表格，包含2行表头和211行数据。需要遍历主表提取每行数据，更新对应现有子表格：主表第二列存储子表格名称，子表格集中在独立文件夹，主表在另一文件夹。当前代码能正确添加表头，但写入错误数据行（比如主表第3行对应C1子表，执行脚本却写入C70对应数据）。

原代码 #

function updateIndividualWorkbooks() {
  // Define the folder where the individual workbooks are located
  var folderId = "1HrzKS6yoxkcgAkBYz6wkDSYVjMLuXnKn"; // Replace with the actual folder ID
  
  // Get the master workbook
  var masterWorkbook = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = masterWorkbook.getSheetByName("ACKTRACKER"); // Name of Master Spreadsheet's sheet
  var data = masterSheet.getDataRange().getValues();

  // Loop through all files in the folder
  
  var folders = DriveApp.getFolderById(folderId);
  var files = folders.getFiles();

  var i = 3;

  while (files.hasNext()) {
    var file = files.next();

    const dataRows = masterSheet.getRange('A'+i+':CT'+i).getValues();
    //console.log("row values = : ",dataRows);

    // Open the individual workbook 
    var individualWorkbook = SpreadsheetApp.open(file);
    var individualSheet = individualWorkbook.getSheetByName("Sheet1");
    
    // Update data in the individual workbook
    individualSheet.clear();
    
    // copy the first two header rows to destination spreadsheet
    individualSheet.getRange("A1:CT2").setValues(headerRows);
    
    // Delete the third row in the individual workbook so we start fresh
    individualSheet.deleteRow(3);
    
    individualSheet.getRange('A3:CT3').setValues(dataRows);

    i++;
  }
}

主表示例（部分） #

期望效果 #

C3子表更新后内容为：

House     C number     username     score
Bamboo       C3          Tim         60

错误原因分析 #

• 文件顺序与主表行顺序不匹配：原代码按Drive默认的文件顺序（通常是创建/修改时间）依次对应主表第3、4...行数据，但文件夹内子表格的顺序和主表中C number的顺序不一定一致，直接导致数据错位。
• 未根据子表格名称匹配主表数据：核心错误是没有通过子表格名称（对应主表第二列的C number）查找对应行，而是单纯按索引顺序取数据。
• headerRows未定义：代码中直接使用headerRows变量但未从主表获取表头数据，会触发报错。
• 冗余操作：执行individualSheet.clear()后表格已清空，再执行individualSheet.deleteRow(3)完全没必要。

修正后的代码 #

function updateIndividualWorkbooks() {
  // 子表格所在文件夹ID
  const folderId = "1HrzKS6yoxkcgAkBYz6wkDSYVjMLuXnKn";
  // 获取主表
  const masterWorkbook = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = masterWorkbook.getSheetByName("ACKTRACKER");
  const allData = masterSheet.getDataRange().getValues();
  
  // 1. 提取主表的两行表头
  const headerRows = allData.slice(0, 2);
  // 2. 构建C number到对应数据行的映射（从第3行开始，索引为2）
  const cNumberMap = {};
  for (let rowIdx = 2; rowIdx < allData.length; rowIdx++) {
    const row = allData[rowIdx];
    const cNumber = row[1]; // 主表第二列是C number（索引从0开始）
    cNumberMap[cNumber] = row;
  }
  
  // 遍历子表格文件夹中的所有文件
  const folder = DriveApp.getFolderById(folderId);
  const files = folder.getFiles();
  
  while (files.hasNext()) {
    const file = files.next();
    const fileName = file.getName();
    
    // 假设子表格文件名就是C number（比如"C3"），若文件名含其他内容，可调整提取逻辑
    const targetCNumber = fileName;
    
    // 检查主表中是否存在该C number的数据
    if (cNumberMap[targetCNumber]) {
      const targetRow = cNumberMap[targetCNumber];
      
      // 打开子表格并更新数据
      const individualWorkbook = SpreadsheetApp.open(file);
      const individualSheet = individualWorkbook.getSheetByName("Sheet1");
      
      // 清空表格并写入表头
      individualSheet.clear();
      individualSheet.getRange(1, 1, headerRows.length, headerRows[0].length).setValues(headerRows);
      
      // 写入对应的数据行（从第3行开始）
      individualSheet.getRange(3, 1, 1, targetRow.length).setValues([targetRow]);
    } else {
      console.log(`未找到C number为${targetCNumber}的主表数据`);
    }
  }
}

关键修正点 #

• 建立数据映射：提前将主表数据按C number存入对象，方便根据子表格名称快速查找对应行。
• 按文件名匹配数据：遍历子表格时，通过文件名获取C number，再从映射中取出对应数据，确保数据匹配准确。
• 修复表头获取逻辑：从主表中提取前两行作为表头，解决headerRows未定义的问题。
• 移除冗余操作：删除清空表格后的deleteRow(3)操作，简化流程。

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