如何通过Google Apps Script高效同步多标签页数据至Google Sheets主表

问题背景 #

我管理一份Google Sheets表格，每日为旅游电商产品导入GA4统计数据，每个漏斗步骤对应独立标签页。主表「Stats of Product IDs」需从其他标签页同步数据：

• A列：唯一产品ID列表
• B-G列：浏览类漏斗步骤数据（对应源表的screenPageViews）
• H-M列：会话类漏斗步骤数据（对应源表的sessions）

源标签页（如「GA4 packages url」「GA4 reservation url dates」等）结构：

• A列：可重复的产品ID
• B列：fullPageUrl
• C列：screenPageViews
• D列：sessions

当前使用GPT生成的脚本初期正常，但因配额限制易停滞，二次运行显示完成却仅同步不足半数数据，需更高效的实现方案。

原脚本：

function copyDataToMasterFile() {
  var masterFileSheetName = "Stats of Product IDs";
  var batchSize = 100; // Adjust the batch size as needed
  
  var masterFile = SpreadsheetApp.getActiveSpreadsheet();
  var masterFileSheet = masterFile.getSheetByName(masterFileSheetName);
  
  var tabMappings = {
    "GA4 packages url": { sourceColumn: "C", destinationColumn: "B" },
    "GA4 reservation url dates": { sourceColumn: "C", destinationColumn: "C" },
    "GA4 reservation url rooms": { sourceColumn: "C", destinationColumn: "D" },
    "GA4 reservation url flights": { sourceColumn: "C", destinationColumn: "E" },
    "GA4 reservation url options": { sourceColumn: "C", destinationColumn: "F" },
    "GA4 reservation url checkout": { sourceColumn: "C", destinationColumn: "G" },
    "GA4 packages url": { sourceColumn: "D", destinationColumn: "H" },
    "GA4 reservation url dates": { sourceColumn: "D", destinationColumn: "I" },
    "GA4 reservation url rooms": { sourceColumn: "D", destinationColumn: "J" },
    "GA4 reservation url flights": { sourceColumn: "D", destinationColumn: "K" },
    "GA4 reservation url options": { sourceColumn: "D", destinationColumn: "L" },
    "GA4 reservation url checkout": { sourceColumn: "D", destinationColumn: "M" }
  };

  for (var tabName in tabMappings) {
    var mapping = tabMappings[tabName];
    var sourceSheet = masterFile.getSheetByName(tabName);
    var sourceData = sourceSheet.getRange("A2:D").getValues();
    var sumData = {};
    
    for (var i = 0; i < sourceData.length; i++) {
      var productId = sourceData[i][0];
      var screenPageViews = sourceData[i][2];
      
      if (!sumData[productId]) {
        sumData[productId] = 0;
      }
      
      sumData[productId] += screenPageViews;
    }
    
    var destinationColumn = getColumnNumber(mapping.destinationColumn);
    var masterFileData = masterFileSheet.getRange("A2:M").getValues();
    
    for (var j = 0; j < masterFileData.length; j++) {
      var masterProductId = masterFileData[j][0];
      
      if (masterProductId && sumData[masterProductId]) {
        var existingValue = masterFileData[j][destinationColumn];
        var newValue = existingValue + sumData[masterProductId];
        masterFileSheet.getRange(j + 2, destinationColumn).setValue(newValue);
      }
    }
    
    // Clear the sumData for each batch to avoid memory buildup
    sumData = {};

    // Pause the execution to stay within quota limits
    Utilities.sleep(500);
  }
}

function getColumnNumber(columnLetter) {
  var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  var columnNumber = 0;
  
  for (var i = 0; i < columnLetter.length; i++) {
    columnNumber += (base.indexOf(columnLetter[i]) + 1) * Math.pow(26, columnLetter.length - i - 1);
  }
  
  return columnNumber;
}

原脚本核心问题 #

• 重复读写触发配额限制：每次循环单独读写单元格，触发大量Spreadsheet API调用，极易触及配额上限
• 映射表键重复覆盖：tabMappings中同一标签页出现两次，后一次会覆盖前一次配置，导致部分数据未同步
• 冗余数据处理：getRange("A2:D")包含空行，增加不必要的计算量
• 无效休眠：固定500ms休眠对配额优化帮助极小，且浪费执行时间

优化后的脚本 #

function syncGA4DataToMaster() {
  const masterSheetName = "Stats of Product IDs";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const masterSheet = ss.getSheetByName(masterSheetName);
  
  // 修正映射表：用数组存储避免重复键覆盖，索引从0开始对应列位置
  const tabMappings = [
    { tab: "GA4 packages url", sourceCol: 2, destCol: 1 },       // C→B
    { tab: "GA4 reservation url dates", sourceCol: 2, destCol: 2 }, // C→C
    { tab: "GA4 reservation url rooms", sourceCol: 2, destCol: 3 }, // C→D
    { tab: "GA4 reservation url flights", sourceCol: 2, destCol: 4 }, // C→E
    { tab: "GA4 reservation url options", sourceCol: 2, destCol: 5 }, // C→F
    { tab: "GA4 reservation url checkout", sourceCol: 2, destCol: 6 }, // C→G
    { tab: "GA4 packages url", sourceCol: 3, destCol: 7 },       // D→H
    { tab: "GA4 reservation url dates", sourceCol: 3, destCol: 8 }, // D→I
    { tab: "GA4 reservation url rooms", sourceCol: 3, destCol: 9 }, // D→J
    { tab: "GA4 reservation url flights", sourceCol: 3, destCol: 10 }, // D→K
    { tab: "GA4 reservation url options", sourceCol: 3, destCol: 11 }, // D→L
    { tab: "GA4 reservation url checkout", sourceCol: 3, destCol: 12 }  // D→M
  ];

  // 一次性读取主表所有数据，避免重复API调用
  const masterRange = masterSheet.getDataRange();
  const masterData = masterRange.getValues();
  const productIdIndex = {}; // 建立产品ID到行索引的映射，实现O(1)快速查找
  
  // 初始化主表产品ID索引
  for (let i = 1; i < masterData.length; i++) { // 跳过表头行
    const productId = masterData[i][0];
    if (productId) {
      productIdIndex[productId] = i;
    }
  }

  // 批量处理每个映射项
  tabMappings.forEach(mapping => {
    const sourceSheet = ss.getSheetByName(mapping.tab);
    if (!sourceSheet) return; // 跳过不存在的标签页
    
    // 读取源表有效数据（过滤空行）
    const sourceRange = sourceSheet.getDataRange();
    const sourceData = sourceRange.getValues().filter(row => row[0]); // 仅保留有产品ID的行
    
    // 按产品ID聚合数据
    const aggregatedData = {};
    sourceData.forEach(row => {
      const productId = row[0];
      const value = row[mapping.sourceCol] || 0;
      aggregatedData[productId] = (aggregatedData[productId] || 0) + value;
    });

    // 在内存中更新主表数据
    Object.keys(aggregatedData).forEach(productId => {
      const rowIndex = productIdIndex[productId];
      if (rowIndex !== undefined) {
        masterData[rowIndex][mapping.destCol] = (masterData[rowIndex][mapping.destCol] || 0) + aggregatedData[productId];
      }
    });
  });

  // 一次性写入所有更新，大幅减少API调用次数
  masterRange.setValues(masterData);
  
  // 可选：添加执行完成提示
  SpreadsheetApp.getUi().alert("GA4数据同步完成");
}

关键优化点 #

• 批量读写操作：一次性读取主表和源表数据，最后统一写入，将API调用次数从数百次降至2次（读+写）
• 修正映射结构：改用数组存储映射关系，彻底解决重复键覆盖问题
• 建立快速索引：提前生成产品ID到主表行索引的映射，将查找时间复杂度从O(n)降至O(1)
• 过滤无效数据：仅处理源表中有产品ID的行，减少不必要的计算量
• 内存中修改数据：所有更新先在内存数组中完成，避免频繁的单元格操作

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