This is an old revision of the document!

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

} This documentation should help your team understand how the script works and make necessary updates in the future.