Build a shift-rota scheduler

Northwind runs a support desk Monday to Friday, and someone has to be on it each day. Working that rota out by hand is tedious and never quite feels fair — whoever builds it tends to remember the loud weeks and forget the quiet ones, so the same people end up covering more than their share.

This script takes the judgement out of it. It reads the support team from a Sheet, works out next week’s Monday, and assigns one person to each weekday by rotating through the list. The offset shifts every week, so over a run of weeks everyone covers each weekday roughly the same number of times. Each shift lands in a shared calendar with the assignee invited.

What you’ll need

• A Team sheet with two columns: name and email, one row per person on the support rota.
• A shared Google Calendar for the rota, and its calendar ID (from the calendar’s Settings and sharing page).
• The team members added to that calendar, or at least able to receive invitations to it.

The script

// The shared calendar that holds the support rota.
const ROTA_CAL = '1abcRotaCalId';

// The spreadsheet with the support team.
const TEAM_SHEET_ID = '1abcTeamId';

// Shift window: 9am to 6pm.
const SHIFT_START_HOUR = 9;
const SHIFT_END_HOUR = 18;

// Working days to cover, Monday to Friday.
const DAYS_TO_COVER = 5;

/**
 * Builds next week's support rota: one person per weekday, rotating
 * the starting offset week by week so coverage stays fair.
 */
function scheduleNextWeek() {
  const team = readSheet(TEAM_SHEET_ID);

  // Bail out if there is nobody to schedule.
  if (!team.length) {
    Logger.log('No team members found — nothing to schedule.');
    return;
  }

  const monday = nextMonday();
  const cal = CalendarApp.getCalendarById(ROTA_CAL);

  // One shift per weekday. The week number shifts the offset so the
  // same person does not always land on Monday.
  for (let d = 0; d < DAYS_TO_COVER; d++) {
    const person = team[(weekNumber(monday) + d) % team.length];

    // The calendar date for this weekday.
    const day = new Date(monday);
    day.setDate(monday.getDate() + d);

    // The 9am-6pm window on that day.
    const start = new Date(day);
    start.setHours(SHIFT_START_HOUR, 0, 0, 0);
    const end = new Date(day);
    end.setHours(SHIFT_END_HOUR, 0, 0, 0);

    cal.createEvent(`Support: ${person.name}`, start, end, {
      guests: person.email,
    });
  }
  Logger.log('Scheduled ' + DAYS_TO_COVER + ' shifts from ' + monday.toDateString());
}

/**
 * Returns the date of the upcoming Monday at midnight.
 */
function nextMonday() {
  const d = new Date();
  d.setDate(d.getDate() + ((1 + 7 - d.getDay()) % 7 || 7));
  d.setHours(0, 0, 0, 0);
  return d;
}

/**
 * Returns the ISO-ish week number for a date, used as the rotation offset.
 */
function weekNumber(d) {
  const onejan = new Date(d.getFullYear(), 0, 1);
  return Math.ceil((((d - onejan) / 86400000) + onejan.getDay() + 1) / 7);
}

/**
 * Reads a sheet and returns its rows as objects keyed by the header row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

How it works

1. scheduleNextWeek reads the Team sheet into a list of {name, email} objects and stops early if the list is empty.
2. It calls nextMonday to find the start of the week being scheduled, then opens the shared rota calendar.
3. For each of the five weekdays it picks a person. The index is (weekNumber + d) % team.length — the day offset d spreads people across the week, and the week number rotates the starting point so the pattern shifts every week.
4. It builds the shift date by adding d days to Monday, then sets the start and end times to 9am and 6pm.
5. It creates a calendar event titled with the person’s name and invites them as a guest, so the shift shows up on their own calendar too.
6. weekNumber and nextMonday are pure date helpers; readSheet does the shared sheet-to-objects conversion.

Example run

With a Team sheet of four people — Ava, Ben, Cara, Dan — a run scheduling the week of Mon 1 June produces:

Day	Assignee
Mon 1 Jun	Cara
Tue 2 Jun	Dan
Wed 3 Jun	Ava
Thu 4 Jun	Ben
Fri 5 Jun	Cara

The following week the offset moves on, so Mon falls to Dan, Tue to Ava, and so on. Each event appears on the rota calendar as “Support: Cara” from 9am to 6pm, with Cara invited.

Trigger it

Run this once a week, ahead of the week it schedules:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for scheduleNextWeek.
3. Choose a week timer, set to run on, say, Friday afternoon, so next week’s rota is in calendars before the weekend.

Watch out for

• The rotation is fair over time, not within a single week — with more team members than five, some people will not appear at all in a given week. That evens out across the run.
• weekNumber is a simple approximation, not strict ISO 8601. That is fine here because it is only used as a rotation offset, but do not rely on it for reporting.
• Running the script twice for the same week creates duplicate events — it does not check for an existing rota. Delete the old events first, or add a guard that searches the calendar for that week before creating.
• Guests are invited but not forced to accept. If someone declines, the shift is still theirs; the script does not reassign.
• Public holidays are ignored. The script always fills all five weekdays, so skip or fix up holiday weeks by hand.