Build an interview-scheduling coordinator

Scheduling interviews at Northwind eats a recruiter’s day. A candidate sends three slots that work, the interviewer’s calendar has shifted since the email was drafted, and the back-and-forth begins. Multiply that by a dozen open roles and a single hire’s coordination cost is measured in hours, not minutes.

This script turns scheduling into a sweep. The recruiter pastes each candidate’s preferred slots into a sheet, the script picks the first slot the interviewer is actually free for, books the 45-minute interview with both parties invited, and writes the chosen time back to the row. The next run skips anyone already scheduled.

What you’ll need

• An Interviews sheet with the columns candidate, email, interviewer, preferredSlots, and scheduled. The recruiter fills the first four; scheduled is left blank for the script.
• The preferredSlots cell holds one or more ISO-style timestamps separated by a pipe, e.g. 2025-07-30T10:00|2025-07-30T14:00|2025-07-31T11:00. The pipe avoids the comma collisions you get with CSV cells.
• The script runs under an account that can see the interviewer’s calendar — typically the recruiter’s account, since interviewers’ calendars are usually shared within the company.

The script

// Sheet that holds the interview queue.
const INTERVIEWS_SHEET = '1abcInterviewsId';

// How long each interview lasts, in minutes.
const INTERVIEW_MINUTES = 45;

/**
 * Reads the Interviews sheet, picks the first preferred slot the
 * interviewer is free for, books a 45-minute calendar event with
 * both parties as guests, and stamps the chosen time back into the row.
 */
function scheduleInterviews() {
  const sheet = SpreadsheetApp.openById(INTERVIEWS_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.getDefaultCalendar();

  rows.forEach((r, i) => {
    // 1. Skip anyone already booked — the row is its own ledger.
    if (r[col.scheduled]) return;
    if (!r[col.candidate] || !r[col.email]) return;

    // 2. Parse the pipe-separated slots. Drop anything that does not
    //    parse to a real Date so a typo cannot crash the run.
    const slots = String(r[col.preferredSlots])
      .split('|')
      .map((s) => new Date(s.trim()))
      .filter((d) => !isNaN(d));

    // 3. Try each slot in order. The first one the interviewer is free
    //    for wins — preferences are listed in priority order.
    for (const slot of slots) {
      const end = new Date(slot.getTime() + INTERVIEW_MINUTES * 60_000);

      // getEvents returns anything that overlaps, so a non-empty array
      // means the interviewer is busy at that time.
      if (cal.getEvents(slot, end).length > 0) continue;

      // 4. Book it. Both candidate and interviewer get the invite.
      cal.createEvent(`Interview: ${r[col.candidate]}`, slot, end, {
        guests: `${r[col.email]},${r[col.interviewer]}`,
        sendInvites: true,
      });

      // 5. Stamp the chosen slot back into the row so we never re-book.
      values[i + 1][col.scheduled] = slot;
      break;
    }
  });

  // 6. One write at the end is cheaper than per-row updates.
  sheet.getDataRange().setValues(values);
}

How it works

1. scheduleInterviews reads the whole sheet at once and builds a col lookup so the rest of the code reads r[col.email] instead of r[1].
2. For every row it checks the scheduled cell. If a time is already there the row is done — the cell is the row’s record of having been booked.
3. It splits preferredSlots on |, turns each piece into a Date, and drops anything that fails to parse. A bad timestamp in one row will not stop the others.
4. It walks the slots in order and asks Calendar whether any events overlap. getEvents(start, end) returns the conflicts; an empty array means free.
5. The first free slot wins. The script creates a 45-minute event with both the candidate and the interviewer as guests, and Calendar sends the invites automatically.
6. It writes the chosen slot back into the row’s scheduled cell and moves on. After the loop, one setValues call flushes the whole sheet.

Example run

Say the Interviews sheet looks like this before a run, where Priya is the hiring manager and her 10:00 slot is already booked:

candidate	email	interviewer	preferredSlots	scheduled
Sam Lee	[email protected]	[email protected]	2025-07-30T10:00|2025-07-30T14:00
Alex Carr	[email protected]	[email protected]	2025-07-31T11:00
Jordan Mei	[email protected]	[email protected]	2025-07-30T09:00	(already booked)

After scheduleInterviews runs, two new events sit on Priya’s calendar and the sheet reads:

candidate	email	interviewer	preferredSlots	scheduled
Sam Lee	[email protected]	[email protected]	2025-07-30T10:00|2025-07-30T14:00	2025-07-30 14:00
Alex Carr	[email protected]	[email protected]	2025-07-31T11:00	2025-07-31 11:00
Jordan Mei	[email protected]	[email protected]	2025-07-30T09:00	(already booked)

Sam’s first preference clashed, so the script fell through to 14:00. Alex only offered one slot and it was free. Jordan was already done.

Trigger it

Run it every couple of hours during recruiting season so candidates get prompt invites:

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

Or run it on demand — paste new candidates in, click Run, and check the sheet a moment later.

Watch out for

• The script only checks the interviewer’s calendar. If you want to check the candidate’s too, you cannot — but you can trust their preferences, and Calendar will tell them the moment a conflict appears.
• A candidate with no free slot will have a blank scheduled cell after the run. Sort by that column and you have your follow-up list.
• 45 minutes is hardcoded as INTERVIEW_MINUTES. If different roles need different lengths, add a minutes column to the sheet and read it instead.
• sendInvites: true means each booking fires an email immediately. For a quiet “draft mode” run, set it to false, review the events, and resend invites once you are happy.
• Timestamps in the sheet are read in the spreadsheet’s timezone. Set the sheet’s timezone (File → Settings) to match where the interviews happen, or you will book some uncomfortable 3am slots.