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") {
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
}