Show team PTO on a shared calendar

At Northwind, time-off lives in a spreadsheet — that is where HR books it and where the team checks who is out. The problem is that nobody opens the sheet before scheduling a meeting, so half-attended workshops and missed deadlines keep happening. People need to see PTO where they already look: the calendar.

This script reads each row of the PTO sheet and adds an all-day event to a shared Northwind Team PTO calendar that everyone subscribes to. It writes the new event’s ID back to the row so the second run does not create duplicates — the standard “ledger” pattern for sheet-driven sync.

What you’ll need

• A PTO Google Sheet with the columns name, startDate, endDate, and eventId. The first three are filled in by whoever books the time off; eventId is left blank for the script to populate.
• A shared Northwind Team PTO calendar that the team subscribes to. Grab its ID from Calendar settings and paste it into PTO_CAL below.
• Nothing else — no API keys, no add-ons.

The script

// The shared calendar that holds team PTO.
const PTO_CAL = '1abcPtoCalId';

// The sheet HR uses to record time off.
const PTO_SHEET = '1abcPtoId';

/**
 * Reads the PTO sheet, creates an all-day event for any row without an
 * eventId, and writes the new event ID back so the next run skips it.
 */
function syncPto() {
  const sheet = SpreadsheetApp.openById(PTO_SHEET).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  const cal = CalendarApp.getCalendarById(PTO_CAL);

  if (!cal) {
    Logger.log('Calendar not found — check PTO_CAL.');
    return;
  }

  // 1. Walk every row. Skip rows that already carry an eventId.
  rows.forEach((r, i) => {
    if (r[col.eventId]) return;
    if (!(r[col.startDate] instanceof Date) || !(r[col.endDate] instanceof Date)) {
      return;
    }

    // 2. Calendar's all-day series treats the end date as exclusive,
    //    so add a day to include the last day of leave.
    const endExclusive = new Date(r[col.endDate].getTime() + 86400000);

    // 3. Create a daily recurrence that ends on the last day of leave.
    //    A series (rather than one long block) reads better in week view.
    const event = cal.createAllDayEventSeries(
      `${r[col.name]} — PTO`,
      r[col.startDate],
      endExclusive,
      CalendarApp.newRecurrence().addDailyRule().until(r[col.endDate]));

    // 4. Stamp the event ID back into the row so we never duplicate it.
    values[i + 1][col.eventId] = event.getId();
  });

  // 5. One bulk write at the end — cheaper than setValue per row.
  sheet.getDataRange().setValues(values);
}

How it works

1. syncPto opens the PTO sheet and reads every row at once, then builds a small col lookup so the rest of the code reads r[col.name] instead of r[0] — easier to follow and order-independent.
2. It opens the shared calendar by ID and bails out with a log line if the ID is wrong, rather than failing later with a less obvious error.
3. For each row, it skips anything that already has an eventId — that is the row’s record of having been synced.
4. It also skips rows whose dates are not real dates, which catches rows that are still being filled in.
5. It creates an all-day event series from startDate to endDate, adding a day to the end because Calendar treats the series end as exclusive.
6. It writes the new event’s ID back into the in-memory values array, and pushes the whole thing back to the sheet in a single setValues call.

Example run

Say the PTO sheet looks like this before a run:

name	startDate	endDate	eventId
Priya	2025-08-11	2025-08-15
Sam	2025-08-18	2025-08-18
Jordan	2025-08-25	2025-08-29	(already filled)

After syncPto runs, the shared calendar shows “Priya — PTO” across that week and “Sam — PTO” on the Monday. Jordan’s row is untouched because it already had an event ID. The sheet now reads:

name	startDate	endDate	eventId
Priya	2025-08-11	2025-08-15	[email protected]
Sam	2025-08-18	2025-08-18	[email protected]
Jordan	2025-08-25	2025-08-29	(already filled)

Trigger it

Set a time-driven trigger so new PTO rows pick up overnight:

1. In the Apps Script editor, open Triggers (clock icon).
2. Add a trigger for syncPto, event source Time-driven, type Day timer, time 2am to 3am.
3. Approve the authorisation prompt the first time.

A daily run is plenty — PTO is not booked minute-by-minute, and an overnight job keeps the calendar quiet during the day.

Watch out for

• The eventId column is the ledger. If you clear it, the next run will create a second event for the same row. To re-sync a row cleanly, delete the event in Calendar first, then clear the cell.
• Editing the PTO sheet does not edit the calendar. This script only creates; it does not update or delete. If someone shortens their leave, delete the event manually (or extend the script to call event.deleteEventSeries() when the row changes).
• Calendar’s “end” is exclusive. The + 86400000 is not a bug — it makes the last day of leave appear on the calendar instead of finishing the night before.
• All-day events show as “free” by default, which is what most teams want. If you would rather show PTO as “busy”, set event.setVisibility() and event.setTransparency() after creation.