如何在Google Apps Script中实现批量处理以高效处理4000行数据

当然可以批量处理全部4000行数据，你的代码目前只能处理1000行左右，核心问题是循环内频繁调用setValue操作单元格——Google Apps Script对单元格读写有性能限制，单次批量读写远快于多次零散读写。下面是优化思路和修改后的完整代码：

核心优化点 #

• 先把所有要处理的数据整理成一个二维数组，最后一次性写入目标表，彻底减少单元格操作次数
• 修正原代码中的索引错误（原代码把行/列索引搞反了，导致读取数据异常）
• 优化列索引逻辑：把Google Sheets的1起始列号转成数组的0起始索引，避免越界
• 支持从master_rate工作表批量读取汇率（替换原代码的硬编码值）
• 减少重复判断，确保每个字段只赋值一次

优化后完整代码 #

function testfn() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('data');
  const targetSheet = ss.getSheetByName('Sheet4');
  const rateSheet = ss.getSheetByName('master_rate');
  
  // 批量读取源数据（A2到最后一行的BZ列）
  const lastRow = sourceSheet.getLastRow();
  const sourceValues = sourceSheet.getRange(2, 1, lastRow - 1, 78).getValues(); // BZ是第78列
  
  // 从master_rate读取汇率（假设表结构：A列是货币符号，B列是汇率）
  const rateValues = rateSheet.getDataRange().getValues();
  const rateMap = new Map();
  rateValues.forEach(row => {
    if (row[0] && row[1]) rateMap.set(row[0], row[1]);
  });

  // 定义目标列的Google Sheets列号，后续自动转成数组索引
  const colSets = {
    amount: [8,14,29,49],       // 金额列
    currency: [77,13,28,47],    // 货币类型列
    customerCode: [0],          // 客户代码列（A列）
    zNumber: [5],               // Z号列（F列）
    company: [66],              // 公司列（BO列）
    date: [9,15,30,50],         // 日期列
    origin: [45,65],            // 来源列
    memo: [51,59,61,62]         // 备注列
  };
  // 转换为数组索引（列号-1）
  Object.keys(colSets).forEach(key => {
    colSets[key] = colSets[key].map(num => num - 1);
  });

  const now = new Date();
  const result = [];

  // 循环处理每一行源数据
  sourceValues.forEach(row => {
    let colA = ''; // 原金额
    let colB = ''; // 货币类型
    let colC = ''; // 转换后金额
    let colD = ''; // 客户代码
    let colE = ''; // Z号
    let colF = ''; // 公司名称
    let colG = ''; // 売上+Z号
    let dateStr = ''; // 格式化日期
    let yearMonthStr = ''; // 年月字符串
    let colI = ''; // 来源
    let colJ = ''; // 备注

    // 遍历当前行的所有列，匹配目标字段
    row.forEach((cellVal, j) => {
      // 处理金额
      if (colSets.amount.includes(j) && cellVal) colA = cellVal;
      
      // 处理货币与金额转换
      if (colSets.currency.includes(j) && cellVal) {
        colB = cellVal.trim();
        // 优先用master_rate的汇率， fallback到硬编码值
        const rate = rateMap.get(colB) || { '¥円':1, '$ドル':120, '€ユーロ':140 }[colB];
        colC = rate ? colA * rate : 'error';
      }
      
      // 处理客户代码
      if (colSets.customerCode.includes(j) && !colD && cellVal) {
        const codeArr = cellVal.split('_');
        const data = codeArr[codeArr.length - 1];
        const res = data.replace(/[^0-9]/g, "");
        colD = '10' + res;
      }
      
      // 处理Z号与売上文本
      if (colSets.zNumber.includes(j) && !colE && cellVal) {
        colE = cellVal;
        colG = '売上 ' + colE;
      }
      
      // 处理公司名称
      if (colSets.company.includes(j) && !colF) {
        colF = cellVal || "有限会社ユニゲ_g02";
      }
      
      // 处理日期格式化
      if (colSets.date.includes(j) && cellVal instanceof Date) {
        dateStr = Utilities.formatDate(cellVal, "GMT", 'yyyy/MM/dd');
        const ym = Utilities.formatDate(cellVal, "GMT", 'MMMM, yyyy');
        yearMonthStr = new Date(ym).toDateString();
      }
      
      // 处理来源
      if (colSets.origin.includes(j) && !colI) {
        colI = cellVal.trim() || "国内";
      }
      
      // 处理备注
      if (colSets.memo.includes(j) && !colJ && cellVal) {
        colJ = cellVal;
      }
    });

    // 将当前行处理结果加入结果数组
    result.push([
      colA, colB, colC, colD, colE, colF, colG,
      dateStr, yearMonthStr, colI, colJ, now
    ]);
  });

  // 清空目标表原有数据（可选，根据需求调整）
  if (targetSheet.getLastRow() > 1) {
    targetSheet.getRange(2, 1, targetSheet.getLastRow() - 1, 12).clearContent();
  }
  
  // 一次性写入所有处理后的数据
  if (result.length > 0) {
    targetSheet.getRange(2, 1, result.length, result[0].length).setValues(result);
  }
}

效果说明 #

优化后代码将单元格操作从48000次（4000行×12次setValue）减少到1次setValues，性能提升几十倍，完全可以处理4000行数据，同时避免触发Google Apps Script的配额限制。

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