Google Apps Script员工排班脚本优化求助：耗时超5分钟

你的排班脚本耗时久的核心原因是频繁触发Spreadsheet API调用（比如循环里逐个读写单元格、重复读取相同数据），以及不必要的同步操作。下面是针对性的优化方案，能大幅压缩运行时间：

1. 批量读写数据，杜绝循环内API调用 #

这是最关键的优化点：原脚本在assignForRoleHalf里循环逐个检查单元格、设置值，每次getCell/setValue都会触发一次API请求，累积下来耗时极长。改成先批量读取数据到内存，处理完后一次性写入：

修改assignForRoleHalf函数： #

function assignForRoleHalf(ss, sheet, roles, role, assignments, lastRow, isSecondHalf, planningDataCache, roleNeeds) {
    var roleInfo = roles[role];
    var needed = roleNeeds[role];
    if (needed <= 0) return;

    var rangeIndex = isSecondHalf ? 1 : 0;
    if (roleInfo.planningRanges.length <= rangeIndex) {
        Logger.log(`No planning range for ${role} in the ${isSecondHalf ? "second" : "first"} half.`);
        return;
    }

    var planningRange = roleInfo.planningRanges[rangeIndex];
    // 从缓存读取规划区域数据，没有则读取并缓存
    if (!planningDataCache[planningRange]) {
        var range = ss.getRange(planningRange);
        planningDataCache[planningRange] = {
            values: range.getValues(),
            range: range
        };
    }
    var planData = planningDataCache[planningRange].values;
    var eligibleNames = collectEligibleIndividuals(assignments, role, isSecondHalf, roles, employeeData, roleInfo.skillColumn);
    shuffleArray(eligibleNames);

    var filledCount = 0;
    // 内存中遍历数据，填充空位
    for (let r = 0; r < planData.length; r++) {
        if (filledCount >= needed) break;
        if (!planData[r][0]) {
            let name = eligibleNames[filledCount];
            planData[r][0] = name;
            recordAssignment(assignments, name, role, isSecondHalf, roles);
            filledCount++;
        }
    }
    // 一次性写入修改后的数据
    planningDataCache[planningRange].range.setValues(planData);
    // 更新所需人数缓存
    roleNeeds[role] = needed - filledCount;
    // 最后统一更新单元格，不用每次写
    sheet.getRange(roleInfo.neededCell).setValue(roleNeeds[role]);
    Logger.log(`Assignments for ${role} in the ${isSecondHalf ? "second" : "first"} half completed.`);
}

然后在assignShiftsBasedOnPlanningNeeds里初始化缓存：

function assignShiftsBasedOnPlanningNeeds() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Instellingen2");
    var sheetje = ss.getSheetByName("Zondag Avond 2.0");
    if (!sheet) {
        Logger.log("Sheet 'Settings' not found.");
        return;
    }

    var lastRow = sheet.getLastRow();
    var totalNeeded = parseInt(sheet.getRange("B40").getValue(), 10);
    var totalOnPlanning = parseInt(sheet.getRange("B41").getValue(), 10);
    if (totalNeeded !== totalOnPlanning) {
        SpreadsheetApp.getUi().alert("Foutje", "Het aantal mensen dat ingepland moet worden is niet gelijk aan het aantal posities dat je wilt inplannen.", SpreadsheetApp.getUi().ButtonSet.OK);
        return;
    }

    var roles = {
        // 原有角色定义不变
    };

    var assignments = {};
    var planningDataCache = {}; // 缓存规划区域数据
    var roleNeeds = {};
    // 预加载所有角色的所需人数
    Object.keys(roles).forEach(role => {
        roleNeeds[role] = parseInt(sheet.getRange(roles[role].neededCell).getValue(), 10);
    });
    // 预加载员工姓名和技能数据
    var employeeData = {
        names: sheet.getRange("I2:I" + lastRow).getValues().map(row => row[0].trim()),
        skills: {}
    };
    // 提前加载所有技能列数据
    Object.keys(roles).forEach(role => {
        var col = roles[role].skillColumn;
        if (!employeeData.skills[col]) {
            employeeData.skills[col] = sheet.getRange(col + "2:" + col + lastRow).getValues().map(row => row[0]);
        }
    });

    // 第一阶段分配
    Object.keys(roles).forEach(role => assignForRoleHalf(ss, sheet, roles, role, assignments, lastRow, false, planningDataCache, roleNeeds));
    // 第二阶段分配
    Object.keys(roles).filter(role => roles[role].doublePlan).forEach(role => assignForRoleHalf(ss, sheet, roles, role, assignments, lastRow, true, planningDataCache, roleNeeds));

    Logger.log("All assignments complete.");
    Logger.log(assignments);

    // 批量读写troubleshoot数据
    var values = sheetje.getRange('J14:J15').getValues();
    sheetje.getRange('M14:M15').setValues(values);
    reassignEmployees();
}

2. 移除不必要的SpreadsheetApp.flush() #

原脚本里大量使用flush()，这个方法会强制脚本和表格同步，非常耗时。你的脚本不需要中间同步，直接删掉所有SpreadsheetApp.flush()即可。

3. 优化assignments数据结构，提升查询效率 #

原脚本在collectEligibleIndividuals里遍历所有已分配用户，效率极低。改成给每个用户的记录添加标记，直接判断：

修改recordAssignment函数： #

function recordAssignment(assignments, name, role, isSecondHalf, roles) {
    let assignmentNotation = role + (isSecondHalf ? ' 2nd Half' : ' 1st Half');
    if (!assignments[name]) {
        assignments[name] = {
            roles: [],
            hasNonDoublePlan: false // 标记是否已分配非双班角色
        };
    }
    assignments[name].roles.push(assignmentNotation);
    // 如果是双班禁用的角色，标记为已分配非双班
    if (!roles[role].doublePlan) {
        assignments[name].hasNonDoublePlan = true;
    }
}

优化collectEligibleIndividuals函数： #

function collectEligibleIndividuals(assignments, role, isSecondHalf, roles, employeeData, skillColumn) {
    var eligible = [];
    var names = employeeData.names;
    var skills = employeeData.skills[skillColumn];

    for (let i = 0; i < skills.length; i++) {
        let name = names[i];
        let skill = skills[i];
        if (skill === true || skill === "TRUE") {
            let userAssignments = assignments[name];
            // 非双班角色：已分配过任何角色就跳过
            if (!roles[role].doublePlan && userAssignments) continue;
            // 双班角色第二阶段：已分配过同角色第一阶段就跳过
            if (roles[role].doublePlan && isSecondHalf && userAssignments?.roles.includes(role + ' 1st Half')) continue;
            // 已分配非双班角色的，直接跳过
            if (userAssignments?.hasNonDoublePlan) continue;

            eligible.push(name);
        }
    }
    return eligible;
}

其他小优化 #

• 原脚本里copyNeeded函数的单元格读写可以改成批量操作：

  function copyNeeded() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var settings = ss.getSheetByName("Settings");
      var instellingen2 = ss.getSheetByName("Instellingen");
      // 批量读取
      var neededValues = settings.getRange("C6:C25").getValues();
      // 批量写入
      instellingen2.getRange("B2:B15").setValues(neededValues.slice(0,14));
      instellingen2.getRange("B18:B23").setValues(neededValues.slice(14,20));
      ensureMinimumSkillsForMultipleRoles();
  }
  

这些优化能把脚本运行时间从5分钟压缩到几十秒甚至更短，核心思路就是减少API调用次数，尽量在内存里处理数据，最后一次性写入。

内容的提问来源于stack exchange，提问作者Thomas van Dooremaal