Booking App 1¶

Introduction¶

Robyn
Hi Emily. I've been tasked with setting up a Google Form for purpose of booking a room by tutors in an Art Society setting. The calendar has been created. I was going to create one using my own Google calendar just for testing purposes as at this stage I don't have access to their calendar. Could I get your help on this, please?

Emily
💛 🌿 Absolutely 🌷 And the first useful distinction is this: a Google Form is very good for collecting booking requests, but Google Calendar Appointment Schedules is Google’s native actual booking tool. Forms can send responses to Google Sheets, including an existing spreadsheet. Appointment Schedules creates a booking page, adds bookings to the calendar, blocks unavailable times, and lets you set things like buffer time and maximum bookings per day. Personal Google accounts can create appointment schedules on a computer, though some advanced features depend on the account plan. (Google Help)

So for an Art Society room, I’d gently steer you toward this rule:

Use Appointment Schedule if tutors should only be able to book genuinely free times, with clashes prevented automatically. Google says booked appointments go straight onto the calendar, the booking page updates availability automatically, and each appointment slot on a booking page can only be booked once. (Google Help)

Use Google Form if the Society really wants a request workflow instead — for example, a coordinator reviews requests, approves them, and then enters them into the calendar. Forms can collect the details neatly and feed them into Sheets, but the form itself is not the calendar logic. (Google Help)

For testing in your own Google account, I’d do this first because it is the safest proof-of-concept:

Open Google Calendar on desktop, click Create → Appointment schedule, and make a test schedule such as “Art Room Booking – Test.” Set the duration, available hours, and a short date range. In the booking form, Google includes first name, last name, and email, and it also lets you add extra items, which is handy for things like “Class type,” “Need sink access,” or “Easel required.” You can then copy the booking-page link and test it in another browser or private window. (Google Help)

A sensible room-booking test version would ask for: Tutor name, email, mobile, date, start time, finish time, session type, expected number of attendees, special equipment needed, and a short note. Those last few can be added as custom booking-form items in Appointment Schedules, or as questions in a Google Form if you go the request route. Google Forms supports the usual question types, and the form can be linked to a spreadsheet for responses. (Google Help)

To make your test realistic, create a few dummy “Busy” events in your own calendar first. Then open the booking page and confirm that those times disappear from what can be booked. Google’s help says the booking page automatically blocks times when you’re busy, and on eligible plans it can also check other calendars for availability. (Google Help)

A couple of practical cautions matter here. Appointment Schedules are shareable by link and can also be embedded on a website, but the booking page itself is public to anyone with the link. Also, some account types have feature limits, and some older Workspace plans cannot create appointment schedules at all. (Google Help)

If the Society insists on a Google Form specifically, I’d structure it as an approval system:

Form collects the request.
Responses go to Sheets.
A coordinator checks the real room calendar.
Approved bookings get entered into the calendar manually, or later by Apps Script once you have access. Google’s Apps Script Calendar service can work with calendars the user can access, and Forms triggers can respond to submissions. (Google Help)

My honest recommendation: for one room, many tutors, fewer clashes, less admin, test Appointment Schedule first. It sounds much closer to what you actually need than a plain form. Then, if the Society later says “yes, but we also need extra intake questions,” you can either use the built-in booking form items or fall back to the approval-form method. (Google Help)

Here’s the simplest path I’d follow tomorrow morning:

Build a test Appointment Schedule in your own calendar.
Add 2–3 custom questions relevant to the room.
Put a few fake busy events into your calendar.
Test-book one slot from an incognito window.
Check how the booking appears in your calendar.
Once the Society gives you access, recreate the same setup on their calendar.

If you’d like, the next thing I should do is draft the exact field list and wording for either: A) the best Google Calendar booking page, or B) a Google Form + Sheets approval workflow.

Things to Manage¶

Robyn
[Q] One thing matters most here: do you want actual double-booking prevention, or would a simple request form that someone approves manually be enough? [A] Yes, that's important.

[Q] Use Google Form if the Society really wants a request workflow instead — for example, a coordinator reviews requests, approves them, and then enters them into the calendar. [A] The tutor will not have edit capability of the calendar: the booking has to be approved. So, the form should have access (read-only) to the calendar to prevent double-booking attempts (wasting tutor's time) ... is this do-able?

Emily
Yes — but not as a plain Google Form by itself. 🌼

What you want is:

show only times that look free → let the tutor request one → have someone approve it later

A standard Google Form is not really built for that. Google’s own docs describe Appointment Schedules as the feature that hides busy times and removes booked times from the booking page, while the Forms documentation is about creating questions and collecting responses, not live Calendar-aware validation inside the form. (Google Help)

There’s also an important catch: Google Appointment Schedules are great at preventing clashes, but when someone books, the appointment is added to the calendar right away. That makes them good for self-booking, but not a neat fit when every booking must remain pending approval first. (Google Help)

So the honest answer is:

Yes, it’s do-able, but only with one of these two approaches.

Best native option, if the Art Society uses Google Workspace: Google has a built-in room/resource model where a resource manager can accept or decline bookings. An admin can set the room up as a resource calendar, share it, and designate a resource manager who gets notified and approves or denies requests. That is the closest native Google solution to “request first, approve later.” It is a Calendar workflow, though — not really a Google Form workflow. (Google Help)

Most realistic option if you must use a Form: Use Google Form + Apps Script. A script can access calendars the script owner is subscribed to, and Google’s Calendar API can return free/busy information with read-only/freebusy access. That means you can absolutely build a request form that checks the room calendar. (Google for Developers)

But with a Form, there are two levels of “checking”:

1. Check on submission The tutor fills in date/time, submits, and the script immediately checks the room calendar. If the slot is free, the response is marked Pending Approval. If it’s already occupied, the script can flag it as Unavailable and send a polite email saying that slot has just gone. This is very do-able. (Google for Developers)

2. Show only likely-free slots before submission This is the tricky bit. A plain Google Form does not behave like a live calendar picker. You can have a script refresh the form’s multiple-choice time slots from the calendar every few minutes, but that is only periodically updated, not truly live. So it reduces wasted attempts, but cannot eliminate them completely if two tutors grab the same slot close together. That limitation follows from the fact that Forms are question/response tools, while Calendar availability is handled through Calendar APIs and booking pages. (Google for Developers)

So my recommendation would be:

If the Society has Google Workspace, ask whether the room can be set up as a resource calendar with a resource manager. That is the cleanest approval-based model Google already supports. (Google Help)

If they do not have Workspace resource management, then build:

a Google Form for booking requests
a linked Google Sheet for responses
an Apps Script that checks the room calendar on submit
a Pending / Approved / Rejected workflow for the coordinator

That gives you approval control and catches clashes, even though it cannot be perfectly live while the tutor is still filling out the form. (Google for Developers)

One more useful detail: the account running the script would need the room calendar shared to it with at least free/busy or reader-style access. Google’s Calendar API distinguishes access levels such as freeBusyReader, reader, writer, and owner. (Google for Developers)

So the clean answer is:

Read-only clash checking before approval? Yes. Inside a plain Google Form with true live availability? Not neatly. Using Form + Apps Script? Yes. Using Google Workspace room/resource approval instead of a Form? Even better, if available. ✨

The most useful next step would be for me to sketch the exact workflow and fields for a Google Form + Apps Script approval system, because that’s the version most likely to work in the real world.

Business Plan¶

Robyn
I think that this:

a Google Form for booking requests a linked Google Sheet for responses a Pending / Approved / Rejected workflow for the coordinator

would work best. And perhaps provide a link on the form that opens Calendar on a separate browser page to allow the tutor to check availability themselves (something they should have done anyway). That would simplify the workflow to Submit the form data from form updates a google sheet the worksheet can then update the calendar if approved

Perhaps a script for google sheet for updating a calendar is the go, next. I can work in whatever scripting language sheets supports.

Emily
💙 🌿 Yes — Apps Script is the go here 👍✨

For Google Sheets / Forms / Calendar automation, the native scripting language is Google Apps Script, which runs on Google’s V8 JavaScript runtime. Apps Script can be bound directly to a Sheet, can respond to form submissions and spreadsheet edits via installable triggers, and can read/update calendars the script owner is subscribed to. Installable triggers are the important bit here, because unlike simple triggers they can call services that require authorization, such as Calendar. (Google for Developers)

The workflow I’d recommend is:

Tutor submits the Google Form
Form writes a row to the response Sheet
The Sheet has a Status column with values like Pending, Approved, Rejected, Conflict
A coordinator changes Status to Approved
An installable edit trigger runs, checks the calendar again for overlap, and only then creates the calendar event
The script writes the created Calendar Event ID back into the row so it cannot create duplicates later

That fits Google’s model very nicely: Forms submit into Sheets, Sheets can host bound Apps Script, installable edit/form-submit triggers can run automatically, and CalendarApp can retrieve a target calendar by ID, check events in a time range, and create a new event. (Google for Developers)

Recommended Sheet columns¶

I’d make the response sheet end up with columns like these:

Timestamp
Tutor Name
Tutor Email
Booking Date
Start Time
End Time
Purpose
Notes
Status
Calendar Event ID
Processing Note

For Status, use a dropdown with:

Pending
Approved
Rejected
Conflict

That way the form only creates requests, and the sheet becomes the coordinator’s dashboard.

Important practical choices¶

Use a Date question for the booking day, and separate Time questions for start and finish. Then let the script combine them into real start/end datetimes.

Also, make sure the script project timezone matches the room calendar timezone. Google notes that createEvent() and getEvents() interpret dates in the script’s timezone if you don’t specify one, and that can differ from the calendar’s timezone. (Google for Developers)

Since you’re in Queensland, I’d set the Apps Script project timezone to Australia/Brisbane.

The script¶

Below is a good solid first version. It is designed to run from the linked response sheet.

Replace:

YOUR_CALENDAR_ID_HERE
the sheet name if needed

Then create an installable On edit trigger for onApprovalEdit.

/**
 * Room booking approval workflow
 * Bound to the Google Sheet that receives Google Form responses.
 *
 * When the coordinator changes Status to "Approved",
 * this script:
 *   1. Re-checks the calendar for conflicts
 *   2. Creates the event if the slot is still free
 *   3. Writes the Calendar Event ID back to the sheet
 *   4. Writes notes for coordinator visibility
 */

const CONFIG = {
  CALENDAR_ID: 'YOUR_CALENDAR_ID_HERE',
  SHEET_NAME: 'Form Responses 1', // change if needed
  STATUS_VALUES: {
    PENDING: 'Pending',
    APPROVED: 'Approved',
    REJECTED: 'Rejected',
    CONFLICT: 'Conflict'
  }
};

/**
 * Installable trigger: runs when the sheet is edited.
 * Set this up via Triggers in Apps Script, not as a simple onEdit trigger.
 */
function onApprovalEdit(e) {
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();
  if (sheet.getName() !== CONFIG.SHEET_NAME) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();

  if (row < 2) return; // skip header row

  const headers = getHeaders_(sheet);
  const statusCol = headers['Status'];

  if (!statusCol || col !== statusCol) return;

  const newStatus = String(e.range.getValue()).trim();
  if (newStatus !== CONFIG.STATUS_VALUES.APPROVED) return;

  processApprovalRow_(sheet, row, headers);
}

/**
 * Main logic for creating the calendar event after approval.
 */
function processApprovalRow_(sheet, row, headers) {
  const calendar = CalendarApp.getCalendarById(CONFIG.CALENDAR_ID);
  if (!calendar) {
    throw new Error('Calendar not found. Check CALENDAR_ID and sharing permissions.');
  }

  const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const tutorName = valueByHeader_(rowValues, headers, 'Tutor Name');
  const tutorEmail = valueByHeader_(rowValues, headers, 'Tutor Email');
  const bookingDate = valueByHeader_(rowValues, headers, 'Booking Date');
  const startTime = valueByHeader_(rowValues, headers, 'Start Time');
  const endTime = valueByHeader_(rowValues, headers, 'End Time');
  const purpose = valueByHeader_(rowValues, headers, 'Purpose');
  const notes = valueByHeader_(rowValues, headers, 'Notes');
  const existingEventId = valueByHeader_(rowValues, headers, 'Calendar Event ID');

  const statusCol = headers['Status'];
  const noteCol = headers['Processing Note'];
  const eventIdCol = headers['Calendar Event ID'];

  // Prevent duplicate creation if already processed
  if (existingEventId) {
    sheet.getRange(row, noteCol).setValue('Already created; skipped duplicate approval.');
    return;
  }

  // Validate required fields
  if (!bookingDate || !startTime || !endTime) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Missing date/time fields.');
    return;
  }

  const start = combineDateAndTime_(bookingDate, startTime);
  const end = combineDateAndTime_(bookingDate, endTime);

  if (!(start instanceof Date) || isNaN(start.getTime()) ||
      !(end instanceof Date) || isNaN(end.getTime())) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Invalid date/time values.');
    return;
  }

  if (end <= start) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('End time must be after start time.');
    return;
  }

  // Re-check calendar for overlaps right before creating event
  const overlapping = calendar.getEvents(start, end);
  if (overlapping.length > 0) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(
      'Conflict detected at approval time. Event not created.'
    );
    return;
  }

  const title = `Art Society Room Booking - ${tutorName || 'Tutor'}`;

  const descriptionLines = [
    `Tutor: ${tutorName || ''}`,
    `Email: ${tutorEmail || ''}`,
    `Purpose: ${purpose || ''}`,
    '',
    'Notes:',
    notes || ''
  ];

  const event = calendar.createEvent(title, start, end, {
    description: descriptionLines.join('\n')
    // You could add location here if useful
    // location: 'Art Society Room'
  });

  sheet.getRange(row, eventIdCol).setValue(event.getId());
  sheet.getRange(row, noteCol).setValue('Calendar event created successfully.');
}

/**
 * Optional helper to backfill missing dropdowns / notes headers if you need them.
 * Run manually once if useful.
 */
function ensureColumnsExist() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) throw new Error('Sheet not found.');

  const headers = getHeaders_(sheet);
  const required = ['Status', 'Calendar Event ID', 'Processing Note'];
  let lastCol = sheet.getLastColumn();

  required.forEach(name => {
    if (!headers[name]) {
      lastCol += 1;
      sheet.getRange(1, lastCol).setValue(name);
    }
  });

  // Refresh headers after adding
  const refreshed = getHeaders_(sheet);
  const statusCol = refreshed['Status'];

  // Add dropdown validation to Status column
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList([
      CONFIG.STATUS_VALUES.PENDING,
      CONFIG.STATUS_VALUES.APPROVED,
      CONFIG.STATUS_VALUES.REJECTED,
      CONFIG.STATUS_VALUES.CONFLICT
    ], true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, statusCol, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(rule);
}

/**
 * Manual setup helper to create the installable edit trigger.
 * Run this once from the Apps Script editor.
 */
function createInstallableTrigger() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onApprovalEdit')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

/**
 * Combine a date-only value and a time-only value into one Date.
 */
function combineDateAndTime_(datePart, timePart) {
  const d = new Date(datePart);
  const t = new Date(timePart);

  return new Date(
    d.getFullYear(),
    d.getMonth(),
    d.getDate(),
    t.getHours(),
    t.getMinutes(),
    0,
    0
  );
}

/**
 * Build a map of header name -> column number (1-based).
 */
function getHeaders_(sheet) {
  const headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headers = {};

  headerValues.forEach((name, i) => {
    const key = String(name).trim();
    if (key) headers[key] = i + 1;
  });

  return headers;
}

/**
 * Safe row lookup by header.
 */
function valueByHeader_(rowValues, headers, headerName) {
  const col = headers[headerName];
  if (!col) return '';
  return rowValues[col - 1];
}

How to set it up¶

Open the Google Sheet linked to the form, then go to Extensions → Apps Script to create a bound script project. Apps Script bound to a spreadsheet can automate that sheet and integrate with other Google services like Calendar. (Google for Developers)

Then:

Paste in the script
Replace the calendar ID
Save
Run ensureColumnsExist() once
Run createInstallableTrigger() once
Authorize the script when prompted

Apps Script will ask for authorization for the scopes it needs; Google documents that scripts accessing user data or acting on behalf of the user require OAuth authorization. (Google for Developers)

How to get the calendar ID¶

Use the actual room calendar’s ID once they give you access. CalendarApp.getCalendarById() can target a non-default calendar that the script owner is subscribed to. (Google for Developers)

In practice, the Society will need to share that room calendar with the Google account that owns/runs this script.

Why this version is strong¶

This setup gives you:

No direct tutor edits to the calendar
A clear Pending / Approved / Rejected / Conflict workflow
A proper final clash check at approval time
A written event ID in the sheet so duplicates are avoided
A durable admin trail in the spreadsheet

It also avoids the biggest weakness of Forms: a tutor may submit a request for a slot that looked free a few minutes ago, but by re-checking at approval time you stop bad calendar writes.

Once the basic version works, the next nice improvement would be an on form submit trigger that immediately checks the requested timeslot and writes Pending or Conflict automatically into the row. Google supports installable form-submit triggers as well as installable spreadsheet edit triggers. (Google for Developers)

That would give the coordinator a cleaner sheet from the outset.

My practical recommendation¶

Start with exactly this:

Google Form
linked Sheet
manual coordinator approval in Status
Apps Script creates the calendar event only when approved

That is simple, controllable, and very maintainable 🌼

Next, I can tailor the script to your exact form fields and column names so you can paste it in almost unchanged.

Throw-away Account¶

Robyn
For testing, I was going to use my "throw-away" gmail account calendar. How do I find the Calendar ID for that account, Emily?

Emily
Yes 😊

For a calendar you own, the usual click-path is:

Google Calendar → left sidebar → My calendars → hover the calendar name → ⋮ → Settings and sharing → scroll to “Integrate calendar” → copy “Calendar ID.” Google documents that path explicitly for shared calendars, and it’s the same place you’d normally look for a calendar’s ID. (Google Help)

For your throw-away Gmail account’s main/default calendar, there’s an even easier possibility: in Apps Script, Google says the default calendar’s ID is the account’s email address, and you can also avoid hard-coding an ID entirely by using CalendarApp.getDefaultCalendar(). (Google for Developers)

So for testing, you can do either of these:

// Easiest if you're using that account's main calendar
const calendar = CalendarApp.getDefaultCalendar();

// Or, if you want to be explicit
const calendar = CalendarApp.getCalendarById('yourthrowaway@gmail.com');

If you want to confirm what Apps Script thinks the ID is, run this once:

function showMyCalendarId() {
  const cal = CalendarApp.getDefaultCalendar();
  Logger.log(cal.getId());
}

Google’s Apps Script docs support both getDefaultCalendar() and getId(), and note that the default calendar’s ID is the user’s email address. (Google for Developers)

My practical advice: for your first test, use getDefaultCalendar() because it removes one moving part. Then, when the Art Society gives you access to their real room calendar, switch to getCalendarById('their_calendar_id'). (Google for Developers)

If you like, I’ll next help you adapt the approval script so it works with your default test calendar first, then make the one-line change for the Society calendar later.

Feature Creep¶

Robyn
Feature-creep time. Apparently there are two rooms to book (Studio vs Gallery). Also, now I need to set up the form that will populate the sheet (I used your column header suggestions, just need to add 'Room').

Emily
🤍 1️⃣2️⃣3️⃣ 🎙️✨ 4️⃣ 5️⃣ 6️⃣ 🎙️✨ Yes — and this is still nicely manageable 🌷

For the two-room version, I would strongly recommend one calendar per room rather than one combined calendar. Google Calendar lets you create additional calendars in the same account from a browser, and Apps Script can work with calendars the script owner owns or is subscribed to. So for testing in your throw-away account, I’d create:

Studio – Test
Gallery – Test

That makes conflict-checking much cleaner, because a Studio booking won’t accidentally block a Gallery booking. (Google Help)

For the form itself, Google Forms supports dropdown questions, date questions, and time questions, and you can link the form’s responses to an existing spreadsheet. So the cleanest build is:

create the Form first
add the questions
link it to your existing spreadsheet
then add your admin columns such as Status, Calendar Event ID, and Processing Note to the response sheet afterwards. (Google Help)

I’d set the form up like this:

Title: Art Society Room Booking Request

Form description: Please check room availability before submitting your request. Submission does not guarantee a booking; all requests require approval.

Then add a clickable Calendar link in the description such as: Check availability here Google Forms supports adding links in form text/descriptions. (Google Help)

My suggested questions would be:

Tutor Name — Short answer
Tutor Email — Short answer
Room — Dropdown
Studio
Gallery
Booking Date — Date
Start Time — Time
End Time — Time
Purpose — Short answer or paragraph
Notes / Special Requirements — Paragraph

That gives you exactly the data the Sheet and script need, without making the form feel bureaucratic. Google Forms supports these question types directly. (Google Help)

I’d also set a confirmation message something like:

Thank you. Your booking request has been submitted and will be reviewed by the coordinator. You will be contacted once it has been approved or declined.

Google Forms lets you customise that message in Settings → Presentation. (Google Help)

The sheet structure¶

Once the form is linked, your working sheet should end up with the form response columns plus these admin columns:

Status
Calendar Event ID
Processing Note

And now, because of the extra room, the script should route requests by Room to the correct calendar.

The script change for two rooms¶

Here’s the clean modification to the earlier script.

1. Replace the single calendar ID with a room map¶

const CONFIG = {
  SHEET_NAME: 'Form Responses 1',
  ROOM_CALENDARS: {
    'Studio': 'studio_test_calendar_id@group.calendar.google.com',
    'Gallery': 'gallery_test_calendar_id@group.calendar.google.com'
  },
  STATUS_VALUES: {
    PENDING: 'Pending',
    APPROVED: 'Approved',
    REJECTED: 'Rejected',
    CONFLICT: 'Conflict'
  }
};

If you are testing with calendars in your own Google account, each extra calendar will have its own Calendar ID under Settings and sharing → Integrate calendar. For non-default calendars, getCalendarById() is the right method. Apps Script supports retrieving additional calendars by ID and reading their events in a given time range. (Google for Developers)

2. Read the Room value from the row¶

Inside processApprovalRow_, add:

const room = valueByHeader_(rowValues, headers, 'Room');

3. Pick the right calendar from the room¶

Replace the old calendar lookup with:

const calendarId = CONFIG.ROOM_CALENDARS[room];
if (!calendarId) {
  sheet.getRange(row, headers['Status']).setValue(CONFIG.STATUS_VALUES.CONFLICT);
  sheet.getRange(row, headers['Processing Note']).setValue(`Unknown room: ${room}`);
  return;
}

const calendar = CalendarApp.getCalendarById(calendarId);
if (!calendar) {
  throw new Error(`Calendar not found for room: ${room}`);
}

4. Put the room name into the event title/description¶

For example:

const title = `${room} Booking - ${tutorName || 'Tutor'}`;

const descriptionLines = [
  `Room: ${room || ''}`,
  `Tutor: ${tutorName || ''}`,
  `Email: ${tutorEmail || ''}`,
  `Purpose: ${purpose || ''}`,
  '',
  'Notes:',
  notes || ''
];

That way, if you open the Studio or Gallery calendar later, each booking is still self-explanatory.

My recommendation for your test setup¶

I’d do it in this order:

In your throw-away Google account, create two calendars: Studio – Test and Gallery – Test (Google Help)
Build the Google Form with the questions above (Google Help)
Link the form to your existing spreadsheet (Google Help)
Add the three admin columns: Status, Calendar Event ID, Processing Note
Use the two-calendar version of the script
Submit one Studio request and one Gallery request
Approve them from the sheet and confirm each appears on the correct calendar

That will give you a proper proof-of-concept without overengineering it 🌼

The next most useful thing I can do is give you a full revised script for the two-room setup, ready to paste in one piece.

One Calendar¶

Robyn
I just checked with the acting President of the Art Society. She's adamant she wants one calendar with a location field on it for the room. Sorry, any chance we can do that?

Emily
🤍📚🧭1️⃣2️⃣3️⃣4️⃣🪞⚖️ Yes — absolutely, we can do that 🌷

A single calendar is still workable. Apps Script can read a calendar, fetch all events in a requested time range with getEvents(start, end), create events with createEvent(...), and set or read the event’s location field. So the logic simply becomes: check overlaps in that one calendar, but only treat them as a conflict when the overlapping event’s location is the same room. Google’s Calendar service supports all of those pieces directly. (Google for Developers)

So the design becomes:

one Google Form
one response Sheet
one calendar
a required Room dropdown in the form: Studio or Gallery
approved events written into that one calendar with location = Studio or location = Gallery

That gives the President what she wants, while still allowing Studio and Gallery to be booked at the same time without colliding. The only caveat is that the room names must be consistent, because the script will use the event location text to decide whether something clashes. (Google for Developers)

I’d keep the Room field in the Sheet as well. It still matters, because the script needs it when the coordinator approves a booking.

Revised script for one calendar, two rooms via Location¶

Replace the earlier script with this version:

/**
 * One-calendar, two-room booking workflow
 *
 * Form writes to Sheet.
 * Coordinator changes Status to "Approved".
 * Script re-checks the single calendar.
 * A conflict only exists if:
 *   - times overlap, AND
 *   - room/location matches ("Studio" vs "Gallery")
 */

const CONFIG = {
  // For testing, you can use:
  // CALENDAR_MODE: 'default'
  // or use a specific calendar ID with CALENDAR_MODE: 'id'
  CALENDAR_MODE: 'default', // 'default' or 'id'
  CALENDAR_ID: 'YOUR_CALENDAR_ID_HERE', // used only if CALENDAR_MODE = 'id'

  SHEET_NAME: 'Form Responses 1',

  ROOM_VALUES: ['Studio', 'Gallery'],

  STATUS_VALUES: {
    PENDING: 'Pending',
    APPROVED: 'Approved',
    REJECTED: 'Rejected',
    CONFLICT: 'Conflict'
  }
};

function onApprovalEdit(e) {
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();
  if (sheet.getName() !== CONFIG.SHEET_NAME) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();
  if (row < 2) return;

  const headers = getHeaders_(sheet);
  const statusCol = headers['Status'];
  if (!statusCol || col !== statusCol) return;

  const newStatus = String(e.range.getValue()).trim();
  if (newStatus !== CONFIG.STATUS_VALUES.APPROVED) return;

  processApprovalRow_(sheet, row, headers);
}

function processApprovalRow_(sheet, row, headers) {
  const calendar = getTargetCalendar_();
  if (!calendar) {
    throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
  }

  const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const tutorName = valueByHeader_(rowValues, headers, 'Tutor Name');
  const tutorEmail = valueByHeader_(rowValues, headers, 'Tutor Email');
  const roomRaw = valueByHeader_(rowValues, headers, 'Room');
  const bookingDate = valueByHeader_(rowValues, headers, 'Booking Date');
  const startTime = valueByHeader_(rowValues, headers, 'Start Time');
  const endTime = valueByHeader_(rowValues, headers, 'End Time');
  const purpose = valueByHeader_(rowValues, headers, 'Purpose');
  const notes = valueByHeader_(rowValues, headers, 'Notes');
  const existingEventId = valueByHeader_(rowValues, headers, 'Calendar Event ID');

  const statusCol = headers['Status'];
  const noteCol = headers['Processing Note'];
  const eventIdCol = headers['Calendar Event ID'];

  const room = normalizeRoom_(roomRaw);

  if (existingEventId) {
    sheet.getRange(row, noteCol).setValue('Already created; skipped duplicate approval.');
    return;
  }

  if (!room) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(`Unknown room: ${roomRaw}`);
    return;
  }

  if (!bookingDate || !startTime || !endTime) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Missing date/time fields.');
    return;
  }

  const start = combineDateAndTime_(bookingDate, startTime);
  const end = combineDateAndTime_(bookingDate, endTime);

  if (!(start instanceof Date) || isNaN(start.getTime()) ||
      !(end instanceof Date) || isNaN(end.getTime())) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Invalid date/time values.');
    return;
  }

  if (end <= start) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('End time must be after start time.');
    return;
  }

  // Re-check overlapping events in the ONE shared calendar
  const overlapping = calendar.getEvents(start, end);

  // Conflict only if the overlapping event is for the SAME room
  const sameRoomConflict = overlapping.some(event => {
    const eventRoom = normalizeRoom_(event.getLocation());
    return eventRoom === room;
  });

  if (sameRoomConflict) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(
      `Conflict detected at approval time: ${room} is already booked.`
    );
    return;
  }

  const title = `Art Society Booking - ${tutorName || 'Tutor'} (${room})`;

  const descriptionLines = [
    `Tutor: ${tutorName || ''}`,
    `Email: ${tutorEmail || ''}`,
    `Room: ${room}`,
    `Purpose: ${purpose || ''}`,
    '',
    'Notes:',
    notes || ''
  ];

  const event = calendar.createEvent(title, start, end, {
    location: room,
    description: descriptionLines.join('\n')
  });

  sheet.getRange(row, eventIdCol).setValue(event.getId());
  sheet.getRange(row, noteCol).setValue(`Calendar event created successfully in ${room}.`);
}

function getTargetCalendar_() {
  if (CONFIG.CALENDAR_MODE === 'default') {
    return CalendarApp.getDefaultCalendar();
  }
  return CalendarApp.getCalendarById(CONFIG.CALENDAR_ID);
}

function normalizeRoom_(value) {
  const v = String(value || '').trim().toLowerCase();

  if (v === 'studio') return 'Studio';
  if (v === 'gallery') return 'Gallery';

  return '';
}

function ensureColumnsExist() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) throw new Error('Sheet not found.');

  const headers = getHeaders_(sheet);
  const required = ['Status', 'Calendar Event ID', 'Processing Note'];
  let lastCol = sheet.getLastColumn();

  required.forEach(name => {
    if (!headers[name]) {
      lastCol += 1;
      sheet.getRange(1, lastCol).setValue(name);
    }
  });

  const refreshed = getHeaders_(sheet);
  const statusCol = refreshed['Status'];

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList([
      CONFIG.STATUS_VALUES.PENDING,
      CONFIG.STATUS_VALUES.APPROVED,
      CONFIG.STATUS_VALUES.REJECTED,
      CONFIG.STATUS_VALUES.CONFLICT
    ], true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, statusCol, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(rule);
}

function createInstallableTrigger() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onApprovalEdit')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

function combineDateAndTime_(datePart, timePart) {
  const d = new Date(datePart);
  const t = new Date(timePart);

  return new Date(
    d.getFullYear(),
    d.getMonth(),
    d.getDate(),
    t.getHours(),
    t.getMinutes(),
    0,
    0
  );
}

function getHeaders_(sheet) {
  const headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headers = {};

  headerValues.forEach((name, i) => {
    const key = String(name).trim();
    if (key) headers[key] = i + 1;
  });

  return headers;
}

function valueByHeader_(rowValues, headers, headerName) {
  const col = headers[headerName];
  if (!col) return '';
  return rowValues[col - 1];
}

What changes on the form¶

Your form should now definitely include:

Tutor Name
Tutor Email
Room → dropdown with Studio and Gallery
Booking Date
Start Time
End Time
Purpose
Notes

And yes, you can still link that form to your existing spreadsheet from the Form’s Responses tab using Select destination for responses → Select existing spreadsheet. (Google Help)

My practical advice for the one-calendar model¶

This version will work well as long as all room bookings created by the system have the location set properly. The weak point in a one-calendar setup is not the script — it’s humans. If someone manually adds an event to the calendar and leaves location blank, the script cannot know whether that booking is for Studio or Gallery. Using a strict Room dropdown in the form and always writing the room into the event location keeps that risk low. (Google for Developers)

For testing in your throw-away Gmail account, I’d use the default calendar first by leaving:

CALENDAR_MODE: 'default'

Then, when they give you access to the real Art Society calendar, switch to:

CALENDAR_MODE: 'id'
CALENDAR_ID: 'their_calendar_id_here'

Apps Script supports both the default calendar and calendars retrieved by ID. (Google for Developers)

One very nice future refinement would be to add an on form submit check that marks a row Pending or Conflict immediately, before the coordinator even looks at it.

Replace All Code¶

Robyn
When I pasted your code into the scripting section, it had a preface: myFunction { which I renamed. But I don't think I did things right.

Emily
😄🧱🔄🧠🧨🧭😄 Yes — I can see exactly what went wrong 😊 You were very close.

The big issue is that the whole script has been wrapped inside function writeToCalendar() { ... }, so onApprovalEdit, createInstallableTrigger, and the helpers are all nested functions, not top-level ones. That means Apps Script won’t see onApprovalEdit the way the trigger expects. Your pasted file also ends with an extra closing brace }, which belongs to that wrapper. And one other small thing: CALENDAR_ID is currently being set to CalendarApp.getDefaultCalendar(), which returns a calendar object, not an ID string.

So the fix is:

remove the outer function writeToCalendar() {
remove the very last }
leave all the other functions at the top level
change CALENDAR_ID to a string, or leave it unused while you are in default mode

Here is the corrected version, ready to paste in as the entire contents of the Apps Script file:

/**
 * One-calendar, two-room booking workflow
 *
 * Form writes to Sheet.
 * Coordinator changes Status to "Approved".
 * Script re-checks the single calendar.
 * A conflict only exists if:
 *   - times overlap, AND
 *   - room/location matches ("Studio" vs "Gallery")
 */

const CONFIG = {
  CALENDAR_MODE: 'default', // 'default' or 'id'
  CALENDAR_ID: '', // only used if CALENDAR_MODE = 'id'

  SHEET_NAME: 'Bookings',

  ROOM_VALUES: ['Studio', 'Gallery'],

  STATUS_VALUES: {
    PENDING: 'Pending',
    APPROVED: 'Approved',
    REJECTED: 'Rejected',
    CONFLICT: 'Conflict'
  }
};

function onApprovalEdit(e) {
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();
  if (sheet.getName() !== CONFIG.SHEET_NAME) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();
  if (row < 2) return;

  const headers = getHeaders_(sheet);
  const statusCol = headers['Status'];
  if (!statusCol || col !== statusCol) return;

  const newStatus = String(e.range.getValue()).trim();
  if (newStatus !== CONFIG.STATUS_VALUES.APPROVED) return;

  processApprovalRow_(sheet, row, headers);
}

function processApprovalRow_(sheet, row, headers) {
  const calendar = getTargetCalendar_();
  if (!calendar) {
    throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
  }

  const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const tutorName = valueByHeader_(rowValues, headers, 'Tutor Name');
  const tutorEmail = valueByHeader_(rowValues, headers, 'Tutor Email');
  const roomRaw = valueByHeader_(rowValues, headers, 'Room');
  const bookingDate = valueByHeader_(rowValues, headers, 'Booking Date');
  const startTime = valueByHeader_(rowValues, headers, 'Start Time');
  const endTime = valueByHeader_(rowValues, headers, 'End Time');
  const purpose = valueByHeader_(rowValues, headers, 'Purpose');
  const notes = valueByHeader_(rowValues, headers, 'Notes');
  const existingEventId = valueByHeader_(rowValues, headers, 'Calendar Event ID');

  const statusCol = headers['Status'];
  const noteCol = headers['Processing Note'];
  const eventIdCol = headers['Calendar Event ID'];

  const room = normalizeRoom_(roomRaw);

  if (existingEventId) {
    sheet.getRange(row, noteCol).setValue('Already created; skipped duplicate approval.');
    return;
  }

  if (!room) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(`Unknown room: ${roomRaw}`);
    return;
  }

  if (!bookingDate || !startTime || !endTime) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Missing date/time fields.');
    return;
  }

  const start = combineDateAndTime_(bookingDate, startTime);
  const end = combineDateAndTime_(bookingDate, endTime);

  if (!(start instanceof Date) || isNaN(start.getTime()) ||
      !(end instanceof Date) || isNaN(end.getTime())) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Invalid date/time values.');
    return;
  }

  if (end <= start) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('End time must be after start time.');
    return;
  }

  const overlapping = calendar.getEvents(start, end);

  const sameRoomConflict = overlapping.some(event => {
    const eventRoom = normalizeRoom_(event.getLocation());
    return eventRoom === room;
  });

  if (sameRoomConflict) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(
      `Conflict detected at approval time: ${room} is already booked.`
    );
    return;
  }

  const title = `Art Society Booking - ${tutorName || 'Tutor'} (${room})`;

  const descriptionLines = [
    `Tutor: ${tutorName || ''}`,
    `Email: ${tutorEmail || ''}`,
    `Room: ${room}`,
    `Purpose: ${purpose || ''}`,
    '',
    'Notes:',
    notes || ''
  ];

  const event = calendar.createEvent(title, start, end, {
    location: room,
    description: descriptionLines.join('\n')
  });

  sheet.getRange(row, eventIdCol).setValue(event.getId());
  sheet.getRange(row, noteCol).setValue(`Calendar event created successfully in ${room}.`);
}

function getTargetCalendar_() {
  if (CONFIG.CALENDAR_MODE === 'default') {
    return CalendarApp.getDefaultCalendar();
  }
  return CalendarApp.getCalendarById(CONFIG.CALENDAR_ID);
}

function normalizeRoom_(value) {
  const v = String(value || '').trim().toLowerCase();

  if (v === 'studio') return 'Studio';
  if (v === 'gallery') return 'Gallery';

  return '';
}

function ensureColumnsExist() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) throw new Error('Sheet not found.');

  const headers = getHeaders_(sheet);
  const required = ['Status', 'Calendar Event ID', 'Processing Note'];
  let lastCol = sheet.getLastColumn();

  required.forEach(name => {
    if (!headers[name]) {
      lastCol += 1;
      sheet.getRange(1, lastCol).setValue(name);
    }
  });

  const refreshed = getHeaders_(sheet);
  const statusCol = refreshed['Status'];

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList([
      CONFIG.STATUS_VALUES.PENDING,
      CONFIG.STATUS_VALUES.APPROVED,
      CONFIG.STATUS_VALUES.REJECTED,
      CONFIG.STATUS_VALUES.CONFLICT
    ], true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, statusCol, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(rule);
}

function createInstallableTrigger() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onApprovalEdit')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

function combineDateAndTime_(datePart, timePart) {
  const d = new Date(datePart);
  const t = new Date(timePart);

  return new Date(
    d.getFullYear(),
    d.getMonth(),
    d.getDate(),
    t.getHours(),
    t.getMinutes(),
    0,
    0
  );
}

function getHeaders_(sheet) {
  const headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headers = {};

  headerValues.forEach((name, i) => {
    const key = String(name).trim();
    if (key) headers[key] = i + 1;
  });

  return headers;
}

function valueByHeader_(rowValues, headers, headerName) {
  const col = headers[headerName];
  if (!col) return '';
  return rowValues[col - 1];
}

A few practical notes:

SHEET_NAME: 'Bookings' must match your actual tab name exactly. Yours does say Bookings, so that part looks intentional.
While testing with your throw-away account’s main calendar, CALENDAR_MODE: 'default' is perfect, so CALENDAR_ID can just stay blank.
Do not put any of this inside another function.

After pasting the corrected code:

Save the script.
Run ensureColumnsExist() once.
Run createInstallableTrigger() once.
Approve the permissions.
In the sheet, change a test row’s Status to Approved.

One more thing 🌼 If you already created a trigger while the code was wrapped incorrectly, go to the Triggers panel in Apps Script and delete any old/broken trigger before creating the new one, so you don’t end up with duplicates.

The earliest failure point is definitely the wrapper function, so fixing that should get you moving again.

If you paste the revised code and then show me any error message Google gives you, I’ll help you chase down the next bump.

Permissions¶

Robyn
How do I set permissions, Emily?

Emily
😄🤍✅⚠️🧱⭐🚆✨ 🤍🎧✨ 🧬💡 🎙️ There are really two kinds of permissions here, and it helps to separate them 🌿

First: permission for the script itself to use Sheets and Calendar. Second: permission for the Google account running the script to access the target calendar.

For the script authorization part, do this:

Open the Sheet.
Go to Extensions → Apps Script.
In the Apps Script editor, select a function such as ensureColumnsExist from the function dropdown.
Click Run.
Google should pop up an Authorization required dialog. Apps Script requires you to manually run a function once to trigger the consent flow when the script needs access to Google services like Calendar or Sheets. (Google for Developers)
Click Review permissions.
Choose the Google account you want this script to run under.
Continue through the consent screens and allow the requested access. Apps Script shows human-readable permission requests based on the OAuth scopes the script needs. (Google for Developers)

After that, set up the trigger:

In Apps Script, click the Triggers clock/alarm-clock icon on the left.
Click Add Trigger.
Choose function: onApprovalEdit
Event source: From spreadsheet
Event type: On edit
Save it. Installable triggers have to be activated before use, and they can call services that require authorization. (Google for Developers)

A very important detail: the installable trigger runs under the account of the person who created it. So if you create the trigger, it uses your authorization when it edits the calendar. (Google for Developers)

For calendar access permission, there are two cases:

If you are still testing with your throw-away account’s default calendar, and the script is running under that same account, you usually do not need extra sharing setup. Using CalendarApp.getDefaultCalendar() uses that account’s own main calendar. Apps Script supports both getDefaultCalendar() and getCalendarById(). (Google for Developers)

Later, when you switch to the Art Society calendar, that calendar must be shared with the Google account that owns/runs the script, otherwise the script will not be able to read or create events on it. Since installable triggers run as the creator, the calendar access needs to exist for that creator account. (Google for Developers)

If Google shows the scary “unverified app” screen during authorization, that can happen for scripts using certain scopes; Google notes you can still proceed in many personal/internal cases by using the advanced option, though for public distribution verification may be needed. (Google for Developers)

So, the shortest path for you right now is:

Run ensureColumnsExist() manually
Accept the permissions
Run createInstallableTrigger()
Accept permissions again if asked
Then test by changing a Sheet row’s Status to Approved

If you’d like, paste me the exact screen you see after clicking Run — especially if it says “Authorization required,” “This app isn’t verified,” or gives no prompt at all — and I’ll tell you exactly what to click next 🌼

Verification¶

Robyn
Instead of offering a place to accept permissions, the dialogue that comes up displays this:

Google hasn’t verified this app The app is requesting access to sensitive info in your Google Account. Until the developer (robindebonnecoeur@gmail.com) verifies this app with Google, you shouldn't use it.

I am logged in under the account that is trying to run the script (I double-checked). If I click the butt, it puts my on a send-email page.

Emily
🎧✨ That warning is normal for a personal/test Apps Script that asks for sensitive scopes like Calendar access. Google says unverified app screens appear when a script requests sensitive or restricted scopes and hasn’t gone through OAuth verification, and Apps Script notes that scripts using sensitive scopes may show this warning during authorization. (Google Help)

The important bit is this: because your script is published from a Gmail account, Google’s Apps Script docs say the unverified authorization flow can appear even for that same Gmail account. So double-checking that you were logged into the right account was sensible, but it does not make the warning go away. (Google for Developers)

Also, for apps in development / test builds, Google says you do not need to complete verification just to keep building and testing. (Google Help)

What you want on that warning screen is not the email/contact button. The route Google documents for development is:

Advanced → Go to {Project Name} (unsafe)

Google’s troubleshooting docs state that during development you can continue past the warning by choosing Advanced and then Go to {Project Name} (unsafe). (Google for Developers)

So try this:

In Apps Script, click Run on ensureColumnsExist.
When the “Google hasn’t verified this app” page appears, look for a small Advanced link or disclosure near the lower-left area of the dialog/page.
Click Advanced.
Then click Go to your project name (unsafe).
After that, you should reach the normal consent screen where you can allow Sheets/Calendar access. Google says the authorization dialog appears when the script is run and lists the scopes the script needs. (Google for Developers)

Why you’re seeing the email page: that’s just the developer contact link from the warning screen, not the bypass path. The bypass path is the Advanced link. 🌿

If you want, you can also make the consent flow a bit less noisy by checking the script’s scopes in Apps Script → Overview → Project OAuth Scopes. Google says you can view the scopes there, and you can explicitly narrow them in appsscript.json if needed. (Google for Developers)

If you do not see any Advanced link at all, tell me exactly what the page shows under the warning text, because then we may be dealing with either a different Google auth surface or a permissions/configuration quirk rather than the normal Apps Script unverified flow.

Typed Columns¶

Robyn
Brilliant. Thank you... don't know what I'd do without you, Emily.

So, it ran, but threw an error:

Error
This operation is not allowed on cells in typed columns.

Should I leave all columns as text fields?

Emily
😄🤍 🧱 🛠️ 🧠⚠️ ✂️✨ 💙 💙 No — I would not leave all columns as plain text. 🌿

What’s almost certainly happened is that your response sheet has been turned into a Google Sheets table, and tables use typed columns. Google’s help says table columns can be set to types like Text, Date, Date and time, Time, Dropdown, Checkbox, Smart chips, or None, and the table enforces that structure. It also says the table menu includes “Revert to unformatted data,” which removes the table styling/structure while leaving the data in place. (Google Help)

Your script’s ensureColumnsExist() function is trying to apply a data validation dropdown to the Status column with setDataValidation(...). Apps Script supports Range.setDataValidation(rule), but Sheets tables can block operations on typed columns, which matches the error you saw. That’s why this is failing. (Google for Developers)

So the clean fix is:

Best option: keep the response sheet as a normal sheet, not a Sheets table. That lets the script add the Status dropdown and write notes/event IDs without fighting the typed-column feature. Google says you can use the table menu to Revert to unformatted data if the range was converted to a table. (Google Help)

Here’s what I’d do:

Open the Bookings tab.
If you see a table name/header bar at the top of the range, it’s a Sheets table.
Open the Table menu.
Choose Revert to unformatted data.
Then run ensureColumnsExist() again.

You do not need every column to be text. In fact:

Booking Date can stay date-like
Start Time and End Time can stay time-like
Tutor Name, Purpose, Notes are naturally text
Status should just be a normal column so the script can add its dropdown

If you really want to keep using a Sheets table, then I’d stop the script from setting data validation and instead manage Status manually in the UI — but for your workflow, that’s more nuisance than it’s worth.

So my recommendation is simply:

Use an ordinary linked response sheet, not a Sheets table. That will make the script much happier 🌼

If you want, the next thing I’d check is whether your Bookings tab is currently a table, and I can tell you exactly where to click based on what you see.

Template: a Table¶

Robyn
My bad. I used a template right at the outset (it had most of the fields I wanted already), thinking it would be a simple sheet with a prettier face, and I guess it's a table instead. No way to convert that back to a normal sheet?

Emily
✂️ ✅🤍 💙 😄💙 😉 Yes — there is 🙂 You do not need to rebuild the whole thing.

Google Sheets’ Table feature has a menu option called “Revert to unformatted data”, and Google says that removes the table’s style/structure while leaving the data unchanged. That is the cleanest way to turn it back into an ordinary sheet range. (Google Help)

So I’d do this:

Open the sheet tab with the table.
At the top of the table, next to the table name, click Table menu.
Choose Revert to unformatted data. Google documents that exact option in the table menu. (Google Help)

A couple of useful clarifications:

Do not choose Delete table — Google says that removes the table and its data. (Google Help)
“Revert to unformatted data” is the safe one: data stays, the special typed-column behavior goes away, and your Apps Script should stop tripping over those table restrictions. (Google Help)

After you revert it, I’d then:

go back to Apps Script
run ensureColumnsExist() again
then test a row by changing Status to Approved

That should put you back on the ordinary-sheet path 🌷

If the table menu isn’t obvious, look at the top of the table near the table name, not in the normal Sheet tab menu — that’s where Google says the Table menu lives. (Google Help)

Function over beauty¶

Robyn
Lovely. Thank you. Worked. Well, not as pretty, but we're about function over looks, aren't we?

Emily
🎧 🚆 🪞 👀 🔥 🌿 🎯 ✍️ ✨🌏💛 📊 🏛📍❗🧭 Exactly 😌🌷

Pretty can come later. Working first is the grown-up victory.

And in your case, the “plain” version is actually the right professional choice: the Form is for tutors, the Sheet is for admin control, and the Calendar is the final source of truth.

That’s a tidy little system.

Next, I’d suggest this test sequence:

submit a dummy booking for Studio
mark it Approved
confirm it appears in the calendar with Location = Studio
submit another booking for the same time in Gallery and confirm it still allows approval
submit another booking for the same time in Studio and confirm it gets marked Conflict

If all three behave, you’ve basically proven the workflow 🎉

And yes — function over looks, especially for anything run by committees and volunteers. That way lies sanity.

In the Rooms and Status columns, is it possible to have dropdowns?¶

Emily
Yes — both can have dropdowns 🌷

For the Google Form, make Room a Dropdown question with:

Studio
Gallery

Google Forms supports dropdown questions directly. (Google Help)

For the Google Sheet:

Status can absolutely be a dropdown.
Room can also be a dropdown if you want manual edits in the sheet to stay clean and consistent.

Google Sheets supports in-cell dropdown lists, and Apps Script can set them with data validation rules. (Google Help)

In fact, your current script already creates a dropdown for Status when you run ensureColumnsExist(), because it uses a data-validation list. (Google for Developers)

If you want, you can extend that same function so it also applies a dropdown to the Room column. Add this block inside ensureColumnsExist() after the Status dropdown is created:

const roomCol = refreshed['Room'];

if (roomCol) {
  const roomRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(['Studio', 'Gallery'], true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, roomCol, Math.max(sheet.getMaxRows() - 1, 1), 1)
    .setDataValidation(roomRule);
}

That uses the same Apps Script data-validation mechanism Google documents for dropdown lists in Sheets. (Google for Developers)

So the neat setup would be:

Form: Room = dropdown
Sheet: Status = dropdown
Sheet: Room = dropdown too, as a safeguard

That gives you fewer typos, fewer odd variants like studio / Studio / gallery room, and a happier script 😊

If you’d like, I can give you the full updated ensureColumnsExist() function with both dropdowns already folded in.

The Form¶

Robyn
So, odd behaviour. With my lovely plain Google sheet (called 'Bookings'), I went to menu-> Tools-> Create a new Form. And promptly ran into issues. How do I link that sheet to the form?

Emily
🤍😄✅ 😄 🌊 🧭 🌺 🪶 Yes — this is the slightly maddening bit 😌

When you use Tools → Create a new form from a Google Sheet, Google creates a new linked Form and adds a new response sheet/tab in that spreadsheet. That route does not let you map the Form directly into an already-existing worksheet tab of your choosing. (Google Help)

The clean way to link things is:

Open the Google Form itself.
Go to Responses.
Click the More menu or the green Sheets icon.
Choose Select destination for responses.
Pick Select existing spreadsheet.
Choose your spreadsheet file. (Google Help)

But here is the important catch: choosing an existing spreadsheet means choosing the spreadsheet file, not an existing tab inside it. Google then stores the responses in a response sheet within that file rather than pouring them into a custom tab layout you already built. That behavior is consistent with Google’s help for both “Create a new form” from Sheets and “Select existing spreadsheet” from Forms. (Google Help)

So for your setup, the easiest path is:

let Google create the response tab
use that tab as the working Bookings sheet
add your admin columns (Status, Calendar Event ID, Processing Note) to the right of the form-response columns
point the script at that tab name (Google Help)

If you already have a manually made tab called Bookings, I’d do one of these:

Option A — simplest

rename your existing manual tab to something like Setup or Old Bookings
let Forms create its response tab
rename the new response tab to Bookings
keep SHEET_NAME: 'Bookings' in the script

Option B

leave the new response tab with its default name, such as Form Responses 1
change the script to match that exact tab name

That second option is perfectly fine too; the script only cares that SHEET_NAME matches the actual tab name. 🌿

So the short answer is: you link the form to the spreadsheet file, not to a pre-existing worksheet tab. Google will create/use a dedicated response sheet inside that file. (Google Help)

I’d recommend this exact next move:

create or open the Form
in Responses, choose Select existing spreadsheet
select your spreadsheet file
let Google create the response tab
rename that response tab to Bookings
add the admin columns on the right
keep going

If you want, paste me the tab names you now have in that spreadsheet, and I’ll tell you which one should be the real Bookings tab.

Form Field Frustration¶

Robyn
Moving the fields on the form is an exercise in frustration. Moving a field down flips another field to the top. Is there another way to organise the fields in the right order?

Emily
🌿 🌊 🎯 🌼 🌿💛 Yes — there is a saner way 😄

Google Forms does support drag handles at the top of each question, but if that is behaving erratically for you, the better workaround is to use the keyboard shortcuts instead of dragging. On a Mac, the official shortcuts are:

Move question up: ⌘ + Shift + K
Move question down: ⌘ + Shift + J

Google documents those shortcuts directly for Forms. (Google Help)

Two other things help a lot:

First, make sure Shuffle question order is off in Settings → Presentation. That setting changes the order respondents see, and if you want a specific order, Google says not to use it. (Google Help)

Second, if the form is getting long, use Sections. Google says you can add a section, move questions into it with the drag control, and reorder sections from the section menu. That often makes the form much easier to manage than one long vertical stack. (Google Help)

For your booking form, I’d probably lay it out like this:

Section 1 — Tutor details Tutor Name Tutor Email

Section 2 — Booking details Room Booking Date Start Time End Time

Section 3 — Purpose Purpose Notes

That way, even if Forms is being a bit daft, you only need to nudge a few items within each section instead of wrestling the whole form.

One caution: changing the question order in the Form does not automatically rearrange the columns in the linked Sheet after the response sheet already exists. Google support threads note that once the sheet is created, later form reordering does not remap those existing response columns for you. (Google Help)

So my practical advice is: stop dragging, use ⌘ + Shift + J/K, and organize into sections. That is usually much less frustrating. 🌷

If you want, I can suggest the cleanest final question order for this exact room-booking form so you can set it once and leave it alone.

Sections?¶

Robyn
I've been trying to move the individual fields into the appropriate section. They move away from each other when I let go (like North North magnet poles) instead of the field going into the section.

Emily
🌿 🌊 🎯 🌼 🌿💛 Yes — that usually means you’re dropping the question onto the section header, not inside the section. In Google Forms, a section begins below its title/description bar, and questions need to be dragged under that header line to become part of it. Third-party guides describing the Forms editor note that sections are added at the current position, and existing questions can be moved into them by dragging the question below the section header. (customguide.com)

The least-annoying way to do it is this:

Click the question you want to move.
Grab the six-dot handle at the top of the question card.
Drag it slowly until you see a horizontal insertion line inside the target section, just under that section’s title/description area.
Only then let go. Reordering questions in Forms is done from that six-dot handle. (Google Help)

A much easier workaround is to stop trying to “drop into” the section from far away. Instead, put the section break in the right place first. Google Forms inserts a new section at the current position, and everything after that point falls into the new section. So the easier method is often:

put your cursor on the question that should be the last item before the new section,
click Add section,
then the questions below will already belong to that new section,
and you only need to nudge one or two strays instead of wrestling the whole form. (customguide.com)

If the problem is with whole sections rather than individual questions, use the section’s three-dot menu → Move section instead of dragging. Guides on Forms sections describe that menu-based section reordering flow. (customguide.com)

For your booking form, the simplest rescue is probably:

leave the questions where they are,
insert Section 2 just before Room,
insert Section 3 just before Purpose/Notes,
then only move anything that still sits in the wrong block.

That avoids the “north-north magnet poles” nonsense almost entirely 😊

[Q] Layout?

Afraid not — not in Google Forms itself 😕🌷

Google’s supported layout controls are basically: reordering questions, adding up to 75 sections, routing people to different sections based on answers, and changing the theme/fonts. Google’s help describes those controls, but it does not offer arbitrary page layout, free placement of fields, or a multi-column designer in Forms. (Google Help)

So for your purposes, the practical answer is:

Yes to a tidy top-to-bottom form
Yes to splitting it into sections
No to redesigning the page layout the way you might in Word, Canva, or a web form builder (Google Help)

For this booking form, I’d keep it brutally simple:

Tutor details Tutor Name Tutor Email

Booking details Room Booking Date Start Time End Time

Extra information Purpose Notes

That will behave better than trying to outfight the Forms editor 😌

If the President later wants something prettier and more controlled, that’s the point where you’d usually move beyond Google Forms to something more like a custom web form or an app-style interface.

Email Tick Box¶

Robyn
When I publish the form, at the top my email is displayed. I would have thought that would automagically be sent to the sheet, but no. Seems silly to have to enter an email when it's already displayed at the top of the form.

Emily
😄 👟 👠 👟 😂 🎭 ✨ 🎭 🌿 🌺 Yes — it does feel silly, but Google splits those two things apart 🙃

The email shown at the top of the form is usually the respondent’s signed-in Google account, not a form field that automatically gets written into the response sheet. Google says usernames are not recorded unless you explicitly turn on Collect email addresses in the form’s Responses settings. (Google Help)

So you have two sensible options:

Option 1: let Google collect the email automatically In the Form, go to Settings → Responses → Collect email addresses and choose:

Verified if you want Google to capture the signed-in account email automatically, or
Responder input if you want Forms to require the person to type an email before submitting. Google documents both modes. (Google Help)

If you choose Verified, you can usually remove your separate “Tutor Email” question, because Google will store the respondent’s confirmed Google Account email with the submission. Google says the respondent’s Google Account email is collected with the response in that mode. (Google Help)

Option 2: keep your own “Tutor Email” field That makes sense if:

some tutors may not be signed into Google,
they might submit on behalf of someone else, or
you want them to provide a preferred contact email that may differ from the Google account shown at the top.

One more useful distinction: the email displayed at the top is also not necessarily your email as the owner. Google support threads note that what people usually see there is their own logged-in account. (Google Help)

My recommendation for your booking form would be:

turn on Collect email addresses → Verified
remove the separate Tutor Email question
let the Sheet use Google’s auto-collected email column instead

That gives you fewer fields, fewer typos, and less duplication 🌷

If you want, I can help you adjust the script so it reads the auto-collected Forms email column instead of your manual Tutor Email field.

TimeStamp¶

Robyn
Two more questions (and thanks again for your help on this. I dragged "TimeStamp" over to the right side of the table (not a simple sheet) and it set up another table name in that column. Do I just create a 'TimeStamp' field in the existing table, then delete the new TimeStamp table?
AND
Can I add fields to the right, ones not filled by the form?

Emily
🤍😄 ⚠️ ✅ 🤍
Yes — but I’d handle it a little differently 🌷

I would not drag Timestamp out of the form-fed block. In current Google Forms/Sheets behavior, community guidance says the safest arrangement is to leave Timestamp in column A, or at least before the first form-populated column, because moving it can interfere with new responses landing properly. (Google Help)

So for your first question: no, don’t create a second Timestamp field in the existing table. If dragging Timestamp created a second table, the clean fix is to undo it if possible. If it’s already saved, I’d remove that accidental second table and put Timestamp back where Forms originally created it. In Google Sheets tables, columns are meant to be added with the table’s own add-column control on the far right, and tables can also be reverted back to ordinary data if needed. (Google Help)

For your second question: yes, you can add fields to the right that are not filled by the form. In a normal Google Sheet, you can insert columns to the right; in a Sheets table, you can add new columns at the far right of the table. (Google Help)

The safe layout is:

That is: keep all the form-fed columns together, then place your admin/script columns to the right. I would avoid inserting manual columns inside the form-fed block. Community guidance also notes that if you later add new questions to the form, their response columns are typically appended on the right-hand end of the linked sheet, so changing the form later can shuffle where your “manual” area starts. (Google Help)

My practical recommendation is:

leave Timestamp where Forms put it
keep the response columns untouched as a block
add only your admin columns on the far right
don’t drag form columns around unless you absolutely must

If you keep running into table weirdness, the sturdier setup is to leave the raw Form response tab alone and do your coordinator work in a separate plain sheet that references it.

Table vs Sheet¶

Robyn
Okay, I've got a Table in a sheet. Was able to add a Status field which is blank after a row (response) has been added. That new column has 'Status' as header label.

There are several seriously large tasks ahead. First, of course, is to do the scheduling to Calendar, and thorough testing. The next, and larger task, is creating the developer's guide to the script. I intend to hand this over at some stage to someone to let them maintain it. I'm not a member of the society (not an artist in that sense), my partner is (she's acting President at the moment). Thoughts?

Emily
✨ 💙 🌿 Yes — and my main thought is: stop and simplify the architecture now, before the bigger tasks begin 🌷

You are no longer building “a handy form.” You are building a small operational system that another person will later inherit.

That changes what matters.

My honest recommendation¶

I would not build the long-term version around a Google Table.

You’ve already had one brush with the table/typed-column behaviour, and that is exactly the sort of thing that becomes a maintenance headache for the poor soul who inherits it later. Even if Status appears to work now, the next thing that may bite is script writes to Calendar Event ID, Processing Note, or future validations.

So, for a handover-friendly system, I would do one of these:

Better option¶

Use the Form response sheet as raw data only, untouched.

Then create a second, ordinary sheet tab — something like:

Responses_Raw
Bookings_Admin

The Form feeds Responses_Raw. The coordinator works from Bookings_Admin. The script reads raw data and writes admin/status/event information in the admin sheet.

That separation is very grown-up and very maintainable.

Acceptable option¶

Use one ordinary, non-table sheet only.

Least desirable option¶

Keep using a Table and hope Apps Script never objects again.

I wouldn’t choose that for something you intend to hand over.

The biggest non-technical issue: ownership¶

This matters more than the code.

Because you are not a society member, I would strongly suggest that the final production version lives under a society-controlled Google account, not yours and not your throwaway account.

That means, ideally, the following should ultimately belong to the Society account:

the Form
the Spreadsheet
the Calendar
the Apps Script project
the trigger that runs the automation

Why? Because otherwise the system quietly depends on you continuing to exist in the chain forever. If your access changes, or you lose interest, or your partner stops being President, the Society could be left with a system they cannot properly administer.

That is the sort of thing committees never think about until it hurts 😌

The system should stay very boring¶

That’s a compliment.

For a volunteer organisation, the best workflow is not the cleverest one. It’s the one that can be explained on one page.

I would aim for this exact state machine:

Pending
Approved
Rejected
Conflict

And that’s it.

No extra flourishes unless they are truly needed.

The next technical milestone¶

Before writing any handover documentation, I would get the workflow stable in this order:

Form submission creates a row
Coordinator changes Status to Approved
Script creates the calendar event
Event is written with:
correct date/time
correct location (Studio or Gallery)
event ID stored back in the sheet
Same-room overlap becomes Conflict
Different-room same-time booking is allowed

Until those six things behave reliably, documentation is premature.

My preferred testing plan¶

I’d test like this:

Happy path¶

submit one Studio booking
approve it
verify calendar entry appears correctly

Parallel-room path¶

submit a Gallery booking for the same time
approve it
verify it also appears correctly

Conflict path¶

submit another Studio booking for the same time
approve it
verify it is blocked and marked Conflict

Duplicate approval path¶

approve the same successful row again
verify no duplicate event is created

Bad-data path¶

test an end time before a start time
verify it fails cleanly

That gives you confidence in the core logic.

For the developer guide¶

Yes — that is absolutely worth doing, and I think you should write two documents, not one.

1. Administrator guide¶

For the non-technical society person.

This should explain:

what the system does
how tutors use it
what the coordinator does
what each status means
how to approve/reject
what to do if a row says Conflict
how to find the calendar entry
who owns the Google assets

This should be plain-English and short.

2. Technical maintenance guide¶

For the future maintainer.

This should explain:

where the Form lives
where the Sheet lives
which calendar is used
which script project is bound to the Sheet
what trigger is installed
what each important column means
how the script decides a conflict
how room/location is encoded
how to change the calendar ID later
how to re-authorize the script
how to recreate the trigger if it disappears
how to test after any changes

That second document is the real developer guide.

System assumptions¶

This is the page that saves future headaches.

It should say things like:

The calendar uses ar*
Room is stored in the event location
Valid room values are exactly Studio and Gallery
Booking is only created when Status = Approved
Calendar Event ID prevents duplicates
Manual edits to raw response columns should be avoided
The script assumes the sheet/tab name is Bookings or whatever final name you settle on

That kind of thing is gold later.

My strongest advice¶

Before you go much further, decide this one thing:

Will the final production system use a plain sheet, or a table?

My vote is very firmly: plain sheet.

Not because tables are bad in general, but because they add one more layer of behaviour, and automation handovers go better when the spreadsheet is as boring as a brick 🧱✨

You’ve actually done the hard conceptual work already. The remaining work is mostly about making it sturdy and transferable.

If you like, the most useful next thing would be for me to help you design the final production layout: raw responses tab, admin tab, columns, script responsibilities, and the outline for the two handover documents.

Worksheet Admin¶

Robyn
So, I have created a plain sheet named Admin. The field entries are linked to the "Form responses 3" table fields. However, unlike the normal worksheet to worksheet behaviour, the fields do not update with, like:

='Form responses 3'!B9

Emily
😄✨ 💙 🌊 🌿 🌺 🌙 💙 Yes — that makes sense, and the behaviour is actually consistent with what you’ve told it to do 🌿

='Form responses 3'!B9 is only a pointer to one specific cell. It will keep showing whatever is in B9; it will not “follow” later form submissions down the table. To make Admin track a growing response table, you want a formula that returns an array / whole column / whole table, not a single cell. Google’s Sheets docs say ARRAYFORMULA can return values into multiple rows and columns, and Google’s new table references are designed to update when rows are added or removed. (Google Help)

Because your source is still a table, the neatest approach is to use table references rather than A1-style cell references. Google says table references use the table name plus column headers, and that spaces in table names become underscores in formulas. It also says you can use ARRAYFORMULA(Table1) or ARRAYFORMULA(Table1[Column1]) to retrieve whole table data or a whole column. (Google Help)

So, instead of this:

='Form responses 3'!B9

use one of these patterns in Admin.

For a single mirrored column, put this in the first data cell under that header, for example B2:

=ARRAYFORMULA(Form_responses_3[Tutor Name])

For another column:

=ARRAYFORMULA(Form_responses_3[Room])

For the timestamp column:

=ARRAYFORMULA(Form_responses_3[Timestamp])

Because Google converts table-name spaces to underscores in formulas, Form responses 3 becomes Form_responses_3. (Google Help)

The even cleaner version is to mirror the entire response table into Admin with one formula. In A2, try:

=ARRAYFORMULA(Form_responses_3[#DATA])

Or, if you want the headers included too, put this in A1:

=ARRAYFORMULA(Form_responses_3[[#HEADERS],[#DATA]])

Google documents both #DATA and the combined [#HEADERS],[#DATA] syntax for table references. (Google Help)

That gives you a very tidy architecture:

left block in Admin = mirrored form data
right block in Admin = your manual/admin fields such as Status, Calendar Event ID, Processing Note

That is probably the cleanest model for what you’re building 🌷

Two cautions, though:

First, leave enough empty columns to the right of the mirrored block, because spilled array formulas need room to expand. Google notes that ARRAYFORMULA expands into neighboring cells. (Google Help)

Second, don’t sort or manually rearrange the raw response table unless you also understand the consequences for row alignment in Admin. Your admin columns are going to rely on “row N in Admin corresponds to row N in the mirrored response data.” That’s fine for a simple append-only workflow, but it depends on the rows staying in the same order.

My practical recommendation would be:

In Admin, row 1: put your final headers
In A2: use one spill formula for the whole source table
Put Status, Calendar Event ID, and Processing Note in columns to the right that are not part of the spill area

So yes — the reason your current link is not updating is that it is a single-cell reference, not a dynamic table/array reference. The fix is to mirror the table or columns with ARRAYFORMULA + table references. (Google Help)

The one I’d try first is this in Admin!A1:

=ARRAYFORMULA(Form_responses_3[[#HEADERS],[#DATA]])

If you want, I can help you lay out the exact Admin sheet so the mirrored block and the manual-status block won’t tread on each other.

Formula Parse Error¶

Robyn
I deleted all the formulas on that sheet. There are 7 columns in the table. The 1st 7 columns on the worksheet are clear. Put

=ARRAYFORMULA(Form_responses_3[[#HEADERS],[#DATA]])

in A2. And got:

Error Formula parse error.

Emily
Yes — and I think I know the exact snag 🌼

The formula syntax itself is valid, but Form_responses_3 is almost certainly not the table’s actual name. In Google Sheets, table references use the table name, not the sheet tab name, and spaces in a table name become underscores in formulas. Google’s examples are things like Table1[Column 1], Table1[[#HEADERS],[#DATA]], and Table1[#DATA]. (Google Help)

So the problem is probably this:

your sheet tab is named Form responses 3
but the table name may still be something like Table1 or something else
so Form_responses_3[...] throws a parse error because Sheets doesn’t know that table name exists. (Google Help)

Here’s the clean way to fix it:

Go to the source table on the Form responses 3 tab.
At the top of the table, next to the table menu, look for the actual table name.
Use that name in the formula. Google says the table menu sits at the top of the table, next to the table name. (Google Help)

So if the actual table name is Table1, then use one of these:

In A1 if you want headers and data:

=ARRAYFORMULA(Table1[[#HEADERS],[#DATA]])

Or in A2 if you want data only:

=ARRAYFORMULA(Table1[#DATA])

Google documents both of those exact table-reference forms, and also notes that ARRAYFORMULA(Table1) or ARRAYFORMULA(Table1[Column1]) is supported for whole-table or whole-column retrieval. (Google Help)

I’d try this first:

in a blank cell anywhere, type:

=Table1[#HEADERS]

If that works, you’ve found the right table name. If it errors, the table is not called Table1, so try the actual name shown at the top of the source table. (Google Help)

One more small correction: if you want headers plus data, put that formula in A1, not A2, because [[#HEADERS],[#DATA]] returns both. If you put it in A2, it will still parse if the name is right, but the spill range will start one row lower than you probably want. Google distinguishes #HEADERS, #DATA, and the combined [[#HEADERS],[#DATA]] forms. (Google Help)

So the most likely winning formula is:

=ARRAYFORMULA(Actual_Table_Name[[#HEADERS],[#DATA]])

with Actual_Table_Name replaced by the table’s real name, not the tab name.

If you tell me the exact table name shown at the top of the source table, I’ll give you the exact formula to paste.

0-Len TimeStamp¶

Robyn
I did as you suggested. Of course, timestamp was the first field, but I just changed the width to next-to-0 and now it looks good.

Emily
✨ Perfect 😌🌷

That’s actually a very practical fix. Keeping Timestamp as the first field keeps Google happy, and making it visually tiny keeps you happy — an excellent compromise.

You’ve now got the beginnings of a properly engineered little system:

form collects requests
raw responses remain intact
admin-facing sheet mirrors what matters
room logic stays consistent
calendar automation can sit on top of that

That’s a solid foundation.

At this point, I’d avoid changing the structure any further unless there’s a compelling reason. The next useful work really is:

finish the calendar-write script against the sheet structure you now actually have
test the approval/conflict cases thoroughly
only then start the handover docs

And one quiet little recommendation: keep a tiny change log as you go. Even just a plain text note with entries like:

moved admin workflow to plain Admin sheet
kept raw form table intact
hidden Timestamp visually instead of relocating it
single calendar, room stored in Location

That will make the future developer guide much easier to write.

You’ve done the messy architecture part now. The rest should feel much less slippery.

Yes 🌷

To make the form fillable by the general public, do this:

Open the form, click Publish at top right, then Manage, and under General access choose Anyone with the link. Then click Publish. After that, copy the responder link from Published → Copy responder link, or from Preview → Copy responder link. Google’s current Forms help says responders cannot access an unpublished form, and that under General access you can set access to anyone with a link. (Google Help)

There are three settings that commonly stop a form from being truly public:

First, if this is a work/school Google account, turn off Settings → Responses → Restrict to users in [your-company.com] and its trusted organizations. Google says that restriction is on by default for organizational forms and must be turned off to allow outside access. (Google Help)

Second, turn off Limit to 1 response unless you deliberately want Google sign-in. Google says that when this is on, users must sign in to a Google Account to fill out the form. (Google Help)

Third, for email collection, use Collect email addresses → Responder input if you still want an email but do not want to force a verified Google login. Google distinguishes Verified email collection, which uses the respondent’s Google Account email, from Responder input, which asks them to type an email manually. (Google Help)

So, for your room-booking form, the safest “anyone can use it” setup is:

Publish the form
General access: Anyone with the link
Limit to 1 response: Off
Collect email addresses: Responder input or just keep your own email field
then share the responder link rather than the edit/share link (Google Help)

One practical note: if you want literally anyone to complete it without a Google account, avoid features that require sign-in, especially Verified email collection. (Google Help)

The quickest test is to open the responder link in a private/incognito window where you are not signed into Google. If it opens straight to the form, you’re set. If it asks for login, one of the settings above is still forcing authentication.

Time Fields on Form¶

Robyn
This is huge progress, Emily. Super work on your part.

The time fields are a massive source of annoyance. You have to type click on and type '00' and it will always give you invalid entry as soon as you've typed any number for the hour and move away. Is the a javascript way I can make it so that when the user types a number in the hours bit of the field, a 00 (editable) shows up automatically in the minutes section?

Emily
❤️ 🌿 Not in Google Forms, sadly 😅 Google’s own docs draw a pretty firm line here: Forms add-ons extend the form editor, not the forms that respondents fill in, and Apps Script’s Forms service is for creating/modifying the form structure rather than injecting custom client-side behavior into the live responder page. In other words, you can automate the form around the edges, but you can’t make the published form run your own JavaScript to auto-populate the minutes box when someone types an hour. (Google for Developers)

So the answer to “can I make the minutes default to 00 as the user types?” is no, not with native Google Forms. What Forms does give you is built-in question types and, for text questions, response validation rules. It does not give you custom scripting inside the responder UI. (Google Help)

The cleanest workaround for a booking form is to stop using the Time question entirely and use Dropdown questions instead. Google Forms supports dropdown questions natively, and for a room-booking workflow they’re usually better anyway because they prevent oddball entries. A very practical setup would be:

Start Time dropdown
End Time dropdown with options like 9:00, 9:30, 10:00, 10:30, and so on. (Google Help)

That would be my strongest recommendation. It makes the form easier for tutors, cleaner for the sheet, and safer for the script. It also reduces the chance of someone entering 9, 09, 9.00, or fat-fingering the minutes field. 🌷

If you need more flexibility than half-hour blocks, the next-best workaround is to split time into two dropdowns:

Start Hour: 8, 9, 10, 11, 12, etc.
Start Minutes: 00, 15, 30, 45 and the same for end time. Google Forms supports dropdowns just fine, and this avoids that maddening built-in time widget altogether. (Google Help)

There is a third option: use a Short answer field and turn on Response validation so people must type time in a format like 09:00 or 14:30. Google documents response validation for form questions, especially short-answer rules. But even then, Forms would only validate the text after the fact; it still would not auto-fill 00 for them while they type. (Google Help)

For your specific case, I’d go with this:

Room — Dropdown Booking Date — Date Start Time — Dropdown with fixed slots End Time — Dropdown with fixed slots

That is much more committee-proof and handover-proof than relying on Google’s quirky time widget 😊

The next useful step is for me to help you choose a sensible list of time-slot options and then tweak the script so it reads dropdown times cleanly.

Timefield Solution¶

Robyn
The half-hourly solution sounds like to best solution for this use-case. I'll do that. And call it a day, but we'll do the entry into the Calendar bit tomorrow.

Emily
🔥 🏆 🌿 💛 That’s the right call 🌷

Half-hour dropdowns are boring in exactly the right way: easier for tutors, cleaner for the sheet, kinder to the script, and much easier to explain in a handover guide.

You’ve made excellent progress today:

settled the architecture
separated admin concerns from form intake
tamed the table/sheet weirdness
got the form public-facing
found a sane answer to the time-field nuisance

That’s a proper day’s work 😊

Tomorrow, the calendar-write piece should feel much more straightforward because the inputs will now be cleaner.

Initial Calendar script¶

24-Mar-2026¶

Use: CalendarApp.getDefaultCalendar() to get default calendarID

https://docs.google.com/forms/d/e/1FAIpQLSf0IuX9IuV9HHtgTyVTv1b5pmBAdvcd8BmbutvPQQRLj6TilQ/viewform?usp=publish-editor

Main Script¶

/**
 * Room booking approval workflow
 * Bound to the Google Sheet that receives Google Form responses.
 *
 * When the coordinator changes Status to "Approved",
 * this script:
 *   1. Re-checks the calendar for conflicts
 *   2. Creates the event if the slot is still free
 *   3. Writes the Calendar Event ID back to the sheet
 *   4. Writes notes for coordinator visibility
 */

const CONFIG = {
  CALENDAR_ID: 'YOUR_CALENDAR_ID_HERE',
  SHEET_NAME: 'Form Responses 1', // change if needed
  STATUS_VALUES: {
    PENDING: 'Pending',
    APPROVED: 'Approved',
    REJECTED: 'Rejected',
    CONFLICT: 'Conflict'
  }
};

/**
 * Installable trigger: runs when the sheet is edited.
 * Set this up via Triggers in Apps Script, not as a simple onEdit trigger.
 */
function onApprovalEdit(e) {
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();
  if (sheet.getName() !== CONFIG.SHEET_NAME) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();

  if (row < 2) return; // skip header row

  const headers = getHeaders_(sheet);
  const statusCol = headers['Status'];

  if (!statusCol || col !== statusCol) return;

  const newStatus = String(e.range.getValue()).trim();
  if (newStatus !== CONFIG.STATUS_VALUES.APPROVED) return;

  processApprovalRow_(sheet, row, headers);
}

/**
 * Main logic for creating the calendar event after approval.
 */
function processApprovalRow_(sheet, row, headers) {
  const calendar = CalendarApp.getCalendarById(CONFIG.CALENDAR_ID);
  if (!calendar) {
    throw new Error('Calendar not found. Check CALENDAR_ID and sharing permissions.');
  }

  const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const tutorName = valueByHeader_(rowValues, headers, 'Tutor Name');
  const tutorEmail = valueByHeader_(rowValues, headers, 'Tutor Email');
  const bookingDate = valueByHeader_(rowValues, headers, 'Booking Date');
  const startTime = valueByHeader_(rowValues, headers, 'Start Time');
  const endTime = valueByHeader_(rowValues, headers, 'End Time');
  const purpose = valueByHeader_(rowValues, headers, 'Purpose');
  const notes = valueByHeader_(rowValues, headers, 'Notes');
  const existingEventId = valueByHeader_(rowValues, headers, 'Calendar Event ID');

  const statusCol = headers['Status'];
  const noteCol = headers['Processing Note'];
  const eventIdCol = headers['Calendar Event ID'];

  // Prevent duplicate creation if already processed
  if (existingEventId) {
    sheet.getRange(row, noteCol).setValue('Already created; skipped duplicate approval.');
    return;
  }

  // Validate required fields
  if (!bookingDate || !startTime || !endTime) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Missing date/time fields.');
    return;
  }

  const start = combineDateAndTime_(bookingDate, startTime);
  const end = combineDateAndTime_(bookingDate, endTime);

  if (!(start instanceof Date) || isNaN(start.getTime()) ||
      !(end instanceof Date) || isNaN(end.getTime())) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Invalid date/time values.');
    return;
  }

  if (end <= start) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('End time must be after start time.');
    return;
  }

  // Re-check calendar for overlaps right before creating event
  const overlapping = calendar.getEvents(start, end);
  if (overlapping.length > 0) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(
      'Conflict detected at approval time. Event not created.'
    );
    return;
  }

  const title = `Art Society Room Booking - ${tutorName || 'Tutor'}`;

  const descriptionLines = [
    `Tutor: ${tutorName || ''}`,
    `Email: ${tutorEmail || ''}`,
    `Purpose: ${purpose || ''}`,
    '',
    'Notes:',
    notes || ''
  ];

  const event = calendar.createEvent(title, start, end, {
    description: descriptionLines.join('\n')
    // You could add location here if useful
    // location: 'Art Society Room'
  });

  sheet.getRange(row, eventIdCol).setValue(event.getId());
  sheet.getRange(row, noteCol).setValue('Calendar event created successfully.');
}

/**
 * Optional helper to backfill missing dropdowns / notes headers if you need them.
 * Run manually once if useful.
 */
function ensureColumnsExist() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) throw new Error('Sheet not found.');

  const headers = getHeaders_(sheet);
  const required = ['Status', 'Calendar Event ID', 'Processing Note'];
  let lastCol = sheet.getLastColumn();

  required.forEach(name => {
    if (!headers[name]) {
      lastCol += 1;
      sheet.getRange(1, lastCol).setValue(name);
    }
  });

  // Refresh headers after adding
  const refreshed = getHeaders_(sheet);
  const statusCol = refreshed['Status'];

  // Add dropdown validation to Status column
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList([
      CONFIG.STATUS_VALUES.PENDING,
      CONFIG.STATUS_VALUES.APPROVED,
      CONFIG.STATUS_VALUES.REJECTED,
      CONFIG.STATUS_VALUES.CONFLICT
    ], true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, statusCol, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(rule);
}

/**
 * Manual setup helper to create the installable edit trigger.
 * Run this once from the Apps Script editor.
 */
function createInstallableTrigger() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onApprovalEdit')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

/**
 * Combine a date-only value and a time-only value into one Date.
 */
function combineDateAndTime_(datePart, timePart) {
  const d = new Date(datePart);
  const t = new Date(timePart);

  return new Date(
    d.getFullYear(),
    d.getMonth(),
    d.getDate(),
    t.getHours(),
    t.getMinutes(),
    0,
    0
  );
}

/**
 * Build a map of header name -> column number (1-based).
 */
function getHeaders_(sheet) {
  const headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headers = {};

  headerValues.forEach((name, i) => {
    const key = String(name).trim();
    if (key) headers[key] = i + 1;
  });

  return headers;
}

/**
 * Safe row lookup by header.
 */
function valueByHeader_(rowValues, headers, headerName) {
  const col = headers[headerName];
  if (!col) return '';
  return rowValues[col - 1];
}

Get Calendar Script¶

// Easiest if you're using that account's main calendar const calendar = CalendarApp.getDefaultCalendar();

// Or, if you want to be explicit const calendar = CalendarApp.getCalendarById('yourthrowaway@gmail.com');

Two Room Script¶

/**
 * One-calendar, two-room booking workflow
 *
 * Form writes to Sheet.
 * Coordinator changes Status to "Approved".
 * Script re-checks the single calendar.
 * A conflict only exists if:
 *   - times overlap, AND
 *   - room/location matches ("Studio" vs "Gallery")
 */

const CONFIG = {
  // For testing, you can use:
  // CALENDAR_MODE: 'default'
  // or use a specific calendar ID with CALENDAR_MODE: 'id'
  CALENDAR_MODE: 'default', // 'default' or 'id'
  CALENDAR_ID: 'YOUR_CALENDAR_ID_HERE', // used only if CALENDAR_MODE = 'id'

  SHEET_NAME: 'Form Responses 1',

  ROOM_VALUES: ['Studio', 'Gallery'],

  STATUS_VALUES: {
    PENDING: 'Pending',
    APPROVED: 'Approved',
    REJECTED: 'Rejected',
    CONFLICT: 'Conflict'
  }
};

function onApprovalEdit(e) {
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();
  if (sheet.getName() !== CONFIG.SHEET_NAME) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();
  if (row < 2) return;

  const headers = getHeaders_(sheet);
  const statusCol = headers['Status'];
  if (!statusCol || col !== statusCol) return;

  const newStatus = String(e.range.getValue()).trim();
  if (newStatus !== CONFIG.STATUS_VALUES.APPROVED) return;

  processApprovalRow_(sheet, row, headers);
}

function processApprovalRow_(sheet, row, headers) {
  const calendar = getTargetCalendar_();
  if (!calendar) {
    throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
  }

  const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const tutorName = valueByHeader_(rowValues, headers, 'Tutor Name');
  const tutorEmail = valueByHeader_(rowValues, headers, 'Tutor Email');
  const roomRaw = valueByHeader_(rowValues, headers, 'Room');
  const bookingDate = valueByHeader_(rowValues, headers, 'Booking Date');
  const startTime = valueByHeader_(rowValues, headers, 'Start Time');
  const endTime = valueByHeader_(rowValues, headers, 'End Time');
  const purpose = valueByHeader_(rowValues, headers, 'Purpose');
  const notes = valueByHeader_(rowValues, headers, 'Notes');
  const existingEventId = valueByHeader_(rowValues, headers, 'Calendar Event ID');

  const statusCol = headers['Status'];
  const noteCol = headers['Processing Note'];
  const eventIdCol = headers['Calendar Event ID'];

  const room = normalizeRoom_(roomRaw);

  if (existingEventId) {
    sheet.getRange(row, noteCol).setValue('Already created; skipped duplicate approval.');
    return;
  }

  if (!room) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(`Unknown room: ${roomRaw}`);
    return;
  }

  if (!bookingDate || !startTime || !endTime) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Missing date/time fields.');
    return;
  }

  const start = combineDateAndTime_(bookingDate, startTime);
  const end = combineDateAndTime_(bookingDate, endTime);

  if (!(start instanceof Date) || isNaN(start.getTime()) ||
      !(end instanceof Date) || isNaN(end.getTime())) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Invalid date/time values.');
    return;
  }

  if (end <= start) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('End time must be after start time.');
    return;
  }

  // Re-check overlapping events in the ONE shared calendar
  const overlapping = calendar.getEvents(start, end);

  // Conflict only if the overlapping event is for the SAME room
  const sameRoomConflict = overlapping.some(event => {
    const eventRoom = normalizeRoom_(event.getLocation());
    return eventRoom === room;
  });

  if (sameRoomConflict) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(
      `Conflict detected at approval time: ${room} is already booked.`
    );
    return;
  }

  const title = `Art Society Booking - ${tutorName || 'Tutor'} (${room})`;

  const descriptionLines = [
    `Tutor: ${tutorName || ''}`,
    `Email: ${tutorEmail || ''}`,
    `Room: ${room}`,
    `Purpose: ${purpose || ''}`,
    '',
    'Notes:',
    notes || ''
  ];

  const event = calendar.createEvent(title, start, end, {
    location: room,
    description: descriptionLines.join('\n')
  });

  sheet.getRange(row, eventIdCol).setValue(event.getId());
  sheet.getRange(row, noteCol).setValue(`Calendar event created successfully in ${room}.`);
}

function getTargetCalendar_() {
  if (CONFIG.CALENDAR_MODE === 'default') {
    return CalendarApp.getDefaultCalendar();
  }
  return CalendarApp.getCalendarById(CONFIG.CALENDAR_ID);
}

function normalizeRoom_(value) {
  const v = String(value || '').trim().toLowerCase();

  if (v === 'studio') return 'Studio';
  if (v === 'gallery') return 'Gallery';

  return '';
}

function ensureColumnsExist() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) throw new Error('Sheet not found.');

  const headers = getHeaders_(sheet);
  const required = ['Status', 'Calendar Event ID', 'Processing Note'];
  let lastCol = sheet.getLastColumn();

  required.forEach(name => {
    if (!headers[name]) {
      lastCol += 1;
      sheet.getRange(1, lastCol).setValue(name);
    }
  });

  const refreshed = getHeaders_(sheet);
  const statusCol = refreshed['Status'];

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList([
      CONFIG.STATUS_VALUES.PENDING,
      CONFIG.STATUS_VALUES.APPROVED,
      CONFIG.STATUS_VALUES.REJECTED,
      CONFIG.STATUS_VALUES.CONFLICT
    ], true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, statusCol, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(rule);
}

function createInstallableTrigger() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onApprovalEdit')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

function combineDateAndTime_(datePart, timePart) {
  const d = new Date(datePart);
  const t = new Date(timePart);

  return new Date(
    d.getFullYear(),
    d.getMonth(),
    d.getDate(),
    t.getHours(),
    t.getMinutes(),
    0,
    0
  );
}

function getHeaders_(sheet) {
  const headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headers = {};

  headerValues.forEach((name, i) => {
    const key = String(name).trim();
    if (key) headers[key] = i + 1;
  });

  return headers;
}

function valueByHeader_(rowValues, headers, headerName) {
  const col = headers[headerName];
  if (!col) return '';
  return rowValues[col - 1];
}

Script Update¶

25-Mar-2026¶

/**
 * One-calendar, two-room booking workflow
 */

const CONFIG = {
  CALENDAR_MODE: 'default', // 'default' or 'id'
  CALENDAR_ID: '', // only used if CALENDAR_MODE = 'id'

  SHEET_NAME: 'Admin',
  RESPONSE_SHEET_NAME: 'Form responses 3',

  ROOM_VALUES: ['Studio', 'Gallery'],

  STATUS_VALUES: {
    PENDING: 'Pending',
    APPROVED: 'Approved',
    REJECTED: 'Rejected',
    CONFLICT: 'Conflict'
  }
};

function onApprovalEdit(e) {
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();
  if (sheet.getName() !== CONFIG.SHEET_NAME) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();
  if (row < 2) return;

  const headers = getHeaders_(sheet);
  const statusCol = headers['Status'];
  if (!statusCol || col !== statusCol) return;

  const newStatus = String(e.range.getValue()).trim();
  if (newStatus !== CONFIG.STATUS_VALUES.APPROVED) return;

  processApprovalRow_(sheet, row, headers);
}

function processApprovalRow_(sheet, row, headers) {
  const calendar = getTargetCalendar_();
  if (!calendar) {
    throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
  }

  const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const tutorName = valueByHeader_(rowValues, headers, 'Full Name');
  const tutorEmail = valueByHeader_(rowValues, headers, 'Email address');

  // Allows for room change as needed by Admin
  const roomRaw = effectiveValue_(rowValues, headers, 'Assigned Room', 'Room');  

  const bookingDate = valueByHeader_(rowValues, headers, 'Event Date');
  const startTime = valueByHeader_(rowValues, headers, 'Start time');
  const endTime = valueByHeader_(rowValues, headers, 'End time');
  const purpose = valueByHeader_(rowValues, headers, 'Course Name');
  const notes = valueByHeader_(rowValues, headers, 'Notes');
  const existingEventId = valueByHeader_(rowValues, headers, 'Calendar Event ID');

  const statusCol = headers['Status'];
  const noteCol = headers['Processing Note'];
  const eventIdCol = headers['Calendar Event ID'];

  const room = normalizeRoom_(roomRaw);

  if (existingEventId) {
    sheet.getRange(row, noteCol).setValue('Already created; skipped duplicate approval.');
    return;
  }

  if (!room) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(`Unknown room: ${roomRaw}`);
    return;
  }

  if (!bookingDate || !startTime || !endTime) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Missing date/time fields.');
    return;
  }

  const start = combineDateAndTime_(bookingDate, startTime);
  const end = combineDateAndTime_(bookingDate, endTime);

  if (!(start instanceof Date) || isNaN(start.getTime()) ||
      !(end instanceof Date) || isNaN(end.getTime())) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Invalid date/time values.');
    return;
  }

  if (end <= start) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('End time must be after start time.');
    return;
  }

  const overlapping = calendar.getEvents(start, end);

  const sameRoomConflict = overlapping.some(event => {
    const eventRoom = normalizeRoom_(event.getLocation());
    return eventRoom === room;
  });

  if (sameRoomConflict) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(
      `Conflict detected at approval time: ${room} is already booked.`
    );
    return;
  }

function effectiveValue_(rowValues, headers, overrideHeader, originalHeader) {
  const overrideValue = valueByHeader_(rowValues, headers, overrideHeader);
  const originalValue = valueByHeader_(rowValues, headers, originalHeader);
  return String(overrideValue || '').trim() ? overrideValue : originalValue;
}

  const title = `Art Society Booking - ${tutorName || 'Tutor'} (${room})`;

  const descriptionLines = [
    `Tutor: ${tutorName || ''}`,
    `Email: ${tutorEmail || ''}`,
    `Room: ${room}`,
    `Purpose: ${purpose || ''}`,
    '',
    'Notes:',
    notes || ''
  ];

  const event = calendar.createEvent(title, start, end, {
    location: room,
    description: descriptionLines.join('\n')
  });

  sheet.getRange(row, eventIdCol).setValue(event.getId());
  sheet.getRange(row, noteCol).setValue(`Calendar event created successfully in ${room}.`);

  // debug output
  console.log(`bookingDate raw: ${bookingDate} | type: ${typeof bookingDate}`);
  console.log(`startTime raw: ${startTime} | type: ${typeof startTime}`);
  console.log(`endTime raw: ${endTime} | type: ${typeof endTime}`);
}

function getTargetCalendar_() {
  if (CONFIG.CALENDAR_MODE === 'default') {
    return CalendarApp.getDefaultCalendar();
  }
  return CalendarApp.getCalendarById(CONFIG.CALENDAR_ID);
}

function normalizeRoom_(value) {
  const v = String(value || '').trim().toLowerCase();

  if (v === 'studio') return 'Studio';
  if (v === 'gallery') return 'Gallery';

  return '';
}

function ensureColumnsExist() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) throw new Error('Sheet not found.');

  const headers = getHeaders_(sheet);
  const required = ['Status', 'Calendar Event ID', 'Processing Note'];
  let lastCol = sheet.getLastColumn();

  required.forEach(name => {
    if (!headers[name]) {
      lastCol += 1;
      sheet.getRange(1, lastCol).setValue(name);
    }
  });

  const refreshed = getHeaders_(sheet);
  const statusCol = refreshed['Status'];

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList([
      CONFIG.STATUS_VALUES.PENDING,
      CONFIG.STATUS_VALUES.APPROVED,
      CONFIG.STATUS_VALUES.REJECTED,
      CONFIG.STATUS_VALUES.CONFLICT
    ], true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, statusCol, Math.max(sheet.getMaxRows() - 1, 1), 1).setDataValidation(rule);
}

function createInstallableTriggers() {
  const ss = SpreadsheetApp.getActive();

  ScriptApp.newTrigger('onApprovalEdit')
    .forSpreadsheet(ss)
    .onEdit()
    .create();

  ScriptApp.newTrigger('setPendingOnFormSubmit')
    .forSpreadsheet(ss)
    .onFormSubmit()
    .create();
}

function combineDateAndTime_(datePart, timePart) {
  const dateObj = parseDatePart_(datePart);
  const timeObj = parseTimePart_(timePart);

  if (!dateObj || !timeObj) {
    return new Date('invalid');
  }

  return new Date(
    dateObj.year,
    dateObj.month,
    dateObj.day,
    timeObj.hours,
    timeObj.minutes,
    0,
    0
  );
}

function parseDatePart_(value) {
  if (value instanceof Date && !isNaN(value.getTime())) {
    return {
      year: value.getFullYear(),
      month: value.getMonth(),
      day: value.getDate()
    };
  }

  const d = new Date(value);
  if (d instanceof Date && !isNaN(d.getTime())) {
    return {
      year: d.getFullYear(),
      month: d.getMonth(),
      day: d.getDate()
    };
  }

  return null;
}

function parseTimePart_(value) {
  // Case 1: already a Date object
  if (value instanceof Date && !isNaN(value.getTime())) {
    return {
      hours: value.getHours(),
      minutes: value.getMinutes()
    };
  }

  // Case 2: text like "08:00" or "8:30"
  if (typeof value === 'string') {
    const m = value.trim().match(/^(\d{1,2}):(\d{2})$/);
    if (!m) return null;

    const hours = Number(m[1]);
    const minutes = Number(m[2]);

    if (hours < 0 || hours > 23 || minutes < 0 || minutes > 59) return null;

    return { hours, minutes };
  }

  // Case 3: numeric spreadsheet fraction of a day
  if (typeof value === 'number' && !isNaN(value)) {
    const totalMinutes = Math.round(value * 24 * 60);
    const hours = Math.floor(totalMinutes / 60) % 24;
    const minutes = totalMinutes % 60;
    return { hours, minutes };
  }

  return null;
}

function getHeaders_(sheet) {
  const headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headers = {};

  headerValues.forEach((name, i) => {
    const key = String(name).trim();
    if (key) headers[key] = i + 1;
  });

  return headers;
}

function valueByHeader_(rowValues, headers, headerName) {
  const col = headers[headerName];
  if (!col) return '';
  return rowValues[col - 1];
}

function setPendingOnFormSubmit(e) {
  const responseSheet = e.range.getSheet();
  if (responseSheet.getName() !== CONFIG.RESPONSE_SHEET_NAME) return;

  const row = e.range.getRow();
  if (row < 2) return;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const adminSheet = ss.getSheetByName(CONFIG.SHEET_NAME);
  if (!adminSheet) {
    throw new Error(`Sheet '${CONFIG.SHEET_NAME}' not found.`);
  }

  SpreadsheetApp.flush();

  const headers = getHeaders_(adminSheet);
  const statusCol = headers['Status'];
  if (!statusCol) {
    throw new Error("Header 'Status' not found on Admin sheet.");
  }

  const statusCell = adminSheet.getRange(row, statusCol);
  if (statusCell.isBlank()) {
    statusCell.setValue(CONFIG.STATUS_VALUES.PENDING);
  }
}


function setPendingOnFormSubmit(e) {
  const RESPONSE_SHEET_NAME = 'Form responses 3';
  const ADMIN_SHEET_NAME = 'Admin';
  const STATUS_COL = 10; // J

  const responseSheet = e.range.getSheet();
  if (responseSheet.getName() !== RESPONSE_SHEET_NAME) return;

  const row = e.range.getRow();
  if (row < 2) return;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const adminSheet = ss.getSheetByName(ADMIN_SHEET_NAME);
  if (!adminSheet) {
    throw new Error("Sheet 'Admin' not found.");
  }

  SpreadsheetApp.flush();

  const statusCell = adminSheet.getRange(row, STATUS_COL);
  if (statusCell.isBlank()) {
    statusCell.setValue('Pending');
  }
}

2nd Update¶

25-Mar-2026¶

//  One-calendar, two-room booking workflow

const CONFIG = {
  CALENDAR_MODE: 'default', // 'default' or 'id'
  CALENDAR_ID: '', // only used if CALENDAR_MODE = 'id'

  SHEET_NAME: 'Admin',
  RESPONSE_SHEET_NAME: 'Form responses 3',

  ROOM_VALUES: ['Studio', 'Gallery'],

  STATUS_VALUES: {
    PENDING: 'Pending',
    APPROVED: 'Approved',
    REJECTED: 'Rejected',
    CANCELLED: 'Cancelled',
    CONFLICT: 'Conflict'
  },

  HEADERS: {
    ROOM: 'Room',
    ASSIGNED_ROOM: 'Assigned Room',
    EVENT_DATE: 'Event Date',
    START_TIME: 'Start time',
    END_TIME: 'End time',
    COURSE_NAME: 'Course Name',
    FULL_NAME: 'Full Name',
    EMAIL: 'Email address',
    NOTES: 'Notes',
    STATUS: 'Status',
    EVENT_ID: 'Calendar Event ID',
    NOTE: 'Processing Note'
  }
};

/**
 * Installable onEdit trigger.
 * Name preserved so your existing trigger can keep pointing at it.
 */
function onApprovalEdit(e) {
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();
  if (sheet.getName() !== CONFIG.SHEET_NAME) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();
  if (row < 2) return;

  const headers = getHeaders_(sheet);
  const statusCol = headers[CONFIG.HEADERS.STATUS];
  const assignedRoomCol = headers[CONFIG.HEADERS.ASSIGNED_ROOM];

  if (col === statusCol) {
    handleStatusEdit_(sheet, row, headers, e);
    return;
  }

  if (assignedRoomCol && col === assignedRoomCol) {
    handleAssignedRoomEdit_(sheet, row, headers, e);
  }
}

function handleStatusEdit_(sheet, row, headers, e) {
  const newStatus = trim_(e.range.getValue());
  const oldStatus = trim_(e.oldValue);

  if (!newStatus) return;

  if (newStatus === CONFIG.STATUS_VALUES.APPROVED) {
    processApprovalRow_(sheet, row, headers);
    return;
  }

  if (newStatus === CONFIG.STATUS_VALUES.CANCELLED) {
    processCancellationRow_(sheet, row, headers, oldStatus);
    return;
  }

  // Safety net: any move away from Approved should remove the live event.
  if (oldStatus === CONFIG.STATUS_VALUES.APPROVED && newStatus !== CONFIG.STATUS_VALUES.APPROVED) {
    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Status changed from Approved to ${newStatus}.`,
      newStatus
    );
  }
}

function handleAssignedRoomEdit_(sheet, row, headers, e) {
  const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
  const currentStatus = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS));

  if (currentStatus !== CONFIG.STATUS_VALUES.APPROVED) return;

  const assignedRoomCol = headers[CONFIG.HEADERS.ASSIGNED_ROOM];
  if (!assignedRoomCol) return;

  const previousRowValues = rowValues.slice();
  previousRowValues[assignedRoomCol - 1] =
    typeof e.oldValue === 'undefined' ? '' : e.oldValue;

  const previousEffectiveRoomRaw = effectiveValue_(
    previousRowValues,
    headers,
    CONFIG.HEADERS.ASSIGNED_ROOM,
    CONFIG.HEADERS.ROOM
  );
  const currentEffectiveRoomRaw = effectiveValue_(
    rowValues,
    headers,
    CONFIG.HEADERS.ASSIGNED_ROOM,
    CONFIG.HEADERS.ROOM
  );

  const previousEffectiveRoom = normalizeRoom_(previousEffectiveRoomRaw);
  const currentEffectiveRoom = normalizeRoom_(currentEffectiveRoomRaw);

  if (previousEffectiveRoom === currentEffectiveRoom) return;

  removeLiveCalendarEvent_(
    sheet,
    row,
    headers,
    `Assigned Room changed from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}; approval invalidated and returned to Pending.`,
    CONFIG.STATUS_VALUES.PENDING
  );
}

function processApprovalRow_(sheet, row, headers) {
  const calendar = getTargetCalendar_();
  if (!calendar) {
    throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
  }

  const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];

  const tutorName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
  const tutorEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);
  const roomRaw = effectiveValue_(
    rowValues,
    headers,
    CONFIG.HEADERS.ASSIGNED_ROOM,
    CONFIG.HEADERS.ROOM
  );
  const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
  const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
  const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);
  const purpose = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
  const notes = valueByHeader_(rowValues, headers, CONFIG.HEADERS.NOTES);
  const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_ID));

  const statusCol = headers[CONFIG.HEADERS.STATUS];
  const noteCol = headers[CONFIG.HEADERS.NOTE];
  const eventIdCol = headers[CONFIG.HEADERS.EVENT_ID];

  const room = normalizeRoom_(roomRaw);

  if (existingEventId) {
    sheet.getRange(row, noteCol).setValue('Already created; skipped duplicate approval.');
    return;
  }

  if (!room) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(`Unknown room: ${roomRaw}`);
    return;
  }

  if (!bookingDate || !startTime || !endTime) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Missing date/time fields.');
    return;
  }

  const start = combineDateAndTime_(bookingDate, startTime);
  const end = combineDateAndTime_(bookingDate, endTime);

  if (
    !(start instanceof Date) || isNaN(start.getTime()) ||
    !(end instanceof Date) || isNaN(end.getTime())
  ) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('Invalid date/time values.');
    return;
  }

  if (end <= start) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue('End time must be after start time.');
    return;
  }

  const overlapping = calendar.getEvents(start, end);

  const sameRoomConflict = overlapping.some(event => {
    const eventRoom = normalizeRoom_(event.getLocation());
    return eventRoom === room;
  });

  if (sameRoomConflict) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, noteCol).setValue(
      `Conflict detected at approval time: ${room} is already booked.`
    );
    return;
  }

  const title = `Art Society Booking - ${tutorName || 'Tutor'} (${room})`;

  const descriptionLines = [
    `Tutor: ${tutorName || ''}`,
    `Email: ${tutorEmail || ''}`,
    `Room: ${room}`,
    `Purpose: ${purpose || ''}`,
    '',
    'Notes:',
    notes || ''
  ];

  const event = calendar.createEvent(title, start, end, {
    location: room,
    description: descriptionLines.join('\n')
  });

  sheet.getRange(row, eventIdCol).setValue(event.getId());
  sheet.getRange(row, noteCol).setValue(`Calendar event created successfully in ${room}.`);
}

function processCancellationRow_(sheet, row, headers, oldStatus) {
  const prefix =
    oldStatus === CONFIG.STATUS_VALUES.APPROVED
      ? 'Booking cancelled.'
      : 'Booking marked as cancelled.';

  removeLiveCalendarEvent_(
    sheet,
    row,
    headers,
    prefix,
    CONFIG.STATUS_VALUES.CANCELLED
  );
}

function removeLiveCalendarEvent_(sheet, row, headers, baseNote, newStatus) {
  const eventIdCol = headers[CONFIG.HEADERS.EVENT_ID];
  const statusCol = headers[CONFIG.HEADERS.STATUS];
  const noteCol = headers[CONFIG.HEADERS.NOTE];

  const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
  const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_ID));

  let outcome = 'No linked calendar event to remove.';

  if (existingEventId) {
    const calendar = getTargetCalendar_();
    if (!calendar) {
      throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
    }

    const event = calendar.getEventById(existingEventId);

    if (event) {
      event.deleteEvent();
      outcome = 'Linked calendar event removed.';
    } else {
      outcome = 'Linked calendar event not found; ID cleared anyway.';
    }

    sheet.getRange(row, eventIdCol).clearContent();
  } else {
    sheet.getRange(row, eventIdCol).clearContent();
  }

  if (newStatus && statusCol) {
    sheet.getRange(row, statusCol).setValue(newStatus);
  }

  sheet.getRange(row, noteCol).setValue(`${baseNote} ${outcome}`.trim());
}

function getTargetCalendar_() {
  if (CONFIG.CALENDAR_MODE === 'default') {
    return CalendarApp.getDefaultCalendar();
  }
  return CalendarApp.getCalendarById(CONFIG.CALENDAR_ID);
}

function normalizeRoom_(value) {
  const v = trim_(value).toLowerCase();
  const match = CONFIG.ROOM_VALUES.find(room => room.toLowerCase() === v);
  return match || '';
}

function effectiveValue_(rowValues, headers, overrideHeader, originalHeader) {
  const overrideValue = valueByHeader_(rowValues, headers, overrideHeader);
  const originalValue = valueByHeader_(rowValues, headers, originalHeader);
  return trim_(overrideValue) ? overrideValue : originalValue;
}

function ensureColumnsExist() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SHEET_NAME);
  if (!sheet) throw new Error('Sheet not found.');

  const headers = getHeaders_(sheet);
  const required = [
    CONFIG.HEADERS.ASSIGNED_ROOM,
    CONFIG.HEADERS.STATUS,
    CONFIG.HEADERS.EVENT_ID,
    CONFIG.HEADERS.NOTE
  ];

  let lastCol = sheet.getLastColumn();

  required.forEach(name => {
    if (!headers[name]) {
      lastCol += 1;
      sheet.getRange(1, lastCol).setValue(name);
    }
  });

  const refreshed = getHeaders_(sheet);
  const statusCol = refreshed[CONFIG.HEADERS.STATUS];

  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList([
      CONFIG.STATUS_VALUES.PENDING,
      CONFIG.STATUS_VALUES.APPROVED,
      CONFIG.STATUS_VALUES.REJECTED,
      CONFIG.STATUS_VALUES.CANCELLED,
      CONFIG.STATUS_VALUES.CONFLICT
    ], true)
    .setAllowInvalid(false)
    .build();

  sheet.getRange(2, statusCol, Math.max(sheet.getMaxRows() - 1, 1), 1)
    .setDataValidation(rule);
}

function createInstallableTriggers() {
  const ss = SpreadsheetApp.getActive();
  const triggers = ScriptApp.getProjectTriggers();

  const hasEditTrigger = triggers.some(t =>
    t.getHandlerFunction() === 'onApprovalEdit' &&
    t.getEventType() === ScriptApp.EventType.ON_EDIT
  );

  const hasFormTrigger = triggers.some(t =>
    t.getHandlerFunction() === 'setPendingOnFormSubmit' &&
    t.getEventType() === ScriptApp.EventType.ON_FORM_SUBMIT
  );

  if (!hasEditTrigger) {
    ScriptApp.newTrigger('onApprovalEdit')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
  }

  if (!hasFormTrigger) {
    ScriptApp.newTrigger('setPendingOnFormSubmit')
      .forSpreadsheet(ss)
      .onFormSubmit()
      .create();
  }
}

function combineDateAndTime_(datePart, timePart) {
  const dateObj = parseDatePart_(datePart);
  const timeObj = parseTimePart_(timePart);

  if (!dateObj || !timeObj) {
    return new Date('invalid');
  }

  return new Date(
    dateObj.year,
    dateObj.month,
    dateObj.day,
    timeObj.hours,
    timeObj.minutes,
    0,
    0
  );
}

function parseDatePart_(value) {
  if (value instanceof Date && !isNaN(value.getTime())) {
    return {
      year: value.getFullYear(),
      month: value.getMonth(),
      day: value.getDate()
    };
  }

  const d = new Date(value);
  if (d instanceof Date && !isNaN(d.getTime())) {
    return {
      year: d.getFullYear(),
      month: d.getMonth(),
      day: d.getDate()
    };
  }

  return null;
}

function parseTimePart_(value) {
  // Case 1: already a Date object
  if (value instanceof Date && !isNaN(value.getTime())) {
    return {
      hours: value.getHours(),
      minutes: value.getMinutes()
    };
  }

  // Case 2: text like "08:00" or "8:30"
  if (typeof value === 'string') {
    const m = value.trim().match(/^(\d{1,2}):(\d{2})$/);
    if (!m) return null;

    const hours = Number(m[1]);
    const minutes = Number(m[2]);

    if (hours < 0 || hours > 23 || minutes < 0 || minutes > 59) return null;

    return { hours, minutes };
  }

  // Case 3: numeric spreadsheet fraction of a day
  if (typeof value === 'number' && !isNaN(value)) {
    const totalMinutes = Math.round(value * 24 * 60);
    const hours = Math.floor(totalMinutes / 60) % 24;
    const minutes = totalMinutes % 60;
    return { hours, minutes };
  }

  return null;
}

function getHeaders_(sheet) {
  const headerValues = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const headers = {};

  headerValues.forEach((name, i) => {
    const key = String(name).trim();
    if (key) headers[key] = i + 1;
  });

  return headers;
}

function valueByHeader_(rowValues, headers, headerName) {
  const col = headers[headerName];
  if (!col) return '';
  return rowValues[col - 1];
}

function trim_(value) {
  return String(value || '').trim();
}

function setPendingOnFormSubmit(e) {
  if (!e || !e.range) return;

  const responseSheet = e.range.getSheet();
  if (responseSheet.getName() !== CONFIG.RESPONSE_SHEET_NAME) return;

  const row = e.range.getRow();
  if (row < 2) return;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const adminSheet = ss.getSheetByName(CONFIG.SHEET_NAME);
  if (!adminSheet) {
    throw new Error(`Sheet '${CONFIG.SHEET_NAME}' not found.`);
  }

  SpreadsheetApp.flush();

  const headers = getHeaders_(adminSheet);
  const statusCol = headers[CONFIG.HEADERS.STATUS];
  if (!statusCol) {
    throw new Error(`Header '${CONFIG.HEADERS.STATUS}' not found on Admin sheet.`);
  }

  const statusCell = adminSheet.getRange(row, statusCol);
  if (statusCell.isBlank()) {
    statusCell.setValue(CONFIG.STATUS_VALUES.PENDING);
  }
}

Booking App 1¶

Introduction¶

Things to Manage¶

Business Plan¶

Recommended Sheet columns¶

Important practical choices¶

The script¶

How to set it up¶

How to get the calendar ID¶

Why this version is strong¶

One refinement I’d add later¶

My practical recommendation¶

Throw-away Account¶

Feature Creep¶

The sheet structure¶

The script change for two rooms¶

1. Replace the single calendar ID with a room map¶

2. Read the Room value from the row¶

3. Pick the right calendar from the room¶

4. Put the room name into the event title/description¶

My recommendation for your test setup¶

One Calendar¶

Revised script for one calendar, two rooms via Location¶

What changes on the form¶

My practical advice for the one-calendar model¶

Replace All Code¶

Permissions¶

Verification¶

Typed Columns¶

Template: a Table¶

Function over beauty¶

In the Rooms and Status columns, is it possible to have dropdowns?¶

The Form¶

Form Field Frustration¶

Sections?¶

Email Tick Box¶

TimeStamp¶

Table vs Sheet¶

My honest recommendation¶

Better option¶

Acceptable option¶

Least desirable option¶

The biggest non-technical issue: ownership¶

The system should stay very boring¶

The next technical milestone¶

My preferred testing plan¶

Happy path¶

Parallel-room path¶

Conflict path¶

Duplicate approval path¶

Bad-data path¶

For the developer guide¶

1. Administrator guide¶

2. Technical maintenance guide¶

System assumptions¶

My strongest advice¶

Worksheet Admin¶

Formula Parse Error¶

0-Len TimeStamp¶

Time Fields on Form¶

Timefield Solution¶

Initial Calendar script¶

24-Mar-2026¶

Main Script¶

Get Calendar Script¶

Two Room Script¶

Script Update¶

25-Mar-2026¶

2nd Update¶

25-Mar-2026¶