Booking App v.1a

Introduction

Robyn
Good morning, Emily. May I refer you to the threads called "Helper Layer Focus" and "Google Sheets Development" for context. For this project, what remains is implementing the "Changing Room" workflow and writing to the Calendar... I believe that was it, wasn't it?

---

Emily
✈️🌫️ 🌿 Good morning, Robyn ☀️ You’re right on the cusp of “this is actually a finished system” now — and yes, your memory of it is very accurate. Let me tidy it into a clean, grounded picture so you can step back in without friction.

---

🧭 Where You Are Right Now

You’ve already built the hard parts:

• ✅ Form → Sheet pipeline working
• ✅ Admin mirror sheet behaving
• ✅ Status-driven workflow (Pending / Approved / Conflict / Rejected)
• ✅ Conflict detection logic solid
• ✅ Calendar creation logic mostly in place
• ✅ Logging + System_Log functioning

That’s a proper system, not a toy. 💪

---

🧩 What Remains

(Your Final Two Pillars)

---

1. 🚪 “Changing Room” Workflow (Assigned Room Logic)

This is your last piece of internal workflow intelligence. What it needs to do:

Trigger condition:

• Booking already Approved
• Admin changes Assigned Room

Expected behaviour:

1. Status → revert to Pending
2. Existing calendar event → removed (or invalidated)
3. Processing Note → updated to reflect change clearly
4. System waits for re-approval → creates new calendar event

👉 In short, “Any material change resets trust in the booking.” That aligns beautifully with your single source of truth philosophy.

---

1. 📅 Calendar Writing (Finalising the Loop)

You’ve already touched this, but this is about making it production-safe and complete. What “done” looks like:

• ✅ On Approved → create calendar event

• ✅ Store:

• Calendar ID

• Event ID

• ✅ On:

• Cancellation

• Room change
• Time change

👉 → delete the old event

• ✅ Re-approval → create a fresh one

---

⚖️ One Key Design Decision

(Important)

You raised this earlier, and it’s the only thing that really matters architecturally: should users edit Calendar manually? Answer (firm, and you already leaned this way): ❌ No — never. Because:

• Two sources of truth = drift = chaos
• Your Sheet is already acting as a UI, a State machine and an Audit trail

👉 The Calendar becomes a projection, not a control surface. That’s excellent system design, by the way.

---

🧠 The Hidden Third Piece

You Almost Have It. There is one subtle thing still sitting underneath everything:

🔄 “Reconciliation Logic”

Right now you're reactive (onEdit, onSubmit). Later (not urgent), you may want a function that scans:

• Approved rows
• Missing calendar IDs
• Rebuilds calendar safely

👉 This is your safety net for script failures, manual tampering and future-proofing. But, not needed for v1. You’re fine without it.

---

🧱 Actual Remaining Work

If I strip everything down to essentials, cCore tasks left:

1. Changing Room logic

2. Detect Assigned Room change

3. Reset status

4. Remove event

5. Calendar lifecycle completion

6. Ensure delete + recreate is clean and consistent

---

🌿 Practical Next Step

When you come back to it fresh, start with Changing Room logic first. Why? Because it forces you to, touch status logic, touch calendar deletion and validate your assumptions. Once that works, Calendar writing becomes trivial and predictable. If you like, next step we can do together is sketch the exact onEdit detection logic for Assigned Room changes and wire it cleanly into your existing functions without breaking anything. Just say the word, poppet. 🌷

---

Assigned Room

Robyn
The 'Assigned Room' data is going to be in column 'O', next to 'Processing Note' in 'N'. Trying to remember the logic that went with that.

---

Emily
Yes — and the logic around it was actually quite neat once reduced to first principles 🌷. The role of Assigned Room in column O was this: column O is not just “extra information.” It becomes the effective room for the booking when populated.

---

The logic

• if Assigned Room is blank → use the original Room from the form
• if Assigned Room has a value → use Assigned Room instead

That means conflict checking and calendar writing should always work from effectiveRoom = Assigned Room if present, otherwise Room.

---

You needed it to let the bookings manager do this kind of thing:

• Tutor requests Studio
• Studio is unavailable
• Manager tries Gallery in Assigned Room
• System re-checks conflict against that room
• If no conflict, booking can then be approved

So it gives the manager a controlled override without editing the original request data.

---

The key workflow logic

1. When status is still Pending or Conflict

If the manager changes Assigned Room:

• the system should use the new effective room for future checks
• no calendar deletion needed yet, because nothing approved/live may exist yet
• Processing Note should update when approval is attempted, especially if the new room still conflicts

So in this state, Assigned Room is mostly just “use this room instead when checking approval”.

1. When the booking is already Approved

This was the important bit. If Assigned Room changes on an already approved booking:

• Status should be forced back to Pending
• existing calendar event should be deleted
• Calendar Event ID should be cleared
• Processing Note should say something clear like:
• “Assigned room changed from Studio to Gallery. Booking reset to Pending for re-approval.”

Why? Because room is not cosmetic. It changes the real-world booking. Once the approved row is materially altered, the approval is no longer valid. That was the heart of the design.

---

Room Rule

Use:

• Assigned Room if present
• otherwise Room

Reset Rule

If row was Approved, and Assigned Room changes:

• remove live calendar event
• set status to Pending
• write note
• require explicit re-approval

---

Structure in code

You were probably heading toward helper logic like this:

  const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
  const assignedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);

  const effectiveRoom = assignedRoom && String(assignedRoom).trim()
    ? String(assignedRoom).trim()
    : requestedRoom;

Then all of these should use effectiveRoom:

• conflict detection
• calendar title/description if room included
• processing notes about approval/conflict

---

Edit trigger logic

In plain English, when column O changes, if status is Approved:

• delete live calendar event
• set status to Pending
• clear calendar event ID
• write Processing Note

Otherwise (any other value than Approved):

• do nothing immediately except let future approval use the new effective room

That was the main workflow.

---

Header/constants

You’d want a constant for:

  ASSIGNED_ROOM: 'Assigned Room'

and then column O becomes just where that header happens to sit. Better to rely on header lookup than hard-coded column number.

---

One subtle but important detail: when checking whether Assigned Room “changed,” compare the old value of column O and the new value of column O, not just whether O is non-blank. Because you only want to reset an approved booking if the room truly changed. Examples:

• blank → Gallery ✅ reset
• Studio → Gallery ✅ reset
• Gallery → blank ✅ reset, because effective room changed back to requested room
• Gallery → Gallery ❌ no reset

---

Clean mental model

Think of it this way:

• Room = what tutor asked for
• Assigned Room = what admin decided
• Effective Room = what the system actually books

That’s the tidy architecture you were circling around. 💛 If you’d like, next I can help you reconstruct the exact onEdit(e) logic for column O so it plugs straight into what you already have.

---

Column O Logic

Robyn
Yesterday, I skimmed what you wrote. Today, I'm studying. Sorry if I seem a bit slow. I do understand the 'why' of your approach, now. And I can also see why, in the past when I was trying to work out logic flow, I ran aground so often (I was working in Excel VBA - wrote terrible code).

Let's go ahead and do the onEdit(e) logic now for column 'O'.

---

Emily
💛 🌿 Not slow at all, Robyn — this is exactly the right way to do it. 🌷 Skimming gets the shape; studying gets the structure. That’s how this sort of logic actually sticks. Let’s build the onEdit(e) logic for column O / Assigned Room in a way that matches the architecture you already have.

---

Logic Task

What this logic needs to do is... when Assigned Room is edited:

If the row is currently Approved then:

1. detect whether the value actually changed
2. remove the live calendar event
3. set status back to Pending
4. write a note explaining why
5. clear the Calendar Event ID if your delete function doesn’t already do that

If the row is not Approved then:

• do nothing dramatic
• simply let future approval logic use the new effective room

---

I’m going to assume you already have or can add these header constants:

  ASSIGNED_ROOM: 'Assigned Room',
  PROCESSING_NOTE: 'Processing Note',
  STATUS: 'Status',
  CALENDAR_EVENT_ID: 'Calendar Event ID',
ROOM: 'Room'

And status constants like:

  APPROVED: 'Approved',
  PENDING: 'Pending'

Also, I’m assuming your main edit trigger is something like:

  function onEdit(e) {

and that you already use header lookup rather than hard-coded columns. That is still the best path.

---

Structure

1. In onEdit(e), detect whether Assigned Room was edited. Here is the block to add to your onEdit(e):

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

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

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

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const editedHeader = headers[col - 1];

    if (editedHeader === CONFIG.HEADERS.ASSIGNED_ROOM) {
      handleAssignedRoomEdit_(e, sheet, row, headers);
      return;
    }

    // ... your other onEdit logic continues here ...
  }

---

1. Add the handler function. This is the real working part:

  function handleAssignedRoomEdit_(e, sheet, row, headers) {
    const oldValue = (e.oldValue || '').toString().trim();
    const newValue = (e.value || '').toString().trim();

    // If nothing materially changed, do nothing
    if (oldValue === newValue) return;

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

    const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);

    const statusCol = columnByHeader_(headers, CONFIG.HEADERS.STATUS);
    const noteCol = columnByHeader_(headers, CONFIG.HEADERS.PROCESSING_NOTE);

    const oldEffectiveRoom = oldValue || requestedRoom;
    const newEffectiveRoom = newValue || requestedRoom;

    // If effective room did not actually change, do nothing
    if (oldEffectiveRoom === newEffectiveRoom) return;

    // Only reset if the booking was already approved
    if (status !== CONFIG.STATUS_VALUES.APPROVED) return;

    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
      CONFIG.STATUS_VALUES.PENDING
    );

    // In case removeLiveCalendarEvent_ does not write these fields itself
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.PENDING);
    sheet.getRange(row, noteCol).setValue(
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`
    );
  }

---

This version is careful about two things that matter a lot:
1. It checks for a real effective-room change. This is important because:

• blank → Gallery = changed
• Gallery → blank = changed back to requested room
• Gallery → Gallery = not changed
• blank → blank = not changed

So it isn’t merely checking whether O changed visually—it checks whether the room the system would actually use has changed. That’s the right logic.

1. It only resets approved rows. That means staff can fiddle with Assigned Room while the booking is still:

2. Pending

3. Conflict
4. Rejected

without triggering calendar deletion or status resets. Exactly as intended.

---

If you want less duplication and if removeLiveCalendarEvent_() already:

• deletes the event
• clears event ID
• writes the note
• sets the new status

then this cleaner version is better:

  function handleAssignedRoomEdit_(e, sheet, row, headers) {
    const oldValue = (e.oldValue || '').toString().trim();
    const newValue = (e.value || '').toString().trim();

    if (oldValue === newValue) return;

    const rowValues = sheet.getRange(row, 1, 1, headers.length).getValues()[0];
    const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);

    const oldEffectiveRoom = oldValue || requestedRoom;
    const newEffectiveRoom = newValue || requestedRoom;

    if (oldEffectiveRoom === newEffectiveRoom) return;
    if (status !== CONFIG.STATUS_VALUES.APPROVED) return;

    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
      CONFIG.STATUS_VALUES.PENDING
    );
  }

I slightly prefer this one, provided removeLiveCalendarEvent_() is already doing the field updates properly.

---

You will also need this in your approval logic. Wherever approval checks conflict and writes to Calendar, make sure you use:

  const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
  const assignedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);

  const effectiveRoom = assignedRoom && String(assignedRoom).trim()
    ? String(assignedRoom).trim()
    : requestedRoom;

Then use effectiveRoom for conflict detection, calendar event creation and note writing. Otherwise Assigned Room will exist, but won’t actually do anything useful.

---

One thing to watch carefully is, in Apps Script, e.oldValue is only available for certain kinds of single-cell edits. That means this logic works well when someone manually changes one Assigned Room cell in the sheet—which is exactly your use case. But it may not behave as expected for:

• paste into multiple cells
• script-driven changes
• some bulk edits

That’s fine for v1. For your bookings manager editing one row at a time, this is perfectly reasonable.

---

A tiny helper if you want it even sturdier would be, if room names may sometimes carry odd spacing, you can normalise them:

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

Then use (Not essential, but tidy):

  const oldValue = normalizeValue_(e.oldValue);
  const newValue = normalizeValue_(e.value);

---

Use this version:

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

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

    if (row < 2) return;

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const editedHeader = headers[col - 1];

    if (editedHeader === CONFIG.HEADERS.ASSIGNED_ROOM) {
      handleAssignedRoomEdit_(e, sheet, row, headers);
      return;
    }

    // existing onEdit logic below...
  }

  function handleAssignedRoomEdit_(e, sheet, row, headers) {
    const oldValue = String(e.oldValue || '').trim();
    const newValue = String(e.value || '').trim();

    if (oldValue === newValue) return;

    const rowValues = sheet.getRange(row, 1, 1, headers.length).getValues()[0];
    const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);

    const oldEffectiveRoom = oldValue || requestedRoom;
    const newEffectiveRoom = newValue || requestedRoom;

    if (oldEffectiveRoom === newEffectiveRoom) return;
    if (status !== CONFIG.STATUS_VALUES.APPROVED) return;

    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
      CONFIG.STATUS_VALUES.PENDING
    );
  }

---

Tests To Try

What I’d do next, immediately after this is: once you’ve inserted it, test these four cases:

Test 1
Approved row, Assigned Room blank → Gallery Expected: event removed, status Pending, note updated

Test 2
Approved row, Assigned Room Gallery → Studio Expected: same reset behaviour

Test 3
Approved row, Assigned Room Gallery → blank Expected: reset, because effective room changed back to requested room

Test 4
Pending row, Assigned Room blank → Gallery Expected: no reset, no event deletion, just quiet update for later approval

---

Paste me your current onEdit(e) and removeLiveCalendarEvent_() if you want, and I’ll splice this into your real code rather than leaving it at the “good template” stage 💛

---

Naming Correctly

Robyn
Going to back up a bit. I had EVENT_ID instead of CALENDAR_EVENT_ID. I've changed that, as well as changed just NOTE to PROCESSING_NOTE. Please check the code that I've done it correctly. Only check that, for now.

---

Emily
Yes — almost all of that rename work looks correct. 👍🌷 I found one leftover reference that still uses the old name. In processApprovalRow_(), this line is still wrong:

  if (!eventIdCol) throw new Error(`Missing header: ${CONFIG.HEADERS.EVENT_ID}`);

It should now be:

  if (!eventIdCol) throw new Error(`Missing header: ${CONFIG.HEADERS.CALENDAR_EVENT_ID}`);

That mismatch is visible in your current code while the surrounding lines have already been updated to CALENDAR_EVENT_ID and PROCESSING_NOTE. Everything else I checked in this rename pass looks consistent:

• HEADERS.CALENDAR_EVENT_ID is defined in CONFIG.HEADERS
• HEADERS.PROCESSING_NOTE is defined in CONFIG.HEADERS
• handleStatusEdit_() now uses PROCESSING_NOTE correctly
• removeLiveCalendarEvent_() uses both CALENDAR_EVENT_ID and PROCESSING_NOTE correctly

---

[Q] Next item: I don't have an onEdit(e) function. I do have a onApprovalEdit(e) function. Is this just a naming issue?

---

Yes — in your case, it is basically a naming issue. 🌷 onEdit(e) is the special simple trigger name that Google Apps Script recognises automatically. Your onApprovalEdit(e) is just a normal function with a custom name. So the difference is this:

onEdit(e):

• runs automatically if it is named exactly that
• no installable trigger needed for the basic edit trigger behaviour

onApprovalEdit(e):

• does not run automatically just because of its name
• only runs automatically if you created an installable trigger that points to it

And from your earlier testing, you did create a trigger for it — so your current setup is perfectly valid.

---

So for your project, what does that mean? You have two options:

1. — keep onApprovalEdit(e)

This is probably the cleanest choice right now. Why? Because you already have the function, the trigger and working behaviour. So for the Assigned Room workflow, you can simply expand your existing onApprovalEdit(e) to also react when column O is edited. That means no need to rename the function unless you want to.

---

1. — rename it to onEdit(e)

You could do that, but then:

• you’d be moving to the simple-trigger convention
• you’d want to think carefully about whether any of your logic needs permissions that a simple trigger may not like

Since your script works with Calendar, installable triggers are usually the safer, saner option anyway. So I would not rename it just for tidiness.

---

My recommendation is to keep:

  function onApprovalEdit(e)

and treat it as your master installed edit-trigger handler. Then we add Assigned Room handling inside that function.

---

In practical terms, right now your function does this:

• check sheet
• check edited row
• get headers
• find status column
• if edited column is status column → run handleStatusEdit_()

What we’ll do next is simply make it do this as well:

• if edited column is Assigned Room column → run handleAssignedRoomEdit_()

So yes: not a logic problem, just a trigger/function naming distinction. 💛 The short version is:

• onEdit(e) = automatic by name
• onApprovalEdit(e) = automatic by installed trigger

And yours is the second kind. Paste the current onApprovalEdit(e) if you want, and I’ll show you exactly how to extend that function for column O without changing the architecture.

---

[Q] I'll do that next, but before we do, you mentioned triggers. I seem to remember creating triggers for the original Booking App. I just had a look at this one: there are no triggers. Should there be? Were the old triggers for the Google Form?

---

Yes — for this workbook, there probably should be triggers if you want your custom-named functions to fire automatically. 😊 Here’s the key distinction:

• A function named exactly onEdit(e) is a simple trigger, runs automatically by virtue of that reserved name. (Google for Developers)
• A function named onApprovalEdit(e) is not a reserved simple-trigger name, so it will only run automatically if you create an installable trigger that points to it. (Google for Developers)

Because your code uses custom names like onApprovalEdit(e) and setPendingOnFormSubmit(e), those functions will not auto-run unless installable triggers exist for them. For a spreadsheet, installable triggers can be created for edit and form submit events. (Google for Developers)

So, if you look in this script project and see no triggers at all, then one of two things is true:

1. the automation is currently not running automatically, or
2. you’re looking at a different script project / copied workbook than the one where the old triggers were created. Installable triggers are attached to a specific script project; they do not magically follow every copy the way code often does. That fits very well with what you described earlier about a “completely NEW copy.” (Google for Developers)

And yes — your memory about the old setup is likely right. In this kind of booking system, you would typically have had:

• an edit trigger for your spreadsheet, pointing to onApprovalEdit
• a spreadsheet form-submit trigger pointing to setPendingOnFormSubmit

Google documents both of those installable spreadsheet triggers explicitly. (Google for Developers)

One more important wrinkle: because your script talks to CalendarApp, an installable trigger is the safer fit anyway. Simple triggers have authorisation restrictions, while installable triggers offer more capability after authorisation. (Google for Developers)

So the practical answer is:

• Yes, there should probably be triggers
• Yes, the old ones were likely for the Google Form / spreadsheet workflow
• and if this is a new copy, you’ll likely need to recreate them for this project

For your setup, the pair to expect is:

• From spreadsheet → On edit → onApprovalEdit
• From spreadsheet → On form submit → setPendingOnFormSubmit (Google for Developers)

After that, we can fold the Assigned Room logic into onApprovalEdit(e) neatly.

---

[Q] Can you walk me through setting up the trigger for those two functions, please?

---

Absolutely 🌷 Here’s the clean walk-through for setting up the two installable triggers you need. Because your functions are named onApprovalEdit and setPendingOnFormSubmit rather than the reserved simple-trigger names, they need installable spreadsheet triggers to run automatically. Google’s Apps Script docs explicitly support both On edit and On form submit triggers for spreadsheets. (Google for Developers)

What you want to end up with is, you should have these two triggers in this script project:

• onApprovalEdit → From spreadsheet → On edit
• setPendingOnFormSubmit → From spreadsheet → On form submit

That second one is the spreadsheet form-submit trigger, which fires when the response lands in the linked sheet. Google documents that separately from the Google Forms-side form-submit trigger. (Google for Developers)

To create them

1. Open the Apps Script project. Open the spreadsheet that owns this booking app, then go to:

2. Extensions

3. Apps Script

Make sure you’re in the script project that contains your current code.

1. Open the Triggers page. In the Apps Script editor:

2. look down the left sidebar for the clock/alarm icon

3. click Triggers

If there are none yet, the page will look a bit empty, with an Add Trigger button.

1. Create the edit trigger. Click Add Trigger and set it like this:

2. Choose which function to run: onApprovalEdit

3. Choose which deployment should run: Head
4. Select event source: From spreadsheet
5. Select event type: On edit

Then click Save.

The first time, Google will probably ask you to authorise the script. That is normal. Installable triggers run with the authorisation of the user who created them, which is one reason they’re the right fit here, especially since your script talks to Calendar. (Google for Developers)

1. Create the form-submit trigger. Click Add Trigger again and set it like this:

2. Choose which function to run: setPendingOnFormSubmit

3. Choose which deployment should run: Head
4. Select event source: From spreadsheet
5. Select event type: On form submit

Then Save and authorise if prompted. That gives you the pair your project needs. Apps Script’s SpreadsheetTriggerBuilder docs list both .onEdit() and .onFormSubmit() for spreadsheets. (Google for Developers)

---

What not to choose

For your current setup, do not choose:

• From form for setPendingOnFormSubmit
• On change instead of On edit

Your function is written to work from the response row in the spreadsheet, not the Form editor side. On change is for structural sheet changes like adding/removing sheets or columns, not normal cell edits. (Google for Developers)

After you save them, you should see two entries listed on the Triggers page. Something like:

• onApprovalEdit — From spreadsheet — On edit
• setPendingOnFormSubmit — From spreadsheet — On form submit

Once those exist, your custom-named functions should begin firing automatically.

---

Sanity test

After the triggers are in place:

1. Submit a test form response. Expected: the new row on the admin sheet gets Pending populated by setPendingOnFormSubmit.
2. Manually change a booking’s status in the sheet Expected: onApprovalEdit runs and processes that edit.

One likely reason they vanished is, if this is a copied spreadsheet or a copied Apps Script project, installable triggers often need to be recreated in the new copy. They are attached to the specific script project rather than magically following every clone. (Google for Developers)

One tiny caution: your current headers in the code use Start Time and End Time, but earlier in your project you had sheet headers like Start time and End time. If the actual sheet headers don’t match exactly, trigger setup will succeed but the function may still error later when it runs. That’s separate from the trigger itself, but worth keeping in mind.

When you’ve created the two triggers, send me a quick “done,” and we’ll wire the Assigned Room logic into onApprovalEdit(e) cleanly. 😊

---

Capitalisation

Robyn
In this version I've made all capitalisation the same: each word of each name is capitalised. I am now going to do a test.

---

Emily
💙 🌿 Perfect 👍🌷 That was the right cleanup to do before testing. Header names are one of those maddening little things where everything can look fine and still fail just because of one capital letter. Making them all consistent is exactly the sensible move. For this test, I’d watch these in order:

1. Form submit test. Submit a dummy booking and check whether the status lands as Pending.
2. Approval test. Change the status to Approved and see whether the Calendar event is created and the Processing Note updates properly.
3. Cancellation test. Change that same row to Cancelled and confirm the event is removed and the note reflects it.
4. Conflict test. Try creating an overlapping booking in the same room and time, then approve it and see whether it flips to Conflict.

If something goes wrong, send me the exact behaviour or error message rather than just “it didn’t work” — that’ll let me zero in much faster 😊

---

[Q]
Form submit test
Submit a dummy booking and check whether the status lands as Pending. It does.
Approval test
Change the status to Approved and see whether the Calendar event is created and the Processing Note updates properly. Nothing is happening. No calendar write, no note.

Stopping there. I'll go back in this thread and see what else I was supposed to check (and add).

---

That narrows it down beautifully. 🌷 If form submit works but changing Status to Approved does nothing at all — no note, no calendar event, no conflict message — then the most likely issue is not your approval logic. It is almost certainly one of these.

Most likely: the edit trigger is missing or pointing at the wrong function, because:

• setPendingOnFormSubmit is clearly firing ✅
• onApprovalEdit appears not to be firing at all ❌

If processApprovalRow_() were running and failing, you would usually see some effect:

• an error in executions
• a changed Processing Note
• a status flip to Conflict
• or at least some sign of life

But you’re getting nothing, which strongly suggests: the edit-trigger function itself is not being invoked.

---

First thing to check: go back to Triggers and confirm you have this exact entry:

• Function: onApprovalEdit
• Event source: From spreadsheet
• Event type: On edit

Not On change
Not From form
Not an old function name

Because the old Booking App may well have had a trigger, but if this is a copied project, that trigger does not automatically come across.

---

Second thing to check, in the Apps Script editor, open Executions after you edit the Status cell. Then manually change a row from Pending to Approved. What should happen? You should see an execution attempt for onApprovalEdit.

• If you see nothing, that confirms the trigger is not firing.
• If you do see an execution, then we know the trigger is fine, and the problem is inside the function.

---

Third thing to check: did you save after edits? A silly one, but absolutely real:

• Save script
• then test again

Apps Script can be annoyingly literal about that.

---

Quick diagnostic trick: put this near the top of onApprovalEdit(e):

  function onApprovalEdit(e) {
    logAction_('INFO', 'onApprovalEdit fired', {
      sheet: e && e.range ? e.range.getSheet().getName() : 'no sheet',
      row: e && e.range ? e.range.getRow() : 'no row',
      col: e && e.range ? e.range.getColumn() : 'no col'
    });

    if (!e || !e.range) return;

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

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

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

    if (!statusCol) {
      throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
    }

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

Then edit Status again.

• If System_Log gets a new line saying onApprovalEdit fired, the trigger is working.
• If nothing appears, the trigger is not firing.

That’s a very clean test.

---

My honest read at this stage, I would not go hunting through approval logic yet, because your symptom is too clean:

• form submit works
• approval edit does absolutely nothing

That points upstream, not downstream. Best next move is to check these in order:

1. Triggers page — confirm onApprovalEdit exists as From spreadsheet / On edit
2. Executions — see whether editing Status creates an execution record
3. Add the logAction_() line at the top of onApprovalEdit(e) and test once

Once you tell me which of those three happens, we’ll know exactly where the fault is 💛

---

Column 'O' Logic

Robyn
I'm still working on the column O logic. I've updated the function onApprovalEdit(e) (analogous to onEdit(e) except it's not a protected function name, right?) to this... please check for accuracy:

  function onApprovalEdit(e) {
  // Room change addition
    if (!e || !e.range) return;

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

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

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const editedHeader = headers[col - 1];

    if (editedHeader === CONFIG.HEADERS.ASSIGNED_ROOM) {
      handleAssignedRoomEdit_(e, sheet, row, headers);
      return;
    }

      if (!e || !e.range) return;

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

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

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

    if (!statusCol) {
      throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
    }

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

---

Emily
Yes — onEdit(e) is the special reserved simple-trigger name, and onApprovalEdit(e) is just your own function name that needs an installable trigger. 👍 As for the code: this version is not accurate yet. There are a few concrete problems. The biggest issue is that this will not parse. You declare the same constants twice in the same function:

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

and then later again:

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

That will throw an error immediately. You can’t redeclare const variables in the same scope.

---

Second issue: you’re using two different kinds of headers. In this part:

  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const editedHeader = headers[col - 1];

headers is an array of header names. Later, in this part:

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

headers is an object/map like:

  {
    "Status": 12,
    "Processing Note": 14
  }

Both approaches are valid, but not under the same variable name in the same function. That’s part of why this got tangled.

Also, your handleAssignedRoomEdit_(e, sheet, row, headers); call is currently passing the array version of headers. If handleAssignedRoomEdit_() expects the mapped version from getHeaders_(sheet), that will break.

---

Third issue: the sheet-name check happens too late. Right now, someone editing any sheet in the workbook could hit this part:

  if (editedHeader === CONFIG.HEADERS.ASSIGNED_ROOM) {
    handleAssignedRoomEdit_(e, sheet, row, headers);
    return;
  }

before you check:

  if (sheet.getName() !== CONFIG.SHEET_NAME) return;

So the sheet name check should happen earlier.

---

This is the version I’d use:

  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 headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const editedHeader = String(headerRow[col - 1]).trim();

    const headers = getHeaders_(sheet);

    if (editedHeader === CONFIG.HEADERS.ASSIGNED_ROOM) {
      handleAssignedRoomEdit_(e, sheet, row, headers);
      return;
    }

    const statusCol = headers[CONFIG.HEADERS.STATUS];
    if (!statusCol) {
      throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
    }

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

---

This version works because it does things in the right order:

1. validate e
2. confirm correct sheet
3. ignore header row
4. determine which header was edited
5. build the header map once

Finally, it will route to:

• handleAssignedRoomEdit_() if Assigned Room was edited
• handleStatusEdit_() if Status was edited

---

The main thing to check next is that your handleAssignedRoomEdit_() function should be written to accept:

  (e, sheet, row, headers)

where headers is the mapped object from getHeaders_(sheet). So if you want, the next best move is for you to paste that function, and I’ll check it against this updated onApprovalEdit(e) so the two match properly. 🌷

---

handleAssignedRoomEdit()

Robyn

  function handleAssignedRoomEdit_(e, sheet, row, headers) {
    const oldValue = (e.oldValue || '').toString().trim();
    const newValue = (e.value || '').toString().trim();

    if (oldValue === newValue) return;

    const rowValues = sheet.getRange(row, 1, 1, headers.length).getValues()[0];
    const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);

    const oldEffectiveRoom = oldValue || requestedRoom;
    const newEffectiveRoom = newValue || requestedRoom;

    if (oldEffectiveRoom === newEffectiveRoom) return;
    if (status !== CONFIG.STATUS_VALUES.APPROVED) return;

    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
      CONFIG.STATUS_VALUES.PENDING
    );
  }

---

Emily
🤍 1️⃣2️⃣3️⃣ 🎙️✨ 4️⃣ 5️⃣ 6️⃣ 🎙️✨ This is very close, but there is one important bug in it. 🌷 This line is wrong:

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

Because in your current architecture, headers comes from:

  const headers = getHeaders_(sheet);

and getHeaders_() returns an object, not an array. Objects do not have a useful length here. So headers.length will not do what you want. Instead, use:

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

That matches the rest of your codebase and is the correct way in this setup.

---

Corrected version

  function handleAssignedRoomEdit_(e, sheet, row, headers) {
    const oldValue = (e.oldValue || '').toString().trim();
    const newValue = (e.value || '').toString().trim();

    if (oldValue === newValue) return;

    const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
    const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);

    const oldEffectiveRoom = oldValue || requestedRoom;
    const newEffectiveRoom = newValue || requestedRoom;

    if (oldEffectiveRoom === newEffectiveRoom) return;
    if (status !== CONFIG.STATUS_VALUES.APPROVED) return;

    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
      CONFIG.STATUS_VALUES.PENDING
    );
  }

---

Other than that, the logic is sound. What it does correctly:

• compares old and new Assigned Room values
• falls back to requested Room when Assigned Room is blank
• checks whether the effective room truly changed
• only acts when status is Approved
• resets by calling removeLiveCalendarEvent_() with Pending

---

One tiny note, this part:

  const oldValue = (e.oldValue || '').toString().trim();
  const newValue = (e.value || '').toString().trim();

is fine for your use case. It will behave sensibly when blank becomes a room, a room becomes blank or one room becomes another room: no issue there. At this point, your next step is to pair this corrected function with the cleaned-up onApprovalEdit(e) version, then test column O on an already approved booking.

---

Cleaned Logic

Robyn

Allow me to go back to the Column O logic note you posted earlier. There are too many changes that I'd need to slot in and I'm unclear where they are meant to go.

If I gave you the current Header, would you be willing to give me a fresh version with all your changes, to reflect the code we need to the room change, please?

---

Emily
🤍📚🧭1️⃣2️⃣3️⃣4️⃣🪞⚖️ Absolutely, Robyn. 🌷 That’s the cleanest way to do it. Paste me your current:

• CONFIG.HEADERS
• onApprovalEdit(e)
• handleAssignedRoomEdit_() if it exists
• and, ideally, processApprovalRow_() too, because that’s where the effective room also has to be used

Then I’ll give you back a fresh, coherent version with the Column O / Assigned Room workflow integrated properly, so you can replace section by section instead of trying to splice little fragments into older code. If you like, you can also paste the current header row from the sheet itself exactly as it appears, and I’ll make sure the names line up.

---

[Q]

  const CONFIG = {
    CALENDAR_MODE: 'id', // 'default' or 'id'
    // NOTE: This ID is to a TEST calendar - change at GoLIVE
    CALENDAR_ID: '1c26f492c1488f8852cbf50f1203ce8efe868869ee4aa77768f73249736a3549@group.calendar.google.com', // only used if CALENDAR_MODE = 'id'

    SHEET_NAME: 'WebForm_Submissions',
    // legacy name from Google Form response flow
    RESPONSE_SHEET_NAME: 'WebForm_Submissions',

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

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

    HEADERS: {
      TIMESTAMP: 'Timestamp',
      FULL_NAME: 'Full Name',
      EMAIL: 'Email',
      COURSE_NAME: 'Course Name',
      ROOM: 'Room',
      EVENT_DATE: 'Event Date',
      START_TIME: 'Start Time',
      END_TIME: 'End Time',
      RECURRING: 'Recurring',
      FREQUENCY: 'Frequency',
      REPEAT_UNTIL: 'Repeat Until',
      STATUS: 'Status',
      CALENDAR_EVENT_ID: 'Calendar Event ID',
      PROCESSING_NOTE: 'Processing Note',
      ASSIGNED_ROOM: 'Assigned Room',
    }
  };

  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 headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const editedHeader = String(headerRow[col - 1]).trim();

    const headers = getHeaders_(sheet);

    if (editedHeader === CONFIG.HEADERS.ASSIGNED_ROOM) {
      handleAssignedRoomEdit_(e, sheet, row, headers);
      return;
    }

    const statusCol = headers[CONFIG.HEADERS.STATUS];
    if (!statusCol) {
      throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
    }

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

  function handleStatusEdit_(sheet, row, headers, e) {
    const newStatus = trim_(e.range.getValue());
    const oldStatus = trim_(e.oldValue);
    const noteCol = headers[CONFIG.HEADERS.PROCESSING_NOTE];

    if (!noteCol) throw new Error(`Missing header: ${CONFIG.HEADERS.PROCESSING_NOTE}`);

    if (!newStatus) {
      return;
    }

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

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

    if (newStatus === CONFIG.STATUS_VALUES.REJECTED) {
      if (oldStatus !== CONFIG.STATUS_VALUES.APPROVED) {
        sheet.getRange(row, noteCol).setValue('Booking rejected by manager.');
        return;
      }
      // fall through to safety net below
    } else if (newStatus === CONFIG.STATUS_VALUES.CONFLICT) {
      if (oldStatus !== CONFIG.STATUS_VALUES.APPROVED) {
        sheet.getRange(row, noteCol).setValue(
          'Booking marked as conflict. Review room/date/time before re-approval.'
        );
        return;
      }
      // fall through to safety net below
    }

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

  function handleAssignedRoomEdit_(e, sheet, row, headers) {
    const oldValue = (e.oldValue || '').toString().trim();
    const newValue = (e.value || '').toString().trim();

    // If nothing materially changed, do nothing
    if (oldValue === newValue) return;

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

    const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);

    const statusCol = columnByHeader_(headers, CONFIG.HEADERS.STATUS);
    const noteCol = columnByHeader_(headers, CONFIG.HEADERS.PROCESSING_NOTE);

    const oldEffectiveRoom = oldValue || requestedRoom;
    const newEffectiveRoom = newValue || requestedRoom;

    // If effective room did not actually change, do nothing
    if (oldEffectiveRoom === newEffectiveRoom) return;

    // Only reset if the booking was already approved
    if (status !== CONFIG.STATUS_VALUES.APPROVED) return;

    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
      CONFIG.STATUS_VALUES.PENDING
    );

    // In case removeLiveCalendarEvent_ does not write these fields itself
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.PENDING);
    sheet.getRange(row, noteCol).setValue(
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`
    );
  }

---

Yes — and thank you for pasting it cleanly. I can now see exactly where the tangles are. 🌷 You’ve got three issues in the current version:

1. headers.length is wrong here, because headers is an object, not an array.
2. columnByHeader_() appears in your handleAssignedRoomEdit_(), but your codebase has been using getHeaders_() + direct lookup, so this may not exist at all.
3. You’re duplicating status/note writing after removeLiveCalendarEvent_(), even though that function already does the reset work for you.

So here is a fresh replacement version for the parts you pasted.

---

CONFIG

Your CONFIG block is fine as written. Keep it exactly like this:

  const CONFIG = {
    CALENDAR_MODE: 'id', // 'default' or 'id'
    // NOTE: This ID is to a TEST calendar - change at GoLIVE
    CALENDAR_ID: '1c26f492c1488f8852cbf50f1203ce8efe868869ee4aa77768f73249736a3549@group.calendar.google.com', // only used if CALENDAR_MODE = 'id'

    SHEET_NAME: 'WebForm_Submissions',
    // legacy name from Google Form response flow
    RESPONSE_SHEET_NAME: 'WebForm_Submissions',

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

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

    HEADERS: {
      TIMESTAMP: 'Timestamp',
      FULL_NAME: 'Full Name',
      EMAIL: 'Email',
      COURSE_NAME: 'Course Name',
      ROOM: 'Room',
      EVENT_DATE: 'Event Date',
      START_TIME: 'Start Time',
      END_TIME: 'End Time',
      RECURRING: 'Recurring',
      FREQUENCY: 'Frequency',
      REPEAT_UNTIL: 'Repeat Until',
      STATUS: 'Status',
      CALENDAR_EVENT_ID: 'Calendar Event ID',
      PROCESSING_NOTE: 'Processing Note',
      ASSIGNED_ROOM: 'Assigned Room',
    }
  };

---

onApprovalEdit(e)

  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 headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const editedHeader = String(headerRow[col - 1] || '').trim();

    const headers = getHeaders_(sheet);

    if (editedHeader === CONFIG.HEADERS.ASSIGNED_ROOM) {
      handleAssignedRoomEdit_(e, sheet, row, headers);
      return;
    }

    const statusCol = headers[CONFIG.HEADERS.STATUS];
    if (!statusCol) {
      throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
    }

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

This is good because it:

• checks the correct sheet early
• ignores row 1
• routes Assigned Room edits to one function
• routes Status edits to the other

---

handleStatusEdit_() (as-is)

Your current version is fine. I’d keep it exactly as follows:

  function handleStatusEdit_(sheet, row, headers, e) {
    const newStatus = trim_(e.range.getValue());
    const oldStatus = trim_(e.oldValue);
    const noteCol = headers[CONFIG.HEADERS.PROCESSING_NOTE];

    if (!noteCol) throw new Error(`Missing header: ${CONFIG.HEADERS.PROCESSING_NOTE}`);

    if (!newStatus) {
      return;
    }

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

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

    if (newStatus === CONFIG.STATUS_VALUES.REJECTED) {
      if (oldStatus !== CONFIG.STATUS_VALUES.APPROVED) {
        sheet.getRange(row, noteCol).setValue('Booking rejected by manager.');
        return;
      }
      // fall through to safety net below
    } else if (newStatus === CONFIG.STATUS_VALUES.CONFLICT) {
      if (oldStatus !== CONFIG.STATUS_VALUES.APPROVED) {
        sheet.getRange(row, noteCol).setValue(
          'Booking marked as conflict. Review room/date/time before re-approval.'
        );
        return;
      }
      // fall through to safety net below
    }

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

---

handleAssignedRoomEdit_()

  function handleAssignedRoomEdit_(e, sheet, row, headers) {
    const oldValue = String(e.oldValue || '').trim();
    const newValue = String(e.value || '').trim();

    // If nothing materially changed, do nothing
    if (oldValue === newValue) return;

    const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
    const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);

    const oldEffectiveRoom = oldValue || requestedRoom;
    const newEffectiveRoom = newValue || requestedRoom;

    // If effective room did not actually change, do nothing
    if (oldEffectiveRoom === newEffectiveRoom) return;

    // Only reset if the booking was already approved
    if (status !== CONFIG.STATUS_VALUES.APPROVED) return;

    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
      CONFIG.STATUS_VALUES.PENDING
    );
  }

This version is better because it removes the messy parts:

• no headers.length
• no columnByHeader_()
• no duplicate resetting of status/note after removeLiveCalendarEvent_()

That last one matters because removeLiveCalendarEvent_() already:

• clears Calendar Event ID
• sets the new status
• writes the Processing Note

There is no need to do it twice.

---

Important: one more change still needs to happen. Even after the above is in place, Assigned Room won’t affect approval until processApprovalRow_() uses the effective room. Right now, if processApprovalRow_() still does this:

  const roomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
  const room = normalizeRoom_(roomRaw);

then Column O won’t actually be used when approving. It needs to become this shape:

  const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
  const assignedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);

  const effectiveRoomRaw = trim_(assignedRoom) || trim_(requestedRoom);
  const room = normalizeRoom_(effectiveRoomRaw);

That is the step that makes Column O real. So, for now, replace these two functions:

• onApprovalEdit(e)
• handleAssignedRoomEdit_()

Leave handleStatusEdit_() alone.

Then the next thing we should do is patch processApprovalRow_() so approval, conflict checking, and calendar writing all use: Assigned Room if present, otherwise Room. Paste processApprovalRow_() and I’ll give you the corrected full version in one pass. 💛

---

Where is normalizeRoom()?

Robyn
?? normalizeRoom()

---

Emily
😄🧱🔄🧠🧨🧭😄 It isn’t in the code you pasted to me here — which likely means you haven’t defined it yet in this version. 🌷 That would explain trouble nicely, because processApprovalRow_() is calling:

  const room = normalizeRoom_(roomRaw);

So if that helper function does not exist anywhere in the script project, approval logic will fail when it reaches that line. Add this helper somewhere with your other utility functions:

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

    if (!room) return '';

    const normalized = room.toLowerCase();

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

    return '';
  }

It takes things like "Studio", "studio" or " studio " and converts them safely to "Studio". Same for Gallery. If the value is unknown, it returns a blank string, which lets your approval logic catch that and write:

  Unknown room: ...

Later, when we update processApprovalRow_(), we’ll feed either an Assigned Room, or a fallback Room into normalizeRoom_(). So yes — this helper is meant to exist, but from what you’ve shown me, it currently does not. Add it, save, and then we can patch processApprovalRow_() next 😊

---

processApprovalRow()

Robyn
In this function:

  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 fullName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
    const fnEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);
    const courseName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
    const assignedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);
    const effectiveRoomRaw = trim_(assignedRoom) || trim_(requestedRoom);
    const room = normalizeRoom_(effectiveRoomRaw);
    const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
    const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
    const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);
    const recurring = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.RECURRING));
    const frequency = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.FREQUENCY));
    const repeatUntil = valueByHeader_(rowValues, headers, CONFIG.HEADERS.REPEAT_UNTIL);
    const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.CALENDAR_EVENT_ID));

    const statusCol = headers[CONFIG.HEADERS.STATUS];
    const eventIdCol = headers[CONFIG.HEADERS.CALENDAR_EVENT_ID];
    const procNoteCol = headers[CONFIG.HEADERS.PROCESSING_NOTE];

    if (!statusCol) throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
    if (!eventIdCol) throw new Error(`Missing header: ${CONFIG.HEADERS.CALENDAR_EVENT_ID}`);
    if (!procNoteCol) throw new Error(`Missing header: ${CONFIG.HEADERS.PROCESSING_NOTE}`);

    const room = normalizeRoom_(roomRaw);

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

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

    if (!bookingDate || !startTime || !endTime) {
      sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
      sheet.getRange(row, procNoteCol).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, procNoteCol).setValue('Invalid date/time values.');
      return;
    }

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

    if (recurring === 'Yes') {
      if (!frequency || !repeatUntil) {
        sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
        sheet.getRange(row, procNoteCol).setValue(
          'Recurring booking is missing Frequency or Repeat Until.'
        );
        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, procNoteCol).setValue(
        `Conflict detected at approval time: ${room} is already booked.`
      );
      return;
    }

    const title = `${fullName || 'Tutor'} (${room})`;

    const descriptionLines = [
      `Full Name: ${fullName || ''}`,
      `Email: ${fnEmail || ''}`,
      `Room: ${room}`,
      `Course Name: ${courseName || ''}`,
      `Recurring: ${recurring || 'No'}`,
      `Frequency: ${frequency || ''}`,
      `Repeat Until: ${repeatUntil || ''}`
    ];

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

    sheet.getRange(row, eventIdCol).setValue(event.getId());

    if (recurring === 'Yes') {
      sheet.getRange(row, procNoteCol).setValue(
        `Calendar event created successfully in ${room}. Recurring logic not yet implemented.`
      );
    } else {
      sheet.getRange(row, procNoteCol).setValue(
        `Calendar event created successfully in ${room}.`
      );
    }
  }

...I've got two instances of:

  const room = normalizeRoom_(roomRaw);

where should it be?

---

Emily
✅ Only one should remain — and it should be the new one near the top, the one based on Assigned Room fallback. 😊 So keep this:

  const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
  const assignedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);
  const effectiveRoomRaw = trim_(assignedRoom) || trim_(requestedRoom);
  const room = normalizeRoom_(effectiveRoomRaw);

And delete this older leftover line entirely:

const room = normalizeRoom_(roomRaw);

Because a roomRaw no longer exists in this updated version and you’ve already computed the correct room from:

• Assigned Room if present
• otherwise Room

Since roomRaw is gone, this line also needs to change:

  sheet.getRange(row, procNoteCol).setValue(`Unknown room: ${roomRaw}`);

Change it to:

  sheet.getRange(row, procNoteCol).setValue(`Unknown room: ${effectiveRoomRaw}`);

  const fullName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
  const fnEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);
  const courseName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
  const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
  const assignedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);
  const effectiveRoomRaw = trim_(assignedRoom) || trim_(requestedRoom);
  const room = normalizeRoom_(effectiveRoomRaw);
  const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
  const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
  const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);

and later:

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

In short: keep the new const room = normalizeRoom_(effectiveRoomRaw); and delete the old const room = normalizeRoom_(roomRaw);. If you want, paste the whole updated processApprovalRow_() after you make those two changes, and I’ll do a final sanity check on just that function 🌷

---

Fixing processApprovalRow()

Robyn

  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 fullName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
    const fnEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);
    const courseName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
    const assignedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);
    const effectiveRoomRaw = trim_(assignedRoom) || trim_(requestedRoom);
    const room = normalizeRoom_(effectiveRoomRaw);
    const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
    const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
    const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);
    const recurring = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.RECURRING));
    const frequency = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.FREQUENCY));
    const repeatUntil = valueByHeader_(rowValues, headers, CONFIG.HEADERS.REPEAT_UNTIL);
    const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.CALENDAR_EVENT_ID));

    const statusCol = headers[CONFIG.HEADERS.STATUS];
    const eventIdCol = headers[CONFIG.HEADERS.CALENDAR_EVENT_ID];
    const procNoteCol = headers[CONFIG.HEADERS.PROCESSING_NOTE];

    if (!statusCol) throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
    if (!eventIdCol) throw new Error(`Missing header: ${CONFIG.HEADERS.CALENDAR_EVENT_ID}`);
    if (!procNoteCol) throw new Error(`Missing header: ${CONFIG.HEADERS.PROCESSING_NOTE}`);

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

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

    if (!bookingDate || !startTime || !endTime) {
      sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
      sheet.getRange(row, procNoteCol).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, procNoteCol).setValue('Invalid date/time values.');
      return;
    }

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

    if (recurring === 'Yes') {
      if (!frequency || !repeatUntil) {
        sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
        sheet.getRange(row, procNoteCol).setValue(
          'Recurring booking is missing Frequency or Repeat Until.'
        );
        return;
      }
    }

---

Emily
✨ This portion looks good to me. 🌷 The important Column O changes are now in the right place:

• requestedRoom is read from the original form room
• assignedRoom is read from Column O
• effectiveRoomRaw correctly prefers Assigned Room when present
• room = normalizeRoom_(effectiveRoomRaw) is in the right spot
• the old stray roomRaw usage is gone
• the unknown-room note now correctly uses effectiveRoomRaw

So this section is now coherent. These lines are right:

  const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
  const assignedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);
  const effectiveRoomRaw = trim_(assignedRoom) || trim_(requestedRoom);
  const room = normalizeRoom_(effectiveRoomRaw);

And this is now right too:

  sheet.getRange(row, procNoteCol).setValue(`Unknown room: ${effectiveRoomRaw}`);

This line is fine:

  if (!room) {

because normalizeRoom_() should return '' for anything invalid, so this catches bad room values nicely. So, for this chunk: ✅ Approved. You can keep going with the rest of processApprovalRow_(). The next thing I’d want to see is the remainder of that function, especially:

• conflict detection
• calendar creation
• Processing Note writing

just to make sure room is used consistently all the way through.

---

[Q] Would it benefit anything for me to just paste you the entire script I have now?

---

Yes — very much so. 😊🌷 At this point, the whole script would help more than isolated fragments, because we’re now at the stage where the important questions are about:

• consistency of helper names
• whether one function is expecting an array or a header map
• whether a later section still uses an old variable name
• whether trigger flow and calendar flow line up cleanly end-to-end

With snippets, I can catch local issues. With the full script, I can catch the cross-function mismatches that are the ones most likely to waste your testing time. So yes: paste the entire current script, and I’ll check it for:

• compile-breaking issues
• leftover renamed constants
• Assigned Room / Column O workflow correctness
• trigger routing
• obvious logic collisions

If it’s long, that’s perfectly fine. You can paste it in one go or upload it as a file.

---

The Full Script Review

Emily
😄🤍 🧱 🛠️ 🧠⚠️ ✂️✨ 💙 💙 This is in pretty good shape now 🌷 I do not see a compile-breaking problem in what you uploaded. The Column O workflow is now wired in coherently, and the approval path is using Assigned Room correctly. The main things I’d flag are these:

1. The core Column O logic now looks right ✅. You now have:

2. onApprovalEdit(e) routing Assigned Room edits to handleAssignedRoomEdit_() and Status edits to handleStatusEdit_()

3. handleAssignedRoomEdit_() resetting an already-approved booking back to Pending and removing the live calendar event when the effective room changes

processApprovalRow_() using:

• Assigned Room if present
• otherwise Room before normalising and conflict-checking the room

That is the architecture we wanted.

1. The most likely reason “Approve” still did nothing earlier is still the trigger 🔧

Your script now contains onApprovalEdit(e), not onEdit(e), so it must have an installable trigger to run automatically on edits. The code itself is fine on that point; the missing piece is operational, not syntactic. So for live testing, I would still verify that you have:

• onApprovalEdit → From spreadsheet → On edit

• setPendingOnFormSubmit → From spreadsheet → On form submit

• One subtle design oddity: SHEET_NAME and RESPONSE_SHEET_NAME are identical. Right now both are:

  SHEET_NAME: 'WebForm_Submissions',
  RESPONSE_SHEET_NAME: 'WebForm_Submissions',

That is not automatically wrong, but it means your “response sheet” and your “admin sheet” are effectively being treated as the same sheet name. That only works if:

• the form is truly writing directly into WebForm_Submissions, and
• that same sheet is the sheet where status edits happen.

If that is your intended new design, fine. If not, this is the first place I’d look for confusion.

1. setPendingOnFormSubmit(e) assumes the submitted row number matches the admin row number. Because you do:

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

That is fine only if the response sheet and admin sheet are the same sheet, or remain perfectly row-aligned. Again, not wrong — just something to be aware of.

1. One practical recommendation: add temporary logging at the top of onApprovalEdit(e). Not because the code looks broken, but because it will tell you immediately whether the edit trigger is firing. Add this right after the function starts:

logAction_('INFO', 'onApprovalEdit fired', {
  sheet: e && e.range ? e.range.getSheet().getName() : '',
  row: e && e.range ? e.range.getRow() : '',
  col: e && e.range ? e.range.getColumn() : '',
  value: e && e.value ? e.value : '',
  oldValue: e && e.oldValue ? e.oldValue : ''
});

Then edit a Status cell to Approved.

If nothing lands in System_Log, the trigger is not firing. If it does land there, then the problem is deeper inside the approval path.

---

Bottom line, my read is:

• Code structure: good ✅
• Column O logic: good ✅
• Approval room selection: good ✅
• Most likely remaining issue: trigger/setup rather than code ❗

The next smartest move is not more rewriting — it’s a live test of whether onApprovalEdit(e) is firing at all.

---

[Q] I have removed the Admin sheet. I will remove this entry as well:

// legacy name from Google Form response flow RESPONSE_SHEET_NAME: 'WebForm_Submissions',

---

Yes — that makes sense. 🌷 If you have now removed the separate Admin sheet and everything is happening directly in WebForm_Submissions, then that old line is just dead weight. So this can go:

  // legacy name from Google Form response flow
  RESPONSE_SHEET_NAME: 'WebForm_Submissions',

But one important consequence

If you remove RESPONSE_SHEET_NAME from CONFIG, then this function will also need changing:

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

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

Because once CONFIG.RESPONSE_SHEET_NAME is gone, that comparison will break. Since it’s now one sheet, simplify it. You can change that function to use CONFIG.SHEET_NAME instead:

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

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

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

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

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

This is better now because you no longer need:

• SpreadsheetApp.getActiveSpreadsheet()
• getSheetByName(CONFIG.SHEET_NAME)
• the assumption that response row and admin row match

You’re now just writing directly to the sheet the form submission landed in. Much cleaner. ✨ So yes:

• remove RESPONSE_SHEET_NAME
• then simplify setPendingOnFormSubmit(e) to use CONFIG.SHEET_NAME

If you want, paste the updated script once you’ve done that and I’ll give it one more tidy pass.

---

setPendingOnFormSubmit()

Robyn
I currently have:

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

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

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

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

    SpreadsheetApp.flush();

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

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

change it?

---

Emily
✂️ Yes — I would change it now. 🌷 What you have will still work, but it’s carrying the old “Admin sheet” structure around even though that sheet no longer exists. So it’s better to simplify it before it turns into future confusion. Replace it with this:

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

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

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

    SpreadsheetApp.flush();

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

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

This is better because now:

• responseSheet and adminSheet are the same thing
• no need to fetch the spreadsheet again
• no need to fetch the same sheet by name again
• no stale “Admin sheet” wording in the error message

It matches your new simpler architecture: form submits directly into the working sheet.

The only changes made were, I removed:

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

and changed all later references from adminSheet to sheet. That’s it. So yes — change it. It’s a tidy, worthwhile cleanup. 😊

---

Open File Error

Robyn
Quick question. I'm trying to share the form with a tester. She is getting this error:

Sorry, unable to open the file at present.

Please check the address and try again.

Get stuff done with Google Drive

Apps in Google Drive make it easy to create, store and share online documents, spreadsheets, presentations and more.

Learn more at drive.google.com/start/apps.

She has been granted 'Editor' permissions on the workbook that generates this form. What do I need to do to make it available to her?

---

Emily
📊❗Ah — this one’s a classic Google Forms / Sheets gotcha. 😊 What you’ve shared access to is the spreadsheet, but the tester is trying to open the form, which has its own sharing permissions. Those are separate objects in Google’s world. What’s happening, right now:

• ✅ She has access to the Google Sheet
• ❌ She likely does not have access to the Google Form

So when she opens the form link, Google says: “Sorry, unable to open the file…” because she doesn’t have permission to that file, even though she can see the sheet.

We can fix it in two easy ways.

✅ Option 1 — Make the form public (best for testers). Open your Google Form, then:

1. Click Send (top right)
2. Click the link icon 🔗

Make sure:

• “Restrict to users in your organisation” is OFF
• It does NOT require sign-in

Then copy that link and send it to her.

✅ Option 2 — Share the form explicitly. In the Form editor:

1. Click the three dots (⋮) top right
2. Click Add collaborators
3. Add her email

This gives her editor access to the form (probably overkill unless she’s helping build/test structure). The key distinction (important mental model): you now have three separate things:

1. Form (input UI)
2. Sheet (data + logic)
3. Apps Script (automation)

Sharing one does not automatically share the others. One more thing to check: if your form says something like: “Only people in [your organisation] can respond” then external testers will always get blocked. You’ll find this setting under:

Form → Settings → Responses

Turn off any restriction like:

• “Restrict to users in domain”
• “Collect email addresses (organisation only)”

---

Before sending again:

• [ ] Form link copied from Send → link icon
• [ ] No domain restriction enabled
• [ ] No sign-in required (unless intentional)
• [ ] Not just sharing the spreadsheet

---

[Q] Sorry, this is not a Google Form, Emily. It's a HTML-based booking form, the html and code is all stored in the App Script area.

---

The Actual Answer

Ahh — yes, that changes the diagnosis. 😊🌷 Because it’s an Apps Script HTML web app, sharing the workbook is not enough, and even sharing the script project is not what the tester uses. The tester needs access to the web app deployment URL, and the deployment itself must allow her to open it. Open the Apps Script project, then:

1. Go to Deploy → Manage deployments
2. Open your Web app deployment

Check these settings:

• Execute as: usually Me
• Who has access:

Set this to either:

• Anyone with Google account, or
• Anyone

Those access settings are part of the Apps Script web app deployment model. Google’s web app docs note that access is controlled by the deployment, not by spreadsheet sharing alone. (Google for Developers)

Your tester is getting that error because, most likely, one of these is true:

• you sent her the development/test URL rather than the deployed web app URL
• the web app is deployed with restrictive access, such as Only myself or a limited audience
• the deployment needs to be updated after recent code changes
• she is opening it from a different Google account than the one you expected, which can trigger the “unable to open the file” style of error in Google properties when permissions don’t match (Google for Developers)

The important distinction for an Apps Script HTML app, there are really four separate things:

• the spreadsheet
• the script project
• the web app deployment
• the deployment URL

Your tester only needs the right URL plus the right deployment access. She does not need editor access to the workbook just to submit/test the form, unless your testing plan specifically requires that. Best setup for your booking form for this kind of tester workflow, I’d use:

• Execute as: Me
• Who has access: Anyone with Google account

That way:

• the tester can open and submit the form
• the script runs under your authorisation, which matters because your code touches SpreadsheetApp and CalendarApp
• the tester doesn’t need direct spreadsheet editing rights just to use the form

Google’s deployment docs distinguish between head deployments and versioned deployments, and for people other than you, you generally want the proper web app deployment URL, not just an editor/test link. (Google for Developers) After changing it, do this in order:

1. Save the script
2. Deploy → Manage deployments
3. Edit the web app deployment
4. Confirm access settings
5. Click Deploy
6. Copy the web app URL
7. Send that URL to your tester

One more thing to watch: if you’ve only been using the head/test deployment, that can also trip you up. Google notes that head deployments are mainly for testing by people who already have access to the script project. For external use, a proper versioned deployment is the safer path. (Google for Developers)

So my strongest hunch is that you need to redeploy the web app and send her the deployment URL, with access set on the deployment itself. If you want, paste what you currently see under Deploy → Manage deployments for the web app, and I’ll tell you exactly which option to choose.

---

Wrong Deployment

Robyn
This deployment does not have access to the right code. I changed wording on the html form, not reflected in the form opened with this url:

Version
Version 1 on 5 Apr 2026, 13:13
Description
Booking App v.1a
Deployment ID
AKfycbys51oecly5BUdJEB3OMotZ5vaVLdGNYoLOCvB3HXbk3Quf8J8EtyA0phZb_6luaAq4
Web app
URL
https://script.google.com/macros/s/AKfycbys51oecly5BUdJEB3OMotZ5vaVLdGNYoLOCvB3HXbk3Quf8J8EtyA0phZb_6luaAq4/exec
Execute as
User accessing the web app
The web app will require users to authorise to run using their account data.
Who has access
Anyone with a Google Account
Library
To let other people and groups use this project as a library, share this 
project with them.

---

Emily
🤍😄✅ 😄 🌊 🧭 🌺 🪶 Yes — that’s exactly what’s happening. 😊 Your current URL is a versioned deployment, and it is pinned to Version 1 created on 5 April 2026 at 13:13. A versioned deployment stays connected to that saved snapshot, so later HTML wording changes won’t appear until you update the deployment to a new version. Google’s Apps Script docs are explicit that head deployments reflect your latest saved code, while versioned deployments remain tied to a specific version. (Google for Developers)

In Apps Script:

1. Save your code.
2. Go to Deploy → Manage deployments.
3. Find your existing Web app deployment.
4. Click the Edit pencil.
5. In the Version section, choose New version.
6. Add or update the description if you like.
7. Click Deploy. Google documents this exact flow for creating a new version from an existing deployment. (Google for Developers)

That updates the deployment so it points to your newer code instead of the old snapshot.

An important detail about the URL to be aware of: when you edit the existing deployment and choose New version, the deployment itself stays the same deployment. In normal Apps Script use, that means the existing deployment URL should continue to work, just now serving the updated version. If instead you create a brand new deployment from scratch, you’ll get a new deployment ID and new URL. Google distinguishes clearly between updating an existing deployment and creating a new one. (Google for Developers) So for your tester, the clean move is:

• don’t create a brand-new web app deployment unless you want a new URL
• edit the current deployment and select New version

One more setting I would change... your current deployment says: Execute as: User accessing the web app. That means each tester has to authorise and run the script with their account. Since your app writes to your spreadsheet and calendar, that setting can create unnecessary friction. Google’s web-app docs note that web apps can run either as the script owner or as the user accessing the app. (Google for Developers) For your booking app, I would strongly recommend:

• Execute as: Me
• Who has access: Anyone with a Google Account

That way:

• the tester just uses the form,
• the script runs with your already-authorised access to Sheets/Calendar,
• and you avoid forcing the tester’s account to have the same permissions chain.

A good pattern here is:

• use the Head deployment for your own immediate testing, because it always reflects the latest saved code and is meant for testing by people with access to the script project, (Google for Developers)
• use the versioned web app deployment for testers, updating it to New version whenever you want them to see the latest stable changes. (Google for Developers)

So the short practical answer is: your tester is seeing the old HTML because the web app URL is still serving Version 1. Edit that deployment, choose New version, and redeploy. While you’re there, I’d also switch Execute as from User accessing the web app to Me. (Google for Developers)

After you do that, send the tester the same URL again and it should show the updated wording.

BTW, it’s in the Apps Script editor menu, not inside the normal “Manage deployments” screen. 😊 For a web app, Google says to use:

• Deploy → Test deployments
• then choose Web app
• then copy the test URL. This test URL ends in /dev, uses the most recently saved code, and is only accessible to people who have edit access to the script project. (Google for Developers)

So the exact clicks are:

1. Open the Apps Script project.
2. At the top right, click Deploy.
3. Click Test deployments.
4. If needed, next to Select type, enable Web app.
5. Copy the URL shown there.
6. Open that URL in your browser for your own immediate testing. (Google for Developers)

That is the “Head deployment” pattern I was referring to. Google describes it as a /dev URL that always runs the latest saved code and is intended for development testing. (Google for Developers) One important wrinkle for your case: that /dev test URL can only be opened by users who have edit access to the script project, not just the spreadsheet. So it’s good for you, and possibly for a trusted co-developer, but not ideal for an ordinary tester. For your tester, the proper route is still the versioned /exec web-app deployment. (Google for Developers)

So the practical split is:

• You use Deploy → Test deployments for fast testing of the newest code.
• Tester uses the versioned /exec URL after you update the deployment to a New version. (Google for Developers)

One tiny caution: if your tester only has access to the workbook, that will not grant access to the /dev test deployment, because Google ties that to editor access on the script project itself. (Google for Developers)

---

Failing 'Pending'

Robyn
Basic test: filled out the form, all fields on the workbook filled out including "Pending" for the Status field. Changed "Pending" to "Approved". No entry written to the Calendar, no note appears in the Processing Note field.

---

Emily
🎯 That result points very strongly to one thing, Robyn: onApprovalEdit(e) is not firing at all. 🌷 Because if processApprovalRow_() were firing and failing, you would usually get something:

• an execution record
• an error
• a conflict note
• a changed Processing Note
• or at least a partial side effect

But you got nothing at all when changing Pending to Approved. Since the form submission successfully set Pending, yourform-submit trigger is working. The missing piece is almost certainly the edit trigger. What to check first: go to Apps Script → Triggers and confirm there is an entry exactly like this:

• Function: onApprovalEdit
• Event source: From spreadsheet
• Event type: On edit

If that trigger is missing, or points to an old function name, then changing Status will do exactly what you saw: nothing. Put this at the very top of onApprovalEdit(e):

  function onApprovalEdit(e) {
    logAction_('INFO', 'onApprovalEdit fired', {
      sheet: e && e.range ? e.range.getSheet().getName() : '',
      row: e && e.range ? e.range.getRow() : '',
      col: e && e.range ? e.range.getColumn() : '',
      value: e && e.value ? e.value : '',
      oldValue: e && e.oldValue ? e.oldValue : ''
    });

    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 headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const editedHeader = String(headerRow[col - 1] || '').trim();

    const headers = getHeaders_(sheet);

    if (editedHeader === CONFIG.HEADERS.ASSIGNED_ROOM) {
      handleAssignedRoomEdit_(e, sheet, row, headers);
      return;
    }

    const statusCol = headers[CONFIG.HEADERS.STATUS];
    if (!statusCol) {
      throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
    }

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

1. save the script
2. edit a Status cell from Pending to Approved
3. look for a new row in System_Log

If System_Log gets a new line, Good. That means the trigger is firing, and the problem is inside the approval path. If System_Log stays unchanged, that means the edit trigger is not firing. And then the fix is operational, not code-related:

• recreate the installable trigger
• or correct it if it points somewhere else

Also check Executions: After changing Status, open Executions in Apps Script. You should see an attempt to run onApprovalEdit.

• No execution listed → trigger not firing
• Execution listed with failure → trigger is firing, and we inspect the error

My read from what you’ve told me is that at this moment, the most likely situation is:

• setPendingOnFormSubmit trigger exists and works ✅
• onApprovalEdit trigger is missing, stale, or not attached to this copied script project ❗

That would perfectly match your symptoms. Do those two checks next:

1. Triggers page
2. logAction_() at top of onApprovalEdit(e)

Then tell me whether System_Log got a new entry.

---

Triggers

Robyn
From the Triggers Page:

Owned by
Last run
Deployment
Event
Function
Error rate
Me  6 Apr 2026, 11:39:13    Head    From spreadsheet - On edit  onApprovalEdit  
100%
Me  -   Head    From spreadsheet - On form submit   setPendingOnFormSubmit  
-

and then:

Created a new booking:
06/04/2026 11:47:10 INFO    Booking submitted   {"fullName":"Celeste Shelley","email":"celestesgrl@proton.me","courseName":"Checking onApprovalEdit()","room":"Studio","eventDate":"2026-04-10","startTime":"08:00","endTime":"12:00","recurring":"No"}

Changed "Pending" to Approved:
06/04/2026 11:49:04 INFO    onApprovalEdit fired    {"sheet":"WebForm_Submissions","row":17,"col":12,"value":"Approved","oldValue":"Pending"}

---

Emily
🌺 Excellent — that tells us a lot. 💛🌷 The good news is:

• the on edit trigger exists
• onApprovalEdit(e) is firing
• your logging proves it

So the earlier suspicion was wrong now that we have evidence. The problem is inside the approval path after the trigger fires. And this line from the Triggers page is the giveaway: Error rate: 100% for onApprovalEdit. That means the function is running and then throwing an error every time. Your edit reached:

  onApprovalEdit(e)

and because you changed column 12, Status, it should then have reached:

  handleStatusEdit_(sheet, row, headers, e)

and then:

  processApprovalRow_(sheet, row, headers)

So now we’re no longer asking “is the trigger firing?” It is. We’re asking: what inside the approval chain is crashing? The first thing I want you to check: open Executions in Apps Script and click the failed run for onApprovalEdit. That should show the exact error message. That exact message is now the fastest path.

My strongest suspicion from the full script you pasted is based on the fact I do not see a definition for:

  combineDateAndTime_()

in the script you gave me. But processApprovalRow_() calls it here:

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

So if that helper does not exist in the actual script project, then the error would be something like: ReferenceError: combineDateAndTime_ is not defined. And that would absolutely produce the behaviour you saw:

• trigger fires
• no calendar write
• no note
• error rate 100%

because the function dies before it can finish. Less likely, but still possible: bookingDate, startTime, or endTime are in a shape your date-time combiner can’t handle. But that would only matter if combineDateAndTime_() exists. Right now, the missing helper is the louder suspect.

1. Check whether this function exists anywhere in the script:

  function combineDateAndTime_(dateValue, timeValue) {

If it does not exist, that is almost certainly the fault.

1. Open the failed execution and read the exact error. That will confirm it.

---

If you do not already have combineDateAndTime_(), add this

  function combineDateAndTime_(dateValue, timeValue) {
    if (!dateValue || !timeValue) return null;

    const date = new Date(dateValue);
    if (isNaN(date.getTime())) return null;

    let hours, minutes;

    if (timeValue instanceof Date) {
      hours = timeValue.getHours();
      minutes = timeValue.getMinutes();
    } else {
      const timeText = String(timeValue).trim();
      const match = timeText.match(/^(\d{1,2}):(\d{2})$/);
      if (!match) return null;

      hours = Number(match[1]);
      minutes = Number(match[2]);
    }

    const combined = new Date(date);
    combined.setHours(hours, minutes, 0, 0);
    return combined;
  }

That version handles either actual Sheets time values as Date objects or "08:00" style strings, which is handy for your setup. I think this is the likely one because your current script includes these helpers:

• normalizeRoom_()
• getHeaders_()
• valueByHeader_()
• trim_()
• getTargetCalendar_()

…but in the full paste, there is no combineDateAndTime_(). That gap stands out. Please check either:

• the Executions error message, or
• whether combineDateAndTime_() exists in the script project

My bet is that we’ve just found the missing piece.

---

ReferenceError

Robyn
ReferenceError: combineDateAndTime_ is not defined
at processApprovalRow_(SheetFunct:219:17)
at handleStatusEdit_(SheetFunct:94:5)
at onApprovalEdit(SheetFunct:75:5)

---

Emily
🤍😄 ⚠️ ✅ 🤍
There we are — found it. 🌷 That is the problem exactly:

ReferenceError: combineDateAndTime_ is not defined

So your trigger chain is fine, your approval logic is being reached, and it is crashing at the point where it tries to build the start and end datetime values. Paste this into your script with the other helper functions:

  function combineDateAndTime_(dateValue, timeValue) {
    if (!dateValue || !timeValue) return null;

    const date = new Date(dateValue);
    if (isNaN(date.getTime())) return null;

    let hours;
    let minutes;

    if (timeValue instanceof Date) {
      hours = timeValue.getHours();
      minutes = timeValue.getMinutes();
    } else {
      const timeText = String(timeValue).trim();
      const match = timeText.match(/^(\d{1,2}):(\d{2})$/);
      if (!match) return null;

      hours = Number(match[1]);
      minutes = Number(match[2]);
    }

    const combined = new Date(date);
    combined.setHours(hours, minutes, 0, 0);
    return combined;
  }

This version is a good fit because it handles both cases:

• time stored as a real Sheets time/date object
• time stored as a string like "08:00"

That makes it robust for your form workflow. After adding it:

1. Save the script
2. Go back to your test booking row
3. Change Status to Approved again

Because the row is still sitting there ready, this should now let the approval path continue properly. One of these should happen:

• success: Calendar event created, Event ID filled, Processing Note written or
• controlled failure: for example a conflict note or invalid time note

But it should no longer fail with that silent “nothing happened” pattern. Once you confirm it works, you can leave the log line in onApprovalEdit(e) for a little while longer during testing, then remove it later if you want the System_Log less chatty. You’ve done the hard part now, Robyn. This is exactly the kind of bug that feels mysterious until the execution log finally says, plainly, what’s missing. 💛

---

Event Written! Testing...

Robyn
We have liftoff!

🌷 The event was written to the calendar and the processing note was written. 🌷

---

Emily
✨🌿 That’s a beautiful moment, Robyn. 🚀 You’ve just crossed the line from “system under construction” to “system working end-to-end” — and not in a trivial way either. This is a proper workflow now:

• form → sheet ✅
• status logic → approval pipeline ✅
• approval → calendar event creation ✅
• processing note → feedback loop ✅

That’s a real system. You now have a single-source-of-truth booking system with controlled state transitions and external side effects (Calendar) managed safely. That’s not beginner scripting — that’s solid application design. Before we declare victory, I’d run these quickly:

1. Cancellation test

Approved → Cancelled

Expect:

• calendar event deleted
• Processing Note updated
• Event ID cleared

---

2. Assigned Room change

(your Column O work)

Take your approved booking and set Assigned Room = Gallery

Expect:

• status → Pending
• calendar event removed
• Processing Note explains why

Then:

• change Status back to Approved

Expect:

• new event created in Gallery

---

3. Conflict test

• create another booking in same room/time
• try to approve

Expect:

• status → Conflict
• Processing Note explains conflict
• no calendar event created

---

Your success note currently says: Calendar event created successfully in Studio. Since you now have Assigned Room logic, you might later tweak that to: Calendar event created successfully in ${room}. (which you already do — so you’re actually ahead here 👍)

---

At this point, what remains is not core logic anymore — it’s polish:

• recurring bookings (v2)
• nicer Processing Notes
• maybe a “reconciliation” safety function later

But the backbone? It’s done. And it works. If you’d like, next we can:

• sanity-check cancellation behaviour (quick)
• or do a tiny refinement pass so your tester experience is rock-solid

But for now — take the win. This was a proper build. 💛

---

Cancellation test

Robyn
1. Cancellation test... Change:
Approved → Cancelled

Expect: calendar event deleted-YES
Processing Note updated-YES
Event ID cleared-YES

BTW, my tester (currently trying the form out) brought up a point: she said (and I can see her booking here on the sheet) that bookings can be made for dates in the past.

---

Emily
😄✨ 💙 🌊 🌿 🌺 🌙 💙 Excellent — cancellation is behaving exactly as it should. 🎉🌷 And your tester has found a very real issue: right now, nothing is stopping someone from submitting a booking for a date in the past. That is worth fixing. You have two possible layers:

1. Front-end HTML form validation: Best for user experience. This stops the user before submission and gives immediate feedback.
2. Server-side Apps Script validation: Best for safety. This ensures that even if someone bypasses the HTML or something odd gets submitted, the booking still won’t be accepted.

My recommendation is to do both.

• HTML layer = friendly
• Apps Script layer = trustworthy

That gives you proper defence in depth.

---

Server-side check first, because that is the most important one. In processApprovalRow_(), after you create start, add a check to reject bookings whose start datetime is in the past. Add after:

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

Add this:

  const now = new Date();

  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue(
      'Booking start time is in the past.'
    );
    return;
  }

I prefer checking start, not just bookingDate, because a booking for today might still be valid if the start time is later.

Examples:

• today at 4:00 pm, current time 10:00 am → valid
• today at 8:00 am, current time 10:00 am → invalid

So checking the full datetime is the right logic. Also good: block it in the HTML form if your form uses:

    <input type="date" name="eventDate">

then you can set its min value to today.

Example JavaScript in the HTML page:

    <script>
    document.addEventListener('DOMContentLoaded', () => {
        const dateInput = document.querySelector('input[name="eventDate"]');
        if (!dateInput) return;

        const today = new Date();
        const yyyy = today.getFullYear();
        const mm = String(today.getMonth() + 1).padStart(2, '0');
        const dd = String(today.getDate()).padStart(2, '0');

        dateInput.min = `${yyyy}-${mm}-${dd}`;
    });
    </script>

That prevents choosing earlier dates in most browsers. The nicest final behaviour on the form: the user cannot pick a past date.

On approval:

If somehow a past booking still gets through, approval changes it to Conflict with: Booking start time is in the past. That’s a good clean message.
A tiny wording suggestion: instead of Conflict, you may later decide this is more naturally Rejected. But for now, leaving it as Conflict is perfectly acceptable if that’s how your system flags invalid approval attempts.

---

My recommendation for right now is to so the server-side fix first, because it is the real protection. Add this block:

  const now = new Date();

  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue(
      'Booking start time is in the past.'
    );
    return;
  }

immediately after:

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

Then, once that’s done, we can add the front-end min date restriction neatly.

---

Changes, Server-side

Robyn
I've made the changes server-side. Here's the current section of the html page.

<script>
  const form = document.getElementById('bookingForm');
  const messageBox = document.getElementById('message');
  const recurringSelect = document.getElementById('recurring');
  const frequencyField = document.getElementById('frequencyField');
  const repeatUntilField = document.getElementById('repeatUntilField');
  const frequencyInput = document.getElementById('frequency');
  const repeatUntilInput = document.getElementById('repeatUntil');

  function showMessage(text, type) {
    messageBox.textContent = text;
    messageBox.className = `message ${type}`;
  }

  function clearMessage() {
    messageBox.textContent = '';
    messageBox.className = 'message';
  }

  function timeToMinutes(timeStr) {
    const [hours, minutes] = timeStr.split(':').map(Number);
    return hours * 60 + minutes;
  }

  function toggleRecurringFields() {
    const isRecurring = recurringSelect.value === 'Yes';

    frequencyField.classList.toggle('hidden', !isRecurring);
    repeatUntilField.classList.toggle('hidden', !isRecurring);

    frequencyInput.required = isRecurring;
    repeatUntilInput.required = isRecurring;

    if (!isRecurring) {
      frequencyInput.value = '';
      repeatUntilInput.value = '';
    }
  }

  recurringSelect.addEventListener('change', toggleRecurringFields);
  toggleRecurringFields();

  form.addEventListener('submit', function (e) {
    e.preventDefault();
    clearMessage();

    const formData = {
      fullName: form.fullName.value.trim(),
      email: form.email.value.trim(),
      courseName: form.courseName.value.trim(),
      room: form.room.value,
      eventDate: form.eventDate.value,
      startTime: form.startTime.value,
      endTime: form.endTime.value,
      recurring: form.recurring.value,
      frequency: form.frequency.value,
      repeatUntil: form.repeatUntil.value
    };

    if (
      !formData.fullName ||
      !formData.email ||
      !formData.courseName ||
      !formData.room ||
      !formData.eventDate ||
      !formData.startTime ||
      !formData.endTime ||
      !formData.recurring
    ) {
      showMessage('Please complete all required fields.', 'error');
      return;
    }

    if (
      timeToMinutes(formData.startTime) % 30 !== 0 ||
      timeToMinutes(formData.endTime) % 30 !== 0
    ) {
      showMessage('Please use 30-minute increments only.', 'error');
      return;
    }

    if (timeToMinutes(formData.endTime) <= timeToMinutes(formData.startTime)) {
      showMessage('End time must be later than start time.', 'error');
      return;
    }

    if (formData.recurring === 'Yes') {
      if (!formData.frequency || !formData.repeatUntil) {
        showMessage('Please complete the recurring booking fields.', 'error');
        return;
      }

      if (formData.repeatUntil < formData.eventDate) {
        showMessage('Repeat Until must be on or after the Event Date.', 'error');
        return;
      }
    }

    google.script.run
      .withSuccessHandler(function (response) {
        showMessage(response.message, 'success');
        form.reset();
        toggleRecurringFields();
      })
      .withFailureHandler(function (error) {
        showMessage(error.message || 'Something went wrong.', 'error');
      })
      .submitBooking(formData);
  });
  </script>

Where would you add that 'min' value code?

---

Emily
Right near the top of that script, after you grab the form elements. 😊🌷 You’ll want a reference to the date input, then set its min when the page loads. Add this new constant with the others, putting it just below your other const lines:

    <script>
    const form = document.getElementById('bookingForm');
    const messageBox = document.getElementById('message');
    const recurringSelect = document.getElementById('recurring');
    const frequencyField = document.getElementById('frequencyField');
    const repeatUntilField = document.getElementById('repeatUntilField');
    const frequencyInput = document.getElementById('frequency');
    const repeatUntilInput = document.getElementById('repeatUntil');
    const eventDateInput = document.getElementById('eventDate');

Then add a small helper function. I’d place it just after timeToMinutes():

  function setMinimumEventDate() {
    const today = new Date();
    const yyyy = today.getFullYear();
    const mm = String(today.getMonth() + 1).padStart(2, '0');
    const dd = String(today.getDate()).padStart(2, '0');

    eventDateInput.min = `${yyyy}-${mm}-${dd}`;
  }

Then call it when the page loads, right after:

  recurringSelect.addEventListener('change', toggleRecurringFields);
  toggleRecurringFields();

add:

  setMinimumEventDate();

---

So that section becomes:

  recurringSelect.addEventListener('change', toggleRecurringFields);
  toggleRecurringFields();
  setMinimumEventDate();

---

So the flow is:

1. declare eventDateInput
2. define setMinimumEventDate()
3. call setMinimumEventDate() during setup

---

One small but worthwhile extra validation: even with min, I’d also add a front-end check in the submit handler, because browser controls can sometimes be bypassed. Add this just before the recurring check would be a good place:

  const today = new Date();
  today.setHours(0, 0, 0, 0);

  const selectedDate = new Date(formData.eventDate);
  selectedDate.setHours(0, 0, 0, 0);

  if (selectedDate < today) {
    showMessage('Event Date cannot be in the past.', 'error');
    return;
  }

That way you have:

• min on the date picker
• explicit front-end validation
• server-side protection too

That’s a very sturdy setup. so, add the min helper:

• after your utility functions
• call it during startup
• optionally add the explicit submit check too

If you want, I can paste back your entire updated <script> block with those additions already slotted in.

---

ReferenceError 2

Robyn
Just now tried to 'Approved' a recurring booking, and got the error:

ReferenceError: Cannot access 'start' before initialization at processApprovalRow_(SheetFunct:192:3) at handleStatusEdit_(SheetFunct:94:5) at onApprovalEdit(SheetFunct:75:5)

---

Emily
✨ Yes — that error tells us exactly what happened. 🌷 You’ve almost certainly added the new “past date” check before this line:

  const start = combineDateAndTime_(bookingDate, startTime);

So JavaScript hits something like:

  if (start < now) {

before start has been declared. That produces: Cannot access 'start' before initialization because const variables are not usable before the line where they are defined. The fix is to make sure the order in processApprovalRow_() is:

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

  const now = new Date();

  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue(
      'Booking start time is in the past.'
    );
    return;
  }

It should go after:

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

and ideally after your invalid-date check too, so you don’t compare null or bad dates. So the clean version is:

  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, procNoteCol).setValue('Invalid date/time values.');
    return;
  }

  const now = new Date();

  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue(
      'Booking start time is in the past.'
    );
    return;
  }

This order is best because it checks things in sensible sequence:

1. build start and end
2. confirm they are valid dates
3. then compare start to now

That avoids both cannot access before initialization and weird comparisons against invalid date values. One small note about recurring bookings: this error is not really about recurrence itself. It only showed up there because approving that row happened to pass through the same broken line order. So the recurring booking merely exposed the bug; it didn’t create a special new one. Move that past-date block lower, save, and test again.

---

Fix Try and Fail

Robyn
Actually, I fixed it this way... just before that trap-for-dates-in-the-past code, I had:

  const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
  const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);

so I changed the code to read like this:

  const now = new Date();
    if (startTime < now) {
      sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
      sheet.getRange(row, procNoteCol).setValue(
        'Booking start time is in the past.'
      );
      return;
    }

---

Emily
❤️ 🌿 That “fix” won’t behave correctly, I’m afraid. 🌷 startTime is only the time-of-day field, not the full booking datetime. In your function it likely holds either:

• a string like "08:00", or
• a Sheets time value

Either way, comparing it directly to:

  const now = new Date();

is the wrong comparison. It’s wrong because a booking being “in the past” depends on:

• the date
• and the time

For example:

• booking date = 10 April 2026
• start time = 08:00

That is not past just because "08:00" is earlier than the current clock time on 6 April 2026.

You need to compare booking date + start time combined against now. That’s exactly what start is for. The correct logic is to use:

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

Then, after validating those dates, do:

  const now = new Date();

  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue(
      'Booking start time is in the past.'
    );
    return;
  }

This is the correct sequence:

  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, procNoteCol).setValue('Invalid date/time values.');
    return;
  }

  const now = new Date();

  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue(
      'Booking start time is in the past.'
    );
    return;
  }

In plain English, your current version is asking: “Is 08:00 earlier than right now?” But what you need is: “Is 10 April 2026 at 08:00 earlier than right now?” That’s a very different question. 🙂 So I would undo that startTime < now change and move the past-date check to use start after combineDateAndTime_() has run.

---

Next Go

Robyn
Look right to you now?

  // * trap for dates in the past
  const start = combineDateAndTime_(bookingDate, startTime);
  const end = combineDateAndTime_(bookingDate, endTime);
  const now = new Date();
  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue(
      'Booking start time is in the past.'
    );
    return;
  }
  // * end past date trap

---

Emily
🔥 🏆 Almost — one small but important adjustment. 🌷 You should validate that start and end are real dates before comparing start to now. So the better order is:

  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, procNoteCol).setValue('Invalid date/time values.');
    return;
  }

  const now = new Date();
  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue(
      'Booking start time is in the past.'
    );
    return;
  }

This order matters for this reason: if combineDateAndTime_() returns null or an invalid date for some reason, then:

  if (start < now)

becomes unreliable or misleading. So the safe sequence is:

1. build start and end
2. check they’re valid
3. only then compare start to now

So your snippet is:

• conceptually right ✅
• just slightly too early in the flow ⚠️

Put it after the invalid-date check, and you’ll be on solid ground.

---

[Q]
So, now on line 240 & 241, I'll need to remove:

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

---

Yes — exactly. 👍🌷 If you have now moved:

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

up into the new “past date trap” section, then you must remove the old duplicate pair later in the function.

Otherwise you’ll get duplicate declarations of const start and duplicate declarations of const end and JavaScript will not like that one bit. So the clean structure should be: in processApprovalRow_() you want one place only where these appear:

  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, procNoteCol).setValue('Invalid date/time values.');
    return;
  }

  const now = new Date();
  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue(
      'Booking start time is in the past.'
    );
    return;
  }

Then later in the function, when you do:

• if (end <= start)
• calendar.getEvents(start, end)
• calendar.createEvent(title, start, end, ...)

you simply use those same already-defined start and end variables. So yes: remove the later duplicate lines on 240 and 241. That is the right cleanup. A quick sanity check: not quite right yet, but the fixes are small. 🌷 The main problem is that in your new “past date trap,” you use statusCol and procNoteCol before those constants are declared. In the current function, the trap comes first, and only later do you define:

  const statusCol = headers[CONFIG.HEADERS.STATUS];
  const eventIdCol = headers[CONFIG.HEADERS.CALENDAR_EVENT_ID];
  const procNoteCol = headers[CONFIG.HEADERS.PROCESSING_NOTE];

So this block will fail as written:

  if (start < now) {
    sheet.getRange(row, statusCol).setValue(...)
    sheet.getRange(row, procNoteCol).setValue(...)
  }

because statusCol and procNoteCol do not exist yet at that point. You can see that ordering clearly in the function you pasted.

You also still have a duplicate invalid-date check later in the function. It is not fatal, but it is redundant, because you already validate start and end in the new trap section and then do it again further down. That duplication is also visible in the pasted function. The clean fix is:

1. Move these declarations up so they happen before the past-date trap:

  const statusCol = headers[CONFIG.HEADERS.STATUS];
  const eventIdCol = headers[CONFIG.HEADERS.CALENDAR_EVENT_ID];
  const procNoteCol = headers[CONFIG.HEADERS.PROCESSING_NOTE];

1. Keep only one invalid-date check.

2. Keep only one start / end pair, which you’ve already done by commenting out the old duplicate lines.

Here is the shape I’d use for the middle of the function:

  const recurring = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.RECURRING));
  const frequency = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.FREQUENCY));
  const repeatUntil = valueByHeader_(rowValues, headers, CONFIG.HEADERS.REPEAT_UNTIL);
  const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.CALENDAR_EVENT_ID));

  const statusCol = headers[CONFIG.HEADERS.STATUS];
  const eventIdCol = headers[CONFIG.HEADERS.CALENDAR_EVENT_ID];
  const procNoteCol = headers[CONFIG.HEADERS.PROCESSING_NOTE];

  if (!statusCol) throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
  if (!eventIdCol) throw new Error(`Missing header: ${CONFIG.HEADERS.CALENDAR_EVENT_ID}`);
  if (!procNoteCol) throw new Error(`Missing header: ${CONFIG.HEADERS.PROCESSING_NOTE}`);

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

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

  if (!bookingDate || !startTime || !endTime) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).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, procNoteCol).setValue('Invalid date/time values.');
    return;
  }

  const now = new Date();
  if (start < now) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
    sheet.getRange(row, procNoteCol).setValue('Booking start time is in the past.');
    return;
  }

After that, leave:

• if (end <= start)
• recurring validation
• overlap check
• createEvent

as they already are.

So: good direction, one ordering bug, one redundant block. Once you move statusCol/eventIdCol/procNoteCol above the trap and remove the second invalid-date check, this function should be tidy.

---

Yes — here is a clean, corrected version of the whole function you can drop in as-is 🌷

💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛💛
Process Approval Row Function

What this version fixes

• uses Assigned Room correctly
• checks for missing headers before using them
• combines date and time once only
• validates date/time before comparing against now
• blocks bookings in the past
• keeps your existing conflict and calendar creation logic intact
• avoids the duplicate checks and ordering issues that were tripping you up

One note: this still treats recurring bookings as single calendar events for now, with the note saying recurring logic is not yet implemented. That matches the structure you’ve been building.

Once you paste this in, save it and test:

• normal approval
• past-date approval
• Assigned Room change on an approved booking
• recurring booking approval

That should give you a much calmer base to work from 💛

---

/exec Question

Robyn
Quick question: if the tester is using a form where the URL ends in a /exec and I make code changes to the html, does the user simply refresh the page or do I need to re-deploy?

---

Emily
🌿 For a /exec URL, you need to re-deploy to a new version for the tester to see HTML changes. A simple browser refresh is not enough, because /exec serves a versioned deployment, not your latest saved code. Google’s docs say head deployments always sync to the current code, while versioned deployments are tied to a specific project version; to update an existing deployment, you create a new version and edit the deployment to point to it, keeping the same URL. (Google for Developers) The practical rule is:

• /exec: save code → Deploy > Manage deployments > Edit > New version > Deploy → then the tester can refresh and see the new HTML. (Google for Developers)
• /dev: save code → refresh is enough, because the head deployment tracks the latest saved code. Google explicitly says the head deployment always syncs to the most recently saved code and is for testing, not public use. (Google for Developers)

So in your case, since the tester is using /exec, the answer is: yes, re-deploy. After that, she can refresh the page and get the updated form.

---

[Q] And doing a test redeployment will do?

---

Yes — for your own testing, a test deployment is enough. 😊 Google says head deployments always sync to the most recently saved code and are intended for testing, while versioned deployments are for public use. (Google for Developers) So the practical split is:

• You testing latest changes: use the test/head deployment (/dev) and refresh after saving. (Google for Developers)
• Tester using the public /exec link: you need to update the versioned deployment to a new version. That keeps the same URL but points it at the new code. (Google for Developers)

So, yes, a test deployment will do for you. and no, it will not update what your tester sees on /exec. The one-line rule is:
/dev for you, /exec for her. 🌷

---

[Q] So, for the /exec version, it has to be "New Deployment"?

---

Great question — and this is where Google’s wording trips everyone up a bit. 😊🌷 Short answer:

• ❌ No — you do NOT need “New Deployment”
• ✅ You just need “New version” on the existing deployment

The correct workflow for your /exec URL. Go to:

Deploy → Manage deployments

Then:

1. Find your existing Web app deployment
2. Click the ✏️ Edit (pencil)
3. In the Version section choose “New version”
4. Click Deploy

This:

• Keeps the same /exec URL ✅
• Updates it to use your latest code ✅
• Your tester just refreshes the page and sees the changes ✅

When would you use “New Deployment”? Only if you want:

• a completely different URL
• multiple environments (e.g. dev vs prod)
• or to experiment without touching the current live version

Otherwise, it just creates confusion. Think of it like this:

• Deployment = the URL
• Version = the code snapshot behind that URL

So you don’t replace the URL — you update what’s behind it. You already have /exec → Version 1 (old HTML). After doing Edit → New version → Deploy, you get /exec → Version 2 (new HTML). Same link, new behaviour. So your answer is: Use **Edit → New version**, not “New Deployment.”

If you want, I can also show you a tiny naming convention for versions (e.g. v1.2 – date validation + assigned room) so you don’t lose track as this grows. 🌷

---

[Q] When I click on new Version, it just increments the version name regardless of what I enter.

---

Yes — that’s normal. 😊 Google auto-increments the version number itself. What you type is just the description/label, not the numeric version ID. So seeing it advance to Version 2, Version 3, and so on regardless of your wording is expected. For the new thread, here’s the clean carry-forward:

Current state

1. Booking app is now working end-to-end:

2. form submission writes to WebForm_Submissions

3. Status is set to Pending
4. changing Pending → Approved triggers onApprovalEdit
5. calendar event is created
6. Processing Note is written
7. cancellation test passed:

So:

1. Assigned Room / Column O workflow has been integrated:

2. onApprovalEdit(e) routes Assigned Room edits to handleAssignedRoomEdit_()

3. if an approved booking’s effective room changes, live event is removed and status resets to Pending
4. processApprovalRow_() uses Assigned Room if present, otherwise Room
5. combineDateAndTime_() was missing and caused approval failure; now identified and added
6. normalizeRoom_() was also added

7. RESPONSE_SHEET_NAME became obsolete after removing the separate Admin sheet; setPendingOnFormSubmit(e) was simplified to use CONFIG.SHEET_NAME

8. Important fixes discovered

9. onApprovalEdit trigger is present and firing

10. earlier “nothing happens” bug was due to combineDateAndTime_ is not defined

11. later recurring approval bug was due to bad ordering around the past-date check

12. I provided a full corrected processApprovalRow_() function that:

13. validates headers first

14. derives effective room correctly
15. checks missing fields
16. combines date+time once
17. validates start/end
18. rejects bookings in the past
19. checks end > start
20. validates recurring fields
21. checks room conflicts

22. creates the event and writes note

23. HTML / web app

24. This is not a Google Form; it is an Apps Script HTML web app

25. /exec uses a versioned deployment
26. /dev / test deployment uses latest saved code

27. For testers on /exec, HTML/code changes require:

28. Deploy → Manage deployments → Edit existing deployment → New version → Deploy

29. not “New Deployment”

30. Google auto-increments the numeric version regardless of the description you type; your text is just the label/description

31. Latest front-end improvement. We discussed blocking past dates in the HTML form by:

32. setting min on the eventDate input

33. adding a front-end validation check

That code had not yet been fully slotted into the final script block at the point this thread ended

1. Likely next steps: confirm the pasted full corrected processApprovalRow_() is now in place and working

Also, re-test:

• normal approval
• recurring approval
• assigned room change after approval
• past date rejection

We need to add the HTML min date + submit-time past-date validation and decide whether to keep Execute as: User accessing the web app or switch to Me for the web app deployment.

---

Next Thread

Robyn

See Summary above

---

Emily
🌿 Perfect 🌷 That gives us a clean handoff. We’re in good shape. The main unfinished piece is the front-end past-date guard in the HTML web app, so let’s tackle that directly. What you want is two layers:

1. the date picker should prevent choosing dates before today
2. submit-time validation should still catch it, just in case

Here’s the pattern to add into your HTML/JS.

1. Set the min value on page load. If your date field looks something like:

    <input type="date" id="eventDate" name="eventDate" required>

then in your script block add:

    <script>
    document.addEventListener('DOMContentLoaded', () => {
        const eventDateInput = document.getElementById('eventDate');
        if (eventDateInput) {
        const today = new Date();
        const yyyy = today.getFullYear();
        const mm = String(today.getMonth() + 1).padStart(2, '0');
        const dd = String(today.getDate()).padStart(2, '0');
        eventDateInput.min = `${yyyy}-${mm}-${dd}`;
        }
    });
    </script>

That handles the picker itself.

---

1. Add a submit-time validation check. Inside your form submit handler, before the data is sent to Apps Script, add this:

  const bookingDateValue = document.getElementById('eventDate').value;

  if (bookingDateValue) {
    const today = new Date();
    today.setHours(0, 0, 0, 0);

    const selectedDate = new Date(bookingDateValue + 'T00:00:00');

    if (selectedDate < today) {
      alert('Please choose today or a future date.');
      return;
    }
  }

That way, even if someone somehow bypasses the picker restriction, the form still refuses submission.

---

1. If you want, here’s the integrated shape. Assuming your form has an ID like bookingForm, the script would look roughly like this:

<script>
document.addEventListener('DOMContentLoaded', () => {
    const form = document.getElementById('bookingForm');
    const eventDateInput = document.getElementById('eventDate');

    if (eventDateInput) {
    const today = new Date();
    const yyyy = today.getFullYear();
    const mm = String(today.getMonth() + 1).padStart(2, '0');
    const dd = String(today.getDate()).padStart(2, '0');
    eventDateInput.min = `${yyyy}-${mm}-${dd}`;
    }

    form.addEventListener('submit', (e) => {
    e.preventDefault();

    const bookingDateValue = eventDateInput.value;

    if (bookingDateValue) {
        const today = new Date();
        today.setHours(0, 0, 0, 0);

        const selectedDate = new Date(bookingDateValue + 'T00:00:00');

        if (selectedDate < today) {
        alert('Please choose today or a future date.');
        return;
        }
    }

    const formData = Object.fromEntries(new FormData(form).entries());

    google.script.run
        .withSuccessHandler((response) => {
        alert('Booking submitted successfully.');
        form.reset();
        })
        .withFailureHandler((error) => {
        alert('Error: ' + error.message);
        })
        .submitBooking(formData);
    });
});
</script>

---

Because this is an Apps Script HTML web app on /exec, once you update the HTML file you’ll need to:

Deploy →
Manage deployments →
Edit existing deployment →
New version →
Deploy

Then your tester refreshes the /exec page.

---

Your next testing order should be:

1. redeploy with the HTML date guard
2. test selecting a past date in the form
3. test a normal future booking
4. test recurring booking again
5. test changing Assigned Room on an approved booking

That should give you a very solid v1 confidence check. 😊 Paste me your current HTML script block when you’re ready, and I’ll slot this into the exact right place instead of leaving you to wrestle with it.

---

[Q] So, there is no way I and my tester could both be running the /dev version of the form?

---

Yes, you both can use the /dev version 🙂 — with one important catch: the /dev URL always serves the latest saved code from the script project, not a frozen version. So in practice:

• if you and your tester both open the same /dev link, you are both seeing the current development build
• if you save a change, /dev updates to that newest code
• then both of you will be testing whatever is currently saved when the page is loaded/refreshed

That means /dev is fine for shared testing, but it is a bit slippery because:

• you can accidentally change the ground under your tester’s feet
• if she reloads after you saved something new, she may no longer be testing the same version she started with

So the real distinction is:

• /dev = shared live draft, always moving
• /exec = stable snapshot, tied to a deployment version

For calm, controlled tester use:

• use /exec when you want both of you testing the same fixed build
• use /dev only when you’re comfortable with it changing as you work

So the answer is not “no, impossible” — it’s more yes, you both can, but it won’t stay stable unless nobody changes the code. 🌷 A very practical way to think of it:

• /dev = workbench
• /exec = released test copy

If you like, I can also explain the cleanest workflow for you developing while one tester uses stable /exec, because that’s usually the least headache.

---

[Q] The problem is this: if she identifies a problem and I fix it, I have to redeploy and send her the new /exec link. She knows new code is being introduced. I might ask her which is easier for her... working with a stable workbase or using live code (/dev).

---

That’s exactly the trade-off, yes. 🌷 And your instinct is good: asking her which workflow she prefers is probably the smartest move. In plain terms:

Stable /exec

• she tests against a fixed version
• if she finds a bug, you fix it, redeploy, and give her the updated /exec link
• that makes testing cleaner, because everyone knows which build a bug belongs to
• but it adds a bit of friction each time

Live /dev

• she keeps one link
• you fix something, save, and she refreshes
• much easier logistically
• but a little messier, because the app may have changed between one test and the next

So it comes down to what kind of tester she is. If she is:

• methodical and likes certainty, /exec is better
• relaxed and tech-comfortable and happy to retest quickly, /dev may be easier

What I’d gently recommend for your situation is this: use /dev during active bug-fixing sessions, when you and she are going back and forth quickly. Then, once things feel solid, switch to /exec for final confirmation testing. That gives you the best of both:

• fast iteration while ironing out bugs
• stable verification at the end

Also, one small but useful correction: you do not necessarily need to send a brand-new /exec link every time. In many Apps Script deployments, if you edit the existing deployment and create a new version, the deployed web app URL itself often stays the same. What changes is the code behind that deployment version. So she may be able to keep using the same /exec URL and simply refresh after you redeploy. The thing to watch is whether you are updating the existing deployment versus creating a wholly separate one. So the practical message to her could be something like:

"I can do this either way: keep you on a stable test version, or let you use the live dev version so fixes appear as soon as I save. Stable is cleaner for bug tracking; live is easier if we’re going back and forth quickly. Which would you find less annoying?"

That wording keeps it simple and lets her choose the style that suits her best. 😊

---

Past-Date Fixes

Robyn
So, I'm testing the past-date thing on the /exec version. Put in a date in the past. Upon submit, the page went white (empty). The URL shows:

https://n-ceoqqh5pvqizir2bovqgd3pdw6xtnp36lvz5kqi-0lu-script.googleusercontent.com/userCodeAppPanel?fullName=Robyn+Sveil&email=robynsveil%40gmail.com&courseName=Testing+Past+Dates&room=Gallery&eventDate=2026-04-04&startTime=08%3A00&endTime=12%3A00&recurring=No&frequency=&repeatUntil=

Executions: event not showing.

---

Emily
That white page is a very strong clue. 😊 It means the browser did a normal HTML form submission instead of your JavaScript intercepting it. The giveaway is the URL itself:

...?fullName=...&email=...&courseName=...

That only happens when the form submits in the ordinary browser way, usually as a GET request. If your JavaScript submit handler had run properly, you would not be dumped onto a blank page with all the fields in the URL.

So this likely means one of these is true:

1. the submit handler is not attached to the form
2. the form ID in JavaScript does not match the real form ID in HTML
3. there is a JavaScript error before e.preventDefault() can run
4. the deployed /exec version does not actually contain the latest script you thought it did

The past-date validation itself probably is not what caused the white page. More likely:

• the form submitted normally
• Apps Script web app received the GET request
• there was no proper route/output for that kind of submission
• so you got the empty white page

And because the execution log shows nothing, that fits too: your google.script.run.submitBooking(...) probably never fired. Look at your form tag. It probably has something like:

    <form id="bookingForm">

Then look at the script. It must be targeting that exact ID:

  const form = document.getElementById('bookingForm');

If your HTML says id="booking-request-form" and your JS says bookingForm, the handler won’t attach.

Then, make sure your script is actually doing:

  form.addEventListener('submit', (e) => {
    e.preventDefault();
    ...
  });

If that listener never attaches, normal browser submission happens.

---

Then, open browser dev tools on the form page and look for a JavaScript error in Console. A single error earlier in the script can prevent the submit handler from ever being registered. For example, if this fails:

  const eventDateInput = document.getElementById('eventDate');

because the ID is wrong and later code touches eventDateInput.value, the script can die before the submit handler is wired up. My strongest guess is:

• your updated HTML/JS did not correctly hook into the real form element in the deployed /exec
• so the browser fell back to ordinary form submission

A very quick test: add this temporary line right at the top of your submit handler:

  alert('submit handler reached');

Like this:

  form.addEventListener('submit', (e) => {
    alert('submit handler reached');
    e.preventDefault();
    ...
  });

Then redeploy /exec and test again.

Outcomes:

• if you see the alert, the handler is running, and the problem is inside the handler
• if you do not see the alert, the handler is not attached, or the new code is not actually deployed

Another very likely issue would arise if your form tag includes an action= attribute, especially something pointing back to the web app, that can contribute to this kind of behaviour. For an Apps Script HTML web app using google.script.run, you generally want JavaScript to control submission, not the browser. So the form should be something simple like:

    <form id="bookingForm">

not something like:

    <form id="bookingForm" method="get" action="">

or similar. Paste me just these two pieces:

1. your <form ...> opening tag
2. your entire current <script>...</script> block

and I’ll pinpoint exactly why it’s bypassing the handler 🌷

---

[Q]
My Script is different:

  form.addEventListener('submit', function (e) {
    e.preventDefault();
    clearMessage();

---

That part is fine. 🌷

  form.addEventListener('submit', function (e) {
    e.preventDefault();
    clearMessage();

is perfectly valid. So the issue is probably not that you used function (e) instead of an arrow function. The real question becomes: did that code actually run at all? Because if it had run, e.preventDefault() should have stopped the browser from dumping you onto that white page with all the form fields in the URL. So we’re back to the likely culprits:

Most likely possibilities

1. form is null If this line earlier in the script is wrong:

const form = document.getElementById('bookingForm');

and your real form ID is something else, then the listener never attaches.

2. the script crashes before it reaches addEventListener For example, something above that line errors out, so the submit handler is never registered.

3. the /exec deployment does not contain the code you think it does Very possible with Apps Script if the HTML was saved but not deployed into the version you’re testing.

---

Tiny diagnostic that will tell us a lot

Right before the addEventListener, add:

console.log('form is', form);

And inside the submit handler add:

console.log('submit handler fired');

So:

console.log('form is', form);

form.addEventListener('submit', function (e) {
  console.log('submit handler fired');
  e.preventDefault();
  clearMessage();

Then test again.

What that tells you

• if form is null appears, wrong ID
• if form exists but submit handler fired never appears, the listener didn’t attach properly or wrong form is being submitted
• if both logs appear, then the problem is further down inside the handler

---

Even quicker test

Put this at the very top of the handler:

alert('handler fired');

like:

form.addEventListener('submit', function (e) {
  alert('handler fired');
  e.preventDefault();
  clearMessage();

If you do not see the alert, that handler is not running. Full stop.

---

Since the browser submitted the form normally and stuck all the fields into the URL, I suspect one of these:

• the form ID in JS doesn’t match the HTML form ID
• there are two forms and you attached the listener to the wrong one
• the deployed /exec version is older than the code you’re looking at

---

Check your opening form tag for me. Something like:

<form id="bookingForm">

Also check whether the submit button is actually inside that form.

Because if the button sits outside it, or uses form="someOtherFormId", then your listener may be attached to one form while another is being submitted.

Paste me:

• the opening <form ...> tag
• the line where form is defined
• the submit button line
• a bit of the script above the listener

and I’ll narrow it down properly 😊

---

getElementById

Robyn

  const form = document.getElementById('bookingForm'); 

1st line of the script.

---

Emily
💙 🌿 Good — that narrows it down nicely 😊

If this is the first line of the script:

  const form = document.getElementById('bookingForm');

then the most likely issue is now this: the script is running before the form exists in the DOM. If the script block sits in the <head> or above the actual form markup, then when that line runs, the browser hasn’t built the form yet, so:

  form === null

Then the event listener never attaches, and the browser falls back to ordinary form submission — which matches your white-page result exactly. That would also explain why the page submits as ...?fullName=...&email=...&courseName=... instead of staying on the page and using google.script.run. Why this happens: JavaScript executes top to bottom as the page loads. So if the browser sees:

    <script>
    const form = document.getElementById('bookingForm');
    form.addEventListener(...)
    </script>

before it has parsed:

    <form id="bookingForm">

then getElementById('bookingForm') returns null.

---

The fix is to wrap your setup code in DOMContentLoaded, like this:

    <script>
    document.addEventListener('DOMContentLoaded', function () {
        const form = document.getElementById('bookingForm');
        const eventDateInput = document.getElementById('eventDate');

        if (eventDateInput) {
        const today = new Date();
        const yyyy = today.getFullYear();
        const mm = String(today.getMonth() + 1).padStart(2, '0');
        const dd = String(today.getDate()).padStart(2, '0');
        eventDateInput.min = `${yyyy}-${mm}-${dd}`;
        }

        form.addEventListener('submit', function (e) {
        e.preventDefault();
        clearMessage();

        const bookingDateValue = eventDateInput.value;
        if (bookingDateValue) {
            const today = new Date();
            today.setHours(0, 0, 0, 0);

            const selectedDate = new Date(bookingDateValue + 'T00:00:00');

            if (selectedDate < today) {
            showMessage('Please choose today or a future date.', true);
            return;
            }
        }

        const formData = Object.fromEntries(new FormData(form).entries());

        google.script.run
            .withSuccessHandler(function (response) {
            showMessage('Booking submitted successfully.', false);
            form.reset();
            })
            .withFailureHandler(function (error) {
            showMessage(error.message || 'Something went wrong.', true);
            })
            .submitBooking(formData);
        });
    });
    </script>

---

1. Guard against form being missing. You can make it a bit safer by putting the following just after getting the form:

  if (!form) {
    console.error('bookingForm not found');
    return;
  }

1. If your script is already at the very bottom of the HTML, then this might not be the issue — but with Apps Script HTML, wrapping in DOMContentLoaded is still the cleanest and safest approach.

---

Change your script so the whole setup is inside:

  document.addEventListener('DOMContentLoaded', function () {
    ...
  });

Then:

1. save
2. redeploy the existing /exec deployment with a new version
3. refresh the /exec page
4. test the past date again

You should no longer get the white page. Instead, the validation message should appear on the form page itself. 🌷 If you want, paste your current full script block and I’ll rewrite it exactly as it should sit in your HTML.

---

Current Form (HTML)

Robyn

💙💙💙💙💙💙💙💙💙💙💙💙💙💙💙💙💙💙💙💙💙💙
The Current HTML

---

Emily
Found it 🌷

The script is breaking before submit because setMinimumEventDate() uses eventDateInput, but that variable is never defined anywhere in the script. You define form, messageBox, recurringSelect, frequencyField, repeatUntilField, frequencyInput, and repeatUntilInput, then call setMinimumEventDate(), which tries to run eventDateInput.min = ... even though there is no const eventDateInput = ... line. That’s visible in your pasted HTML here: the eventDate field exists in the form, but the script never captures it, and setMinimumEventDate() still uses it. That means the script throws a JavaScript error at page load on this line sequence:

• function setMinimumEventDate() { ... eventDateInput.min = ... }
• then later setMinimumEventDate();
• so anything after that, including your submit handler, is unreliable because the script has already crashed.

The fix is to add this line with your other DOM lookups:

  const eventDateInput = document.getElementById('eventDate');

So the top of your script becomes:

    <script>
    const form = document.getElementById('bookingForm');
    const messageBox = document.getElementById('message');
    const recurringSelect = document.getElementById('recurring');
    const frequencyField = document.getElementById('frequencyField');
    const repeatUntilField = document.getElementById('repeatUntilField');
    const frequencyInput = document.getElementById('frequency');
    const repeatUntilInput = document.getElementById('repeatUntil');
    const eventDateInput = document.getElementById('eventDate');

Then add the actual past-date validation in Submit. Right now you only set the picker minimum, but you do not yet validate eventDate during submit. Your current submit handler checks required fields, 30-minute increments, end-after-start, and recurring logic, but there is no past-date check yet. Add this after the required-fields block:

    const today = new Date();
    today.setHours(0, 0, 0, 0);

    const selectedDate = new Date(formData.eventDate + 'T00:00:00');

    if (selectedDate < today) {
      showMessage('Please choose today or a future date.', 'error');
      return;
    }

Here is your script block with both fixes applied:

✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨
Corrected javascript in HTML

The page went white because the script error prevented your JS workflow from fully controlling the form, the browser fell back to normal submission behaviour, which is why you saw the blank page and the URL full of query-string values. Once eventDateInput is defined and the script loads cleanly, that should stop. The next test order should be:

1. add const eventDateInput = document.getElementById('eventDate');
2. add the submit-time past-date check
3. save
4. redeploy the existing /exec deployment with a new version
5. refresh the page and test again

The expected result is: no white page, and an in-page red error message saying the date must be today or later. 😊 Your uploaded HTML is here too, in case you want to reopen it quickly.

---

Entire Script

Robyn
In order to prevent my screwing things up again, please paste me the entire script. I'm inadvertently putting code where it doesn't belong, for some reason.

---

Emily
🤍 1️⃣2️⃣3️⃣ 🎙️✨ 4️⃣ 5️⃣ 6️⃣ 🎙️✨
Absolutely — let’s make this clean and bulletproof so you can just drop it in and move on. 😊

Below is your complete <script> block, correctly structured, with:

• ✅ eventDateInput defined properly
• ✅ minimum date set safely
• ✅ past-date validation on submit
• ✅ no ordering issues
• ✅ nothing floating outside where it shouldn’t be

---

✅ Replace your entire <script> with this:

🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍
Final Entire Script

---

Why this version is safe:

• Everything waits for the page to load → no missing elements
• No undefined variables → no silent crashes
• Validation happens before submission → no white screen
• UI resets cleanly after submit

---

Your next step (simple, no thinking required):

1. Replace your <script> with this one
2. Save
3. Deploy → Manage deployments → Edit → New version → Deploy
4. Refresh /exec
5. Try a past date

You should now get a clean red message on the page, not that ghostly white void. 😄 If anything still behaves oddly after this, we’re past “mystery bug” territory — we’ll be down to something very specific and easy to squash.

---

[Q]
Okay, now the calendar picker has greyed out past dates. And *manually entering a past date shows a popup on [Submit] that says "Value must be 06/04/2026 or later. So, we're heading them off at the pass.

---

That’s excellent. 😄🌷 That means the browser is now doing exactly what we wanted:

• the date picker UI blocks past dates visually
• manual typing is also blocked by the input’s min constraint
• the form is being intercepted properly instead of wandering off into white-page oblivion

So yes — you’re now stopping the problem before it can even get to Apps Script, which is ideal. What this means practically is that your front end is now enforcing:

• no past booking dates
• no accidental ordinary browser submission
• cleaner tester experience
• fewer junk rows reaching the sheet

That is a nice little win. ✅ One subtle point: the browser popup saying: Value must be 06/04/2026 or later is native HTML validation, not your custom showMessage(...) box. That’s perfectly fine — honestly, for this case it’s probably better. It’s immediate and hard to miss. So at this point, your custom JS past-date check is almost a backup layer rather than the main defence.

This part of v1 sounds solid:

• form submits properly
• approval path works
• cancellation works
• assigned-room reset logic works
• past-date blocking now works on the form itself

That’s a lot of ground covered. 😊

---

Sensible Next Tests

I’d now move to a quick regression sweep:

1. normal future booking
2. recurring future booking
3. approved booking → change assigned room
4. cancel approved booking
5. end time earlier than start time
6. recurring = yes, but missing repeat fields

If all of those behave, you’re getting very close to “safe enough for real humans” rather than just “works in theory.” And lovely catch, by the way — this was one of those tiny front-end issues that can make everything look haunted until the right line lands.

---

A Conflicting Room

Robyn
So, I've started testing of the conflicting room setup. Wasn't there some logic for setting Status from 'Conflict' back to 'Pending'?

---

Emily
🤍📚 Yes — there was. 🌷 The intended logic was:

• if a booking is in Conflict
• and the bookings manager changes something that could resolve it, especially the Assigned Room
• then the row should be re-evaluated
• and if the conflict is no longer real, Status should move back to Pending
• so it can then be approved normally

There are really two slightly different cases:

1. Conflict discovered at approval time. That happens when someone changes Status to Approved, but the room/time is already taken. Then the script sets:

2. Status = Conflict

3. Processing Note = ...already booked

4. Later edit that may resolve the conflict. That is where the “back to Pending” logic belongs. From our earlier structure, the most likely place was in the handler for edits to the effective room, meaning handleAssignedRoomEdit_(), and possibly also when date/time fields are edited. The idea was:

5. if the row is currently Conflict

6. and the user changes Assigned Room, Event Date, Start Time, or End Time
7. then the script should clear the stale conflict state
8. and set the row back to Pending
9. because the old conflict diagnosis may no longer be true

A important distinction to make here is that the reset usually should not happen just because the status is Conflict sitting there by itself. It should happen when one of the conflict-driving fields changes. Those are usually:

• Assigned Room
• Room
• Event Date
• Start Time
• End Time

The behaviour you probably want

If a row is currently Conflict, and the user changes Assigned Room to another room, then:

• re-check whether conflict still exists in the new effective room
• if conflict still exists, keep Conflict and update the note
• if conflict is gone, change Status to Pending and update the note accordingly

That is cleaner than blindly setting Conflict → Pending, because otherwise you can mark it pending even though it still clashes. So the answer is: Yes, there should be logic for returning "Conflict" to "Pending", but only when a relevant edit means the conflict may have been resolved. If you want, I can reconstruct that exact handler for you now — including the logic for:

• current status = Conflict
• Assigned Room edited
• recheck clash
• keep Conflict if still clashing
• otherwise set Pending

That’s probably the next clean step.

---

handleAssignedRoomEdit()

Robyn
It looks like it was meant to be handled here, at the end of handleAssignedRoomEdit():

  removeLiveCalendarEvent_(
    sheet,
    row,
    headers,
    `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
    CONFIG.STATUS_VALUES.PENDING
  );

---

Emily
😄🧱🔄🧠🧨🧭😄 Yes — that line is for a different case. 😊 That call:

  removeLiveCalendarEvent_(
    sheet,
    row,
    headers,
    `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
    CONFIG.STATUS_VALUES.PENDING
  );

is really aimed at:

• booking was already Approved
• therefore it may already have a live calendar event
• assigned room changed
• remove the old live event
• reset status to Pending

So it makes perfect sense for Approved → Pending. But it does not really cover Conflict → Pending properly, because in a conflict case:

• there usually is no live calendar event to remove
• the point is not cleanup of a live booking
• the point is to reassess whether the conflict still exists

So if you were expecting a conflicted booking to go back to Pending, this snippet by itself would not reliably do that unless handleAssignedRoomEdit_() explicitly branches for conflict rows too. In other words, you likely need logic that looks conceptually like this:

  if (oldStatus === CONFIG.STATUS_VALUES.APPROVED) {
    removeLiveCalendarEvent_(... PENDING ...);
    return;
  }

  if (oldStatus === CONFIG.STATUS_VALUES.CONFLICT) {
    // re-check whether the edited room/date/time still conflicts
    // if yes: keep CONFLICT and update note
    // if no: set PENDING and update note
    return;
  }

This matters because Conflict is not just a state to clear blindly. It is a diagnosis. So on Assigned Room edit, the script should:

• work out the new effective room
• check whether that room still clashes at that date/time
• if it still clashes, leave Status = Conflict
• if it no longer clashes, set Status = Pending

That’s the missing piece. Most likely conclusion: your memory is right that Conflict back to Pending was intended, but the bit you’ve quoted is specifically the approved-booking reset path, not the conflict-resolution path. What I’d add inside handleAssignedRoomEdit_(), something along these lines:

  if (oldStatus === CONFIG.STATUS_VALUES.CONFLICT) {
    const statusCol = findHeaderColumn_(headers, CONFIG.HEADERS.STATUS);
    const noteCol = findHeaderColumn_(headers, CONFIG.HEADERS.PROCESSING_NOTE);

    const effectiveRoom = newEffectiveRoom;
    const bookingDate = valueByHeader_(sheet.getRange(row, 1, 1, headers.length).getValues()[0], headers, CONFIG.HEADERS.EVENT_DATE);
    const startTime = valueByHeader_(sheet.getRange(row, 1, 1, headers.length).getValues()[0], headers, CONFIG.HEADERS.START_TIME);
    const endTime = valueByHeader_(sheet.getRange(row, 1, 1, headers.length).getValues()[0], headers, CONFIG.HEADERS.END_TIME);

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

    const sameRoomConflict = findConflictingBooking_(
      sheet,
      row,
      headers,
      effectiveRoom,
      start,
      end
    );

    if (sameRoomConflict) {
      sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
      sheet.getRange(row, noteCol).setValue(
        `Conflict still exists: ${effectiveRoom} is already booked for that time.`
      );
    } else {
      sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.PENDING);
      sheet.getRange(row, noteCol).setValue(
        `Assigned room changed to ${effectiveRoom}. Conflict cleared; booking reset to Pending for re-approval.`
      );
    }

    return;
  }

The exact helper names may need to match your current script, but that is the right shape. The short version of the diagnosis is: that line is not wrong. It is just for Approved rows, not Conflict rows. So yes — you’ve found why your conflict test is not behaving the way you expected. 🌷 Paste me your current handleAssignedRoomEdit_() and I’ll rewrite the whole function properly so it handles both:

• Approved → Pending with calendar cleanup
• Conflict → Pending only if the conflict is actually resolved

---

  function handleAssignedRoomEdit_(e, sheet, row, headers) {
    const oldValue = String(e.oldValue || '').trim();
    const newValue = String(e.value || '').trim();

    // If nothing materially changed, do nothing
    if (oldValue === newValue) return;

    const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
    const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
    const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);

    const oldEffectiveRoom = oldValue || requestedRoom;
    const newEffectiveRoom = newValue || requestedRoom;

    // If effective room did not actually change, do nothing
    if (oldEffectiveRoom === newEffectiveRoom) return;

    // Only reset if the booking was already approved
    if (status !== CONFIG.STATUS_VALUES.APPROVED) return;

    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
      CONFIG.STATUS_VALUES.PENDING
    );
  }

---

Yes — there it is. And this confirms the issue exactly. 🌷 Your current function only handles Approved rows:

  // Only reset if the booking was already approved
  if (status !== CONFIG.STATUS_VALUES.APPROVED) return;

So for a row in Conflict, it just exits and does nothing. That means if:

• a booking was marked Conflict
• then you changed Assigned Room
• even if that change should resolve the clash

…the function never re-checks the conflict and never moves it back to Pending. Here is the corrected full function, which handles both cases:

• Approved: remove live calendar event and reset to Pending
• Conflict: re-check whether the conflict still exists in the new effective room
• if yes, keep Conflict
• if no, set Pending

🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍
Handle Assigned Room v2

One important thing to note, here, is... I used this helper name:

  hasRoomConflict_(
    sheet,
    row,
    headers,
    newEffectiveRoom,
    start,
    end
  )

That helper may not be the exact name in your project. Earlier, your code used this kind of logic in approval:

  const sameRoomConflict = ...

So you may already have a helper with a different name, such as:

• findConflictingBooking_()
• hasConflictingBooking_()
• checkRoomConflict_()

If hasRoomConflict_() does not exist in your script, swap that line to the real helper name you already use for approval-time conflict checking. If you want the safest version for your project, use the exact same helper that processApprovalRow_() already uses to detect room conflicts. That way approval logic and reassessment logic both use the same test.

What changed conceptually is this. Before:

• Approved rows were handled
• Conflict rows were ignored

Now:

• Approved rows still behave as before
• Conflict rows are re-evaluated when Assigned Room changes

That should give you the behaviour you remembered 😊

If you paste the conflict-check portion of processApprovalRow_(), I’ll swap in the exact helper name so you can paste this function in without any guessing.

---

Here's what we did before:

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

    if (!assignedRoomCol) return;

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

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

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

    if (previousEffectiveRoom === currentEffectiveRoom) return;

    // If the booking is live, changing room invalidates approval and removes the event.
    if (currentStatus === CONFIG.STATUS_VALUES.APPROVED) {
      removeLiveCalendarEvent_(
        sheet,
        row,
        headers,
        `Assigned Room changed from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}; approval invalidated and returned to Pending.`,
        CONFIG.STATUS_VALUES.PENDING
      );
      return;
    }

    // If the booking is currently in conflict, update the note so the manager knows
    // the new room choice has been recorded, but still needs re-approval to test.
    if (currentStatus === CONFIG.STATUS_VALUES.CONFLICT) {
      sheet.getRange(row, noteCol).setValue(
        `Assigned Room changed from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}. Re-approve to test availability for the new room.`
      );
      return;
    }

    // For Pending / Rejected / Cancelled, just note the room change.
    sheet.getRange(row, noteCol).setValue(
      `Assigned Room updated from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}.`
    );
  }

---

Yes — this older version shows the original intent very clearly. 😊🌷 At that stage, the Conflict behaviour was:

• do not automatically change Status
• just update the note to say: the assigned room changed and the manager should re-approve to test the new room

So the logic then was:

• Approved → invalidate live booking, remove event, reset to Pending
• Conflict → leave as Conflict, but tell the manager to re-approve
• Pending / Rejected / Cancelled → just note the room change

That means the earlier idea was not auto-reset Conflict to Pending. It was more conservative: We’ve recorded the room change; now the manager must re-approve to re-test availability. And honestly, that is quite sensible. It avoids silently changing state when the clash may or may not actually be resolved. So what changed? Your newer simplified function lost that middle branch entirely. Old version had:

  if (currentStatus === CONFIG.STATUS_VALUES.CONFLICT) {
    sheet.getRange(row, noteCol).setValue(
      `Assigned Room changed from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}. Re-approve to test availability for the new room.`
    );
    return;
  }

New version effectively says:

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

So for Conflict, it now does nothing at all. I would restore the old Conflict note behaviour unless you specifically want automatic re-testing. Why? Because it is safer and clearer for a bookings manager:

• they see the row is still in Conflict
• they see a note explaining the new room was recorded
• they know they must re-approve to test again

That keeps the workflow explicit. Here is a clean version of handleAssignedRoomEdit_() that preserves that earlier, good logic, but in the style of your current setup:

🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍
Handle Assigned Room v3

I like this version because it makes the workflow very understandable:

• Approved booking changed → live event removed, must be re-approved
• Conflict booking changed → still marked conflict until manager explicitly re-tests
• everything else → just informational note

That feels tidy, predictable, and hard to misuse.

One small caution, I used:

  const noteCol = findHeaderColumn_(headers, CONFIG.HEADERS.PROCESSING_NOTE);

because that matches the style of your newer code better than:

  const noteCol = headers[CONFIG.HEADERS.NOTE];

If your header lookup system is different, and findHeaderColumn_() is not what this script uses elsewhere, swap it to whichever approach the rest of your current project uses. You were remembering correctly that there was conflict logic — but it was not Conflict automatically goes back to Pending. It was: Conflict stays Conflict, and the note tells the manager to re-approve after the room change. Which is probably the safer design. 👍 If you want, I can next give you a version that does the full automatic re-test instead, so you can choose between the two workflows.

---

Leaving Things Alone

Robyn
I might just leave things the way they are. If at some stage someone wants the Status field to return to 'Pending' when the Assigned Room was selected, I'll consider it. In any event, if I could at least have a look at the 'full automatic retest' code, that would be great.

---

Emily
✅ Absolutely. 🌷 Keeping it as-is is a perfectly sensible choice. The current behaviour is conservative, understandable, and less likely to produce surprises for the bookings manager. But here’s the full automatic re-test version so you can inspect it and keep it in reserve.

When Assigned Room changes:

Case 1. if the booking is Approved, remove the live calendar event and reset to Pending
Case 2. if the booking is Conflict:

• immediately re-test the new effective room against existing bookings
• if the clash is gone, set Status to Pending
• if the clash remains, keep Status as Conflict

• update Processing Note either way

• if the booking is Pending / Rejected / Cancelled, just update the note

---

Full automatic re-test version

🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍🤍
Handle Assigned Room v4

---

The one thing you’d need to check is this line:

  const sameRoomConflict = hasRoomConflict_(

uses a helper name I cannot guarantee matches your project exactly. You should replace hasRoomConflict_ with whatever helper processApprovalRow_() already uses to check room clashes. Common possibilities are things like:

  `findConflictingBooking_`
  `checkRoomConflict_`
  `hasConflict_`

So the logic is right, but the helper name may need to be swapped to your real one. I would not rush to adopt this version, although this version is cleverer. However, it is also a little less obvious to a human user because:

• changing Assigned Room can silently alter Status
• the manager may not realise the row has been re-evaluated automatically
• debugging later becomes a touch more abstract

Your current approach is more old-fashioned, but very transparent: change room, then re-approve explicitly. That’s often nicer in admin tools. 😊 My recommendation would be to keep your current version live. Save this automatic-retest version in a notes file or comment block for later, in case someone eventually asks for a smoother workflow. That way you don’t introduce extra moving parts before v1 settles.

---

[Q] This helper function you reference here:

  const sameRoomConflict = hasRoomConflict_()

appears to be managed in this block of code:

  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, procNoteCol).setValue(
      `Conflict detected at approval time: ${room} is already booked.`
    );
    return;
  }

Unless I'm mistaken, which is likely.

---

You’re not mistaken — that’s the key point. 😊🌷 What you’ve found suggests there may not be a separate reusable helper at all. The conflict test is probably being done inline inside processApprovalRow_(), using this logic:

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

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

So when I wrote:

  const sameRoomConflict = hasRoomConflict_(...)

that was really a placeholder for: whatever your script already uses to decide whether this room/time clashes. And in your case, that appears to be this inline block, not a named helper. So yes: your reading is correct. Right now the approval logic seems to be:

1. get all calendar events overlapping start / end
2. normalise each event’s location
3. compare that location to the requested/effective room
4. if one matches, set Status = Conflict

That means if you ever wanted automatic re-test inside handleAssignedRoomEdit_(), you’d either:

• extract that overlap-check into a new helper, which is cleaner, or
• duplicate the logic inside handleAssignedRoomEdit_(), which works but is a bit untidy

The better option is to make a helper. If you want this reusable later, I’d strongly recommend creating a helper like this:

  function hasRoomConflict_(calendar, start, end, room) {
    const overlapping = calendar.getEvents(start, end);

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

Then in processApprovalRow_() you replace:

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

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

with:

  const sameRoomConflict = hasRoomConflict_(calendar, start, end, room);

That way the exact same logic can later be reused in handleAssignedRoomEdit_(). That’s nicer because then:

• processApprovalRow_() stays cleaner
• handleAssignedRoomEdit_() can use the same rule
• if you ever change how conflicts are detected, you only change it in one place

If you wanted the automatic-retest version later, then the conflict part inside handleAssignedRoomEdit_() would use something like:

  const calendar = getBookingCalendar_();
  const sameRoomConflict = hasRoomConflict_(calendar, start, end, newEffectiveRoom);

assuming your calendar getter is named something like getBookingCalendar_() or equivalent.

---

So the short answer is:

Yes — that block is effectively the conflict-checking logic now.
No — you don’t appear to already have a standalone helper for it.
And yes — if you ever want automatic re-test, extracting that block into a helper would be the cleanest way forward.

You spotted exactly the right thing. 👍 If you want, paste the relevant chunk of processApprovalRow_() and I’ll refactor it into a helper plus show you the exact edits needed in both places.

---

'Helper' Functions

Robyn
Excellent. Thank you - that is very useful, to know how to write those 'helper' functions. They're sort-of little 'black-boxes'... do one thing, do it well, don't care about the rest of the code, just focus on one little task.

---

Emily
✨ Exactly that — you’ve captured it beautifully. 🌷 A good helper function really is a little contract with the rest of your code: Give me these inputs, and I will give you a reliable answer — nothing more, nothing less. That “black box” idea is what lets everything else stay calm and readable. Helpers matter a lot, especially in your project. You’re already feeling it with this booking system. Without helpers, your main functions become long, fragile and hard to reason about. With helpers, they become:

• readable (“what is happening” instead of “how it happens”)
• easier to debug
• reusable (you just saw that with conflict detection)

---

A really nice way to think about them is: each helper should answer one question. For example:

• hasRoomConflict_(...) → “Does this booking clash with anything?”
• combineDateAndTime_(...) → “Turn these inputs into a proper Date object.”
• normalizeRoom_(...) → “Make room names comparable.”

That’s why they feel like little black boxes — each one answers a single, very clear question.

---

The hidden superpower (you’re already stepping into this) are: helpers let your main logic read almost like English. Instead of:

  const overlapping = calendar.getEvents(start, end);
  const sameRoomConflict = overlapping.some(event => {
    const eventRoom = normalizeRoom_(event.getLocation());
    return eventRoom === room;
  });

You get:

  const sameRoomConflict = hasRoomConflict_(calendar, start, end, room);

Now your code reads like: Check if there is a room conflict. That’s huge for maintainability.

---

A small rule-of-thumb I use (you’ll like this). If you ever see code and think: I need to stop and mentally simulate what this is doing… that’s a candidate for a helper.
Another subtle benefit (very relevant to your project) is: you reduce logic drift. Right now, your conflict logic exists in one place. If you later copy/paste it somewhere else and tweak it slightly, you can end up with:

• approval logic behaving one way
• reassessment logic behaving another way

By extracting:

  hasRoomConflict_()

you guarantee: one rule, one place, consistent behaviour everywhere. You’re already doing this instinctively with things like:

• combineDateAndTime_()
• normalizeRoom_()
• valueByHeader_()

…those are all solid helper patterns. So you’re not learning this from scratch — you’re refining something you’re already doing well. 😊 If you want one gentle next step at some point (no rush), you could extract:

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

into:

  hasRoomConflict_()

purely as a cleanliness upgrade. Not required for v1 — but a nice polish.

---

And I’ll say this quietly but sincerely: you’ve crossed the line from “writing scripts” into designing systems. That moment — when you start thinking in helpers and responsibilities — is where everything gets a lot more powerful (and a lot more enjoyable). 🌷

---

Fix Go 3

Robyn
I'm going to try this.

Here is what I'm going to fix in processApprovalRow(), sending this functionality to that new little helper hasRoomConflict()...

  // fix this
  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, procNoteCol).setValue(
      `Conflict detected at approval time: ${room} is already booked.`
    );
    return;
  }
  // end fix


So, it will read:

  // fixed
  const calendar = getBookingCalendar_();
  const sameRoomConflict = hasRoomConflict_(calendar, start, end, newEffectiveRoom);
  // end fixed

start and end were initialised earlier, calendar was not. Just need to sort how to initialise newEffectiveRoom.

---

Emily
😄🤍 🧱 🛠️ 🧠⚠️ ✂️✨ 💙 💙 Yes — you’re very close. 😊🌷 The one thing to correct is this: inside processApprovalRow_(), you probably do not want newEffectiveRoom, because that name belongs naturally to the Assigned Room edit workflow. In processApprovalRow_(), what you want is simply the effective room for this row at approval time. That is usually:

• Assigned Room if present
• otherwise Room

So the missing piece is not really newEffectiveRoom—it’s what room is this booking actually trying to use right now? So your approval logic should become something like this:

  const calendar = getBookingCalendar_();
  const effectiveRoomRaw = effectiveValue_(
    rowValues,
    headers,
    CONFIG.HEADERS.ASSIGNED_ROOM,
    CONFIG.HEADERS.ROOM
  );
  const room = normalizeRoom_(effectiveRoomRaw);

  const sameRoomConflict = hasRoomConflict_(calendar, start, end, room);

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

And your helper would be

  function hasRoomConflict_(calendar, start, end, room) {
    const overlapping = calendar.getEvents(start, end);

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

newEffectiveRoom is the wrong fit here because newEffectiveRoom implies:

• there was an old room
• and now there is a new room after an edit

That makes sense in: handleAssignedRoomEdit_() but in processApprovalRow_(), you’re not comparing old and new. You’re just asking: What room is this booking using right now? So a name like one of these is better:

• effectiveRoomRaw
• effectiveRoom
• room

My strongest recommendation is to use this pattern:

  const effectiveRoomRaw = effectiveValue_(
    rowValues,
    headers,
    CONFIG.HEADERS.ASSIGNED_ROOM,
    CONFIG.HEADERS.ROOM
  );
  const room = normalizeRoom_(effectiveRoomRaw);

That keeps things very readable:

• effectiveRoomRaw = original chosen value
• room = normalised version used for comparison

One small caution: only do this if getBookingCalendar_() already exists. If it doesn’t, and processApprovalRow_() already has a calendar object available, then don’t introduce a new getter just for the sake of it. Reuse the existing calendar variable if one is already in scope. So the cleanest refactor is usually:

Before

  const overlapping = calendar.getEvents(start, end);
  const sameRoomConflict = overlapping.some(event => {
    const eventRoom = normalizeRoom_(event.getLocation());
    return eventRoom === room;
  });

After

  const sameRoomConflict = hasRoomConflict_(calendar, start, end, room);

That way you’re only changing one thing at a time. You likely do not need newEffectiveRoom in processApprovalRow_(). You need:

• the row’s current effective room
• normalised into room
• then passed into hasRoomConflict_()

So yes — this is the right direction, just with room or effectiveRoom, not newEffectiveRoom. If you want, paste just the top half of processApprovalRow_() and I’ll slot the exact lines into the correct place.

---

[Q]
So, fixed, it should read:

  // fix this
  const overlapping = calendar.getEvents(start, end);
  const sameRoomConflict = overlapping.some(event => {
    const eventRoom = normalizeRoom_(event.getLocation());
    return eventRoom === room;
  });
const sameRoomConflict = hasRoomConflict_(calendar, start, end, room);
  // end fix

---

Almost — but not quite. 😊 You would replace the old block with the new line, not keep both. Right now, what you’ve written has:

• the old const sameRoomConflict = ...
• and then another const sameRoomConflict = ...

That would cause an error because you’re declaring the same constant twice. So this:

  // fix this
  const overlapping = calendar.getEvents(start, end);
  const sameRoomConflict = overlapping.some(event => {
    const eventRoom = normalizeRoom_(event.getLocation());
    return eventRoom === room;
  });
  const sameRoomConflict = hasRoomConflict_(calendar, start, end, room);
  // end fix

becomes this:

  const sameRoomConflict = hasRoomConflict_(calendar, start, end, room);

And then keep your existing if block underneath it:

  const sameRoomConflict = hasRoomConflict_(calendar, start, end, room);

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

So the full replacement is:

Old

  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, procNoteCol).setValue(
      `Conflict detected at approval time: ${room} is already booked.`
    );
    return;
  }

New

  const sameRoomConflict = hasRoomConflict_(calendar, start, end, room);

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

And then add the helper somewhere else in your .gs file

  function hasRoomConflict_(calendar, start, end, room) {
    const overlapping = calendar.getEvents(start, end);

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

That’s the whole point of the helper:

• move the overlap logic out
• keep the approval function cleaner
• preserve exactly the same behaviour 🌷

You’re on the right track. This is just a replace, not an add alongside.

---

[Q] I had it back to front. Thank you.

---

Ahh — easy done. 😄🌷 That’s exactly the kind of slip everyone makes when refactoring. The important part is: you caught it quickly, and your instinct about how to structure it was absolutely right. And what you’ve just done is actually a pretty big step:

• you recognised repeated logic
• you extracted it cleanly
• you preserved behaviour while improving structure

That’s proper engineering thinking, not just scripting. If anything, this is the moment where your codebase starts becoming something you can trust and extend without it turning into spaghetti later.

When you test this:

• behaviour should be identical to before
• but now you’ve unlocked the ability to reuse that logic anywhere (like your Assigned Room re-test idea later)

If anything behaves differently, that’s actually a great signal—it would mean the old logic had some hidden assumption we can now see more clearly. Go give it a run, and if anything even slightly odd pops up, just drop it in here—we’ll chase it down together. 😊

---

Focus on Pretty

Robyn
I'm going to focus on 'pretty for a moment. How would I attach a logo to the form?

---

Emily
🤍 💙 The easiest way is to add an <img> near the top of the form and style it a little. 🌷 The simplest version is to put this just inside your .container, above the <h1>:

  <div class="logo-wrap">
    <img src="YOUR_IMAGE_URL_HERE" alt="Art Society logo" class="logo">
  </div>

Then add this to your <style> block:

    .logo-wrap {
    text-align: center;
    margin-bottom: 16px;
  }

  .logo {
    max-width: 180px;
    height: auto;
    display: inline-block;
  }

So this part:

  <div class="container">
    <h1>Booking Request - Test App</h1>
    <form id="bookingForm">

would become:

  <div class="container">
    <div class="logo-wrap">
      <img src="YOUR_IMAGE_URL_HERE" alt="Art Society logo" class="logo">
    </div>

    <h1>Booking Request - Test App</h1>
    <form id="bookingForm">

---

The important question is: where does the image live? For an Apps Script HTML web app, the image needs a URL the browser can reach. The usual options are:

1. Public image URL. If the logo is already hosted somewhere public, use that URL directly. Example:

  <img src="https://example.com/logo.png" alt="Art Society logo" class="logo">

1. Google Drive image. This can work, but only if the file permissions are set so the image is publicly viewable or accessible to your users. Drive links can be fiddly. A direct-ish format is often:

  https://drive.google.com/uc?export=view&id=FILE_ID

So:

  <img src="https://drive.google.com/uc?export=view&id=YOUR_FILE_ID" alt="Art Society logo" class="logo">

But Drive can sometimes be temperamental with image serving.

1. Base64 embedded image. This is very reliable for a small logo. You embed the image right in the HTML. It looks like:

<img src="data:image/png;base64,AAAA..." alt="Art Society logo" class="logo">

That’s excellent for a small logo because it keeps everything self-contained.

---

My recommendation for your use case, I’d lean toward one of these:

• public hosted image URL if you already have one
• base64 embedded image if you want the form self-contained and dependable

Drive works, but it’s not my favourite for “just make it behave.”

---

If you want it to look a bit more polished, you could also centre the title and make the logo sit nicely above it:

  h1 {
    margin-top: 0;
    font-size: 1.6rem;
    text-align: center;
  }

That usually makes the top of the form feel much more intentional. Nice polished example:

  <div class="container">
    <div class="logo-wrap">
      <img src="YOUR_IMAGE_URL_HERE" alt="Art Society logo" class="logo">
    </div>

    <h1>Booking Request - Test App</h1>

    <form id="bookingForm">

  .logo-wrap {
    text-align: center;
    margin-bottom: 12px;
  }

  .logo {
    max-width: 180px;
    height: auto;
    display: inline-block;
  }

  h1 {
    margin-top: 0;
    font-size: 1.6rem;
    text-align: center;
  }

If you paste me the kind of logo source you have — file on your Mac, Google Drive, website, etc. — I can tell you the neatest way to hook that specific one in.

---

[Q] To do the actual embedding of the photo, what do I have to do in App Script? Add it to Libraries?

---

No — not Libraries, 😊 For a logo image, you do not add it via Apps Script Libraries. Libraries are for importing other people’s/script projects’ code, not image assets. You’ve basically got three ways to do it:

1. Easiest: use an image URL. Nothing special in Apps Script at all. Just put this in your HTML:

  <img src="https://wherever-your-logo-lives/logo.png" alt="Logo" class="logo">

That’s it.

1. Self-contained: embed the image directly in the HTML. Again, nothing special in Apps Script itself. You convert the image to base64, then paste that string into the src:

  <img src="data:image/png;base64,PASTE_THE_LONG_BASE64_STRING_HERE" alt="Logo" class="logo">

So in this method, Apps Script doesn’t “store” the image separately — the image data is literally inside the HTML file. What you actually do is: take your logo file (.png, .jpg, etc.), convert it to base64 and paste the result into the HTML.

---

1. More advanced: serve the image from Drive or from Apps Script. Possible, but more fiddly, and not what I’d recommend for your current stage.

---

The best option for for a small logo, I’d recommend base64 embedding because:

• very reliable
• no permissions headache
• no Drive weirdness
• no separate hosting needed

How to make a base64 version, on your Mac, the quickest way is Terminal. For a PNG:

base64 -i /path/to/your/logo.png

For example:

base64 -i ~/Desktop/logo.png

That will output a huge block of text. Then in your HTML:

  <div class="logo-wrap">
    <img src="data:image/png;base64,PASTE_THE_OUTPUT_HERE" alt="Art Society logo" class="logo">
  </div>

If it’s a JPG, use:

  <img src="data:image/jpeg;base64,PASTE_THE_OUTPUT_HERE" alt="Art Society logo" class="logo">

---

So, in direct answer to your question. You do not add the image to Libraries. You either:

• point the HTML at an image URL, or
• paste the image data directly into the HTML as base64

---

My practical advice, if the logo is modest in size, do this:

1. resize it first if needed
2. convert to base64
3. paste into the <img src="data:image/...">

That’s probably the least annoying path. 🌷 If you want, paste me the logo file type (png or jpg) and where it is on your Mac, and I’ll give you the exact one-line command plus the exact HTML snippet to use.

---

[Q] Oh dear. base64 is a HUGE text dump. That's going to be an issue.

---

It is. 😄 That’s the downside of base64 — wonderfully self-contained, but visually awful to manage. So for your case, I would not use base64 unless the logo is tiny. Better options:

1. Use a normal image URL. This is the cleanest. If the logo can live somewhere public, your HTML stays neat:

  <img src="https://example.com/logo.png" alt="Art Society logo" class="logo">

No giant text blob in your file.

1. Use Google Drive as the host. Upload the logo to Drive, make it viewable, then use the file ID in a direct image URL. The pattern is:

  <img src="https://drive.google.com/uc?export=view&id=FILE_ID" alt="Art Society logo" class="logo">

That keeps your HTML tidy and avoids base64 bloat. My recommendation for you, since this is an internal-ish booking form, I’d try Google Drive hosting first.

Steps

1. Upload the logo image to Google Drive.
2. Open the file’s sharing settings.
3. Set it so the intended users can view it. For easiest testing, “Anyone with the link can view.”
4. Copy the file ID from the Drive URL.

A Drive file URL usually looks like:

  https://drive.google.com/file/d/1AbCdEfGhIjKlMnOpQrStUvWxYz/view?usp=sharing

The file ID is the bit between /d/ and /view:

  1AbCdEfGhIjKlMnOpQrStUvWxYz

Then use:

  <div class="logo-wrap">
    <img src="https://drive.google.com/uc?export=view&id=1AbCdEfGhIjKlMnOpQrStUvWxYz" alt="Art Society logo" class="logo">
  </div>

Add this to your HTML. Inside .container, above the heading:

  <div class="logo-wrap">
    <img src="https://drive.google.com/uc?export=view&id=YOUR_FILE_ID_HERE" alt="Art Society logo" class="logo">
  </div>

And in your CSS:

  .logo-wrap {
    text-align: center;
    margin-bottom: 12px;
  }

  .logo {
    max-width: 180px;
    height: auto;
    display: inline-block;
  }

One small caution: Google Drive image serving sometimes behaves a little oddly with permissions or caching. Usually it works fine, but if it acts up, the next-best route is to host the image somewhere more straightforward. But for now, this is far nicer than stuffing base64 into your HTML. 🌷 If you want, paste me the Drive share link and I’ll convert it into the exact <img> tag for you.

---

[Q] I know! I'll set up a github for the code, and host of from there. No?

---

Yes — that can work, and it’s a much tidier idea than stuffing base64 into your HTML. 😊 The cleanest GitHub-based approach is to put the logo in a repo and serve it either from GitHub Pages or from GitHub’s raw file hosting. GitHub Pages is a static hosting service for HTML/CSS/JS files from a repository, and raw file URLs are also available from raw.githubusercontent.com. (GitHub Docs) For your form, I’d lean this way:

• Best: host the image on GitHub Pages
• Acceptable: use a raw.githubusercontent.com URL for the image
• Less ideal: base64 in the HTML

Why I prefer Pages: it is specifically meant to publish static assets from a repo, and GitHub says you can publish from a branch or /docs folder. (GitHub Docs) So your workflow could be:

1. create a GitHub repo
2. add something like logo.png
3. either enable GitHub Pages for the repo, or use the raw file URL
4. point your form’s <img> tag at that URL

Example if you use GitHub Pages:

  <img src="https://YOUR-USERNAME.github.io/YOUR-REPO/logo.png" alt="Art Society logo" class="logo">

Example if you use raw GitHub content:

  <img src="https://raw.githubusercontent.com/YOUR-USERNAME/YOUR-REPO/main/logo.png" alt="Art Society logo" class="logo">

GitHub’s docs note that raw file content is served from raw.githubusercontent.com, and Pages docs explain that Pages publishes a site from repository content. (GitHub Docs)

A couple of practical cautions:

• If the repo is private, GitHub Pages availability depends on your plan; GitHub documents Pages support for public repos on Free, and for private repos on Pro, Team, Enterprise Cloud, and Enterprise Server. (GitHub Docs)
• If you want a URL that never changes for a specific file version, GitHub recommends using a permalink to a specific commit rather than a branch-tip URL. (GitHub Docs)

My plain recommendation: make a tiny public repo just for form assets or use an existing public repo, enable GitHub Pages, upload the logo, and point your <img> there. That keeps your Apps Script HTML clean and easy to maintain. 🌷 If you’d like, I can give you the exact GitHub repo structure and the final <img> line to paste in.

---

That’s fantastic — nice clean solution too. 😊🌷 You’ve now got:

• a proper asset pipeline (GitHub 👍)
• clean HTML (no base64 monsters lurking)
• and a form that’s starting to look like a real product, not just a tool

That “pretty” pass you’re doing now is actually more important than it feels — it’s what makes people trust the system instinctively. If you feel like continuing that polish phase later, a few low-effort/high-impact tweaks you might enjoy:

• subtle spacing tweaks between fields (breathing room does wonders)
• slightly softer border colours (#ddd instead of #ccc)
• maybe a lighter background for the container header area
• small hover/active feedback on the submit button (you’ve already got a start there)

But honestly — logo in place is the big psychological win. When you’re ready, we can also:

• add a “loading…” state on submit (prevents double-clicks)
• or a confirmation summary before submission

For now though — well earned pause moment. This is shaping up really nicely 🌷

---

Accessing Forms

Robyn
I do have a question. I am currently logged into the Art Society google account. So is my partner. I can access everything. She cannot. Why is that?

---

Emily
🔥 🌿 Ah — this one catches a lot of people out. 😊🌷 It feels like “same account = same access”, but Google doesn’t quite work that way under the hood. Let’s unpack the likely causes.

---

🔑 Most common reason is, even if you’re both “using” the Art Society account, one of these is usually true: ou are actually the owner of the files/script, and she is not.

In Google’s world:

• the owner has full control (Apps Script, triggers, deployments, etc.)
• other users (even editors) can be missing key capabilities

---

🧠 Big gotcha #1 — Apps Script permissions: even if she can see the spreadsheet, she may not have:

• permission to run the script
• permission to create triggers
• permission to deploy web apps

Especially if:

• the script was created under your session
• or the project ownership hasn’t been transferred

---

⚙️ Big gotcha #2 — triggers are user-specific... things like:

• onEdit
• onFormSubmit

are tied to the user who created them

So:

• your triggers → run under your account
• she won’t see or control them unless she creates her own

---

🌐 Big gotcha #3 — web app execution context: in your deployment settings, you chose something like:

• Execute as: Me
• or Execute as: User accessing the web app

If it’s: Execute as: Me :

• everything runs as you
• she doesn’t need backend permissions
• but she still needs access to the files the script touches

If it’s Execute as: User accessing :

• she must have permission to the spreadsheet, the calendar and anything else the script touches. Otherwise things silently fail or don’t appear.

---

👀 Big gotcha #4 — she’s not actually logged in as that account. This happens constantly:

• browser has multiple Google accounts
• script runs under one
• Drive opens under another

Result: “I can’t see anything” / “it doesn’t work for me”

---

🔍 Quick diagnostic checklist

Ask her to check:

1. Which account is active?... top-right avatar in browser
2. Can she open: the spreadsheet? the Apps Script editor?
3. In Apps Script: does she see the code? can she run a function manually?
4. In Triggers: does she see any? (likely: no)

---

🛠️ Likely fix path

Option A — simplest for now

Keep everything as: Execute as: Me and just make sure she has access to the spreadsheet and she uses the /exec form. This is usually best for v1.

---

Option B — shared ownership (later, cleaner)

You can: transfer ownership of the spreadsheet (or script project) or ensure both of you are editors on everything. Have her recreate triggers under her account if needed.

---

💡 My read of your situation: given everything you’ve described, the most likely cause is:

👉 You are effectively the “owner/operator” of the script environment, even if she’s logged into the same account at times.

And/or:

👉 her browser session is not actually using the same Google identity consistently

---

If you want a precise answer, tell me:

• what she can see vs can’t (e.g. “can open sheet but no script”, “form works but nothing logs”, etc.)
• your web app setting (Execute as: Me or User)

…and I’ll pinpoint it exactly.

---

But overall — nothing is “broken” here. You’ve just bumped into one of Google’s classic “permissions are… nuanced” moments 😄

---

Checklist

Robyn
Tell me:

what she can see vs can’t (e.g. “can open sheet but no script”, “form works but nothing logs”, etc.)

She could see the worksheet. But was unable to open App Scripts, even though she was logged in exactly the same as me. I added her as editor and now she can see the form. I'm hoping we won't have to add each tutor once we go live. I'm sort-of stopping for tonight... back tomorrow to finish up. Can you put together a dot-point testing sequence to use tomorrow, please?

---

Emily
🤍😄✅ 😄 🌊 🧭 🌺 🪶 That behaviour actually makes perfect sense now that you’ve described it. 😊🌷 Even though it felt like you were both “in the same account,” Google was treating you as different identities, and Apps Script is stricter than Sheets about that. Adding her as an editor was exactly the right move. 👍 And no — good news — you will not need to add each tutor individually once live (as long as your web app access is set correctly). We’ll lock that in after testing.

---

✅ Clean Testing Sequence for Tomorrow

Here’s a practical, no-nonsense checklist you can work through with your tester. Think of it as: Can this survive real humans? 😄

---

🔹 Phase 1 — Basic Form Behaviour

• [ ] Open /exec link (not /dev)
• [ ] Logo displays correctly
• [ ] Layout looks clean on desktop
• [ ] Try mobile (optional but nice)

Field validation

• [ ] Leave required fields blank → error shown
• [ ] Enter past date → blocked by picker / browser
• [ ] Enter end time earlier than start → error shown
• [ ] Enter non-30-min time → error shown

---

🔹 Phase 2 — Submission Flow

Normal booking

• [ ] Submit valid booking
• [ ] Success message appears
• [ ] Form resets
• [ ] Row appears in sheet
• [ ] Status = Pending

---

🔹 Phase 3 — Approval Workflow

In the sheet:

• [ ] Change Status from Pending → Approved
• [ ] Calendar event is created
• [ ] Processing Note updated
• [ ] Event ID written

---

🔹 Phase 4 — Conflict Detection

Create a clash

• [ ] Create booking A (Approved)
• [ ] Create booking B (same room + overlapping time)
• [ ] Try to approve booking B

Expected:

• [ ] Status becomes Conflict
• [ ] Processing Note explains conflict
• [ ] No calendar event created

---

🔹 Phase 5 — Assigned Room Logic

Resolve conflict

• [ ] Change Assigned Room on conflicted booking

• [ ] Confirm:

• note updates correctly

• status behaves as expected (your current design: stays Conflict)

Approved booking change

• [ ] Take an approved booking
• [ ] Change Assigned Room

Expected:

• [ ] Calendar event deleted
• [ ] Status → Pending
• [ ] Note updated

---

🔹 Phase 6 — Cancellation Flow

• [ ] Change Status → Cancelled on approved booking

Expected:

• [ ] Calendar event removed
• [ ] Event ID cleared
• [ ] Note updated

---

🔹 Phase 7 — Recurring Bookings

Validation

• [ ] Select “Recurring = Yes” without fields → error
• [ ] Enter invalid repeatUntil < eventDate → error

Valid recurring

• [ ] Submit valid recurring booking
• [ ] Approve it
• [ ] Confirm expected behaviour (depending on your implementation)

---

🔹 Phase 8 — Multi-User Reality Check

With your partner:

• [ ] She submits a booking via form
• [ ] You see it in sheet
• [ ] You approve it
• [ ] Calendar updates correctly

---

🔹 Phase 9 — Edge Cases (Quick Sweep)

• [ ] Blank Assigned Room → still uses requested Room
• [ ] Change time after Pending → still valid
• [ ] Change time after Approved → triggers reset (if implemented)
• [ ] Very short booking (30 mins) → works
• [ ] Long booking → works

---

🔹 Phase 10 — Final Deployment Sanity

Before going live:

• [ ] Confirm web app setting:

👉 Execute as: Me 👉 Who has access: Anyone with the link

• [ ] Test form from:

• different browser

• incognito window

---

🌷 One very important reassurance: I'm hoping we won't have to add each tutor once we go live. You won’t — as long as:

• web app = Anyone with the link
• execute as = Me

Then:

• tutors just use the form
• they do NOT need spreadsheet or script access
• everything runs under your authority

---

🌿 Final thought for tomorrow

Don’t try to “perfect” everything. You’re looking for:

• 🔴 things that break
• 🟡 things that confuse
• 🟢 things that feel smooth

If it survives this list without surprises, you’re well into “safe to release” territory.

---

When you’re back tomorrow, we can also:

• tighten UX (loading state, nicer messages)
• or prep a tiny user guide for tutors

For now — you’ve earned the pause 🌷

---