基于Google Script优化SUMPRODUCT批量计算效率的技术问询

问题背景 #

你当前通过.setFormula()批量应用SUMPRODUCT公式，再复制粘贴为值的方式处理数据，6万行耗时12分钟，数据增长后会面临脚本超时问题。原公式逻辑是：判断「Main Dataset」中每个人员ID，是否完成了「Settings」表某列内的任意一门课程（匹配「Completion Data」中的已完成记录），返回Completed或Incomplete。

优化思路 #

核心是减少Spreadsheet API的读写次数，把数据处理放在内存中完成：

1. 一次性读取所有需要的数据源到内存
2. 预处理「Completion Data」，构建人员ID与已完成课程的映射（用Set实现快速查询）
3. 批量计算所有行的结果后，一次性写入表格

实现代码 #

function updateCourseCompletionStatus() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. 一次性读取所有需要的数据，减少API调用
  // 读取Completion Data：A列=课程代码，B列=人员ID，C列=状态
  const completionSheet = ss.getSheetByName('Completion Data');
  const completionData = completionSheet.getDataRange().getValues();
  
  // 读取Main Dataset：D列=人员ID，结果写入对应列（自行调整目标列）
  const mainSheet = ss.getSheetByName('Main Dataset');
  const mainData = mainSheet.getDataRange().getValues();
  const mainRowCount = mainData.length;
  const personIdColIndex = 3; // D列对应索引3（从0开始计数）
  
  // 读取Settings中的5个课程列（E-I列，从第5行开始）
  const settingsSheet = ss.getSheetByName('Settings');
  const courseColumns = ['E', 'F', 'G', 'H', 'I'];
  const courseDataList = courseColumns.map(col => {
    const range = settingsSheet.getRange(`${col}5:${col}`);
    return range.getValues().flat().filter(val => val !== ''); // 过滤空课程代码
  });
  
  // 2. 构建人员ID到已完成课程的映射，用Set实现O(1)查询
  const personCompletedCourses = {};
  completionData.forEach(row => {
    const courseCode = row[0];
    const personId = row[1];
    const status = row[2];
    
    if (personId && status === 'Completed' && courseCode) {
      if (!personCompletedCourses[personId]) {
        personCompletedCourses[personId] = new Set();
      }
      personCompletedCourses[personId].add(courseCode);
    }
  });
  
  // 3. 批量计算每个课程列的结果
  const resultColumns = [];
  courseDataList.forEach(courseList => {
    const results = [];
    for (let i = 0; i < mainRowCount; i++) {
      const personId = mainData[i][personIdColIndex];
      // 跳过表头（根据实际表头内容调整判断逻辑）
      if (i === 0 && typeof personId === 'string' && personId.includes('人员ID')) {
        results.push('完成状态');
        continue;
      }
      
      if (!personId) {
        results.push('');
        continue;
      }
      
      const completedCourses = personCompletedCourses[personId] || new Set();
      // 检查是否完成该列任意一门课程
      const isCompleted = courseList.some(course => completedCourses.has(course));
      results.push(isCompleted ? 'Completed' : 'Incomplete');
    }
    resultColumns.push(results.map(val => [val])); // 转二维数组适配批量写入
  });
  
  // 4. 批量写入结果到Main Dataset（示例写入F-J列，对应索引5-9）
  const startCol = 5;
  resultColumns.forEach((resultCol, index) => {
    const writeRange = mainSheet.getRange(1, startCol + index, mainRowCount, 1);
    writeRange.setValues(resultCol);
  });
  
  SpreadsheetApp.getUi().alert('课程完成状态更新完成！');
}

关键优化点 #

• 减少API调用：所有数据一次性读取，结果一次性写入，避免循环读写表格的巨大开销
• 快速查询：用Set存储每个人员的已完成课程，判断是否包含某门课程的时间复杂度为O(1)
• 内存处理：所有计算逻辑在内存中完成，远快于表格公式计算

使用说明 #

1. 调整代码中的列索引、工作表名称、目标写入列，确保与你的表格结构匹配
2. 首次运行时授权脚本访问权限
3. 可通过Google Apps Script的定时触发器，设置每周自动执行更新

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