Build a vacation-coverage auto-router

When someone at Northwind goes on leave, their triage queue does not pause with them. New mail keeps landing under their owner label, sits unread for a week, and the client wondering why nobody replied is the one who finds out the cover arrangement was only ever a Slack message.

This script makes the handover real. A PTO sheet records who is away, the dates, and which label their cover uses. While someone is on leave, the script moves any unread thread off their owner label and onto their cover’s label — so the work actually lands in the right person’s queue. Run it on a short timer and the routing keeps up with the inbox.

What you’ll need

• A PTO sheet with columns: email, startDate, endDate, coverLabel.
• Owner labels already in use, named owner/<username> — for example owner/maria for [email protected].
• The cover’s label (the value in coverLabel) must already exist in Gmail; the script reassigns to it but does not create it.

The script

// The sheet recording who is on leave and who covers for them.
const PTO_SHEET = '1abcPtoSheetId';

// Owner labels follow this prefix: owner/<username>.
const OWNER_PREFIX = 'owner/';

/**
 * Reads the PTO sheet and, for everyone currently on leave, moves their
 * unread threads from their owner label onto their cover's label.
 */
function applyVacationCoverage() {
  const today = new Date();

  // 1. Read every row of the PTO sheet.
  const [header, ...rows] = SpreadsheetApp.openById(PTO_SHEET).getSheets()[0]
    .getDataRange().getValues();

  if (!rows.length) {
    Logger.log('No PTO rows found — nothing to do.');
    return;
  }

  // 2. Turn each row into an object and keep only people on leave today.
  const active = rows
    .map((r) => Object.fromEntries(header.map((h, i) => [h, r[i]])))
    .filter((r) => r.startDate <= today && r.endDate >= today);

  if (!active.length) {
    Logger.log('Nobody is on leave today — nothing to route.');
    return;
  }

  let moved = 0;
  for (const pto of active) {
    // 3. Derive the owner label from the email's username.
    const ownerLabel = `${OWNER_PREFIX}${pto.email.split('@')[0]}`;
    const own = GmailApp.getUserLabelByName(ownerLabel);
    const cover = GmailApp.getUserLabelByName(pto.coverLabel);

    // Skip if either label is missing — nothing safe to do.
    if (!own || !cover) {
      Logger.log(`Skipping ${pto.email}: missing owner or cover label.`);
      continue;
    }

    // 4. Move every unread thread from the owner label to the cover's.
    for (const t of own.getThreads()) {
      if (t.getMessages()[0].isUnread()) {
        t.removeLabel(own);
        t.addLabel(cover);
        moved++;
      }
    }
  }
  Logger.log(`Routed ${moved} thread(s) to cover labels.`);
}

How it works

1. applyVacationCoverage reads the whole PTO sheet and bails out early if it is empty.
2. It converts each row into a plain object keyed by header name, then filters to people whose leave window includes today (startDate <= today and endDate >= today). If nobody is currently away, it stops.
3. For each person on leave it derives their owner label from the username part of their email — [email protected] becomes owner/maria — and looks up both that label and the coverLabel. If either label is missing, the row is logged and skipped rather than risking a half-applied move.
4. It walks every thread under the owner label and, for any thread whose first message is still unread, removes the owner label and adds the cover label. Threads the owner has already read are left alone, on the assumption they were triaged before leave began.

Example run

Suppose the PTO sheet contains:

email	startDate	endDate	coverLabel
[email protected]	2026-05-20	2026-05-29	owner/awadesh

The script runs on 25 May. María is on leave, so the script looks up owner/maria and owner/awadesh. It finds three threads under owner/maria: two unread, one already read. The two unread threads have owner/maria removed and owner/awadesh added; the read thread is untouched. The log records Routed 2 thread(s) to cover labels. On 30 May, with María’s leave ended, the script finds no active rows and routes nothing.

Trigger it

1. In the Apps Script editor, open Triggers and click Add trigger.
2. Function: applyVacationCoverage. Event source: time-based. Type: minutes timer, every 30 minutes (or restrict to business hours with a custom schedule).
3. Save. Incoming mail is rerouted within half an hour while a teammate is away.

Watch out for

• The routing is one-way. When someone returns, threads already moved stay on the cover label — the script does not move them back. The cover finishes what they picked up, which is usually what you want, but be aware nothing reverts automatically.
• Only threads whose first message is unread are moved. A long thread the owner read weeks ago but that just received a new reply will not be picked up if its first message is read. For strict coverage, check the last message’s unread state instead.
• startDate <= today and endDate >= today compare Date objects. If the sheet stores those columns as text, the comparison is unreliable — keep them formatted as dates.
• The owner label is derived purely from the email username. If your owner labels follow a different naming convention, adjust OWNER_PREFIX and the split logic to match.
• Reassigning labels does not redirect replies or notifications — it only changes which queue the thread appears in. The cover still needs to actually watch their label for this to help.