wiki:facility:software:scheduling_documentation

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
wiki:facility:software:scheduling_documentation [2025/05/04 16:15] – Shanmukha Doddipatlawiki:facility:software:scheduling_documentation [2025/05/04 16:22] (current) – Shanmukha Doddipatla
Line 60: Line 60:
  
 Key Functions Key Functions
 +
 //getShiftDuration()// //getShiftDuration()//
 +
 This function determines the shift timeframe to use based on date. This function determines the shift timeframe to use based on date.
 Reads the "Shift Timeframes" sheet Reads the "Shift Timeframes" sheet
Line 68: Line 70:
 Returns null if no valid timeframe is found Returns null if no valid timeframe is found
 //getShiftTimings()// //getShiftTimings()//
 +
 This function determines which shift types to include based on the ES1 start time. This function determines which shift types to include based on the ES1 start time.
 Calls getShiftDuration() to get the ES1 start time Calls getShiftDuration() to get the ES1 start time
Line 75: Line 78:
 Returns a mapping of shift types to their start times Returns a mapping of shift types to their start times
 //scheduleShifts()// //scheduleShifts()//
 +
 This is the main entry point function that: This is the main entry point function that:
 Clears/creates the "Scheduled Shifts" sheet Clears/creates the "Scheduled Shifts" sheet
Line 83: Line 87:
 Handles email notifications if not in TEST_MODE Handles email notifications if not in TEST_MODE
 Autoformats the output sheet for readability Autoformats the output sheet for readability
- 
 //scheduleAllShifts()// //scheduleAllShifts()//
 +
 This is the core scheduling algorithm that: This is the core scheduling algorithm that:
 Reads the "Availability" sheet and retrieves the headers Reads the "Availability" sheet and retrieves the headers
Line 103: Line 107:
 Logs confirmation of sent emails Logs confirmation of sent emails
 //shuffleArray(array)// //shuffleArray(array)//
 +
 Helper function that randomly shuffles arrays using the Fisher-Yates algorithm for fair distribution. Helper function that randomly shuffles arrays using the Fisher-Yates algorithm for fair distribution.
 Takes an array as input Takes an array as input
Line 109: Line 114:
  
 //getEmployeeEmail(employeeName, dataValues)// //getEmployeeEmail(employeeName, dataValues)//
 +
 Helper function that retrieves email addresses from the availability data. Helper function that retrieves email addresses from the availability data.
 Searches for the employee name in the first column of data values Searches for the employee name in the first column of data values
Line 114: Line 120:
 Returns null if no match is found Returns null if no match is found
 **Configuration Options** **Configuration Options**
 +
 //Global Settings// //Global Settings//
 +
 TEST_MODE (boolean): When true, email notifications are disabled TEST_MODE (boolean): When true, email notifications are disabled
 Future date offset (7 days): Added to the current date for timeframe selection Future date offset (7 days): Added to the current date for timeframe selection
Line 122: Line 130:
 Shift variety: The algorithm tries to ensure employees get different shift types Shift variety: The algorithm tries to ensure employees get different shift types
 **Scheduling Logic** **Scheduling Logic**
 +
 The scheduling algorithm is implemented through multiple phases and constraints to ensure fair and effective shift distribution. The scheduling algorithm is implemented through multiple phases and constraints to ensure fair and effective shift distribution.
 Prioritization Phases Prioritization Phases
Line 151: Line 160:
 Prevents exceeding maximum shifts per employee Prevents exceeding maximum shifts per employee
 Specific Assignment Criteria Specific Assignment Criteria
 +
 The algorithm considers these factors when assigning shifts: The algorithm considers these factors when assigning shifts:
 Availability Check Availability Check
 +
 Employee must have indicated availability for the specific shift Employee must have indicated availability for the specific shift
 Only employees with TRUE in the corresponding availability column are considered Only employees with TRUE in the corresponding availability column are considered
Line 160: Line 171:
 Once an employee reaches this limit, they're removed from all future availability pools Once an employee reaches this limit, they're removed from all future availability pools
 Day Assignment Tracking Day Assignment Tracking
 +
 if (employeeDayAssignments[emp][shiftDay]) return false; if (employeeDayAssignments[emp][shiftDay]) return false;
 Prevents multiple shifts on the same day for an employee Prevents multiple shifts on the same day for an employee
 Uses the employeeDayAssignments object to track which days each employee is assigned Uses the employeeDayAssignments object to track which days each employee is assigned
 GS Shift Rest Day Rule GS Shift Rest Day Rule
 +
 // Check if employee worked a GS shift yesterday // Check if employee worked a GS shift yesterday
 var prevDayIndex = (currentDayIndex - 1 + 7) % 7; var prevDayIndex = (currentDayIndex - 1 + 7) % 7;
Line 205: Line 218:
 For ES/MS shifts: prioritizes employees who don't yet have an ES or MS shift For ES/MS shifts: prioritizes employees who don't yet have an ES or MS shift
 Fairness Through Randomization Fairness Through Randomization
 +
 // Shuffle employees with the same shift count // Shuffle employees with the same shift count
 if (i > startIndex) { if (i > startIndex) {
Line 218: Line 232:
 The scheduling logic combines these rules to create a fair, balanced schedule that respects constraints while maximizing shift coverage. The scheduling logic combines these rules to create a fair, balanced schedule that respects constraints while maximizing shift coverage.
 Output Format Output Format
 +
 The script outputs the schedule to the "Scheduled Shifts" sheet in this format: The script outputs the schedule to the "Scheduled Shifts" sheet in this format:
 Monday Evening --> Tuesday Morning Monday Evening --> Tuesday Morning
Line 235: Line 250:
 ... ...
 **Troubleshooting** **Troubleshooting**
 +
 Common Issues Common Issues
 +
 No Results from Scheduling No Results from Scheduling
 +
 Cause: Could be missing data in the Shift Timeframes or Availability sheets Cause: Could be missing data in the Shift Timeframes or Availability sheets
 Solution:  Solution: 
 +
 Check that both sheets exist with exactly these names: "Shift Timeframes" and "Availability" Check that both sheets exist with exactly these names: "Shift Timeframes" and "Availability"
 Verify the Shift Timeframes sheet has data in the expected format Verify the Shift Timeframes sheet has data in the expected format
Line 245: Line 264:
 Missing Shift Types (MS Shifts) Missing Shift Types (MS Shifts)
 Cause: MS shifts only appear when ES1 start time is between 16:00-17:30 Cause: MS shifts only appear when ES1 start time is between 16:00-17:30
 +
 Solution:  Solution: 
 +
 This is expected behavior based on the condition: if (totalMinutes >= 960 && totalMinutes <= 1050) 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 If MS shifts should be included regardless of time, modify the getShiftTimings() function
Line 252: Line 273:
  
 Making Updates Making Updates
 +
 This section provides detailed guidance on common modifications your team might need to make. This section provides detailed guidance on common modifications your team might need to make.
 Modifying the Date Offset Modifying the Date Offset
 Currently, the script adds 7 days to the current date to determine the shift timeframe. To change this: Currently, the script adds 7 days to the current date to determine the shift timeframe. To change this:
 // In getShiftDuration() function // In getShiftDuration() function
 +
 var today = new Date(); var today = new Date();
 today.setDate(today.getDate() + 7); // Change 7 to your desired days(based on when we schedule) 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: 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 var DAYS_TO_ADD = 7; // Configure how many days to look ahead
- 
 // Then in getShiftDuration() // Then in getShiftDuration()
 +
 var today = new Date(); var today = new Date();
 today.setDate(today.getDate() + DAYS_TO_ADD); today.setDate(today.getDate() + DAYS_TO_ADD);
Line 267: Line 290:
 To change the maximum number of shifts per employee: To change the maximum number of shifts per employee:
 // In scheduleAllShifts() function // In scheduleAllShifts() function
 +
 var maxShiftsPerEmployee = 3; // Change to desired number var maxShiftsPerEmployee = 3; // Change to desired number
 Consider adding this as a global constant at the top of the script for easier configuration: Consider adding this as a global constant at the top of the script for easier configuration:
Line 276: Line 300:
  
 Modifying Shift Types Modifying Shift Types
 +
 If you need to add or remove shift types, update the checks in the getShiftTimings() function. 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: For example, to always include MS shifts regardless of ES1 start time:
Line 306: Line 331:
 } }
 Changing Email Notification Format Changing Email Notification Format
 +
 To modify the email content, update the sendEmailNotifications() function: To modify the email content, update the sendEmailNotifications() function:
 var subject = "Your Observatory Shift Assignment"; // Change subject line var subject = "Your Observatory Shift Assignment"; // Change subject line
  
 // Change email body format // Change email body format
 +
 var body = "Hello " + employee + ",\n\n" + var body = "Hello " + employee + ",\n\n" +
            "You have been assigned the following shifts:\n\n" +            "You have been assigned the following shifts:\n\n" +
Line 316: Line 343:
            "Regards,\nObservatory Management Team";            "Regards,\nObservatory Management Team";
 Adding Different Constraints Adding Different Constraints
 +
 To add new scheduling constraints, update the filtering logic in the assignmentPhases array in the scheduleAllShifts() function. 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: Example - Adding a constraint to prevent working more than 2 consecutive days:
Line 330: Line 358:
 if (workingPrevDay1 && workingPrevDay2) return false; if (workingPrevDay1 && workingPrevDay2) return false;
 Manually Setting a Specific Date 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: If you need to run the script for a specific date instead of current date + 7 days, you can replace:
 var today = new Date(); var today = new Date();
Line 337: Line 366:
 Implementing a Date Selection Dialog Implementing a Date Selection Dialog
 For more flexibility, you could implement a date picker dialog: For more flexibility, you could implement a date picker dialog:
 +
 function showDatePickerDialog() { function showDatePickerDialog() {
   var ui = SpreadsheetApp.getUi();   var ui = SpreadsheetApp.getUi();
Line 402: Line 432:
            (TEST_MODE ? 'DISABLED' : 'ENABLED'));            (TEST_MODE ? 'DISABLED' : 'ENABLED'));
 } }
-This documentation should help your team understand how the script works and make necessary updates in the future.+