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
T

Thomas van Dooremaal

Guest
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:

Code:
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") {
  clear();
} 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']);
  rangeList.clearContent();

  copyRange();

}

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});

    ensureMinimumSkillsForMultipleRoles();
    

}

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.");
        return;
    }


    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);
    return;
  }

    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));
    SpreadsheetApp.flush();
    // 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));
    SpreadsheetApp.flush();
    Logger.log("All assignments complete.");
    SpreadsheetApp.flush();
    Logger.log(assignments);

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


    sheetje.getRange("M14").setValue(troubleshoota1);
    sheetje.getRange("M15").setValue(troubleshoota2);
    SpreadsheetApp.flush();
    reassignEmployees();
}

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
    SpreadsheetApp.flush();
    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];
    var eligibleNames = collectEligibleIndividuals(sheet, lastRow, roleInfo.skillColumn, assignments, role, isSecondHalf);
    SpreadsheetApp.flush();
    // Randomize eligible names to prevent bias in selection
    shuffleArray(eligibleNames);

    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()) {
                cell.setValue(name);
                recordAssignment(assignments, name, role, isSecondHalf);
                needed--;
                break; // Move to the next eligible individual after successful assignment
            }
        }
    });

    // Update the 'needed' count for the role
    sheet.getRange(roleInfo.neededCell).setValue(needed);
    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();
    SpreadsheetApp.flush();
    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]) {
        assignments[name].push(assignmentNotation);
    } 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...
 
Top