const config = input.config({ title: 'Shift scheduler', description: 'Lets you assign individuals to particular shifts, based on their availability during that time.', items: [ input.config.table('shiftsTable', { label: '🛠Shifts table', description: 'Table that stores shifts that need staffing' }), input.config.field('shiftsStartField', { label: 'Shifts table: start date/time field', parentTable: 'shiftsTable', }), input.config.field('shiftsEndField', { label: 'Shifts table: end date/time field', parentTable: 'shiftsTable', }), input.config.field('shiftsPeopleField', { label: 'Shifts table: assignees (people) field', description: 'This field should link to people in the People table', parentTable: 'shiftsTable', }), input.config.table('peopleTable', { label: '👩‍🔧 People table', description: 'Table that stores people' }), // Comment this out if you don't need an inactive field input.config.field('peopleInactiveField', { label: 'People table: inactive field (checkbox)', description: 'Check this box to temporarily exclude someone from consideration', parentTable: 'peopleTable', }), input.config.table('availabilityTable', { label: '🗓 Availability table', description: 'Table that stores the times when people are available' }), input.config.field('availabilityPersonField', { label: 'Availability table: person field', description: 'This field should link to one record in the People table', parentTable: 'availabilityTable', }), input.config.field('availabilityStartField', { label: 'Availability table: start date/time field', parentTable: 'availabilityTable', }), input.config.field('availabilityEndField', { label: 'Availability table: end date/time field', parentTable: 'availabilityTable', }), input.config.field('availabilityUnavailableField', { label: 'Availability table: unavailable field (checkbox)', description: 'Check this box when someone is NOT available during the specified time period', parentTable: 'availabilityTable', }), input.config.field('availabilityRepeatsField', { label: 'Availability table: repeats weekly field (checkbox)', description: 'Check this box when availability repeats every week', parentTable: 'availabilityTable', }), // Comment this out if you don't need an inactive field input.config.field('availabilityRepeatsUntilField', { label: 'Availability table: repeats until field (date/time)', description: 'Note: The "repeats until" field only works if the "repeats weekly" field is checked', parentTable: 'availabilityTable', }), ] }); const MINUTE_IN_MS = 60 * 1000; const WEEK_IN_MS = 7 * 24 * 60 * MINUTE_IN_MS; var TIME_ZONE = 'client'; const VALID_TIME_ZONES = new Set(['client', 'utc']) const TIME_FIELDS = [ config.availabilityStartField, config.availabilityEndField, config.availabilityRepeatsUntilField, config.shiftsStartField, config.shiftsEndField, ]; // ================================================================================ // // TIME ZONE HELPER FUNCTIONS // // If the customer is using client time zones, then we need to be careful about // daylight savings. For example, let's say Alice is available on Mondays 9AM-10AM // starting in January. If we have a shift on a Monday 9AM-10AM in June, Alice // should be available for that shift. One way to verify that Alice is available // is to project her availability forward in time and then see if it overlaps with // the shift time in June. // // If we're in the U.S., though, January will use standard // time and June will use daylight savings time. If we naively project her // availability forward to June by adding a whole number of weeks' worth of time // to her availability time, we would end up with 10AM-11AM instead of 9AM-10AM. // We could use Javascript's Date.setDate to add days instead, but we don't know // how many days to add (and iterating is slow). In order to calculate how many // weeks to add, we need to account for the time zone offsets present in each // date/time field value. // // If the date/time field values are in GMT/UTC time instead, then we don't need // to make this adjustment—9AM means the same thing in January as it does in June. // To make things easier to work with, let's just validate that all date/time // fields use the same time zone and store that time zone in a global. function checkAndSetTimeZones() { const timeZones = new Set(); for (const field of TIME_FIELDS) { if (!field) continue; // in case someone comments out an optional field if (!field.options || !field.options.timeZone) { throw `options.timeZone not found in field ${field.name}. Is it a valid date/time field?`; } if (!VALID_TIME_ZONES.has(field.options.timeZone)) { throw `Invalid time zone ${field.options.timeZone} in field ${field.name}`; } timeZones.add(field.options.timeZone); } if (timeZones.size > 1) { throw 'All date/time fields must use the same time zone (client or GMT/UTC).' } TIME_ZONE = timeZones.values().next().value; } // Time-zone adjusted difference between two times. See comments above for a // clearer description of what this does. function getEffectiveMillisecondsBetween(start, end) { return TIME_ZONE == 'client' ? treatAsUTC(end).getTime() - treatAsUTC(start).getTime() : end.getTime() - start.getTime(); } function treatAsUTC(date) { return new Date(date.getTime() - date.getTimezoneOffset()) } // ================================================================================ class TimeSpan { constructor(start, end, repeats=false, repeatsUntil=null) { this.start = start; this.end = end; this.repeats = repeats; this.repeatsUntil = repeatsUntil; this.lastRepeat = (this.repeats && this.repeatsUntil) ? this._getLastRepeatEndingAtOrBefore(this.repeatsUntil) : null; this.record = null; } isValid() { return this.start && this.end && this.end > this.start } comesBefore(otherTimeSpan) { return this.end <= otherTimeSpan.start } comesAfter(otherTimeSpan) { return this.start >= otherTimeSpan.end } overlaps(otherTimeSpan) { return !(this.comesBefore(otherTimeSpan) || this.comesAfter(otherTimeSpan)) } covers(otherTimeSpan) { return this.start <= otherTimeSpan.start && this.end >= otherTimeSpan.end } _getLastRepeatStartingBefore(time) { return this.start >= time ? this : this._getRepeat('start', time) } _getLastRepeatEndingAtOrBefore(time) { return this.end > time ? this : this.repeatsUntil ? this._getRepeat('end', time) : null } _getRepeat(key, time) { const diffMs = getEffectiveMillisecondsBetween(this[key], time); let weeksBetween = Math.floor(diffMs / WEEK_IN_MS); const newStartDate = new Date(this.start); newStartDate.setDate(newStartDate.getDate() + weeksBetween * 7); const newEndDate = new Date(this.end); newEndDate.setDate(newEndDate.getDate() + weeksBetween * 7); return new TimeSpan(newStartDate, newEndDate); } isAfterLastRepeatEnds(time) { return this.lastRepeat && time > this.lastRepeat.end } getRepeatsThatOverlap(otherTimeSpan) { const potentialRepeats = new Array(); if (this.repeats) { let repeat = this._getLastRepeatStartingBefore(otherTimeSpan.start); if (!this.isAfterLastRepeatEnds(repeat.end)) { potentialRepeats.push(repeat); } while (true) { repeat = repeat._getNextWeeklyRepeat(); if (repeat.comesAfter(otherTimeSpan) || this.isAfterLastRepeatEnds(repeat.end)) { break; } potentialRepeats.push(repeat); } } else { potentialRepeats.push(this); } return potentialRepeats.filter(repeat => repeat.overlaps(otherTimeSpan)); } _getNextWeeklyRepeat() { const newStartDate = new Date(this.start); newStartDate.setDate(newStartDate.getDate() + 7); const newEndDate = new Date(this.end); newEndDate.setDate(newEndDate.getDate() + 7); return new TimeSpan(newStartDate, newEndDate) } } class TimeSpanCollection { constructor(timeSpans) { if (timeSpans) { this.timeSpans = timeSpans.filter(ts => ts.isValid()); this.sort(); } else { this.timeSpans = new Array(); } } add(timeSpan) { if (timeSpan.isValid()) { this.timeSpans.push(timeSpan); this.sort(); } } // sort by start and then end time sort() { this.timeSpans.sort((a, b) => a.start - b.start || a.end - b.end) } hasOverlap(otherTimeSpan) { for (let timeSpan of this.timeSpans) { const overlapping = timeSpan.getRepeatsThatOverlap(otherTimeSpan); if (overlapping.length > 0) { return true } } return false } getRepeatsThatOverlap(otherTimeSpan) { const overlappingTimeSpans = new Array(); for (const timeSpan of this.timeSpans) { overlappingTimeSpans.push(...timeSpan.getRepeatsThatOverlap(otherTimeSpan)); } overlappingTimeSpans.sort((a, b) => a.start - b.start || a.end - b.end); return overlappingTimeSpans } covers(otherTimeSpan) { // We'll use this to track how much of the shift remains uncovered const remainingOtherTimeSpan = new TimeSpan(otherTimeSpan.start, otherTimeSpan.end); for (const timeSpan of this.getRepeatsThatOverlap(otherTimeSpan)) { if (timeSpan.covers(remainingOtherTimeSpan)) { return true } else if (timeSpan.start <= remainingOtherTimeSpan.start) { // We've partially covered remainingOtherTimeSpan, so adjust its start time remainingOtherTimeSpan.start = timeSpan.end; } } return false } } class Person { constructor(record) { this.availability = new TimeSpanCollection(); this.unavailability = new TimeSpanCollection(); this.shifts = new TimeSpanCollection(); this.record = record; } isAvailableFor(shift) { return !this.shifts.hasOverlap(shift) && !this.unavailability.hasOverlap(shift) && this.availability.covers(shift) } } // ================================================================================ async function getShift() { const shiftRecord = await input.recordAsync('Select a shift', config.shiftsTable); if (shiftRecord) { const shift = new TimeSpan( new Date(shiftRecord.getCellValue(config.shiftsStartField)), new Date(shiftRecord.getCellValue(config.shiftsEndField)), ); if (shift.isValid()) { shift.record = shiftRecord; return shift; } else { throw 'The selected shift has invalid start and/or end times' } } return null } async function getPeople() { const people = {}; // initialize a new object for each person in the People table const peopleQuery = await config.peopleTable.selectRecordsAsync(); for (const record of peopleQuery.records) { // Skip adding new person to people object if they are excluded through config.peopleInactiveField if (config.peopleInactiveField && record.getCellValue(config.peopleInactiveField)) continue; people[record.id] = new Person(record); } // add time ranges where people are (or aren't) available const availabilityQuery = await config.availabilityTable.selectRecordsAsync(); for (const record of availabilityQuery.records) { const linkedPeople = record.getCellValue(config.availabilityPersonField); if (linkedPeople && linkedPeople.length > 0 && linkedPeople[0].id) { for (const linkedPerson of linkedPeople) { if (linkedPerson.id in people) { const key = record.getCellValue(config.availabilityUnavailableField) ? 'unavailability' : 'availability'; const repeatsUntil = config.availabilityRepeatsUntilField ? record.getCellValue(config.availabilityRepeatsUntilField) : null; people[linkedPerson.id][key].add(new TimeSpan( new Date(record.getCellValue(config.availabilityStartField)), new Date(record.getCellValue(config.availabilityEndField)), record.getCellValue(config.availabilityRepeatsField), repeatsUntil ? new Date(repeatsUntil) : null, )); } } } } // add shifts that people have already been assigned to const shiftsQuery = await config.shiftsTable.selectRecordsAsync(); for (const record of shiftsQuery.records) { const linkedPeople = record.getCellValue(config.shiftsPeopleField); if (linkedPeople && linkedPeople[0]?.id) { for (const linkedPerson of linkedPeople) { // if linkedPerson is excluded through config.peopleInactiveField, they will not exist in the people object if (!people[linkedPerson.id]) continue; people[linkedPerson.id].shifts.add(new TimeSpan( new Date(record.getCellValue(config.shiftsStartField)), new Date(record.getCellValue(config.shiftsEndField)), )); } } } return people } function getAvailablePeople(people, shift) { const availablePeople = new Array(); for (const person of Object.values(people)) { if (person.isAvailableFor(shift)) { availablePeople.push(person.record); } } return availablePeople } async function selectPerson(shift, availablePeople) { if (!availablePeople || !availablePeople.length) { output.text('Nobody is available for this shift'); } else { const person = await input.recordAsync('Add a person to the shift', availablePeople); if (person) { const shiftPeople = await shift.record.getCellValue(config.shiftsPeopleField) || new Array(); await config.shiftsTable.updateRecordAsync( shift.record, {[config.shiftsPeopleField.name]: [...shiftPeople, {id: person.id}]} ); output.markdown(`Added ${person.name} to _${shift.record.name}_`); } } } // ================================================================================ checkAndSetTimeZones(); const shift = await getShift(); if (shift) { const people = await getPeople(); await selectPerson(shift, getAvailablePeople(people, shift)); }
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter