wiki:facility:software:scheduling_documentation

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision		 Previous revision
wiki:facility:software:scheduling_documentation [2025/04/16 11:48] – created Max Breitmeyerwiki:facility:software:scheduling_documentation [2025/05/04 16:22] (current) – Shanmukha Doddipatla
Line 1: Line 1:
-START HERE+**Overview** 
 + 
 +The Observatory Shift Scheduler is a Google Apps Script that automatically assigns staff to different shift types based on: 
 +Staff availability submissions 
 +Seasonal shift timeframes 
 +Fair distribution of shifts among operators 
 +Specific scheduling constraints 
 +The script reads data from multiple sheets, performs the scheduling algorithm, and outputs the results to a "Scheduled Shifts" sheet. It can also send email notifications to staff about their assignments. 
 + 
 +**How It Works** 
 + 
 +Data Sources 
 +The script uses three key data sources: 
 +Shift Timeframes sheet: Contains seasonal information about shift start times 
 +Start Date: Date when this timeframe becomes active 
 +Sunset Time: Time of sunset for this period 
 +Sunrise Time: Time of sunrise for this period 
 +Night Duration: Duration of night in hours 
 +ES 1 Start Time: The start time for Evening Shift 1 in this period 
 +Availability sheet: Contains staff availability submissions for different shifts 
 +First column: Staff names 
 +Second column: Staff email addresses 
 +Subsequent columns: Boolean values (TRUE/FALSE) indicating availability for specific shifts 
 +Column headers format: "Day ShiftType" (e.g., "Mon ES1", "Tue GS2") 
 +Current date + 7 days: Used to determine which seasonal timeframe to use 
 +The script adds 7 days to the current date to calculate shifts for the upcoming week 
 +This allows scheduling to be done one week in advance 
 + 
 +**Workflow** 
 + 
 +The script determines the correct shift timeframe based on the current date + 7 days 
 +It looks up the most recent applicable row in the Shift Timeframes sheet 
 +The timeframe determines the ES1 start time which affects other shifts 
 +It identifies which shift types are available (ES 1, ES 2, GS 1, GS 2, and sometimes MS 1 and MS 2) 
 +ES and GS shifts are always included 
 +MS shifts are only included when ES1 starts between 16:00 and 17:30 
 +This logic is based on observatory-specific requirements 
 +It retrieves staff availability from the "Availability" sheet 
 +Maps column headers to full shift names 
 +Creates a data structure tracking which employees are available for each shift 
 +Excludes shifts that aren't applicable for the current timeframe 
 +It assigns shifts based on availability, constraints, and fair distribution 
 +First tries to give everyone at least one shift 
 +Then fills remaining shifts prioritizing those with fewer assignments 
 +Ensures employees who work GS shifts get the next day off 
 +Tries to distribute different shift types among employees 
 +Uses randomization to ensure fairness among equally-qualified staff 
 +Limits employees to a maximum of 3 shifts each 
 +It outputs the schedule to the "Scheduled Shifts" sheet 
 +Organized by day of the week 
 +Shows which employee is assigned to each shift 
 +Includes a summary of filled vs. unfilled shifts 
 +Lists any shifts that couldn't be assigned 
 +If not in TEST_MODE, it sends email notifications to staff 
 +Emails contain personalized schedules for each employee 
 +The email format is clean and professional 
 + 
 + 
 +**Script Components** 
 + 
 +Key Functions 
 + 
 +//getShiftDuration()// 
 + 
 +This function determines the shift timeframe to use based on date. 
 +Reads the "Shift Timeframes" sheet 
 +Uses current date + 7 days to find the appropriate timeframe 
 +Iterates through rows in the Shift Timeframes sheet to find the latest applicable row 
 +Returns start time for ES1 and other timeframe info (sunset, sunrise, etc.) 
 +Returns null if no valid timeframe is found 
 +//getShiftTimings()// 
 + 
 +This function determines which shift types to include based on the ES1 start time. 
 +Calls getShiftDuration() to get the ES1 start time 
 +Always includes ES 1, ES 2, GS 1, and GS 2 shifts 
 +Calculates total minutes from ES1 start time (converts HH:MM to minutes) 
 +Includes MS 1 and MS 2 shifts only if ES1 starts between 16:00 (960 minutes) and 17:30 (1050 minutes) 
 +Returns a mapping of shift types to their start times 
 +//scheduleShifts()// 
 + 
 +This is the main entry point function that: 
 +Clears/creates the "Scheduled Shifts" sheet 
 +Calls scheduleAllShifts() to get assignments 
 +Groups shift assignments by day for readability 
 +Formats the results for display with a specific output format 
 +Creates a summary of filled/unfilled shifts 
 +Handles email notifications if not in TEST_MODE 
 +Autoformats the output sheet for readability 
 +//scheduleAllShifts()// 
 + 
 +This is the core scheduling algorithm that: 
 +Reads the "Availability" sheet and retrieves the headers 
 +Gets shift timings from getShiftTimings() 
 +Creates a mapping between availability column headers and full shift names 
 +Initializes tracking variables for shift assignments and employee shift counts 
 +Gathers employee availability for each possible shift 
 +Prioritizes employees with fewer shifts for fair distribution 
 +Applies logic to ensure no one works more than the maximum allowed shifts 
 +Implements special rules for different shift types (like rest days after GS shifts) 
 +Returns a detailed object with assignment results and statistics 
 +sendEmailNotifications(employeeShifts) 
 +This function sends email notifications to staff about their assigned shifts. 
 +Checks if TEST_MODE is active (skips emails if true) 
 +Retrieves email addresses using getEmployeeEmail() 
 +Formats a personalized email for each employee with their shift assignments 
 +Uses MailApp.sendEmail to send the notifications 
 +Logs confirmation of sent emails 
 +//shuffleArray(array)// 
 + 
 +Helper function that randomly shuffles arrays using the Fisher-Yates algorithm for fair distribution. 
 +Takes an array as input 
 +Randomly reorders the elements in place 
 +Returns the shuffled array 
 + 
 +//getEmployeeEmail(employeeName, dataValues)// 
 + 
 +Helper function that retrieves email addresses from the availability data. 
 +Searches for the employee name in the first column of data values 
 +Returns the corresponding email from the second column 
 +Returns null if no match is found 
 +**Configuration Options** 
 + 
 +//Global Settings// 
 + 
 +TEST_MODE (boolean): When true, email notifications are disabled 
 +Future date offset (7 days): Added to the current date for timeframe selection 
 +Shift Assignment Rules 
 +Maximum shifts per employee: 3 (can be adjusted in the scheduleAllShifts() function) 
 +Rest day rule: Employees who work a GS shift can't work the next day 
 +Shift variety: The algorithm tries to ensure employees get different shift types 
 +**Scheduling Logic** 
 + 
 +The scheduling algorithm is implemented through multiple phases and constraints to ensure fair and effective shift distribution. 
 +Prioritization Phases 
 +The algorithm uses two prioritization phases defined in the assignmentPhases array: 
 +Phase 1: Give everyone at least one shift 
 +function(availableEmployees, shiftKey) { 
 +  return availableEmployees[shiftKey].filter(emp =>  
 +    employeesWithNoShift.indexOf(emp) >= 0 && 
 +    !employeeDayAssignments[emp][shiftKey.split(" ")[0]] 
 +  ); 
 +} 
 +Filters employees who have zero shifts assigned 
 +Checks that the employee isn't already assigned to another shift on the same day 
 +This ensures every operator gets at least one shift before anyone gets multiple 
 +Phase 2: Fair distribution 
 +function(availableEmployees, shiftKey) { 
 +  var shiftDay = shiftKey.split(" ")[0]; 
 +  var currentDayIndex = fullDayNames.indexOf(shiftDay); 
 +  var isGSShift = shiftKey.includes("GS 1") || shiftKey.includes("GS 2"); 
 +   
 +  return availableEmployees[shiftKey].filter(emp => { 
 +    // Various filtering criteria... 
 +  }); 
 +} 
 +Applies more complex filtering based on several criteria 
 +Prioritizes employees with fewer shifts 
 +Enforces rest days after GS shifts 
 +Promotes shift type variety 
 +Prevents exceeding maximum shifts per employee 
 +Specific Assignment Criteria 
 + 
 +The algorithm considers these factors when assigning shifts: 
 +Availability Check 
 + 
 +Employee must have indicated availability for the specific shift 
 +Only employees with TRUE in the corresponding availability column are considered 
 +Maximum Shift Limit 
 +if (employeeShiftCount[emp] >= maxShiftsPerEmployee) return false; 
 +No employee gets more than 3 shifts (configurable via maxShiftsPerEmployee) 
 +Once an employee reaches this limit, they're removed from all future availability pools 
 +Day Assignment Tracking 
 + 
 +if (employeeDayAssignments[emp][shiftDay]) return false; 
 +Prevents multiple shifts on the same day for an employee 
 +Uses the employeeDayAssignments object to track which days each employee is assigned 
 +GS Shift Rest Day Rule 
 + 
 +// Check if employee worked a GS shift yesterday 
 +var prevDayIndex = (currentDayIndex - 1 + 7) % 7; 
 +var prevDay = fullDayNames[prevDayIndex]; 
 + 
 + 
 +for (var i = 0; i < employeeShifts[emp].length; i++) { 
 +  var pastShift = employeeShifts[emp][i]; 
 +  if (pastShift.includes(prevDay) && (pastShift.includes("GS 1") || pastShift.includes("GS 2"))) { 
 +    return false; // Needs rest day after GS shift 
 +  } 
 +} 
 +If an employee works a GS shift, they cannot work the next day 
 +This is enforced by checking previous day assignments for GS shifts 
 +Shift Type Variety Promotion 
 +// For GS shifts, check if they already have one 
 +if (isGSShift) { 
 +  var hasGSShift = employeeShifts[emp].some(shift =>  
 +    shift.includes("GS 1") || shift.includes("GS 2") 
 +  ); 
 +   
 +  if (hasGSShift && employeeShiftCount[emp] > 0) { 
 +    return false; 
 +  } 
 +} 
 + 
 + 
 +// For non-GS shifts, prioritize those without ES/MS shifts 
 +if (!isGSShift && employeeShiftCount[emp] > 0) { 
 +  var hasESorMSShift = employeeShifts[emp].some(shift =>  
 +    shift.includes("ES 1") || shift.includes("ES 2") ||  
 +    shift.includes("MS 1") || shift.includes("MS 2") 
 +  ); 
 +   
 +  if (!hasESorMSShift) { 
 +    return true; // Prioritize 
 +  } 
 +} 
 +Promotes shift type variety by avoiding assigning the same type repeatedly 
 +For GS shifts: deprioritizes employees who already have a GS shift 
 +For ES/MS shifts: prioritizes employees who don't yet have an ES or MS shift 
 +Fairness Through Randomization 
 + 
 +// Shuffle employees with the same shift count 
 +if (i > startIndex) { 
 +  var group = eligibleEmployees.slice(startIndex, i); 
 +  group = shuffleArray(group); 
 +  for (var j = 0; j < group.length; j++) { 
 +    eligibleEmployees[startIndex + j] = group[j]; 
 +  } 
 +} 
 +After prioritizing by shift count and type, adds randomness within priority groups 
 +Uses the shuffleArray() function to randomly reorder employees with the same priority 
 +This prevents the same employees from always getting preferred shifts 
 +The scheduling logic combines these rules to create a fair, balanced schedule that respects constraints while maximizing shift coverage. 
 +Output Format 
 + 
 +The script outputs the schedule to the "Scheduled Shifts" sheet in this format: 
 +Monday Evening --> Tuesday Morning 
 +Shift: Prep + ES 1, Assigned to: Employee1 
 +Shift: ES 2, Assigned to: Employee2 
 +Shift: GS 1, Assigned to: Employee3 
 +Shift: GS 2, Assigned to: Employee4 
 + 
 +Tuesday Evening --> Wednesday Morning 
 +... 
 + 
 +Shifts Summary: 20/28 filled 
 + 
 +Unassigned Shifts: 
 +Monday MS 1 
 +Monday MS 2 
 +... 
 +**Troubleshooting** 
 + 
 +Common Issues 
 + 
 +No Results from Scheduling 
 + 
 +Cause: Could be missing data in the Shift Timeframes or Availability sheets 
 +Solution:  
 + 
 +Check that both sheets exist with exactly these names: "Shift Timeframes" and "Availability" 
 +Verify the Shift Timeframes sheet has data in the expected format 
 +Check if any dates in the Shift Timeframes sheet are applicable to current date + 7 days 
 +Look for error messages in the script execution logs 
 +Missing Shift Types (MS Shifts) 
 +Cause: MS shifts only appear when ES1 start time is between 16:00-17:30 
 + 
 +Solution:  
 + 
 +This is expected behavior based on the condition: if (totalMinutes >= 960 && totalMinutes <= 1050) 
 +If MS shifts should be included regardless of time, modify the getShiftTimings() function 
 +To debug, add a log statement: `Logger.log("ES1 start time is: " + es1Start + " (" + totalMinutes + " minutes 
 + 
 + 
 +Making Updates 
 + 
 +This section provides detailed guidance on common modifications your team might need to make. 
 +Modifying the Date Offset 
 +Currently, the script adds 7 days to the current date to determine the shift timeframe. To change this: 
 +// In getShiftDuration() function 
 + 
 +var today = new Date(); 
 +today.setDate(today.getDate() + 7); // Change 7 to your desired days(based on when we schedule) 
 +If you want to make this configurable at the top of the script, add: 
 +var DAYS_TO_ADD = 7; // Configure how many days to look ahead 
 +// Then in getShiftDuration() 
 + 
 +var today = new Date(); 
 +today.setDate(today.getDate() + DAYS_TO_ADD); 
 +Changing Maximum Shifts Per Employee 
 +To change the maximum number of shifts per employee: 
 +// In scheduleAllShifts() function 
 + 
 +var maxShiftsPerEmployee = 3; // Change to desired number 
 +Consider adding this as a global constant at the top of the script for easier configuration: 
 +var MAX_SHIFTS_PER_EMPLOYEE = 3; // Configure maximum shifts 
 + 
 +// Then update the reference in scheduleAllShifts() 
 +var maxShiftsPerEmployee = MAX_SHIFTS_PER_EMPLOYEE; 
 + 
 + 
 +Modifying Shift Types 
 + 
 +If you need to add or remove shift types, update the checks in the getShiftTimings() function. 
 +For example, to always include MS shifts regardless of ES1 start time: 
 +// Always include these shift types 
 +shiftTimings["ES 1"] = es1Start; 
 +shiftTimings["ES 2"] = es1Start; 
 +shiftTimings["GS 1"] = es1Start; 
 +shiftTimings["GS 2"] = es1Start; 
 +shiftTimings["MS 1"] = es1Start; // Always include MS shifts 
 +shiftTimings["MS 2"] = es1Start; // Always include MS shifts 
 + 
 +// Remove or comment out the conditional MS shift logic 
 +// if (totalMinutes >= 960 && totalMinutes <= 1050) { ... } 
 +To add a new shift type: 
 +// Add new shift type 
 +shiftTimings["NEW_SHIFT"] = es1Start; 
 + 
 +// Don't forget to update the shifts array in scheduleShifts() 
 +var shifts = ["Prep + ES 1", "ES 2", "GS 1", "GS 2", "MS 1", "MS 2", "NEW_SHIFT"]; 
 +Changing MS Shift Inclusion Criteria 
 +To modify when MS shifts are included: 
 +// Change the time range for MS shifts 
 +// Current: 16:00 (960 min) to 17:30 (1050 min) 
 +if (totalMinutes >= 900 && totalMinutes <= 1080) { // Changed to 15:00-18:00 
 +  Logger.log("ES1 start time is between 15:00 and 18:00, including MS shifts"); 
 +  shiftTimings["MS 1"] = es1Start; 
 +  shiftTimings["MS 2"] = es1Start; 
 +} else { 
 +  Logger.log("ES1 start time is outside 15:00-18:00 range, excluding MS shifts"); 
 +} 
 +Changing Email Notification Format 
 + 
 +To modify the email content, update the sendEmailNotifications() function: 
 +var subject = "Your Observatory Shift Assignment"; // Change subject line 
 + 
 +// Change email body format 
 + 
 +var body = "Hello " + employee + ",\n\n" + 
 +           "You have been assigned the following shifts:\n\n" + 
 +           assignedShifts + "\n\n" + 
 +           "Please acknowledge receipt of this schedule.\n\n" + 
 +           "Regards,\nObservatory Management Team"; 
 +Adding Different Constraints 
 + 
 +To add new scheduling constraints, update the filtering logic in the assignmentPhases array in the scheduleAllShifts() function. 
 +Example - Adding a constraint to prevent working more than 2 consecutive days: 
 +// Check if employee is already working the previous two days 
 +var prevDay1Index = (currentDayIndex - 1 + 7) % 7; 
 +var prevDay2Index = (currentDayIndex - 2 + 7) % 7; 
 +var prevDay1 = fullDayNames[prevDay1Index]; 
 +var prevDay2 = fullDayNames[prevDay2Index]; 
 + 
 +var workingPrevDay1 = employeeDayAssignments[emp][prevDay1]; 
 +var workingPrevDay2 = employeeDayAssignments[emp][prevDay2]; 
 + 
 +// If working both previous days, don't assign this day 
 +if (workingPrevDay1 && workingPrevDay2) return false; 
 +Manually Setting a Specific Date 
 + 
 +If you need to run the script for a specific date instead of current date + 7 days, you can replace: 
 +var today = new Date(); 
 +today.setDate(today.getDate() + 7); 
 +With: 
 +var today = new Date("YYYY-MM-DD"); // Replace with your target date 
 +Implementing a Date Selection Dialog 
 +For more flexibility, you could implement a date picker dialog: 
 + 
 +function showDatePickerDialog() { 
 +  var ui = SpreadsheetApp.getUi(); 
 +  var response = ui.prompt( 
 +    'Schedule for Date', 
 +    'Enter the date to schedule for (YYYY-MM-DD):', 
 +    ui.ButtonSet.OK_CANCEL 
 +  ); 
 + 
 +  if (response.getSelectedButton() == ui.Button.OK) { 
 +    var dateString = response.getResponseText(); 
 +    // Validate date format 
 +    var dateRegex = /^\d{4}-\d{2}-\d{2}$/; 
 +    if (dateRegex.test(dateString)) { 
 +      // Override the current date 
 +      var schedulingDate = new Date(dateString); 
 +      // Call scheduling function with this date 
 +      scheduleShiftsForDate(schedulingDate); 
 +    } else { 
 +      ui.alert('Invalid date format. Please use YYYY-MM-DD format.'); 
 +    } 
 +  } 
 +} 
 + 
 +// Modified scheduling function that accepts a date parameter 
 +function scheduleShiftsForDate(targetDate) { 
 +  // Store the original date calculation 
 +  var originalGetShiftDuration = getShiftDuration; 
 +   
 +  // Override getShiftDuration temporarily 
 +  getShiftDuration = function() { 
 +    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Shift Timeframes"); 
 +    if (!sheet) { 
 +      Logger.log("Error: 'Shift Timeframes' sheet not found."); 
 +      return null; 
 +    } 
 + 
 +    var data = sheet.getDataRange().getValues(); 
 +    var today = targetDate; // Use the provided date 
 +    // Rest of function remains the same... 
 +  }; 
 +   
 +  // Call the regular scheduling function 
 +  scheduleShifts(); 
 +   
 +  // Restore the original function 
 +  getShiftDuration = originalGetShiftDuration; 
 +} 
 +Adding Custom Menu Items 
 +You can add menu items to make the script more user-friendly: 
 +function onOpen() { 
 +  var ui = SpreadsheetApp.getUi(); 
 +  ui.createMenu('Observatory Scheduler') 
 +    .addItem('Schedule Shifts', 'scheduleShifts') 
 +    .addItem('Schedule for Specific Date', 'showDatePickerDialog') 
 +    .addSeparator() 
 +    .addItem('Toggle Email Notifications', 'toggleEmailNotifications') 
 +    .addToUi(); 
 +} 
 + 
 +function toggleEmailNotifications() { 
 +  TEST_MODE = !TEST_MODE; 
 +  var ui = SpreadsheetApp.getUi(); 
 +  ui.alert('Email notifications are now ' +  
 +           (TEST_MODE ? 'DISABLED' : 'ENABLED')); 
 +} 
 + 
 +