Booking App 2¶

Robyn
Hi Emily. I hope I can actually focus on our story later today, but I need to put this booking app to bed first. A few things I need the worksheet (google sheets, linked to a table, see the "Google Form vs Appointment Schedule" thread for details) to do is set the 'Status' field (a dropdown) to 'Pending'. It sort of needs to act similarly to MS Excel's WorksheetChange(), if you're familiar with that behaviour.

Emily
✈️🌫️ 🌿 Yes — this is very doable in Google Sheets, but the Excel analogy is split across a few different trigger types. In Sheets/App Script terms, onEdit(e) fires when a user manually edits cells, while a form submission to a spreadsheet has its own trigger: onFormSubmit(). For a Google Form feeding rows into your sheet, form submit is the right hook for setting a new booking’s Status to Pending. (Google for Developers)

One important gotcha: if your Admin sheet is being populated by ARRAYFORMULA, that formula spills its results across neighboring cells. In practice, that means you should not try to write your Status into the spill range itself. Put Status in its own separate, non-spilled column, or write it directly onto the actual form-response sheet instead. (Google Help)

Here’s the cleanest version for your setup.

Recommended approach¶

If the Google Form writes into Form Responses 3 and that sheet contains a Status column, use this Apps Script:

function setPendingOnFormSubmit(e) {
  const sheet = e.range.getSheet();

  // Change this to the sheet that actually receives the Google Form rows
  if (sheet.getName() !== 'Form Responses 3') return;

  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const statusCol = headers.indexOf('Status') + 1;

  if (statusCol === 0) {
    throw new Error('Could not find a column called "Status".');
  }

  const row = e.range.getRow();
  const statusCell = sheet.getRange(row, statusCol);

  // Only set it if blank
  if (!statusCell.getValue()) {
    statusCell.setValue('Pending');
  }
}

How to install it¶

Open the spreadsheet.
Go to Extensions → Apps Script.
Paste the code in.
Save.
In Apps Script, go to Triggers.
Add a trigger for setPendingOnFormSubmit
Event source: From spreadsheet
Event type: On form submit

That uses the spreadsheet’s installable form submit trigger, which is specifically meant for rows added by a linked Google Form. (Google for Developers)

If your Admin sheet is the one you care about¶

If Admin is only a mirrored view created by formula, then I would do this instead:

keep columns A:G as your spilled form data
make H = Status
let the script write to H on the real response row, or map it separately in Admin

If you want, you can also maintain Status entirely on Admin as a standalone column keyed by row, but the simplest and strongest design is usually:

raw response data on the form response sheet
Status stored there too
Admin just displays what you need

If you also want Excel-like “change it back to Pending when edited”¶

That is a second behaviour. For example: if someone edits the date, time, or name later, you may want Status to go back to Pending unless it’s already Completed or something similar. That would use an edit trigger:

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'Form Responses 3') return;

  const row = e.range.getRow();
  if (row === 1) return; // ignore header row

  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const statusCol = headers.indexOf('Status') + 1;
  if (statusCol === 0) return;

  // Don't react when the Status cell itself is edited
  if (e.range.getColumn() === statusCol) return;

  const statusCell = sheet.getRange(row, statusCol);
  const current = statusCell.getValue();

  // Adjust these rules however you like
  if (current !== 'Completed' && current !== 'Cancelled') {
    statusCell.setValue('Pending');
  }
}

That one behaves more like Excel’s WorksheetChange, but only for manual edits. onEdit is not the right mechanism for newly submitted Google Form rows, so the nicest setup is often:

setPendingOnFormSubmit(e) for new bookings
onEdit(e) for later manual changes (Google for Developers)

The part I’d do next, in your place, is this: add a real Status column to the actual response sheet and let the script stamp Pending there. That will save you a lot of grief later 🌷

Paste me your current column layout if you like — just the headers in order — and I’ll tailor the exact script to your sheet names and columns.

Sheet vs Table¶

Robyn
One thing I want to emphasise here is: the form auto-populates a table, and the worksheet (called 'Admin') takes its values from that table.
Columns A-H are auto-populated.
Column I is a formula field: it calculates total time for the course based on start and end times, for billing tutors.
Column J is the Status field with dropdowns: Pending, Conflict, Approved, Rejected.
I need the formula =(Hx - Gx) —where x is the row number—inserting into a new entry's 'I' field.

Emily
🌿 🌫️🚆 Perfect — for column I, I would not use Apps Script at all unless you absolutely have to. Because I is derived entirely from G and H, the clean Google Sheets way is to put one ARRAYFORMULA in I2, and let it fill itself for every new row that appears. ARRAYFORMULA is specifically meant to return results across multiple rows, so you do not need to “insert a new formula” row by row. (Google Help)

Put this in I2:

=ARRAYFORMULA(IF((G2:G<>"")*(H2:H<>""), H2:H-G2:G, ))

That will:

stay blank until both G and H have values
calculate H - G for every populated row
automatically cover future form entries as new rows arrive

The one rule is: leave I3:I completely empty apart from the spilled results, because array formulas need room to expand down the column. (Google Help)

If those start/end values are times of day, the result is a time duration, so after adding the formula, format column I as a duration/time-style number format via Format → Number (or use a custom number format if you want a particular display). Google Sheets supports changing number and time formats from that menu. (Google Help)

Two useful variants¶

If you want duration, such as 1:30 for one and a half hours:

=ARRAYFORMULA(IF((G2:G<>"")*(H2:H<>""), H2:H-G2:G, ))

If you want decimal hours for billing, such as 1.5 instead of 1:30:

=ARRAYFORMULA(IF((G2:G<>"")*(H2:H<>""), 24*(H2:H-G2:G), ))

In that second case, format column I as a normal number, not as time. The 24* works because Sheets stores times as fractions of a day. (Google Help)

If a booking could cross midnight¶

Use this version instead:

=ARRAYFORMULA(IF((G2:G<>"")*(H2:H<>""), MOD(H2:H-G2:G,1), ))

That prevents a negative duration if something starts late in the evening and ends after midnight.

For your `Status` column `J`¶

That one is different, because it is not a calculation from other columns — it is a value you want defaulted to Pending. That is where Apps Script with an onFormSubmit trigger still makes sense, because Apps Script can run automatically when a form response lands in the sheet. (Google for Developers)

So the neat split is:

Column I → formula only, with ARRAYFORMULA
Column J → Apps Script sets default to Pending

That is the most Google-Sheets-ish way to do it 🌷

If you’d like, I can now give you the exact script for J = Pending that matches your current Admin/responses setup, without disturbing the formula in I.

ArrayFormula()¶

Robyn
That ArrayFormula() thing is working perfectly. I'm using the one that turns it into fractions (duration). Now, for the App Script.

Emily
💛 🌿 Lovely — this part is straightforward 🌷

Because the form submits to the spreadsheet, the right mechanism is an installable spreadsheet “On form submit” trigger, and that trigger receives an event object you can use to identify the submitted row. (Google for Developers)

Use this script:

function setPendingOnFormSubmit(e) {
  const ADMIN_SHEET_NAME = 'Admin';
  const STATUS_COL = 10; // Column J

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const adminSheet = ss.getSheetByName(ADMIN_SHEET_NAME);

  if (!adminSheet) {
    throw new Error("Sheet 'Admin' not found.");
  }

  // Row number of the new form submission on the linked response sheet
  const row = e.range.getRow();

  // Ignore header row just in case
  if (row < 2) return;

  const statusCell = adminSheet.getRange(row, STATUS_COL);

  // Only set it if blank, so we don't overwrite anything manually changed later
  if (statusCell.isBlank()) {
    statusCell.setValue('Pending');
  }
}

How to install it¶

In the spreadsheet, go to Extensions → Apps Script, paste that in, save, then add a trigger:

Function: setPendingOnFormSubmit
Event source: From spreadsheet
Event type: On form submit

That is the documented trigger type for spreadsheet-linked form responses. (Google for Developers)

Why this should work for your setup¶

Your Admin sheet is mirroring the incoming form data row-for-row in columns A:H, with I already handled by your ARRAYFORMULA, and J reserved for manual/dropdown status. So the script only needs to write to Admin!J on the same row number that the form submission created. The event object for the spreadsheet form-submit trigger includes the submitted range, which is how e.range.getRow() identifies that row. (Google for Developers)

One small thing to check¶

Make sure the dropdown/data validation for J is already applied far enough down the column, or to the whole column, so that when the script writes Pending, it matches your allowed choices. Google Sheets supports dropdowns through data validation rules. (Google Help)

If you want it a little more defensive¶

This version checks that the row actually looks like a real booking row before writing Pending:

function setPendingOnFormSubmit(e) {
  const ADMIN_SHEET_NAME = 'Admin';
  const STATUS_COL = 10; // J
  const FIRST_DATA_COL = 1; // A
  const LAST_AUTOPOP_COL = 8; // H

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const adminSheet = ss.getSheetByName(ADMIN_SHEET_NAME);

  if (!adminSheet) {
    throw new Error("Sheet 'Admin' not found.");
  }

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

  const rowValues = adminSheet
    .getRange(row, FIRST_DATA_COL, 1, LAST_AUTOPOP_COL)
    .getDisplayValues()[0];

  const hasAnyData = rowValues.some(v => v !== '');
  if (!hasAnyData) return;

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

My instinct is that the first version is enough unless your Admin layout has some extra complication.

If you hit a snag, paste your exact header row for Admin and I’ll tailor the script precisely to it 💛

Forms WriteTo¶

Robyn
So, just looking at your setPendingOnFormSubmit(e) function. It appears to reference the table (called Form Responses, per the actual tab over the table, not the sheet name at the bottom). However, that table isn't going to have a status column: it's just there to collect data from the forms. The 'Admin' sheet is the one with the billing (duration) number and the 'Status' column. So, Do I just change :

if (sheet.getName() !== 'Form Responses 3') return;

to

if (sheet.getName() !== 'Admin') return;

??

Emily
No — don’t change that test to Admin 🌼

The key distinction is:

the trigger fires because the form writes to the response sheet
but your script can then write the result to Admin

For a spreadsheet form submit trigger, the event object includes the submitted range, and e.range.getSheet() is the sheet where the form response landed, not your mirrored Admin sheet. (Google for Developers)

So this:

if (sheet.getName() !== 'Form Responses 3') return;

means:

“Only run when the submission lands on the real form-response sheet.”

If you change it to:

if (sheet.getName() !== 'Admin') return;

then the function will almost certainly exit every time, because the submit event is not originating from Admin. (Google for Developers)

What you want is this pattern:

check that the submission came from the response sheet
get the submitted row number
write "Pending" into Admin!J on that same row

Like this:

function setPendingOnFormSubmit(e) {
  const RESPONSE_SHEET_NAME = 'Form Responses 3'; // the sheet receiving the form data
  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.");
  }

  const statusCell = adminSheet.getRange(row, STATUS_COL);

  if (statusCell.isBlank()) {
    statusCell.setValue('Pending');
  }
}

One important caveat: this assumes Admin stays in the same row order as the response sheet. If Admin is sorted differently, filtered into a different order, or otherwise not one-to-one by row number, then row-based writing will hit the wrong booking. (Google for Developers)

And one more tiny clarification: Apps Script’s getSheet() is working with the worksheet/tab object, not “the table” as a separate concept. So the name you test there should be the worksheet tab name that receives form submissions. (Google for Developers)

If you tell me the exact name of the response tab at the bottom of the workbook, I’ll rewrite the final script with your exact identifiers so you can paste it straight in 💛

Direct Edits¶

Robyn
I am going to stipulate to the end users that booking can only be done through the form (no alteration, additions/subtractions, sorting, etc) to be done directly to the Worksheet itself. I suppose if the name is wrong, they can change that on the table, perhaps, but then, what's saved on the form won't sync up properly, will it?

Emily
💙 🌿 Yes — your instinct is right. Treat the Form Responses tab as the raw intake ledger, and treat Admin as the place where you calculate, label, filter, and manage. Google officially supports linking a form to a response spreadsheet, and respondent-side corrections are meant to happen through the form’s Allow response editing setting, not by hand-editing the linked response sheet. (Google Help)

So, on the specific question: if someone changes a name directly in the response sheet, that change is sheet-only; it does not sync back into the Form’s stored response/Responses view. Google’s help/community guidance is consistent on this point: edits made in the linked sheet are not reflected back into the form, and the form remains the source of truth for the response record. (Google Help)

That means your safest operating rule is:

Bookings are created through the form only
Form Responses tab is append-only — no sorting, no inserting rows, no “tidying”
Admin is where staff work
if a customer made a mistake, either:
edit via the form’s response-edit mechanism, if you enable it, or
cancel/reject the old booking and have them submit a fresh one

That setup will keep your row alignment intact for the script and your ARRAYFORMULA, and it avoids the awkward situation where the sheet says one thing while the form record says another. This is also why people often keep a separate working tab for sorting/querying rather than manipulating the live response tab itself. (Google Help)

My practical recommendation for your app 🌷

Form Responses 3: untouched except for automated writing that you control very carefully
Admin: mirrored/derived working sheet
corrections by staff: preferably in Admin status fields only, not by altering submitted customer data
customer data corrections: resubmit or use response-edit link

So in plain English: no, changing the name on the response sheet will not properly “sync back” to the form. That is not a reliable bidirectional workflow. (Google Help)

The good news is that your current design is actually sensible: raw responses in one place, operational fields in Admin. That’s the right shape for this kind of system 😊

The next clean step is for me to give you the final Apps Script for Admin!J = Pending on each new form submission, based on that row-preserving design.

Yes, please.

Absolutely 💛 Here’s the final version to paste into Apps Script.

function setPendingOnFormSubmit(e) {
  const RESPONSE_SHEET_NAME = 'Form Responses 3'; // the tab the Google Form writes into
  const ADMIN_SHEET_NAME = 'Admin';
  const STATUS_COL = 10; // column J

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

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

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

  const statusCell = adminSheet.getRange(row, STATUS_COL);

  // Only set default if blank
  if (statusCell.isBlank()) {
    statusCell.setValue('Pending');
  }
}

What you must edit¶

Only this line, if your actual form-response tab has a different name:

const RESPONSE_SHEET_NAME = 'Form Responses 3';

Leave 'Admin' as-is if that is your actual admin sheet name.

How to install it¶

In the spreadsheet:

Extensions → Apps Script
Paste the code in
Save
Click the Triggers clock icon on the left
Add Trigger
Choose:
Function: setPendingOnFormSubmit
Deployment: Head
Event source: From spreadsheet
Event type: On form submit

Then authorise it when prompted.

Important assumption¶

This works only because your Admin sheet stays in the same row order as the form-response sheet. Since you’re stipulating no sorting/inserting/deleting on the worksheet itself, that should be fine 👍

One small improvement¶

If you want to be extra safe, use this version instead. It waits for formulas to settle before writing:

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');
  }
}

That’s the version I’d personally use.

After that, do one test submission through the form. You should see:

A–H populate
I calculate automatically from your ARRAYFORMULA
J become Pending

If you hit any hiccup, paste the exact tab names from the bottom of the workbook and I’ll sanity-check the script with you 🌷

Spelling 101¶

Robyn
Thank you. Didn't we run into an issue with RESPONSE_SHEET_NAME... the sheet name tab at the bottom said 'Form Response 3' while the Table Name said 'Form Response'.

Emily
Yes — the script cares about the worksheet tab name at the bottom, not the table’s display name 😊 e.range.getSheet() returns a Sheet object, and getName() returns the name of the sheet. Likewise, getSheetByName(...) looks up a sheet by its tab name. (Google for Developers)

So for this line:

const RESPONSE_SHEET_NAME = 'Form Responses 3';

you should use the exact tab name shown on the bottom tab. If the tab actually says:

const RESPONSE_SHEET_NAME = 'Form Response 3';

then that is what you must use — character for character. Apps Script is not looking at the table name here. (Google for Developers)

In other words:

tab at bottom → relevant to this script
table name → irrelevant to this script

That distinction exists because your code is using the Spreadsheet service’s Sheet and Range objects, not a table-specific API. (Google for Developers)

So the safest version is:

function setPendingOnFormSubmit(e) {
  const RESPONSE_SHEET_NAME = 'Form Response 3'; // <-- use the exact bottom-tab name
  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');
  }
}

So yes — if the bottom tab says Form Response 3 and not Form Responses 3, change it to match exactly 🌷

A quick sanity check is to open the response tab and copy its name exactly from the bottom tab into the script.

It actually says 'Form responses 3' - lower-case r for responses...

Then use it exactly like that — capitalization matters 😌

So this line should be:

const RESPONSE_SHEET_NAME = 'Form responses 3';

It needs to match the bottom tab name exactly, including:

capital letters
lower-case letters
spaces
numbers

So yes, lower-case r in responses is important.

Your final script should therefore be:

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');
  }
}

That was a very good catch 🌷 One wrong capital can make these scripts look “mysteriously broken.”

Populating Calendars¶

Robyn
That is all working a treat now, Emily.

Now, to populate the Calendar. The situation is this: there are two rooms: Gallery and Studio. Start times range from 8:00 in the morning (half-hour increments) to 19:00 (7pm). There could be, conceivably, 3 classes per room per day. So, our script ... I'm mean now to run EnsureColumnsExist() for this code:

/**
 * 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: 'Admin',

  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];
}


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');
  }
}

Emily
🤍 1️⃣2️⃣3️⃣ 🎙️✨ 4️⃣ 5️⃣ 6️⃣ 🎙️✨ Yes — for this code, ensureColumnsExist() is a one-time setup step, not something to run on every submission 😊

In your current Admin layout, Status already exists in J, so when ensureColumnsExist() scans row 1 it should leave Status alone and only append the missing helper columns — Calendar Event ID and Processing Note — to the right. It also reapplies the dropdown validation for the Status column. That is exactly the sort of job it’s good for. (Google for Developers)

Your overall workflow is sensible:

Form submit trigger → sets Admin!Status to Pending
coordinator changes status to Approved
edit trigger sees that change and runs the calendar logic
script checks overlap in the calendar with getEvents(start, end) and creates an event with createEvent(...) only if the same room is free. Apps Script’s Calendar service supports exactly that pattern. (Google for Developers)

The only thing I would change before you lock it in is this: make setPendingOnFormSubmit() find the Status column by header name rather than hard-coding 10. Hard-coding J will work today, but header lookup makes the whole thing much tougher and less brittle. The rest of your helper functions already use header lookup, so it keeps the code consistent too. (Google for Developers)

Here is the version I would use.

/**
 * 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, '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 createInstallableTriggers() {
  const ss = SpreadsheetApp.getActive();

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

  ScriptApp.newTrigger('setPendingOnFormSubmit')
    .forSpreadsheet(ss)
    .onFormSubmit()
    .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];
}

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);
  }
}

A few important notes for your exact setup:

Your ensureColumnsExist() should be run once manually after pasting the code. It is just a setup helper. SpreadsheetApp.flush() simply applies pending spreadsheet changes, so using it before you touch Admin is fine. (Google for Developers)

Your script needs two installable triggers, not one: an on form submit trigger for setPendingOnFormSubmit, and an on edit trigger for onApprovalEdit. Apps Script supports both on spreadsheets, and onFormSubmit() is specifically the spreadsheet trigger for linked form submissions. (Google for Developers)

Your conflict logic is sound for a single calendar with two rooms. calendar.getEvents(start, end) returns events that start in, end in, or encompass that range, so checking those returned events for matching location is a reasonable way to detect same-room overlap. createEvent(title, start, end, options) supports both location and description, and getId() gives you the event’s ID to store back in the sheet. (Google for Developers)

One subtle thing worth checking before you test: the header names in row 1 must match exactly what your code expects — for example Tutor Name, Tutor Email, Room, Booking Date, Start Time, End Time, Purpose, Notes, Status. Your helper functions do exact string matching after trimming, so even a small wording difference will come back as blank. (Google for Developers)

And one very practical trap: make sure the script time zone and the spreadsheet time zone are both set to Australia/Brisbane. Google notes that calendar creation and event retrieval interpret time values in the script’s time zone unless you specify otherwise, and mismatched spreadsheet/script time zones are a common source of bugs. (Google for Developers)

So the sequence I’d use is:

Paste the code.
Run ensureColumnsExist() once.
Run createInstallableTriggers() once.
Check that both triggers now exist.
Submit a test booking through the form.
Confirm Admin shows Pending.
Change that row’s status to Approved.
Confirm the calendar event appears in the correct room and the sheet gets an event ID plus processing note. (Google for Developers)

The one feature you have not built yet — just so you know where the next bump in the road will be — is what happens if someone changes an already-approved booking to Rejected, or edits the time after approval. Right now this script creates events neatly, but it does not yet update or delete them afterward.

Paste me your exact row-1 headers from Admin, and I’ll do a quick sanity pass for any naming mismatches before you test it.

Header Names¶

Robyn
"One subtle thing worth checking before you test: the header names in row 1 must match exactly what your code expects — for example Tutor Name, Tutor Email, Room, Booking Date, Start Time, End Time, Purpose, Notes, Status. Your helper functions do exact string matching after trimming, so even a small wording difference will come back as blank."

So, I can change those values in the script if they don't match? For example, I have "Full Name" instead of "Tutor Name"...

Emily
🤍📚🧭1️⃣2️⃣3️⃣4️⃣🪞⚖️ Yes — exactly 😊

Those names in the script are just string labels used to find the right column by matching the text in row 1. So if your sheet says Full Name instead of Tutor Name, you should change the script to match your actual header text.

For example, change this:

const tutorName = valueByHeader_(rowValues, headers, 'Tutor Name');

to this:

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

That’s the whole idea: the script must use the exact header text your sheet actually has.

In your code, these are the header names currently being looked up

Inside processApprovalRow_():

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

And elsewhere:

ensureColumnsExist() expects:

'Status'
'Calendar Event ID'
'Processing Note'

setPendingOnFormSubmit() expects:

'Status'

So if your actual headers are, say:

Full Name instead of Tutor Name
Email Address instead of Tutor Email

then you would change those two lines accordingly.

Example¶

Suppose your actual headers are:

Full Name
Email Address
Room
Booking Date
Start Time
End Time
Purpose
Notes
Status

Then this section becomes:

const tutorName = valueByHeader_(rowValues, headers, 'Full Name');
const tutorEmail = valueByHeader_(rowValues, headers, 'Email Address');
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');

Best practice¶

You have two choices:

1. Change the script to match the sheet headers This is easiest right now.

2. Change the sheet headers to match the script This can make the code tidier, but only if you’re happy renaming the columns.

For where you are now, I’d simply change the script to match your sheet.

Even better¶

If you want to make the script cleaner, I’d move the header names into CONFIG, like this:

const CONFIG = {
  CALENDAR_MODE: 'default',
  CALENDAR_ID: '',
  SHEET_NAME: 'Admin',
  RESPONSE_SHEET_NAME: 'Form responses 3',

  HEADERS: {
    NAME: 'Full Name',
    EMAIL: 'Email Address',
    ROOM: 'Room',
    DATE: 'Booking Date',
    START: 'Start Time',
    END: 'End Time',
    PURPOSE: 'Purpose',
    NOTES: 'Notes',
    STATUS: 'Status',
    EVENT_ID: 'Calendar Event ID',
    PROCESSING_NOTE: 'Processing Note'
  },

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

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

Then your lookups become much safer:

const tutorName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.NAME);
const tutorEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);

That way, if you rename a sheet column later, you only change it in one place.

So yes: you absolutely can change those values in the script. In fact, you should — the script must reflect your actual row-1 headers exactly 🌷

Paste your exact Admin header row left-to-right, and I’ll map every one of them for you.

Header Row¶

Robyn
Timestamp,
Email address,
Course Name,
Full Name,
Room,
Event Date,
Start time,
End time,
Total time,
Status,
Notes,
Calendar ID,
Calendar Event ID,
Processing Note,

Emily
😄🧱🔄🧠🧨🧭😄 Yes — with your headers, you should change the lookup strings to match them exactly 🌷

Your actual headers are:

Timestamp
Email address
Course Name
Full Name
Room
Event Date
Start time
End time
Total time
Status
Notes
Calendar ID
Calendar Event ID
Processing Note

So this block in processApprovalRow_() should become:

const tutorName = valueByHeader_(rowValues, headers, 'Full Name');
const tutorEmail = valueByHeader_(rowValues, headers, 'Email address');
const roomRaw = valueByHeader_(rowValues, headers, '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');

So yes, the ones that changed are:

Tutor Name → Full Name
Tutor Email → Email address
Booking Date → Event Date
Start Time → Start time
End Time → End time
Purpose → Course Name

The rest already match:

Room
Status
Notes
Calendar Event ID
Processing Note

A few useful observations from your header row:

Total time is fine to keep, but this calendar script does not use it.
Timestamp is also not used by this script.
Calendar ID exists in your sheet, but your current code does not use that column. Right now it uses:
CONFIG.CALENDAR_MODE
CONFIG.CALENDAR_ID

So if you leave the code as-is, the sheet column Calendar ID is just sitting there unused.

The good news¶

With your headers, ensureColumnsExist() should not need to add anything, because you already have:

Status
Calendar Event ID
Processing Note

So running it should mainly just apply the dropdown validation to Status 👍

Your corrected `processApprovalRow_()` header section¶

Here it is as a ready-to-paste replacement:

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');
  const roomRaw = valueByHeader_(rowValues, headers, '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;
  }

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

  const descriptionLines = [
    `Tutor: ${tutorName || ''}`,
    `Email: ${tutorEmail || ''}`,
    `Room: ${room}`,
    `Course: ${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}.`);
}

Two tiny polish notes:

I changed Purpose: in the calendar description to Course: because your sheet uses Course Name.
The title still says Tutor, which is fine, but if these are not really tutors, you could rename that label too.

You’re very close now ✨ The only remaining thing I’d want to sanity-check is whether you want to use the default calendar for all bookings, or whether that Calendar ID column was meant to select a specific calendar.

Which Calendar¶

Robyn
For testing, I want to use the default calendar for now, just to make sure things happen. Then, we can get specific for Calendar name. My understanding is that this email account and Calendar is primarily bookings-focused, so we should be good. So now, let's first look at the workflow the 'Admin' person will take to book sessions. She is likely going to have a look at availability manually (i.e., sight the calendar herself), then click on the Pending and make it Approved. Which then copies name, room, email, course, start and end times to the date. Right?

Emily
😄🤍✅⚠️🧱⭐🚆✨ 🤍🎧✨ 🧬💡 🎙️ Yes — that is exactly the workflow your script is set up for 🌷

For testing with the default calendar, the admin flow is:

A booking comes in through the form.
The Admin sheet gets the row.
Status is automatically set to Pending.
The admin looks at the calendar herself and makes a human judgment.
If she’s satisfied, she changes Status from Pending to Approved.
That edit fires onApprovalEdit(e).
The script then does its own final safety check against the calendar.
If all is well, it creates the calendar event.

So yes: the trigger point is changing the dropdown to Approved.

What gets copied into the calendar event¶

From your current script, when approval succeeds, it creates an event on the chosen date with:

Title: Art Society Booking - [Full Name] ([Room])
Start / end: from Event Date + Start time + End time
Location: the room, either Studio or Gallery
Description:
Full Name
Email address
Room
Course Name
Notes

So in practical terms, yes — it carries over:

name
email
course
room
date
start time
end time
notes

One important nuance¶

Even if the admin has already looked manually, the script still performs its own conflict check at the moment she clicks Approved.

That means:

if the room is still free, the event is created
if something overlapping already exists in the same room, the script changes the status to Conflict instead and writes a note in Processing Note

That is good design. It protects against a race condition where the admin looked at the calendar a minute ago, but someone else created an event before she approved the booking.

What happens in the sheet after approval¶

If successful:

Status stays Approved
Calendar Event ID gets populated
Processing Note says the event was created successfully

If not successful:

Status becomes Conflict
Processing Note explains why

So the admin experience, in plain English¶

“See a new request in Pending, check the calendar, and if it looks fine, set it to Approved. The script then creates the booking and records that it did so.”

That’s a very workable workflow for a human coordinator 💛

One tiny clarification to keep in mind: the script is not really “copying to the date” in the sheet sense — it is creating a real calendar event on that date and time in Google Calendar.

The next useful thing to think about is whether, once approved, you want the admin to be able to change an entry back to Rejected or edit a time and have the calendar update accordingly. Right now, your system is excellent at creating approved bookings, but not yet at editing or deleting them afterward.

Change Back To...¶

Robyn
Correct. As in: what if they way to change times or the room? Should go back to 'Pending'. So, there's still logic to write here, isn't there?
For now, we'll focus on ideal activity, make sure that's right, check our current exceptions, make sure they all work as advertised, and then, work in new features.

Emily
🎧✨ Yes — there is still more logic to write, and you’re thinking about it in exactly the right order 🌷 What you have now is a solid happy-path approval workflow: a form submission can set Pending via a spreadsheet form-submit trigger, and an admin changing Status to Approved can fire an edit trigger that checks the calendar, then creates the event if the room is free. Apps Script supports those trigger types, and CalendarApp supports both reading overlapping events and creating the booking event. (Google for Developers)

So the “ideal activity” is exactly this: the admin sees a Pending request, glances at the calendar, changes the dropdown to Approved, and the script performs a final safety check before creating the event. That second check is worth keeping even if the admin already looked manually, because it protects you from someone else creating an overlapping event in the meantime. (Google for Developers)

And yes, “what if they change the room or times later?” is a separate branch of logic from the current one. Right now your code is strong at creating an approved booking, but it does not yet handle post-approval changes. The Calendar service does give you the tools for that next phase: you can retrieve an existing event by ID and then update its time, location, title, or description, or delete it entirely if needed. (Google for Developers)

For now, your test plan is sensible 💛 I’d treat the current build as covering these present-day outcomes:

Pending gets set on form submission. (Google for Developers)
Changing Status to Approved triggers the approval handler. (Google for Developers)
If the room is unknown, date/time is missing or invalid, or end time is not after start time, the row is pushed to Conflict with a note. That’s your own validation logic, and it’s good defensive design.
If an overlapping event already exists in the same room, the script marks Conflict instead of creating a duplicate booking. getEvents(start, end) is the right CalendarApp method for that calendar-window check. (Google for Developers)
If an event was already created and you already have a stored event ID, the script skips duplicate creation. Storing and later using event IDs is supported by CalendarApp methods like getId() and getEventById(). (Google for Developers)

The next feature set, once this passes testing, is usually one of these two policies:

Strict review policy: if any key booking field changes after approval — date, start time, end time, room, maybe course name — the row automatically goes back to Pending, the processing note says it needs re-review, and the calendar event is either left alone until re-approved or cleared deliberately.
Live sync policy: if those fields change after approval, the script updates the existing calendar event in place using the saved event ID. Apps Script supports updating an event’s time, location, description, and title directly. (Google for Developers)

Given the kind of booking app you’re building, I’d start with the strict review policy. It is simpler, safer, and easier to reason about: “approved data changed, so the booking is no longer approved.” Then later, once you trust the workflow, you can decide whether to automate updates and deletions. The Calendar service does support deletion too, so that option is there when you’re ready. (Google for Developers)

So yes: for now, focus on the ideal path and the exceptions you already coded. Once those behave exactly as advertised, the next logic layer is “approved booking was changed afterward; what should the system do?” That is the right next problem, not something you need to solve before proving the current workflow works. 🌼

When you’re ready, I’d tackle that next branch with you as: which columns count as “material changes,” and on such a change, should we revert to Pending only, or also touch the existing calendar event?

Ready to test?¶

Right, so am I ready to test? Where should I be looking to debug?

Yes — you’re ready to test 🙂 And for this stage, I’d debug in four places at once, because each one tells you a different part of the story.

First, make sure the plumbing exists at all: in Apps Script → Triggers, you should see two installable triggers, one for setPendingOnFormSubmit with From spreadsheet / On form submit, and one for onApprovalEdit with From spreadsheet / On edit. If those triggers are missing, nothing downstream will happen. Installable-trigger failures also show up in the project’s execution history, and Google notes they can generate error notification emails for failures. (Google for Developers)

Second, your best debugging view is Apps Script → Executions. That is where you’ll see whether the function actually ran, whether it succeeded or failed, and the error message if it blew up. Google’s docs point to Executions as the place to inspect failed runs and diagnose trigger problems. (Google for Developers)

Third, look directly in the Admin sheet after each test. For a successful approval, you want to see:

Status stay as Approved
Calendar Event ID get filled in
Processing Note say the event was created successfully

For an unsuccessful approval, you want:

Status changed to Conflict
Processing Note explaining why

That sheet is effectively your first-line operational log, even before you add formal logging. The logic you wrote already records outcomes there, so that is the first place I would glance after every test.

Fourth, look in the default Google Calendar itself. Your current script uses CalendarApp.getDefaultCalendar(), so if approval succeeds, the event should appear on the default calendar for the Google account running the script. CalendarApp is the correct Apps Script service for reading the default calendar, checking events in a time window, and creating the new booking event. (Google for Developers)

For your first clean test, I’d do it in this order 🌿 Submit one fresh form entry. Confirm Admin gets the row and Status becomes Pending. Then manually change that row to Approved. After that, check:

did onApprovalEdit appear in Executions?
did Calendar Event ID populate?
did Processing Note say success?
did the event appear in the default calendar? That gives you a full end-to-end proof in one pass. (Google for Developers)

If something fails, the most likely culprits are very mundane ones: the trigger wasn’t created, the tab name doesn’t exactly match, a header name doesn’t exactly match, or the script never got authorized properly. The event object passed to triggers contains the sheet/range context, so if the wrong sheet name is being checked, your function will simply return without doing anything. (Google for Developers)

A very worthwhile improvement before heavy testing is to add a few log lines. Apps Script supports both Logger and console output in the Execution log, and those logs also appear in Executions. Even a handful of lines makes debugging much easier. (Google for Developers)

For example, I’d add lines like these:

console.log(`onApprovalEdit fired: sheet=${sheet.getName()} row=${row} col=${col}`);
console.log(`New status value: ${newStatus}`);
console.log(`Room=${room}, start=${start}, end=${end}`);
console.log(`Overlapping events found: ${overlapping.length}`);

and in the form-submit function:

console.log(`Form submit fired: sheet=${responseSheet.getName()} row=${row}`);
console.log(`Setting Pending in Admin row ${row}`);

Those will show you whether the trigger fired, whether the row/header logic worked, and whether the calendar conflict logic saw what you expected. Google recommends using the Execution log and the Executions panel for exactly this kind of development-time debugging. (Google for Developers)

One last thing to check before testing: make sure the script’s time zone and the spreadsheet/calendar context are what you expect. Your combineDateAndTime_() function builds JavaScript Date objects from separate date and time parts, so if there is any date/time weirdness, that’s one of the first places I’d inspect in the logs by printing the computed start and end values. And since your calendar logic uses getEvents(start, end) and createEvent(title, start, end, ...), confirming the actual computed timestamps is the fastest way to catch odd behavior. (Google for Developers)

So yes: you’re ready. My practical rule would be:

Triggers page tells you whether the automation exists.
Executions tells you whether it ran and failed.
Admin sheet tells you what the script thought happened.
Calendar tells you whether the business outcome actually occurred. 🌷

If you like, paste me your current script after you add the console.log(...) lines, and I’ll mark the best exact spots to instrument.

Testing-Round 1¶

Robyn
First test completed. An error occurred (because, of course it did). I forgot to change 'Start Time' to 'Start time'. Spelling matters. 2nd go.

Emily
😄🤍 🧱 🛠️ 🧠⚠️ ✂️✨ 💙 💙 Exactly the sort of error I’d expect at this stage 😄 And honestly, that’s a good sign: it means the trigger fired, the code ran, and it failed for a real, traceable reason rather than silently doing nothing.

Yes — spelling and capitalization matter here because your header lookup is doing exact string matches. Start Time and Start time are different as far as the script is concerned.

So your first test has already told us something useful: the plumbing is alive.

Now on the second go, the things I’d be watching for are:

Status stays Approved
Calendar Event ID gets populated
Processing Note reports success
the event appears in the default calendar at the correct date/time and room

If it still fails, you’re now in the very nice phase where it’s probably just another header-name mismatch, not a structural problem. That’s much easier to tame 🌷

Tell me what happens on round two.

Missing date/time fields¶

So, the first time, the error I got was "Missing date/time fields." Fixed.

Beautiful — that means the error message did its job 🌷

“Missing date/time fields” told us the script was reaching this check:

it found the row
it read the headers
one or more of the expected date/time columns came back blank because the header text didn’t match exactly

Now that you’ve corrected Start time, you’re past that particular gate.

On the next run, I’d check these in order:

Status remains Approved
Calendar Event ID gets a value
Processing Note says the event was created successfully
the event appears in the default calendar with:
the right date
the right start/end times
the right room in Location
the right name/course in the description

If it fails again, the next most likely culprits are:

End time mismatch
Event Date mismatch
Email address mismatch
time values being read oddly, even though the headers now match

But this is still all very encouraging 😊 You’re debugging actual field mapping now, not fighting broken triggers or dead logic.

Run the second test, and tell me exactly what lands in:

Status
Calendar Event ID
Processing Note

That will tell us almost everything immediately.

Testing Round 2¶

Robyn
This time, the error I got was:

Invalid date/time values.

If you recall, we did times on the form as a list of 'options':

08:00
08:30
09:00
09:30
etc

Interestingly, the worksheet can do maths on the start-time, end time fields, but the calendar can't seem to accept it?

Emily
✂️ ✅🤍 💙 😄💙 😉 Yes — I think the problem is not that Google Calendar “can’t accept” the time. It’s that your script is trying to turn the sheet value into a JavaScript Date with new Date(timePart), and plain HH:mm strings like 08:00 are not a reliably parseable date format for the JavaScript Date constructor. When parsing fails, you get an invalid date/NaN, which matches your Invalid date/time values. branch. Also, CalendarApp.createEvent(...) expects real Date objects for the start and end times. (Google for Developers)

That also explains why Sheets maths can still work. In Apps Script, values read from a range can come back as Number, Date, or String, among other types. If your dropdown time is landing as text, your sheet formulas may still coerce or interpret it usefully, while your script’s new Date("08:00") does not. So the issue is really in your combineDateAndTime_() parsing logic, not in Calendar itself. (Google for Developers)

I’d replace combineDateAndTime_() with a version that explicitly handles:

a real Date
a string like 08:00
and, just to be safe, a numeric spreadsheet time value if one ever appears. (Google for Developers)

Use this:

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;
}

That change should let your script cope with the dropdown options exactly as you described: 08:00, 08:30, 09:00, and so on. And once start and end are proper Date objects, calendar.getEvents(start, end) and calendar.createEvent(...) are the right methods to use. (Google for Developers)

Before you re-test, I’d also add two tiny debug lines inside processApprovalRow_() so you can see what the sheet is actually handing back:

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

That will tell you immediately whether Start time and End time are arriving as strings, dates, or numbers in Apps Script. Apps Script’s logging tools surface that in the execution logs, which is the right place to inspect trigger runs. (Google for Developers)

So the short diagnosis is: Calendar is fine; your parser is too optimistic. 🌼 Replace the combiner with the stricter parser above, and I’d expect your next test to get much further.

Using Debug Mode¶

Robyn
I added the console.log statements to processApprovalRow_() and got:

25 Mar 2026, 12:57:50 Debug bookingDate raw: Thu Mar 26 2026 00:00:00 GMT+1000 (Australian Eastern Standard Time) | type: object
25 Mar 2026, 12:57:50 Debug startTime raw: 08:30 | type: string
25 Mar 2026, 12:57:50 Debug endTime raw: 12:00 | type: string

And, the item was successfully added to the calendar (all in the correct format, time, room, etc). Success!

Question: I had set colour for the Status options. For some reason, those colours keep getting set back to white. Not a show-stopper, but is there any way to prevent this from happening?

Emily
🎧 🚆 🪞 👀 🔥 🌿 🎯 ✍️ ✨🌏💛 📊 🏛📍❗🧭 Yes — I think I know why 🤍

The most likely culprit is ensureColumnsExist(). In that function, you rebuild the Status validation rule and apply it to the whole column with setDataValidation(rule). Apps Script’s DataValidationBuilder officially supports things like the allowed list, whether to show a dropdown, whether invalid values are allowed, and help text — but it does not expose the newer per-option dropdown chip colors/style settings in the Spreadsheet service docs. So when you recreate the rule in script, Sheets can fall back to the default/plain appearance, which is why your colored options seem to revert to white. (Google for Developers)

The good news is: from the code you’ve shown, this should only happen when you run ensureColumnsExist() or otherwise reapply validation. Your normal booking workflow functions mostly do setValue(...), not setDataValidation(...), so they should not normally be repainting the dropdown rule over and over. In other words, ensureColumnsExist() is best treated as a one-time setup helper, not something to keep running after you’ve manually prettied the dropdown. (Google for Developers)

So my practical recommendation is:

run ensureColumnsExist() once to create/check the helper columns and validation
then manually set your dropdown colors in the Sheets UI
after that, don’t run ensureColumnsExist() again unless you actually need to rebuild the validation rule

That should stop the colors being wiped in normal use. (Google for Developers)

If you want something more robust than the dropdown chip colors, the safer option is conditional formatting on column J. Conditional formatting is a separate Sheets feature from data validation, so you can color the whole cell based on the status text — for example:

Pending = yellow
Approved = green
Conflict = red
Rejected = grey

Google’s Sheets help documents custom-formula conditional formatting, which is perfect for this. (Google Help)

For example, apply these rules to J2:J:

=$J2="Pending"
=$J2="Approved"
=$J2="Conflict"
=$J2="Rejected"

Then give each rule its fill color in the sidebar. That won’t give you the cute little colored chip itself, but it will give you stable visual status colors even if the validation rule is recreated later. (Google Help)

So the short answer is: yes, you can mostly prevent it — by not rerunning the validation builder after styling, or by switching to conditional formatting for the visible colors. The latter is the more bulletproof approach 🌷

Changing Rooms Logic¶

Robyn
I have done as you suggested. Working a treat. And so far, all valid booking is happening as advertised. This is beautiful. The conflicts work correctly, etc.
Now, second set of logic.

If a user tries to book 'Studio' but is only doing pencil drawings where gallery would suit (for example) Admin needs to be able to change the room and have that change stick. I.e., if a new booking appears, will the entire group of columns A - H reflect what's on the table, thus changing a room back to the original booked setting? Not sure if my question is clear...

Emily
🤍😄✅ 😄 🌊 🧭 🌺 🪶 Yes — and your question is clear 😊

If Admin!A:H are being populated from the form-response data by a spilled formula/table reference, then those columns should be treated as read-only mirrors. Google’s help says array results “spill over” into cells to the right and below, which means those cells are effectively the output of the formula, not independent hand-editable data. (Google Help)

So in practical terms: no, I would not rely on changing Room directly in column E of Admin and expecting it to stick. If A:H are mirror columns, then the source/form data remains the source of truth, and your mirrored values will continue to reflect that source. Even where Sheets lets you interact around a spill range, the design intent of a spilled array is still “formula-owned output,” not an operational override field. That is the important bit here. (Google Help)

The clean pattern is this:

keep A:H as the original booking data
add separate admin-controlled columns for overrides, such as:
Assigned Room
later, perhaps Assigned Date
Assigned Start time
Assigned End time

Then your script should use:

Assigned Room if it has a value
otherwise the original Room

That gives you two very nice things:

the original request is preserved exactly as submitted
admin changes can “stick” without fighting the form mirror

I would strongly recommend that structure over trying to edit the mirrored form columns directly 🌷

For your exact use case, I’d add just one new column first:

Assigned Room

Then change this line:

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

to something like this:

const requestedRoom = valueByHeader_(rowValues, headers, 'Room');
const assignedRoom = valueByHeader_(rowValues, headers, 'Assigned Room');
const roomRaw = assignedRoom || requestedRoom;

That way:

customer books Studio
admin decides Gallery is more appropriate
admin enters Gallery in Assigned Room
approval logic uses Gallery
original request in Room remains visible for audit/history

That is a much safer model than rewriting the customer’s raw submission.

I’d actually go one step further and rename things conceptually like this:

Room → requested room
Assigned Room → actual approved room

That makes the sheet self-explanatory to an admin.

So the answer to your underlying concern is:

Yes, if A:H mirror the form data, they will keep reflecting that source.
No, those are not the right columns for admin overrides.
Yes, you need a separate override column if you want changes like room reassignment to persist.

That same pattern will help beautifully later for time changes too:

Assigned Date
Assigned Start time
Assigned End time

Then your script always works from the “effective” values: assigned if present, otherwise original.

The next clean step is for me to rewrite your approval logic to support Assigned Room first, without yet touching dates/times.

Columns Described¶

Not ArrayFormula()¶

Robyn
That would be great, Emily. Just as you have done with the const replacements, I can do the same with the logic code. Question: in terms of adding columns, I was thinking of keeping mirror structures intact. Directly after the last mirrored column is the maths column (on duration) that doesn't need to be there: I could replace it with the Assigned Room column, and have that read and used when Approving a booking. So, I'd add an ArrayFormula() to pick up the booked room, but then, allow the Admin to change if needed?

Emily
🌿 🌊 🎯 🌼 🌿💛 Yes — but I would not do it with an ARRAYFORMULA in the same editable cells. ARRAYFORMULA is designed to spill results across multiple rows/columns, and those spilled cells are formula output, not good places for manual overrides. Google’s own help notes that array formulas expand into neighboring cells, and Sheets throws the familiar “array result was not expanded because it would overwrite data” error when something else occupies that spill range. (Google Help)

So the answer to your specific idea is: replace Total time with Assigned Room if you like, but make Assigned Room a normal editable column, not an array-formula column. Then let the script use Assigned Room when it has a value, otherwise fall back to the original Room. That way the mirrored raw booking stays intact, and the admin’s reassignment “sticks.” (Google Help)

The clean pattern is:

Room = what the user asked for, mirrored from the form
Assigned Room = what admin wants it to actually be
approval logic uses Assigned Room if present, else Room

So your room logic becomes:

const requestedRoom = valueByHeader_(rowValues, headers, 'Room');
const assignedRoom = valueByHeader_(rowValues, headers, 'Assigned Room');
const roomRaw = assignedRoom || requestedRoom;

That is the safest model because it preserves the audit trail: you can still see what the customer requested, while the booking actually goes into the room the admin approved. 🌷

If you want the admin to have a dropdown in Assigned Room, that’s fine too. Apps Script’s data-validation builder supports requiring values from a list, so Studio / Gallery is easy to enforce there. (Google for Developers)

There are really two good options here:

Option A — simplest and safest

Keep Assigned Room blank by default.
Admin only fills it in when overriding.
Script uses assigned || requested.

That is my preferred approach, because blank means “no override.”

Option B — prefill for convenience

Add Assigned Room as a normal editable column.
On form submit, have the script copy the original Room into Assigned Room as a plain value.
Admin can then change it later.

That also works, but it’s slightly more moving parts than you need.

What I would not do is this:

put an ARRAYFORMULA in Assigned Room to mirror Room
then expect admin to type over individual cells

That will fight the spill logic, and sooner or later Sheets will complain or overwrite. (Google Help)

So, if you want my blunt recommendation 💛

Yes, repurpose Total time if you no longer need it there.
Make that new column Assigned Room.
Do not put an ARRAYFORMULA in it.
Let the script fall back to Room when Assigned Room is blank.

That gives you the cleanest “requested vs approved” separation.

The exact code change is tiny:

const requestedRoom = valueByHeader_(rowValues, headers, 'Room');
const assignedRoom = valueByHeader_(rowValues, headers, 'Assigned Room');
const roomRaw = assignedRoom || requestedRoom;

And if you want, the next step is for me to show you the matching changes for:

ensureColumnsExist()
dropdown validation for Assigned Room
and the same future-ready pattern for Assigned Start time / Assigned End time.

Yes, please.¶

Yes — this is the right next step 🌷

My recommendation is:

make Assigned Room a plain editable column
do not fill it with ARRAYFORMULA
let the script use Assigned Room when present, otherwise fall back to the original Room

That’s the safest pattern because array results spill into neighboring cells, which makes them a poor fit for cells you want staff to edit manually. Google’s array docs describe spill behavior, and Apps Script supports setting validation rules and ordinary cell values cleanly on a normal editable range. (Google Help)

What I’d change now¶

If you no longer need Total time in column I, then yes — the simplest move is:

change header Total time to Assigned Room
remove the duration formula from column I
make I a normal editable dropdown column with:
blank
Studio
Gallery

Blank will mean “no override.” Then the script falls back to the original Room.

Small code pattern to adopt¶

Add this helper:

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

Then change your room lookup from:

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

to:

const roomRaw = effectiveValue_(rowValues, headers, 'Assigned Room', 'Room');

That gives you the override behavior with almost no fuss.

Updated `processApprovalRow_()` lines¶

Use this block:

const tutorName = valueByHeader_(rowValues, headers, 'Full Name');
const tutorEmail = valueByHeader_(rowValues, headers, 'Email address');
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');

`ensureColumnsExist()` with `Assigned Room`¶

If you are repurposing Total time, you may not need this to create the column, but it’s still useful for validation. Apps Script’s DataValidationBuilder.requireValueInList(...) is the right tool for dropdowns. (Google for Developers)

Here is a version that:

ensures helper columns exist
applies dropdown validation to Status
applies dropdown validation to Assigned Room if that header exists

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 assignedRoomCol = refreshed['Assigned Room'];

  const statusRule = 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(statusRule);

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

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

Range.setDataValidation(rule) is the Apps Script method that applies the rule to the range. (Google for Developers)

Future-ready pattern for assigned times¶

When you’re ready, use the same override approach for times:

Assigned Start time
Assigned End time

Then your approval logic becomes:

const bookingDate = effectiveValue_(rowValues, headers, 'Assigned Event Date', 'Event Date');
const startTime = effectiveValue_(rowValues, headers, 'Assigned Start time', 'Start time');
const endTime = effectiveValue_(rowValues, headers, 'Assigned End time', 'End time');

I would keep those as plain editable columns too, not array formulas.

Since your time slots are fixed half-hour increments, you can give those override columns a dropdown as well. Apps Script can build dropdowns from a supplied list with requireValueInList(...). (Google for Developers)

Use a helper like this:

function buildHalfHourSlots_() {
  const slots = [];
  for (let hour = 8; hour <= 19; hour++) {
    slots.push(`${String(hour).padStart(2, '0')}:00`);
    if (hour < 19) {
      slots.push(`${String(hour).padStart(2, '0')}:30`);
    }
  }
  return slots;
}

And in ensureColumnsExist() later:

const assignedStartCol = refreshed['Assigned Start time'];
const assignedEndCol = refreshed['Assigned End time'];

const timeRule = SpreadsheetApp.newDataValidation()
  .requireValueInList(buildHalfHourSlots_(), true)
  .setAllowInvalid(false)
  .build();

if (assignedStartCol) {
  sheet.getRange(2, assignedStartCol, Math.max(sheet.getMaxRows() - 1, 1), 1)
    .setDataValidation(timeRule);
}

if (assignedEndCol) {
  sheet.getRange(2, assignedEndCol, Math.max(sheet.getMaxRows() - 1, 1), 1)
    .setDataValidation(timeRule);
}

My blunt recommendation¶

Right now, I’d do only this:

Replace Total time with Assigned Room
Add the effectiveValue_() helper
Change the room lookup to use Assigned Room
Add dropdown validation for Assigned Room

That gets you the room-reassignment feature without disturbing your working approval flow 💛

When you’re ready, I can give you the exact “version 2” script with Assigned Room fully wired in and nothing else changed.

Development Freeze¶

Robyn
I am going to temporarily "freeze" development here, and just do bug-fixes for now. We'll look at other changes later. For now, I've coloured all columns A through H beige = "don't touch, please"... these are the mirror fields, and are there for reading only. I've added functionality for changing Room, and will consider start and end time changes as well, but for now, we'll make this as solid as we can.

Apparently, the 'Admin' person is quite excited about this tool we're developing: they can't wait to trial it. So, it wants to be pretty solid before I do.

The other, truly crucial thing we have to do is provide comprehensive documentation, to include: User Manual - how to use it Code Documentation - what does what (for future developers) ... and anything else you might see as essential.

Emily
😄 👟 👠 👟 😂 🎭 ✨ 🎭 🌿 🌺 That is exactly the right moment to freeze development a bit 🌷

What you’ve described is the sensible transition from building to hardening: make the current workflow reliable, lock down the “don’t touch” areas, fix only real bugs, and document the living daylights out of it before anyone depends on it.

And yes — for a tool like this, documentation is not an optional extra. It is part of the product.

Here is the documentation set I would regard as essential.

1. User Manual¶

This is the plain-English guide for the person actually using the tool day to day.

It should cover:

what the tool is for
what each sheet is for
what the coloured columns mean
what a normal booking workflow looks like
what each Status means
what happens when a booking is Approved
what a Conflict means
what to do when something looks wrong
what not to edit manually

For your tool, this manual should be written for a non-technical admin person, with zero code talk.

I would make this the most polished document of the lot.

2. Admin Operations Guide¶

This is slightly different from the User Manual. The User Manual explains the interface; the Operations Guide explains the working practice.

It should include:

daily workflow
how to process new bookings
how to check the calendar before approving
how to use Assigned Room
when to leave a booking Pending
when to mark Rejected
how to interpret Processing Note
how to handle a customer correction
what to do if the script fails
manual fallback procedure if automation is temporarily broken

This document is the one that prevents panic when the real-world messiness arrives.

3. Technical / Developer Documentation¶

This is for future you, or anyone else who inherits the spreadsheet and script.

It should explain:

overall system architecture
how Google Form, response sheet, Admin sheet, Apps Script, and Calendar interact
trigger logic
what each function does
what each helper function does
which headers are critical and case-sensitive
which columns are mirrors and must not be edited
which columns are operational/admin-controlled
which fields are used when creating a calendar event
how conflicts are detected
how Assigned Room overrides requested Room
known assumptions and limitations

This should be written as if the next developer has never seen the project before.

4. Setup and Deployment Guide¶

This is crucial, because a lot of spreadsheet tools fail not because the code is bad, but because setup is incomplete.

This document should include:

required Google account permissions
how to link the form to the spreadsheet
exact sheet/tab names expected
required column headers
how to install the script
how to authorize the script
how to create the installable triggers
how to test the default calendar mode
how to later switch to a specific calendar ID
how to verify the script time zone

This is the “from zero to working system” document.

5. Data Dictionary / Sheet Schema¶

This sounds dry, but it is gold later.

For each column, record:

column name
purpose
source
editable or read-only
example value
whether script depends on it
whether case-sensitive header matching matters

For your Admin sheet this is especially important, because you now have two classes of columns:

mirrored submission data
admin/automation fields

This document prevents accidental renaming and “tidying up” that silently breaks the script.

6. Business Rules Specification¶

This is not code documentation. It is the rules of the system in plain logic.

For example:

all new bookings enter as Pending
a calendar booking is created only when Status becomes Approved
conflict exists only when time overlaps and room matches
Assigned Room, when present, overrides requested Room
mirrored columns A:H are read-only
script uses exact header names
approved bookings currently do not auto-update if core details are later changed
admin must manually inspect calendar before approval even though script also performs a final check

This document is very useful when someone later says, “Should it do X?” because you can compare that idea against the defined rules.

7. Testing / UAT Checklist¶

Absolutely essential before the trial.

This should not be prose. It should be a checklist of scenarios.

At minimum:

new form submission appears in Admin
Status auto-sets to Pending
Assigned Room blank uses requested Room
Assigned Room override works
approving a valid booking creates a calendar event
same-room overlapping booking becomes Conflict
different-room overlapping booking is allowed
missing date/time fields are handled
invalid room values are handled
duplicate approval does not create duplicate events
Processing Note is written correctly
Calendar Event ID is written correctly

This is the document you use before handing it to the excited admin.

8. Troubleshooting Guide¶

This is the “what do I do when something odd happens?” document.

It should include:

booking stayed Pending after form submit
approving didn’t create a calendar event
Status turned to Conflict unexpectedly
dropdown colours changed
script says sheet/header not found
calendar event created in wrong place
event ID missing
Processing Note contains an error
trigger disappeared or stopped running

Each issue should have:

likely cause
where to look
what to try
whether it is safe for admin to fix or should go to developer

9. Known Limitations / Current Boundaries¶

This is one of the most important documents, because it protects trust.

For example:

mirrored booking fields must not be edited directly
approved bookings are not yet auto-rescheduled if time/date changes later
script depends on exact header names
current testing uses default calendar
future calendar-ID support is planned but not yet production-configured
room reassignment is supported; time reassignment may come later

This avoids the dangerous phrase, “I assumed it would do that.”

10. Change Log / Version History¶

Simple but important.

Track:

date
version number
what changed
why
whether change was bug fix or feature
whether retesting was done

Even a plain table is enough.

11. Backup / Recovery Notes¶

For anything someone might actually rely on, I’d include this too.

It should say:

where the source code lives
how often to copy/export the script
whether the spreadsheet should be duplicated before major changes
what to do before modifying formulas or headers
how to disable triggers safely if needed
how to fall back to manual booking if the automation is down

That is the “don’t lose the farm” document.

What is essential before trial¶

If you want the minimum solid pack before the admin trials it, I would prioritise these five:

User Manual
Admin Operations Guide
Testing / UAT Checklist
Troubleshooting Guide
Technical / Developer Documentation

Everything else is valuable, but those five are the core survival kit.

What I would add that you did not explicitly mention¶

Two extra things.

A one-page Quick Reference¶

Something the admin can keep open beside the sheet.

For example:

New booking arrived → check calendar → adjust Assigned Room if needed → set Status to Approved Conflict → read Processing Note → resolve manually Never edit beige columns If something odd happens → stop and check Processing Note / contact support

That one-pager is often more useful in real life than a full manual.

A Manual Fallback Procedure¶

This matters because no automation is perfect.

For example:

if approval script fails, create the calendar event manually
note manual intervention in Processing Note
keep Status as Pending until calendar entry is confirmed
then set or annotate accordingly

That gives the admin confidence that the whole system does not collapse if one trigger misbehaves.

I would organise the docs like this:

01_User_Manual.md 02_Admin_Operations_Guide.md 03_Quick_Reference.md 04_Test_Checklist.md 05_Troubleshooting.md 06_Technical_Overview.md 07_Setup_and_Deployment.md 08_Data_Dictionary.md 09_Business_Rules.md 10_Change_Log.md 11_Known_Limitations.md 12_Backup_and_Recovery.md

That is tidy, future-proof, and easy to maintain.

My recommendation for tone¶

For the admin-facing docs:

calm
practical
no jargon
step-by-step
screenshots later, if useful

For the developer docs:

precise
explicit
assumptions stated clearly
function names referenced exactly

My honest opinion¶

You are now at the stage where documentation will save more time than additional coding. The admin being excited is lovely — but excitement is exactly when people start clicking things confidently. Good documentation turns that from danger into success 😄

The best next move is to draft the documents while the logic is still fresh and relatively small.

I’d start with these three first: User Manual, Admin Operations Guide, and Technical Overview.

Creating Drafts¶

Robyn
Are you happy to generate these as drafts, something I can tweak later if needed?

Another question: does hiding a sheet affect sheet numbering or any behaviours?

Emily
🤍😄 ⚠️ ✅ 🤍
Yes — happily 😊 I can draft the full pack in a form you can tweak later: User Manual, Admin Operations Guide, Technical / Developer Documentation, plus the practical extras that will matter most during trialing: a Quick Reference, Troubleshooting Guide, Test Checklist, and Known Limitations note. 🌷

On the hidden-sheet question: hiding a sheet is usually safe for this project. In Google Sheets, hiding a sheet just hides it from view; it is not the same as protecting it, and editors can still unhide it. Hidden sheets also stay hidden on import/export and when copied, though editors can still reveal them. (Google Help)

For your scripts and formulas, the practical answer is: hiding a sheet does not by itself change the sheet’s name, and it does not move the tab order. Reordering is a separate action in Sheets, and Apps Script treats hiding/showing as separate sheet operations from moving sheets. So code that uses getSheetByName('Admin') or formulas that explicitly reference a sheet name should keep working when that sheet is hidden. (Google Help)

The one thing I would avoid is relying on sheet position rather than sheet name. Anything written like “the second tab” or getSheets()[1] is always a bit more fragile, because tab order can change when someone manually reorders sheets, even though hiding alone is not what changes that order. Name-based references are the sturdier choice for exactly the kind of workflow you’re building. (Google Help)

So my advice would be:

hide the raw/form or helper sheets if that makes the admin experience cleaner
keep using exact sheet names in code
don’t treat hiding as security
don’t rely on numeric tab position in scripts unless absolutely necessary. (Google Help)

I’d start the draft pack with these three first: User Manual, Admin Operations Guide, and Technical Overview.

Hiding Sheets with Tables¶

Robyn
Yes, I'm just 'hiding' that sheet so the 'Admin' only sees her sheet. I'm not relying on that to be a complete security solution. And, given user unreliability, I anticipate panicked or, less likely accusatory emails in my inbox about glitches. Right then. Looking forward to the documentation! And thank you, you've been a champion, Emily!!

Emily
✨ 💙 🌿 I’m putting the whole pack into a tidy draft document so you can tweak it as the system settles 🌷 I’ll include the admin-facing guides, the technical notes, the troubleshooting/UAT material, and the known limits so you’ve got a proper trial-ready bundle.

BookingApp Docs-v0.1 ¶

Documentation Completed¶

Done 💛 I’ve put together a full draft pack you can now tweak and grow as the system settles.

It includes:

User Manual Admin Operations Guide Quick Reference Troubleshooting Guide Test / UAT Checklist Technical / Developer Documentation Setup / Deployment Notes Data Dictionary Known Limitations Change Log Backup / Recovery Notes

Your instinct about panicked emails is probably dead right 😄 so I’ve leaned toward calm, practical documentation that tells the admin what to do, what not to touch, and where to look first when something goes sideways.

One useful next pass will be to tailor the tone of the admin-facing docs to the actual person who’ll use it — some people want brisk bulletproof steps, others want gentler explanation. This draft is a strong middle ground.

And thank you, Robyn — that’s very kind 🌷 You’ve done the hard part here: you’ve kept the workflow sensible instead of letting it turn into spaghetti.

Events¶

Robyn
I've just shown the product to my partner: she's currently acting President of the Art Society. She very impressed! In the course of demonstration I've discovered a functionality need (additional logic) but yeah, she's very excited how this is already working. The additional logic is for: Assigned Room... if there is a room change on an Approved booking, the "Approved" status needs to change to 'Pending'. But then, what else needs to happen? Delete the entry in Calendar? Or, does the user have to manage the booking in Calendar on her own, and then, update via the sheet? In any event, the first thing the script SHOULD do is change 'Approved" to 'Pending'. What to do next is going to depend on what you think is the cleanest course of action.

Emily
😄✨ 💙 🌊 🌿 🌺 🌙 💙 That is a very good catch, and I think the cleanest course is this 🌷

My recommendation¶

If a material booking detail is changed on an Approved row — and Assigned Room definitely counts — then the system should:

Delete the existing calendar event
Clear Calendar Event ID
Set Status back to Pending
Write a clear Processing Note
Then wait for the admin to review and approve again

Why this is the cleanest approach¶

Because anything else creates drift.

If you only change Approved to Pending but leave the old calendar event in place, then:

the calendar no longer matches the sheet
the old room remains blocked
the admin may think the booking is not live when in fact it still is
you can accidentally create duplicates later

If you let the admin manage the calendar manually, you create two sources of truth:

the sheet
the calendar

That is exactly the sort of thing that causes “but I changed it over here” chaos later.

And while auto-updating the event in place is possible, I would not do that yet during your freeze. It skips the deliberate re-approval step, which is one of your main safeguards.

So for now:

approved booking changed materially = old approval is invalidated

That is tidy, logical, and safe.

The business rule in plain English¶

Once a booking is Approved, changing Assigned Room means the approval no longer stands. The existing calendar booking is removed, and the row returns to Pending so it can be reviewed again.

That feels like the right operational model to me.

What should count as a “material change”?¶

For now:

Assigned Room

Later, very likely:

Assigned Start time
Assigned End time
Assigned Event Date

Possibly:

Course Name, if it affects suitability
maybe not Notes

But for now, keep it simple: just Assigned Room.

The safest sequence¶

When Assigned Room is edited on a row whose Status is Approved:

look up the existing Calendar Event ID
try to delete that event from the calendar
clear the stored event ID
set Status to Pending
write a note like:

Assigned Room changed after approval. Previous calendar event deleted. Booking returned to Pending for re-review.

If the event cannot be found:

still set it to Pending
keep the note explicit:

Assigned Room changed after approval. Previous calendar event could not be found; check calendar manually. Booking returned to Pending.

That way the sheet always tells the truth.

Why I would not leave the event in Calendar¶

Because then the booking is still “live” in the calendar while the sheet says Pending.

That is worse than a glitch. That is silent divergence.

I would much rather have:

one missing booking that needs re-approval

than:

one hidden stale booking occupying the wrong room

The shape of the code¶

I would broaden your current edit handler so it can respond to two kinds of edits:

Status changed to Approved → create booking
Assigned Room changed on an already Approved booking → invalidate approval and remove old event

Something like this:

function onAdminEdit(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'];
  const assignedRoomCol = headers['Assigned Room'];

  if (col === statusCol) {
    const newStatus = String(e.range.getValue()).trim();
    if (newStatus === CONFIG.STATUS_VALUES.APPROVED) {
      processApprovalRow_(sheet, row, headers);
    }
    return;
  }

  if (assignedRoomCol && col === assignedRoomCol) {
    handleApprovedBookingChange_(sheet, row, headers, 'Assigned Room');
    return;
  }
}

And then:

function handleApprovedBookingChange_(sheet, row, headers, fieldName) {
  const statusCol = headers['Status'];
  const noteCol = headers['Processing Note'];
  const eventIdCol = headers['Calendar Event ID'];

  const currentStatus = String(sheet.getRange(row, statusCol).getValue()).trim();
  if (currentStatus !== CONFIG.STATUS_VALUES.APPROVED) return;

  const existingEventId = String(sheet.getRange(row, eventIdCol).getValue() || '').trim();
  let note = `${fieldName} changed after approval. Booking returned to Pending for re-review.`;

  if (existingEventId) {
    const calendar = getTargetCalendar_();
    const event = calendar.getEventById(existingEventId);

    if (event) {
      event.deleteEvent();
      sheet.getRange(row, eventIdCol).clearContent();
      note = `${fieldName} changed after approval. Previous calendar event deleted. Booking returned to Pending for re-review.`;
    } else {
      note = `${fieldName} changed after approval. Previous calendar event was not found; check calendar manually. Booking returned to Pending.`;
    }
  }

  sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.PENDING);
  sheet.getRange(row, noteCol).setValue(note);
}

Then your trigger should point to onAdminEdit rather than onApprovalEdit.

One subtle but important benefit¶

This makes the workflow consistent:

Pending means “not currently live in calendar”
Approved means “live in calendar”

That is a very clean mental model for the admin.

The moment you allow Pending rows to still have active calendar entries, things get fuzzy very fast.

My recommendation in one line¶

Yes, change Approved to Pending — and delete the existing calendar entry automatically at the same time.

That is the cleanest and safest course while the system is still being hardened 💛

If you want, I’ll fold this neatly into your existing script so it matches your exact current function names and headers.

Context Summary¶

Robyn
Yes, I get why having two sources of truth being a recipe for disaster. So, at this stage the Status fields do 2 jobs:
1. they show the state of the booking
2. they can change the state of the booking

And now, I've just got the old feature-creep request: can the sheet (meaning: status column) handle a cancellation of a class?

Before we go on, could I get you to write a context summary, please? Things are getting a bit laggy - might need to start a new thread.

Emily
Absolutely 🌷 Here’s a clean context summary you can paste into a new thread.

Context Summary: Google Sheets + Calendar Booking Tool¶

We are building a booking workflow for an Art Society using:

a Google Form for booking requests
a linked Google Sheets response tab called Form responses 3
an operational sheet called Admin
Apps Script automation
a Google Calendar, currently using the account’s default calendar for testing

Overall workflow¶

A user submits a booking through the Google Form.
The submission lands in Form responses 3.
The Admin sheet mirrors the booking details.
A script sets the new booking’s Status to Pending.
The admin checks the calendar manually.
The admin changes Status to Approved.
The script performs a final conflict check and, if valid, creates a calendar event.
The script writes back:
Calendar Event ID
Processing Note

Current Admin sheet structure¶

Mirror / read-only area¶

Columns A:H are beige and are intended to be read-only.

These mirrored columns are:

Timestamp
Email address
Course Name
Full Name
Room
Event Date
Start time
End time

These come from the form submission and should not be edited manually.

Operational columns¶

Later columns are for admin/system use, including:

Assigned Room (new override field; replaces earlier Total time concept in practice)
Status
Notes
Calendar ID (currently not actively used)
Calendar Event ID
Processing Note

Status values and meaning¶

Current status values are:

Pending
Approved
Rejected
Conflict

At present, Status serves two roles:

it shows the current booking state
it can change the booking state when the admin edits it

Current meaning¶

Pending = received, not yet approved
Approved = approved and intended to exist in calendar
Rejected = not accepted
Conflict = system prevented approval because something is wrong

Current working features¶

1. Auto-set Pending on form submit¶

A spreadsheet installable trigger runs setPendingOnFormSubmit(e).

It:

watches submissions landing on Form responses 3
writes Pending into the matching row in Admin

This is working.

2. Approval creates calendar booking¶

An installable edit trigger watches the Admin sheet.

When Status is changed to Approved:

the row is validated
the calendar is checked for overlap
if valid, an event is created in Google Calendar
Calendar Event ID is stored
Processing Note is updated

This is working.

3. Conflict logic¶

A conflict exists only if:

times overlap, and
the room matches

So:

same time + different room = allowed
same time + same room = conflict

This is working correctly.

4. Assigned Room override¶

There is now logic/planning in place for Assigned Room.

Purpose:

preserve the original requested room in the mirrored data
allow admin to override the actual room used for approval

Rule:

if Assigned Room has a value, use that
otherwise use original Room

Important design decision:

mirrored columns are not to be edited
overrides belong in operational/admin columns, not in the mirrored data

Key bug fixes and lessons already discovered¶

Header names are exact-match sensitive¶

Apps Script header lookup depends on exact row-1 text.

Examples of mismatches that caused problems:

Start Time vs Start time
Tutor Name vs Full Name
Booking Date vs Event Date

Actual Admin headers¶

The actual header row in Admin is:

Timestamp
Email address
Course Name
Full Name
Room
Event Date
Start time
End time
Total time / later repurposed operationally
Status
Notes
Calendar ID
Calendar Event ID
Processing Note

Time parsing issue was solved¶

Form times are dropdown strings like:

08:00
08:30
09:00

The original date/time combiner failed because new Date("08:30") is unreliable.

Debugging showed:

bookingDate came through as a real Date object
startTime and endTime came through as strings

This was fixed by using a safer parser for time strings.

After that, calendar creation worked correctly.

Validation / formatting notes¶

Conditional formatting¶

Dropdown color styling was being lost because validation rules were being rebuilt in script.

Current solution:

use conditional formatting on the Status column instead of relying on dropdown chip colors

This is working well.

Hidden sheets¶

We discussed hiding sheets:

hiding a sheet is fine for decluttering
it does not act as real security
it does not break name-based references
code should rely on sheet names, not sheet positions

The raw response sheet is being hidden so the admin only sees the Admin sheet.

Current scripts / architecture (conceptually)¶

Core functions¶

setPendingOnFormSubmit(e) Sets Status to Pending for new form submissions.
onApprovalEdit(e) or equivalent edit handler Detects when Status is changed to Approved.
processApprovalRow_(sheet, row, headers) Validates the row, checks conflicts, creates calendar event, writes event ID and note.
ensureColumnsExist() Helper/setup function to make sure needed columns exist and validation is applied.
createInstallableTriggers() Creates installable triggers for:
form submit
edit
combineDateAndTime_(...) Updated to safely combine a date plus time strings like 08:30.
normalizeRoom_(...) Normalizes room names to supported values (Studio, Gallery).
getHeaders_(sheet) and valueByHeader_(...) Helper functions for exact header-based lookup.
effectiveValue_(rowValues, headers, overrideHeader, originalHeader) Proposed helper for override logic, e.g. Assigned Room falling back to Room.

Current business rules¶

Booking creation¶

A calendar booking is created only when:

Status becomes Approved
room is valid
date/time values are valid
end time is after start time
there is no same-room overlap

Mirror columns¶

Columns A:H in Admin are read-only mirrors and should not be edited.

Assigned Room¶

If present, Assigned Room overrides original Room.

Single source of truth principle¶

We explicitly identified that we do not want the sheet and calendar to drift apart.

That led to the next important design discussion.

Newly identified feature need: changing an approved booking¶

Problem¶

If an admin changes Assigned Room on a booking that is already Approved, the old calendar event becomes wrong.

Clean design decision reached¶

If a material booking detail changes after approval, the old approval should be invalidated.

For Assigned Room, the recommended logic is:

detect that Assigned Room changed on an Approved row
delete the existing calendar event using Calendar Event ID
clear Calendar Event ID
set Status back to Pending
write a clear Processing Note

Reason:

avoids having two sources of truth
avoids stale/incorrect calendar events
preserves the workflow that only Approved rows are live in calendar

This logic has been discussed and agreed as the cleanest current direction, but may not yet be fully coded.

New feature request just raised¶

A new “feature creep” question has now appeared:

Can the Status column also handle cancellation of a class?¶

This has not yet been fully designed in code.

But it is the next logical design question.

Likely issue to resolve in the next thread:

if a booking is cancelled via the sheet, what should happen?
delete calendar event?
mark status as cancelled?
preserve history?
keep status values as both state + action?

This sits alongside the earlier observation that Status is currently doing two jobs:

representing state
triggering workflow transitions

Documentation work completed¶

A documentation draft pack has already been created in canvas. It includes:

User Manual
Admin Operations Guide
Quick Reference
Troubleshooting Guide
Test / UAT Checklist
Technical / Developer Documentation
Setup / Deployment Notes
Data Dictionary
Known Limitations
Change Log
Backup / Recovery Notes

The draft is intended as something Robyn can tweak later before rollout.

Current development posture¶

Development is temporarily frozen except for:

bug fixes
stabilisation
hardening existing logic

Priority now is:

make current workflow solid
document it properly
only then consider further feature additions

Booking App 2¶

Dropdown Behaviour¶

Recommended approach¶

How to install it¶

If your Admin sheet is the one you care about¶

If you also want Excel-like “change it back to Pending when edited”¶

Sheet vs Table¶

Two useful variants¶

If a booking could cross midnight¶

For your Status column J¶

ArrayFormula()¶

How to install it¶

Why this should work for your setup¶

One small thing to check¶

If you want it a little more defensive¶

Forms WriteTo¶

Direct Edits¶

What you must edit¶

How to install it¶

Important assumption¶

One small improvement¶

Spelling 101¶

Populating Calendars¶

Header Names¶

Example¶

Best practice¶

Even better¶

Header Row¶

The good news¶

Your corrected processApprovalRow_() header section¶

Which Calendar¶

What gets copied into the calendar event¶

One important nuance¶

What happens in the sheet after approval¶

So the admin experience, in plain English¶

Change Back To...¶

Ready to test?¶

Testing-Round 1¶

Missing date/time fields¶

Testing Round 2¶

Using Debug Mode¶

Changing Rooms Logic¶

Columns Described¶

Not ArrayFormula()¶

Yes, please.¶

What I’d change now¶

Small code pattern to adopt¶

Updated processApprovalRow_() lines¶

ensureColumnsExist() with Assigned Room¶

Future-ready pattern for assigned times¶

My blunt recommendation¶

Development Freeze¶

1. User Manual¶

2. Admin Operations Guide¶

3. Technical / Developer Documentation¶

4. Setup and Deployment Guide¶

5. Data Dictionary / Sheet Schema¶

6. Business Rules Specification¶

7. Testing / UAT Checklist¶

8. Troubleshooting Guide¶

9. Known Limitations / Current Boundaries¶

10. Change Log / Version History¶

11. Backup / Recovery Notes¶

What is essential before trial¶

What I would add that you did not explicitly mention¶

A one-page Quick Reference¶

A Manual Fallback Procedure¶

The structure I would recommend¶

My recommendation for tone¶

My honest opinion¶

Creating Drafts¶

Hiding Sheets with Tables¶

BookingApp Docs-v0.1¶

Documentation Completed¶

Events¶

My recommendation¶

Why this is the cleanest approach¶

The business rule in plain English¶

What should count as a “material change”?¶

The safest sequence¶

For your `Status` column `J`¶

Your corrected `processApprovalRow_()` header section¶

Updated `processApprovalRow_()` lines¶

`ensureColumnsExist()` with `Assigned Room`¶

BookingApp Docs-v0.1 ¶