OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Optimize script

  • Thread starter Thread starter Thomas van Dooremaal
  • Start date Start date

Thomas van Dooremaal

I have a apps script that makes a employee planning, but currently it is very slow as it is checking if someone is eligible and has not been planned yet to prevent duplicates.

Does someone see improvements to make it faster? As it takes around/more then 5 minutes.

Thanks in advance for the assistance, hereby the script:

function maakPlanning() {
  var response = Browser.msgBox('Planning Maken', 'Je staat op het punt de planning te maken. Dit duurt ongeveer 5 minuten. In de tussentijd kan je andere zaken doen zoals je mail beantwoorden etc. Zorg er wel voor dat je dit tabblad niet afsluit. Een ander tabblad openen of dit tablad naar de achtergrond doen kan wel. Zodra de planning klaar is kan je weer aanpassingen doen in deze sheet. Wil je hiermee doorgaan?', Browser.Buttons.YES_NO);
  if (response == "yes") {
} else {
  Logger.log('Het maken van de planning is geannuleerd!');

function clear() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Zondag Avond 2.0');
  var rangeList = sheet.getRangeList(['C9:C10', 'C13:C14', 'C19:C21', 'C27:C28', 'C31:C33', 'C36:C43', 'F9:F10', 'F13:F14', 'F19:F21', 'F27:F28', 'F31:F33', 'F36:F43', 'J6:J7', 'J10:J11', 'J14:J15', 'J18:J20', 'J23:J24', 'J27:J28', 'J31:J32', 'J36:J43', 'M6:M7', 'M10:M11', 'M14:M15', 'M18:M20', 'M23:M24', 'M27:M28', 'P6:P11', 'P14:P20', 'P23:P24', 'S6:S23', 'S26:S37', 'V6:V11', 'V14:V19', 'Y6:Y11', 'Y14:Y20', 'Y23:Y24', 'AB6:AB23', 'AB26:AB37', 'AE6:AE11', 'AE14:AE19', 'AH6:AX13', 'AH20:AX45']);



function copyNeeded() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var instellingen = ss.getSheetByName("Instellingen2");
    var instellingen2 = ss.getSheetByName("Instellingen");
    var settings = ss.getSheetByName("Settings");
    var needed1 = settings.getRange("C6:C19");
    var opslaan1 = instellingen2.getRange("B2:B15")
    var needed2 = settings.getRange("C20:C25");
    var opslaan2 = instellingen2.getRange("B18:B23")
    needed1.copyTo(opslaan1, {contentsOnly:true});
    needed2.copyTo(opslaan2, {contentsOnly:true});



function assignShiftsBasedOnPlanningNeeds() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Instellingen2");
    var sheetje = ss.getSheetByName("Zondag Avond 2.0"); // Adjust to match your actual sheet name
    if (!sheet) {
        Logger.log("Sheet 'Settings' not found.");

    var lastRow = sheet.getLastRow();

  // Check if the total amount of people needed matches the total on the planning
  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);

    var roles = {
    buddy: {
      neededCell: "B9",
      skillColumn: "BG",
      planningRanges: ["Zondag Avond 2.0!C27:C28"],
      doublePlan: false
    buddyvddag: {
      neededCell: "B10",
      skillColumn: "BG",
      planningRanges: ["Zondag Avond 2.0!F27:F28"],
      doublePlan: false
    dockmaster: {
      neededCell: "B2",
      skillColumn: "AR",
      planningRanges: ["Zondag Avond 2.0!J18:J21"],
      doublePlan: false
    bezorgmaster: {
      neededCell: "B3",
      skillColumn: "AT",
      planningRanges: ["Zondag Avond 2.0!M18:M21"],
      doublePlan: false
    duitsland: {
      neededCell: "B4",
      skillColumn: "AV",
      planningRanges: ["Zondag Avond 2.0!M27:M28"],
      doublePlan: false
    wmscoordinator: {
      neededCell: "B5",
      skillColumn: "AY",
      planningRanges: ["Zondag Avond 2.0!M6:M7"],
      doublePlan: false
    ascoordinator: {
      neededCell: "B6",
      skillColumn: "BA",
      planningRanges: ["Zondag Avond 2.0!J6:J7"],
      doublePlan: false
    truckerskantoor: {
      neededCell: "B7",
      skillColumn: "BC",
      planningRanges: ["Zondag Avond 2.0!J31:J32"],
      doublePlan: false
    lodge: {
      neededCell: "B8",
      skillColumn: "BE",
      planningRanges: ["Zondag Avond 2.0!J27:J28"],
      doublePlan: false
    tshala: {
      neededCell: "B11",
      skillColumn: "AE",
      planningRanges: ["Zondag Avond 2.0!J10:J11"],
      doublePlan: false
    tshalb: {
      neededCell: "B12",
      skillColumn: "AH",
      planningRanges: ["Zondag Avond 2.0!M10:M11"],
      doublePlan: false
    tsas: {
      neededCell: "B13",
      skillColumn: "AC",
      planningRanges: ["Zondag Avond 2.0!J14:J15"],
      doublePlan: false
    karrunnercbb: {
      neededCell: "B14",
      skillColumn: "AL",
      planningRanges: ["Zondag Avond 2.0!M23:M24"],
      doublePlan: false
    karrunnerdock: {
      neededCell: "B15",
      skillColumn: "AN",
      planningRanges: ["Zondag Avond 2.0!J23:J24"],
      doublePlan: false
    // Double planned positions
    packen: {
      neededCell: "B18",
      skillColumn: "J",
      planningRanges: ["Zondag Avond 2.0!P6:P11", "Zondag Avond 2.0!Y6:Y11"],
      doublePlan: true
    sorterencbb: {
      neededCell: "B19",
      skillColumn: "U",
      planningRanges: ["Zondag Avond 2.0!V14:V19", "Zondag Avond 2.0!AE14:AE19"],
      doublePlan: true
    sorterenpostnl: {
      neededCell: "B20",
      skillColumn: "S",
      planningRanges: ["Zondag Avond 2.0!V6:V11", "Zondag Avond 2.0!AE6:AE11"],
      doublePlan: true
    wms: {
      neededCell: "B21",
      skillColumn: "W",
      planningRanges: ["Zondag Avond 2.0!S6:S23", "Zondag Avond 2.0!AB6:AB23"],
      doublePlan: true
    aspicken: {
      neededCell: "B22",
      skillColumn: "Y",
      planningRanges: ["Zondag Avond 2.0!S26:S37", "Zondag Avond 2.0!AB26:AB37"],
      doublePlan: true
    runnera: {
      neededCell: "B23",
      skillColumn: "AJ",
      planningRanges: ["Zondag Avond 2.0!P23:P24", "Zondag Avond 2.0!Y23:Y24"],
      doublePlan: true

    var assignments = {}; // Tracks which roles each individual is assigned to, including halves

    // First Phase: Assign for the first half across all roles
    Object.keys(roles).forEach(role => assignForRoleHalf(ss, sheet, roles, role, assignments, lastRow, false));
    // Second Phase: Assign for the second half, only for roles that allow double planning
    Object.keys(roles).filter(role => roles[role].doublePlan).forEach(role => assignForRoleHalf(ss, sheet, roles, role, assignments, lastRow, true));
    Logger.log("All assignments complete.");

    var troubleshoota1 = sheetje.getRange('J14').getValue();
    var troubleshoota2 = sheetje.getRange('J15').getValue();


function assignForRoleHalf(ss, sheet, roles, role, assignments, lastRow, isSecondHalf) {
    var roleInfo = roles[role];
    var needed = parseInt(sheet.getRange(roleInfo.neededCell).getValue(), 10);
    if (needed <= 0) return; // Skip if no one is needed for this role
    var rangeIndex = isSecondHalf ? 1 : 0;
    if (roleInfo.planningRanges.length <= rangeIndex) {
        Logger.log(`No planning range for ${role} in the ${isSecondHalf ? "second" : "first"} half.`);

    var planningRange = roleInfo.planningRanges[rangeIndex];
    var eligibleNames = collectEligibleIndividuals(sheet, lastRow, roleInfo.skillColumn, assignments, role, isSecondHalf);
    // Randomize eligible names to prevent bias in selection

    eligibleNames.forEach(name => {
        if (needed <= 0) return; // Stop if needed count has been met
        for (let r = 1; r <= sheet.getRange(planningRange).getNumRows(); r++) {
            var cell = sheet.getRange(planningRange).getCell(r, 1);
            if (cell.isBlank()) {
                recordAssignment(assignments, name, role, isSecondHalf);
                break; // Move to the next eligible individual after successful assignment

    // Update the 'needed' count for the role
    Logger.log(`Assignments for ${role} in the ${isSecondHalf ? "second" : "first"} half completed.`);


function collectEligibleIndividuals(sheet, lastRow, skillColumn, assignments, role, doublePlan, roles) {
    var eligible = [];
    var skills = sheet.getRange(skillColumn + "2:" + skillColumn + lastRow).getValues();
    var names = sheet.getRange("I2:I" + lastRow).getValues();
    for (let i = 0; i < skills.length; i++) {
        let name = names[i][0].trim();
        let skill = skills[i][0];
        if (skill === true || skill === "TRUE") {
            // For non-double-plan roles, ensure not already assigned this shift
            if (!doublePlan && assignments[name]) continue;

            // For double-plan roles in the second half, ensure they weren't assigned to the same role in the first half
            if (doublePlan && assignments[name] && assignments[name].includes(role + ' 1st Half')) continue;

            // Ensure not already assigned to any non-double-plan role in any half
            let alreadyAssignedNonDoublePlan = Object.keys(assignments).some(assignedName => {
                return assignments[assignedName] && assignedName === name && assignments[assignedName].some(assgn => roles[assgn.replace(/ 1st Half| 2nd Half/g, '')]?.doublePlan === false);

            if (!alreadyAssignedNonDoublePlan) eligible.push(name);
    return eligible;

function recordAssignment(assignments, name, role, isSecondHalf) {
    // Record the assignment with a notation indicating which half of the shift
    let assignmentNotation = role + (isSecondHalf ? ' 2nd Half' : ' 1st Half');
    if (assignments[name]) {
    } else {
        assignments[name] = [assignmentNotation];
function shuffleArray(array) {
    for (let i = array.length - 1; i > 0; i--) {
        const j = Math.floor(Math.random() * (i + 1));
        [array[i], array[j]] = [array[j], array[i]]; // Swap elements
    return array; // Return the shuffled array

Continue reading...