Build a room and resource booking system

Northwind shares a photo studio and a couple of meeting rooms across the whole team. Without a single source of truth, two people book the same space for the same hour and only find out when they both turn up. Calendars solve that, but asking everyone to learn the right calendar for each room is its own friction.

This script lets people book the simple way — by adding a row to a Bookings sheet — while still getting a real calendar event behind the scenes. It reads the sheet, creates a Calendar event for any booking that does not have one yet, and writes the event ID back so the booking is never duplicated. The sheet stays the easy front door; the calendars stay the reliable record.

What you’ll need

• A Bookings sheet with a header row and these columns: resource, requester, start, end, and eventId (left blank when a booking is added).
• One Google Calendar per bookable resource. Note each calendar’s ID — found under Settings and sharing for the calendar.
• The start and end cells must contain real date-time values, not text.

The script

// The spreadsheet holding the booking requests.
const BOOKINGS_SHEET_ID = '1abcBookingsId';

// Maps each resource name to the Calendar that records its bookings.
const CALENDARS = {
  'Photo studio': '1abcPhotoCalId',
  'Meeting room A': '1abcRoomACalId',
};

/**
 * Reads the Bookings sheet and creates a Calendar event for every row
 * that does not yet have one, writing the new event ID back to the row.
 */
function syncBookings() {
  const sheet = SpreadsheetApp.openById(BOOKINGS_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();

  // 1. Split the header from the data and map column names to indexes.
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  // 2. Bail out early if there are no booking rows.
  if (!rows.length) {
    Logger.log('No bookings to sync — nothing to do.');
    return;
  }

  let created = 0;

  // 3. Walk every row and book the ones that have no event yet.
  rows.forEach((row, i) => {
    // Skip rows already linked to a calendar event.
    if (row[col.eventId]) return;

    // Find the calendar for this resource; skip unknown resources.
    const cal = CalendarApp.getCalendarById(CALENDARS[row[col.resource]]);
    if (!cal) {
      Logger.log(`Unknown resource: ${row[col.resource]} (row ${i + 2}).`);
      return;
    }

    // 4. Create the event and record its ID back on the row.
    const event = cal.createEvent(
      `${row[col.resource]} — ${row[col.requester]}`,
      row[col.start],
      row[col.end]
    );
    values[i + 1][col.eventId] = event.getId();
    created++;
  });

  // 5. Write the event IDs back in one call.
  if (created) {
    sheet.getDataRange().setValues(values);
  }
  Logger.log(`Created ${created} booking event(s).`);
}

How it works

1. syncBookings opens the Bookings sheet and reads every row in one call.
2. It peels off the header and builds a col lookup so columns are referred to by name, then stops early if there are no rows.
3. For each row it checks the eventId cell — a row that already has an ID has been booked, so it is skipped. This is what makes the script safe to run repeatedly.
4. It looks up the resource name in CALENDARS to find the right calendar. An unknown resource is logged and skipped rather than crashing the run.
5. It creates a Calendar event titled with the resource and requester, spanning the booking’s start and end.
6. It records the new event’s ID in the in-memory grid, and after the loop writes the whole grid back once so every new eventId is saved together.

Example run

The Bookings sheet before a run:

resource	requester	start	end	eventId
Photo studio	Priya	2025-07-15 09:00	2025-07-15 11:00
Meeting room A	Sam	2025-07-15 14:00	2025-07-15 15:00	abc123…

The Priya row has a blank eventId, so the script creates an event titled Photo studio — Priya on the photo-studio calendar and writes its ID back. The Sam row already has an ID, so it is left untouched. After the run:

resource	requester	start	end	eventId
Photo studio	Priya	2025-07-15 09:00	2025-07-15 11:00	def456…
Meeting room A	Sam	2025-07-15 14:00	2025-07-15 15:00	abc123…

Trigger it

Run this on a short interval so bookings reach the calendars quickly:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for syncBookings, time-driven, Minutes timer, every 10 or 15 minutes.
3. Save and approve the Calendar authorisation prompt. New rows are now picked up within a few minutes of being added.

To book instantly instead of waiting for the timer, also add an On edit trigger for the same function — though the timed run is the dependable safety net.

Watch out for

• There is no double-booking check. The script will happily create two overlapping events on the same resource calendar. Add a conflict check against existing events with getEvents before createEvent if clashes are a real risk.
• Editing a booking’s times in the sheet after it has an eventId does nothing — the event is already created and the row is skipped. Clear the eventId cell to force a fresh event, and delete the stale one yourself.
• Deleting a row does not delete its calendar event. The event lingers until someone removes it manually.
• start and end must be genuine date-time values. A time typed as text will make createEvent throw.
• A resource name in the sheet that is not a key in CALENDARS is logged and skipped, so a typo means a booking silently never reaches a calendar.