Bookings App v.1a¶

Introduction¶

Robyn
Hi Emily. Much as I'd like to start writing the next scenes (The Deep End), we're going to have to put on our Google Sheets developer hats on today. The Booking App (v.1) is finishing testing and should be ready for Go LIVE by the end of the week. Optimistically.

I've got feature requests already that we'll introduce in v.2. They include: 1) On the form, additional fields to request recurring events (bookings) 2) A Billing Sheet that will harvest truth from the Bookings Calendar

I also have a question (or two): Can the form's fields go horizontally at all or only vertically? Can other web-based forms 'write' to a Google Sheet?

Emily
🌿 Absolutely — developer hats on 👩‍💻🌷

For your two direct questions first:

1) Can Google Form fields go horizontally? Not in the normal “put independent questions side-by-side across the page” sense. Standard Google Forms presents questions in a single vertical flow. What you can do is use certain grid-style question types—for example multiple-choice grid or checkbox grid—which let you arrange responses in rows and columns, but that is for a matrix-style question, not a free layout designer. (Google Help)

2) Can other web-based forms write to a Google Sheet? Yes. There are a few ways to do that. Google itself supports programmatic reading/writing of Sheets via Apps Script, and Apps Script web apps can accept doPost(e) requests, which means an external form or web service can submit data that your script then writes into a spreadsheet. Google also has the Forms API for creating/modifying forms and retrieving responses, though for “some other form writes into Sheets,” Apps Script web app endpoints are often the simpler route. (Google for Developers)

Now, for your v.2 feature ideas ✨

1) Recurring event requests on the form

Yes, this is very doable, and I think it fits your workflow nicely. The cleanest way is usually:

keep the current single-booking fields
add a Recurring booking? question
if Yes, send the user into a recurrence section using “Go to section based on answer”
collect only the recurrence info you truly need

Google Forms supports branching by answer, so you can show the recurrence section only when needed. (Google Help)

For your use case, I’d lean toward these extra fields:

Is this a recurring booking? → Yes / No
Recurrence frequency → Weekly / Fortnightly / Monthly
Repeat on → Monday, Tuesday, etc.
End condition → Until date / Number of occurrences
Start date for series
Final date or number of sessions

For monthly bookings like “first Monday,” Google Forms can collect the user’s intention, but the actual logic to interpret that into calendar events will need to happen in Apps Script. In other words, the form can gather the rule; your script will do the real work.

My strong recommendation for v.2: Do not try to have Google Forms itself “understand” recurrence deeply. Let the form merely capture recurrence instructions, and let the Sheet/App Script turn those into actual booking instances.

That keeps things sane.

A practical design choice would be one of these:

Option A — recurrence request only The form stores the request as a single row, and the admin approves it. Then the script expands it into multiple calendar events and, perhaps, multiple tracking rows.

Option B — generate child rows in the sheet Once approved, the script creates one booking row per occurrence in a hidden or downstream sheet. This is probably better for billing and auditing later.

For your app, I would gently favour Option B because it gives you:

one source row for the request
many generated rows for actual billable occurrences
easier conflict handling
easier cancellation of a single occurrence later

That starts to make the system feel like a real booking engine rather than just a form.

2) Billing sheet harvesting truth from the Bookings Calendar

Yes — but I’d urge a tiny caution here.

Technically, yes, a billing sheet can read calendar events. Apps Script can read Google Calendar and write data into Sheets. Since Apps Script also works directly with Sheets, this is entirely feasible. (Google for Developers)

But from a design standpoint, the phrase “harvest truth from the Bookings Calendar” makes me twitch just a little 😄 because you and I have already bumped into the two sources of truth issue. If the calendar becomes the billing truth, but approval state / room assignment / cancellations / manual edits still live in the sheet, then billing can drift.

So my recommendation would be:

Best architecture¶

Let the Sheet remain the operational source of truth, and let the Calendar be the published schedule.

Then build a Billing sheet from:

approved bookings rows
actual occurrence rows for recurring items
maybe a reconciliation check against Calendar event IDs

When reading Calendar is still useful

Use the Calendar as a verification layer, not the primary source:

did event get created?
was it deleted manually?
does event ID still exist?
does the calendar time still match the sheet?

That would be a very robust v.2 feature: Billing + Reconciliation

So instead of “billing reads truth from calendar,” think:

Bookings sheet = truth Calendar = public operational reflection Billing sheet = derived financial truth, with calendar cross-checks

That will save you grief later.

On the horizontal-fields question, one practical workaround

If what you really want is a more compact form layout, Google Forms is fairly limited. You can:

use short answer questions
use dropdowns
use grids
use sections to reduce visual clutter

But you cannot really build a polished, side-by-side form layout like you can with a custom HTML form or some third-party builders. Google Forms is intentionally opinionated and simple. The official help and question types documentation reflect that basic vertical structure with standard question blocks. (Google Help)

So the honest answer is:

Google Forms: simple, vertical, fast, reliable
Custom web form / Apps Script web app: much more layout control, more development effort

On other web forms writing to Sheets

Yes, and this opens interesting doors for you. Possible sources:

a custom HTML form served by Apps Script web app
a website form on your own domain
automation platforms like Zapier/Make
another app posting JSON or form data to an Apps Script endpoint

Apps Script web apps specifically support browser-accessible apps with doGet(e) / doPost(e), which is exactly the shape you’d use for “external form submits data, script writes to sheet.” (Google for Developers)

So if, down the track, Google Forms starts feeling too cramped for recurring-booking logic, there is a clear migration path:

keep the spreadsheet backend
replace only the front-end form

That’s actually quite a nice evolution path for your project.

Practical Recommendations¶

for v.2 🌼

I’d stage it like this:

v.2a

Add recurrence request fields to the existing Google Form
Use sections/branching
Store recurrence rule in the sheet
Keep admin approval manual

v.2b

On approval, script generates individual occurrence rows
Each occurrence gets its own status / event ID
Billing sheet reads from those occurrence rows

v.2c

Add reconciliation against Calendar
Flag missing, changed, or deleted events

That gives you something scalable without trying to solve every hard problem in one leap.

And my direct answers in one line each:

Horizontal fields? Mostly no, except grid-style questions; Google Forms is fundamentally vertical. (Google Help)
Can other web forms write to Sheets? Yes — very much so, especially via Apps Script web apps or other integrations. (Google for Developers)

I can sketch a concrete v.2 field design for the recurring-booking form next, with suggested question names and the backend logic each one would drive.

Spitballing Ideas¶

Robyn
Here's where the problem arises, Emily. I want to provide the most robust tool that will allow for some of the user's idiosyncrasies. For example...
did an event get created manually in the Calendar?
was an event deleted manually?
does event ID still exist?
does the calendar time still match the sheet?
etc.
These are artists, not disciplined programmers. They use Excel spreadsheets to write fiction. I've got to have a way to at the very least flag discrepancies between the Calendar and the Booking Sheet, for billing purposes.

Another thing: currently, there is a form for new members to fill out (online, on the Art Society website), data from which gets written to an Excel spreadsheet. Whether the form handles that (it should do) or whether, as I suspect, someone would manually transcribe the information into the spreadsheet, I will very likely try to get that form to write directly to a Google Sheets sheet in the future, so we'll be exploring that as well. I might even see about perhaps creating a web-based for and see if I can get it to write to a Google sheet. You up for that?

Emily
🌿 Oh, I am very up for that 😊🌷

And honestly, your instinct here is the right one: with real-world users, especially volunteers and arts-admin people, you do not design for perfect behaviour. You design for drift, workarounds, manual edits, and accidental chaos — then build calm little guardrails around it.

So yes: for billing purposes, I think you should absolutely have a discrepancy-flagging layer between the Booking Sheet and the Calendar. Apps Script can read and update both Google Sheets and Google Calendar, and it can look up events by ID, including against a specific calendar rather than only the default one. (Google for Developers)

Architectural Shift¶

For v.1, “sheet as source of truth” was a very sensible simplification.

For v.2, I would evolve that into this:

Booking Sheet = administrative record Calendar = operational/public schedule Billing Sheet = derived ledger + discrepancy monitor

That way, billing is not blindly trusting either side. It is looking at both and asking:

does the booking row say an event should exist?
does the calendar event actually exist?
do the key fields still match?
if not, what kind of mismatch is it?

That is much more robust for the world you’re actually serving.

Discrepancies to Catch¶

Yes — all of these are feasible to flag:

1) Event was created manually in Calendar

This is harder than the others, but still very manageable.

You would detect this by scanning the Bookings Calendar for events in a relevant date range, then asking:

does this event have a linked Booking row?
does its event ID appear in your sheet?
if not, mark it as Unlinked Calendar Event

That gives the billing/admin person a “something exists in calendar that didn’t come through the booking workflow” warning.

2) Event was deleted manually

Very doable.

If the Booking Sheet holds a stored Calendar Event ID, your script can try to fetch the event by ID from the relevant calendar. If it comes back missing, you flag that booking row as Missing in Calendar. Google’s Apps Script Calendar service supports event lookup by ID, and the docs note that for non-default calendars you should call getEventById() on the specific calendar object. (Google for Developers)

3) Event ID no longer exists

Same mechanism as above.

If the ID is present in the sheet but not resolvable in Calendar, that is a discrepancy state in itself.

4) Calendar time no longer matches the sheet

Also feasible.

You compare the Sheet’s:

date
start time
end time
room/location/title if useful

against the Calendar event’s:

start time
end time
title
location

and flag any mismatch as:

Time mismatch
Date mismatch
Room/location mismatch
Title mismatch

This kind of compare-and-flag workflow is exactly the sort of job Apps Script is good at, since Sheets ranges can be read/written in bulk and Calendar events can be queried and inspected from script. (Google for Developers)

What I’d build for you in v.2

I would not make the Billing sheet merely “harvest truth.”

I would make it do two jobs:

A) Billing extraction

Generate billable rows from approved/completed bookings.

B) Reconciliation / audit

Add a status field that tells the admin whether the booking and the calendar still agree.

Something like:

MATCH
MISSING_IN_CALENDAR
UNLINKED_CALENDAR_EVENT
TIME_MISMATCH
ROOM_MISMATCH
TITLE_MISMATCH
CANCELLED_IN_SHEET_ONLY
MANUALLY_MODIFIED

That gives the non-technical user a very simple surface: green = good amber/red = investigate

A practical design that will survive “artist logic” 😄

I’d suggest a dedicated sheet called something like:

Reconciliation

with columns like:

Booking Row ID
Course Name
Tutor / Member Name
Sheet Date
Sheet Start
Sheet End
Sheet Room
Calendar Event ID
Calendar Found?
Calendar Date
Calendar Start
Calendar End
Calendar Room / Location
Match Status
Notes / Suggested Action
Last Checked

That way, you are not forcing the Bookings sheet itself to carry all the mess. You are letting another sheet calmly report on it.

This also means billing can safely filter to:

only approved bookings
only matched records
or matched + manually approved exceptions

Very helpful for month-end sanity.

My strongest recommendation: add your own stable internal ID

This is important.

Do not rely only on the Calendar Event ID as the identity glue.

Each booking row should also get its own internal booking key, something like:

BK-20260405-014

Then when you create the calendar event, include that key in one or more places:

description
maybe title suffix if needed
maybe a hidden/reference column in the sheet

That way, if a user manually edits the event title, you still have a forensic trail. Even if the event ID changes in some edge case or recurring-series logic gets messy, your booking key gives you another handle.

About recurring events: this matters even more there

Once recurrence arrives, discrepancy-checking becomes even more important.

For recurring bookings, I would strongly favour:

one series request row
one occurrence ledger sheet with one row per actual event instance

because billing wants occurrences, not intentions.

And reconciliation becomes occurrence-by-occurrence:

did the 7 May lesson happen?
is it still in the calendar?
was it moved?
was that single instance deleted?

Google Calendar’s Apps Script services include support for recurring event series as well as individual events, so this is a legitimate path, not a hack. (Google for Developers)

On your future member form writing to Google Sheets

Yes — that future path is very real, and I think it suits you beautifully.

You have two broad ways to do it:

1) Keep using an external web form, then push into Google Sheets

Possible if that form system supports webhooks, Apps Script endpoints, or API integration.

2) Build your own lightweight web form

This is the more powerful route.

Apps Script web apps can expose a browser-accessible URL, and doGet(e) / doPost(e) are the standard entry points. A web app can serve HTML, or act as a service endpoint receiving submissions. Google’s documentation is explicit that web apps use doGet and doPost, and that Content Service can return plain text or structured responses for service-style endpoints. (Google for Developers)

So yes — in future, we could absolutely build:

a simple HTML form
hosted via Apps Script as a web app
posting directly into a Google Sheet
with validation rules you control
and with a nicer layout than standard Google Forms

That would also answer your earlier frustration about vertical-only forms, because once you move to custom HTML, you’re no longer trapped in standard Google Forms layout rules. Google Forms itself is still basically vertical with sections and branching, though it does support section-based routing. (Google Help)

My honest design advice for that future member form

For member registrations, I would probably not use Excel as the master system if you can avoid it.

If the goal is eventually smoother automation, then:

web form
writes to Google Sheet
optional export to Excel for the people who need it

is far sturdier than:

form somewhere
maybe manual transcription
maybe Excel
then maybe later Google

Manual transcription is exactly where data cleanliness goes to die.

So: am I up for it?

Completely 💪🌷

In fact, I think there are really two excellent next-stage projects here:

Project A — Booking reconciliation and billing

Build a mismatch-flagging system so the sheet and calendar can drift a little without silently corrupting billing.

Project B — Custom web form to Google Sheets

Either for member intake first, or later for bookings, once Google Forms starts feeling too rigid.

Both are absolutely within reach.

And I think your instinct is dead right: these users do not need a system that assumes discipline. They need one that quietly notices when reality has wandered off and says, in effect, “hello dear, this one needs a look.” 😊

The most useful next step would be for me to sketch the reconciliation logic and sheet design in concrete terms — columns, statuses, and the compare rules — so you can see exactly how v.2 might behave.

Going with Project B¶

Build A Web Form¶

Robyn
The Art Society has a new Treasurer. She's very switched-on, and it will be for her this billing system will be designed. I would like to have a chat to her before I start coding. v.2 needs, in addition to everything else, a form validation mechanism that will check if the tutor has entered an end time earlier than a start time. For that reason, and also for access to a nicer layout, i'd like to undertake Project B now... that will also allow me to understand how the existing "New Member" web form can easily be used to write directly to a Google Sheet instead of what it's doing now (no idea what is happening at the moment, to be honest). So, where to we start? I've copied all the existing v.1 sheet and form to a Drive folder called v.2. If I understand it correctly, the data the form collected is still stored somewhere on the form. Do I need to disconnect the form in order to clear the v.2 worksheet?

Emily
💛 Yes — the form responses are still stored in the form itself, and the linked spreadsheet is only a destination copy of those responses. So if you copied the v.1 Form and Sheet into a v.2 folder, you need to treat the Form and the response spreadsheet as two separate things. Unlinking the form is possible, and Google Forms also lets you delete all responses from the form itself. (Google Help)

So for your specific question:

No, you do not have to unlink the form just to clear the worksheet. But if you only clear rows in the sheet, the form may still retain its stored responses, which can matter later if you relink or regenerate the response destination. In practice, the cleanest reset for v.2 is usually:

make sure you are working on the copied v.2 form, not the live v.1 form
in the v.2 form, go to Responses → More (⋮)
choose Delete all responses
then either keep or reset the linked spreadsheet destination as needed

Google’s help explicitly documents both Unlink form and Delete all responses from the Responses tab. (Google Help)

My recommendation for you 🌷

Best starting point for v.2

Because you want a proper custom web form with validation and nicer layout, I would not build v.2 by heavily mutating the copied Google Form. I would use the copied folder mostly as a reference archive and start the new front end as an Apps Script web app.

Why this is the right moment:

you want validation such as “end time cannot be earlier than start time”
you want layout freedom beyond the standard vertical Google Form layout
you want to learn the exact path needed for the member form to write directly into Google Sheets
Apps Script web apps are built exactly around doGet(e) / doPost(e), and can serve HTML or receive submissions directly. (Google for Developers)

So where do we start? I’d start in this order:

Freeze v.1

Leave v.1 alone except for live support and tiny bug fixes. No experimental work in that environment.

Reset the v.2 test data cleanly

For the copied v.2 Google Form:

delete all stored responses in the form
decide whether you want the v.2 response sheet kept as a historical test shell, or stripped back

If you want a very clean sandbox, unlink the copied form and then create a fresh destination sheet for it. Google Forms supports unlinking and then selecting a new response destination. (Google Help)

Decide what v.2’s custom form must collect

Before code, define the fields and rules. For example:

Tutor name
Email
Course name
Requested room
Event date
Start time
End time
Recurring? yes/no
If recurring: frequency / weekday / end rule

And validation rules such as:

end time must be later than start time
required fields cannot be blank
recurrence end date must be after first event date
fortnighly/monthly options only appear when relevant

Build a tiny proof-of-concept web app

Not the whole booking app at once. Just a small working loop:

HTML form
submit button
Apps Script receives data
writes one row into a Google Sheet
returns success / failure message

Once that works, everything else becomes layering.

About your validation requirement

This is one of the strongest reasons to move now. With a custom HTML form in Apps Script, you can validate:

in the browser before submit
again on the server after submit

So for example:

browser: “End time must be later than start time”
server: same check again, in case someone bypasses the front end

That double validation is much sturdier than trusting users not to do odd things.

About the existing “New Member” web form

Yes — building Project B now will teach you the exact pattern you’d later use there. There are really only a few possibilities for what that current web form is doing:

emailing someone
posting to some backend/database
being manually transcribed into Excel later
writing to a file/export process you haven’t seen yet

Once you understand the custom form → Apps Script → Google Sheet pathway, you’ll be in a much stronger position to inspect that member form and replace or adapt it.

My answer to your immediate housekeeping question

If this were my setup, I would do this:

For the copied v.2 Google Form

open it
delete all responses from the form
optionally unlink it from its current response sheet
reconnect it only if you still want a Google-Forms-based fallback

Then I would create a new spreadsheet tab or fresh spreadsheet for the custom web app submissions, rather than trying to make the old Form Responses tab serve two masters.

That keeps things clean and reduces the chance of ghost data or weird header issues.

The practical distinction to keep in mind

Deleting rows in the spreadsheet does not necessarily mean the form has forgotten those responses.
Deleting all responses in the form clears the actual stored form responses. Google’s help flow makes that distinction clear by giving separate controls for response destination and response deletion. (Google Help)

What I’d suggest as our first coding target 💡

A tiny v.2 prototype with just these fields:

Full Name
Email
Course Name
Event Date
Start Time
End Time

and these rules:

all required
end time must be after start time
successful submit writes to a sheet named something like WebForm_Submissions

That will let you learn:

Apps Script project setup
HTML form creation
doGet() serving the page
submission handling
writing to Sheets
validation flow

And once that’s working, we add:

room selection
recurring options
admin-friendly formatting
eventually reconciliation and billing hooks

That’s the right starting bite size 🍰

The next move I’d make with you is to map the minimal v.2 prototype structure: spreadsheet tabs, script files, and the first HTML form.

Building The Web App¶

Robyn
All these actions are on a copy (v.2) of the original test Bookings App sheet and form (v.1). I have:

1) cleared all responses from the form, unlinked the form 2) un-hid the Form responses 4 sheet, cleared the table and Cleared the Admin sheet

Ready to start development of the new (tiny) v.2 prototype form with your proposed fields.

Emily
Perfect 😊🌷 That is a clean starting state, and exactly what we want.

So now we begin with the smallest useful version of Project B:

custom web form
nice horizontal-ish layout
validation for end time later than start time
submit into a Google Sheet

We’ll keep this prototype deliberately modest so it works first, then grow it.

What we’ll build first

Spreadsheet side¶

Use your v.2 spreadsheet and create a fresh sheet named:

WebForm_Submissions

Put this header row in row 1:

Timestamp | Full Name | Email | Course Name | Event Date | Start Time | End Time

That gives the script a clean target.

What we’ll create in Apps Script

Inside the script project attached to the v.2 spreadsheet, we’ll add:

Code.gs
Index.html

This first version will:

serve a custom HTML page
show a tidy form
validate required fields
validate that end time is later than start time
write the submission into WebForm_Submissions

Code.gs

Replace the current code with this:

🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥 Code base

Index.html

Now create a new HTML file called Index and paste this in:

🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥 HTML Form

Deploy it as a web app

In Apps Script:

click Deploy
choose New deployment
select Web app
description: v2 prototype
execute as: Me
who has access: for testing, Anyone with the link or Only myself depending on how you want to test

Then click Deploy and open the URL.

What to test

Enter:

Full Name
Email
Course Name
Event Date
Start Time
End Time

Test these cases:

Valid

Start: 10:00
End: 11:30

Should submit and write a row.

Invalid

Start: 14:00
End: 13:00

Should block submission with:

End time must be later than start time.

What this gives you already

This tiny prototype proves all the important things:

custom form layout ✅
browser-side validation ✅
server-side validation ✅
writing directly to Google Sheets ✅

And once that’s working, we can very naturally add:

Room dropdown
recurring booking fields
conditional display logic
prettier design
admin notes
booking key generation
conflict checking

One important note

Because this is attached to the spreadsheet, SpreadsheetApp.getActiveSpreadsheet() should work nicely here. If later we move this to a standalone Apps Script project, we may switch to opening the spreadsheet explicitly by ID instead.

When you’ve pasted both files in, the next thing I’d like you to tell me is simply:

“It deployed” or what error showed up

…and we’ll fix it from there 🌷

Not Quite, Poppet¶

Robyn
Quick question: the Index.html page is a separate file? I created this on my desktop, and uploaded it to the Drive folder housing the worksheet. Did I do that correctly, Emily?

Emily
💙 🌿 Not quite, poppet 😊

Index.html should be created inside the Apps Script project itself, not as a separate file in Drive.

So, in the Apps Script editor attached to your v.2 spreadsheet:

click the + button
choose HTML
name it Index
paste the HTML code into that file

You should then see something like:

Code.gs
Index.html

inside the script project.

Uploading an HTML file to the Drive folder won’t make HtmlService.createHtmlOutputFromFile('Index') find it, because that function looks for an HTML file inside the Apps Script project, not in Google Drive. Google’s Apps Script HTML service expects HTML files to live within the project itself. So your next move is:

leave the Drive-uploaded file alone for the moment, or delete it later
create Index as an HTML file in Apps Script
paste the HTML there
save everything
then deploy the web app

Once you’ve done that, we’re back on the rails nicely 🌷

rrrrr
That worked an absolute treat! Woohoo! And oh, that form is SO much nicer. We can add graphics and make it device-friendly with css... Ideal! Tested the earlier end time thing: works perfectly. I like the time fields better too. Can we make the minute fields half-hourly?

eeeee Yes — very easily 😄🌷

For the browser UI, we can strongly encourage half-hourly times by adding a step value to the time inputs.

HTML time inputs use seconds for the step size, so:

1800 seconds = 30 minutes

So in your Index.html, change these two lines:

<input type="time" id="startTime" name="startTime" required>
<input type="time" id="endTime" name="endTime" required>

to:

<input type="time" id="startTime" name="startTime" step="1800" required>
<input type="time" id="endTime" name="endTime" step="1800" required>

That will make the control operate in half-hour increments on browsers that respect the native time picker.

But — important little caveat: browsers can still be a bit inconsistent, and some users can still type values manually. So we should also add server-side validation to reject anything that isn’t on the half-hour. That means:

10:00 ✅
10:30 ✅
10:15 ❌
10:45 ❌

Add this to Code.gs

In submitBooking(formData), after parsing the times, add half-hour checks:

function submitBooking(formData) {
  const sheetName = 'WebForm_Submissions';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    throw new Error(`Sheet "${sheetName}" not found.`);
  }

  const requiredFields = [
    'fullName',
    'email',
    'courseName',
    'eventDate',
    'startTime',
    'endTime'
  ];

  for (const field of requiredFields) {
    if (!formData[field] || String(formData[field]).trim() === '') {
      throw new Error(`Missing required field: ${field}`);
    }
  }

  const start = parseTimeToMinutes(formData.startTime);
  const end = parseTimeToMinutes(formData.endTime);

  if (start % 30 !== 0 || end % 30 !== 0) {
    throw new Error('Times must be entered in 30-minute increments.');
  }

  if (end <= start) {
    throw new Error('End time must be later than start time.');
  }

  sheet.appendRow([
    new Date(),
    formData.fullName,
    formData.email,
    formData.courseName,
    formData.eventDate,
    formData.startTime,
    formData.endTime
  ]);

  return {
    success: true,
    message: 'Booking request submitted successfully.'
  };
}

And add matching browser-side validation in Index.html

Inside your submit handler, just before the end-after-start check, add:

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

So that section becomes:

if (
  !formData.fullName ||
  !formData.email ||
  !formData.courseName ||
  !formData.eventDate ||
  !formData.startTime ||
  !formData.endTime
) {
  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;
}

My recommendation

Do both:

step="1800" for a nicer picker
validation in JS and Apps Script for robustness

That way the form is pleasant for disciplined users and still safe from creative ones 😊 And yes — this is exactly why this custom-form route is so much better for you. You now have real control over:

layout
validation
behaviour
future logic
prettiness ✨

Next after this, I’d be tempted to add a Room dropdown and make the form look a little more like a real branded booking page.

rrrrr Done.

I'm getting a strange error when I save: Syntax error: SyntaxError: Illegal return statement line: 86 file: Code.gs

eeeee That error nearly always means one thing: somewhere in Code.gs, a return has ended up outside a function because of a misplaced { or } 🌷

In your case, it is very likely that when you added the half-hour validation, one of these happened:

an extra } closed submitBooking() too early
the return { ... } block is now sitting outside the function
a paste landed in the wrong place

What to do first

Open Code.gs and look around line 86. You’ll probably see something like:

return {
  success: true,
  message: 'Booking request submitted successfully.'
};

If Apps Script says Illegal return statement, that return is no longer inside submitBooking().

Here is the full correct Code.gs

Replace your entire Code.gs with this clean version:

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index')
    .setTitle('Booking Request Prototype');
}

function submitBooking(formData) {
  const sheetName = 'WebForm_Submissions';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    throw new Error(`Sheet "${sheetName}" not found.`);
  }

  const requiredFields = [
    'fullName',
    'email',
    'courseName',
    'eventDate',
    'startTime',
    'endTime'
  ];

  for (const field of requiredFields) {
    if (!formData[field] || String(formData[field]).trim() === '') {
      throw new Error(`Missing required field: ${field}`);
    }
  }

  const start = parseTimeToMinutes(formData.startTime);
  const end = parseTimeToMinutes(formData.endTime);

  if (start % 30 !== 0 || end % 30 !== 0) {
    throw new Error('Times must be entered in 30-minute increments.');
  }

  if (end <= start) {
    throw new Error('End time must be later than start time.');
  }

  sheet.appendRow([
    new Date(),
    formData.fullName,
    formData.email,
    formData.courseName,
    formData.eventDate,
    formData.startTime,
    formData.endTime
  ]);

  return {
    success: true,
    message: 'Booking request submitted successfully.'
  };
}

function parseTimeToMinutes(timeStr) {
  const parts = String(timeStr).split(':');

  if (parts.length !== 2) {
    throw new Error(`Invalid time format: ${timeStr}`);
  }

  const hours = Number(parts[0]);
  const minutes = Number(parts[1]);

  if (
    Number.isNaN(hours) ||
    Number.isNaN(minutes) ||
    hours < 0 ||
    hours > 23 ||
    minutes < 0 ||
    minutes > 59
  ) {
    throw new Error(`Invalid time value: ${timeStr}`);
  }

  return hours * 60 + minutes;
}

Why this happened

JavaScript in Apps Script is very fussy about braces. For example, this would break:

function submitBooking(formData) {
  const start = parseTimeToMinutes(formData.startTime);
  const end = parseTimeToMinutes(formData.endTime);
}

return {
  success: true,
  message: 'Booking request submitted successfully.'
};

That return is floating out in the open, so Apps Script objects.

Fastest fix

The safest move is:

copy your current Code.gs somewhere temporary just in case
replace the whole file with the clean version above
save again

That should clear it straight away.

After that, re-test:

valid half-hour times
invalid 10:15
end earlier than start

You’re very much on the right track here — this is exactly the sort of tiny syntax gremlin that shows up when a project starts becoming real 😄

Problem Fixed¶

Robyn
I sort-of made a hash of things (duplicating code instead of replacing it)... fixed it now:

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index')
    .setTitle('Booking Request Prototype');
}

function submitBooking(formData) {
  const sheetName = 'WebForm_Submissions';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    throw new Error(`Sheet "${sheetName}" not found.`);
  }

  const requiredFields = [
    'fullName',
    'email',
    'courseName',
    'eventDate',
    'startTime',
    'endTime'
  ];

  for (const field of requiredFields) {
    if (!formData[field] || String(formData[field]).trim() === '') {
      throw new Error(`Missing required field: ${field}`);
    }
  }

  const start = parseTimeToMinutes(formData.startTime);
  const end = parseTimeToMinutes(formData.endTime);

  if (start % 30 !== 0 || end % 30 !== 0) {
    throw new Error('Times must be entered in 30-minute increments.');
  }

  if (end <= start) {
    throw new Error('End time must be later than start time.');
  }

  sheet.appendRow([
    new Date(),
    formData.fullName,
    formData.email,
    formData.courseName,
    formData.eventDate,
    formData.startTime,
    formData.endTime
  ]);

  return {
    success: true,
    message: 'Booking request submitted successfully.'
  };
}

function parseTimeToMinutes(timeStr) {
  const parts = String(timeStr).split(':');
  if (parts.length !== 2) {
    throw new Error(`Invalid time format: ${timeStr}`);
  }

  const hours = Number(parts[0]);
  const minutes = Number(parts[1]);

  if (
    Number.isNaN(hours) ||
    Number.isNaN(minutes) ||
    hours < 0 ||
    hours > 23 ||
    minutes < 0 ||
    minutes > 59
  ) {
    throw new Error(`Invalid time value: ${timeStr}`);
  }

  return hours * 60 + minutes;
}

Emily
That looks spot on to me 😊🌷

Your Code.gs is now clean, balanced, and doing exactly what it should:

serves the custom form ✅
checks required fields ✅
validates half-hour increments ✅
rejects end times earlier than start times ✅
writes to WebForm_Submissions ✅

So the next step is simply to save, then redeploy the web app if needed, and test these cases:

Good test

Start: 10:00
End: 11:30

Bad increment test

Start: 10:15
End: 11:15

Expected: Times must be entered in 30-minute increments.

Bad order test

Start: 14:00
End: 13:30

Expected: End time must be later than start time.

One small note: because you changed the script code, if your deployment is using a fixed version rather than the latest editor state, you may need to edit the deployment / create a new version so the live web app uses the updated code. If the results look “old,” that is usually why.

And truly — this is lovely progress. You now have the bones of a proper custom booking front end rather than a workaround. Very nice work 💪✨

The natural next improvement is to add:

a Room dropdown
better labels/layout for phones
perhaps a gentle header/banner so it starts looking like a real Art Society form rather than a prototype

rrrrr I have "New deployment | Manage Deployment | Test Deployment"... which would be best to use?

eeeee For what you’re doing right now, use Test deployment first ✅😊

Here’s the practical difference:

Test deployment Use this while you’re actively building. It runs the latest saved code and gives you a test URL ending in /dev. Google says head deployments are for testing and always sync to your most recently saved code; for web apps, the /dev URL is intended for development only and is accessible to users with edit access to the script. (Google for Developers)

New deployment Use this when you want a more stable, shareable version of the web app. This creates a versioned deployment, which is what Google recommends for public use. (Google for Developers)

Manage deployments Use this when you already have a versioned deployment and want to:

update it to a newer version
change its description
inspect its deployment ID/URL

Google’s deployment docs describe Manage deployments as the place to view and edit active deployments. (Google for Developers)

So, my recommendation is:

Right now: use Test deployment

When you want to give the Treasurer or testers a stable link: create a New deployment as a web app

A good rhythm is:

save code
test with Test deployment
once happy, make or update a versioned deployment
later use Manage deployments to roll the live one forward

One small warning: the Test deployment URL is lovely for your own tinkering, but it is not the one to treat as the “real” production link. Google explicitly distinguishes the testing /dev URL from a proper deployed web app version. (Google for Developers)

So for your next immediate step: click Test deployment, open the /dev link, and make sure the half-hour validation is behaving exactly as expected 🌷

rrrrr Clicking "Test deployment" gave me these options:

Head deployment ID AKfycbykv1UTQoZSonKZoSOcHhD9zHCAM532MbRuOj87Uno

Web app URL https://script.google.com/macros/s/AKfycbykv1UTQoZSonKZoSOcHhD9zHCAM532MbRuOj87Uno/dev

I do see '/dev' tacked on the web-app URL... same thing? And Uno? Is this what I think it is?

eeeee Yes — same deployment, different purpose 🌷

The /dev URL is the head deployment web-app link for testing. Google says head deployments always sync to your most recently saved code and are for testing, while versioned deployments are the ones intended for public use. (Google for Developers)

So, practically:

.../dev = your live-in-the-editor test version
a versioned web app URL you create via New deployment = your stable/shareable version

And yes, the long string like:

AKfycbykv1UTQoZSonKZoSOcHhD9zHCAM532MbRuOj87Uno

is just the deployment ID / script web-app identifier, not something secretive or alarming. Google’s deployment docs describe deployments as having their own unique URL or ID. (Google for Developers)

As for the “Uno” on the end 😄 That is almost certainly just part of the automatically generated identifier. It is not a special label meaning “version one,” nor is it anything you set manually. These IDs are opaque machine-generated strings, so sometimes they coincidentally look like words. Google documents them as unique IDs, but not as human-meaningful codes. (Google for Developers)

So your interpretation should be:

No, /dev is not the same as production, even though it runs the same app shape.
Yes, that odd-looking string is normal.
No, “Uno” doesn’t mean anything special here — just a lucky flourish from the ID generator 😊

For now, keep using the /dev link while we build. When you’re happy enough to let someone else test it as a proper stable form, use New deployment.

Rethinking GoLIVE¶

Robyn
So, this is all testing out just fine... all working as advertised. We now have time validation. Excellent!

A couple of things are in the back of my mind, here. This form is SO much better than the native one it's not even funny, in so many ways. No 2-sheet table -> worksheet nonsense: straight writing to our single worksheet. I'm thinking of running this by a couple of people, than they hold off trying to go live for just a bit longer, use this form and this mechanism vs the other, and include the 'recurring' stuff now. We already have the other code working, for the dropdowns and writing to the calendar. Can we just add it to the AppScript page?

Emily
🤍 Yes — very likely, we can reuse most of your working v.1 logic inside this same Apps Script project 🎉

A bound Apps Script project can contain multiple .gs files and HTML files, all part of the same script project, so you do not have to keep the web form logic isolated from the calendar/dropdown logic. Apps Script projects are designed to hold multiple code files, and HTML-service web apps can call server-side Apps Script functions from the page. (Google for Developers)

So the short practical answer is:

Yes:

keep doGet() and your HTML form
add more server-side functions in .gs files
call those functions from the form with google.script.run
keep any spreadsheet triggers you still want, such as onEdit(e) or installable triggers, in the same project as well. Apps Script supports both simple and installable triggers in the same project. (Google for Developers)

What I would do next¶

Not copy-paste everything in one wild lump 😄 Instead, I’d merge in layers so we don’t break the lovely clean prototype you’ve just got working.

Add the Room dropdown to the web form

This is the easiest and most natural next step.

We can either:

hard-code the room options in the HTML for now, or
fetch them from a config sheet/server-side function

For speed, I’d start with a simple dropdown in the form and write that value into the sheet.

Expand submitBooking()

Once the form writes:

Room
recurring fields
maybe notes

then submitBooking() can append those columns too.

Reuse your existing calendar-creation logic

This is where the good news is especially good: your server-side function can call Calendar/Sheets functions exactly the way your old bound-sheet code did, because Apps Script web apps are still just Apps Script. Apps Script explicitly supports integrating with Google Sheets and Google Calendar from the same project. (Google for Developers)

The main question is when to create the calendar event.

You now have two possible models:

Model A — keep approval workflow¶

The form writes a row into the sheet as Pending, and your existing admin-side approval logic creates the calendar event later.

That is the safest and closest to v.1.

Model B — create calendar event on submit¶

The form writes the row and immediately creates the calendar event. That is simpler for code flow, but riskier operationally unless every submission should go straight onto the calendar. For your Art Society workflow, I still strongly prefer Model A:

user submits request
row lands in sheet
admin reviews
admin sets status to Approved
event gets created

That preserves oversight and avoids the calendar filling with unvetted requests.

About “the dropdowns and writing to the calendar”

Yes — but with one caution. If your old v.1 code depended on:

a specific sheet name
a specific tab structure
exact header names
onEdit(e) watching a certain column number

then we need to adapt those assumptions to your new v.2 structure rather than blindly pasting them in. That’s the bit that usually bites. So I’d suggest:

keep the current prototype working
add new fields/columns deliberately
then port over one old function at a time

Can recurring be included now?

Yes, I think it can — if we keep the first version of recurrence modest 🌷 I would not begin with every possible pattern at once. I’d start with:

One-off
Weekly
Fortnightly

Then later add:

Monthly on first Monday
custom occurrence counts
exceptions/skip dates

That way, we don’t tangle the form, the sheet, and the calendar logic all at once.

The clean architecture I’d recommend now

In the web form¶

Collect:

Full Name
Email
Course Name
Room
Event Date
Start Time
End Time
Recurring? Yes/No
Frequency (if yes)
Repeat Until date (if yes)

In the sheet¶

Write:

Timestamp
Full Name
Email
Course Name
Requested Room
Event Date
Start Time
End Time
Recurring Flag
Frequency
Repeat Until
Status = Pending
Calendar Event ID
Processing Note

In script¶

Keep separate functions like:

submitBooking(formData)
createCalendarEventForRow(rowNumber)
checkConflicts(...)
handleApprovalEdit(e)

That separation will make your life much easier than putting everything inside one giant submitBooking().

So yes — can we “just add it”?

Yes, but carefully. 💪

You absolutely can extend the same Apps Script project with:

more .gs files
more functions
your existing approval/calendar code
your new HTML web form

That is a normal Apps Script structure. (Google for Developers)

My strong recommendation is that we do not jump straight to “paste all the old code in and hope.” Instead, we make the next move:

Next best move¶

Add a Room dropdown and a Status column defaulting to Pending to the web-form workflow first.

That gives us the bridge from prototype → real booking intake, without yet disturbing the approval logic.

Then we port the approval/calendar code once the row structure is settled.

If you want, paste me the current v.1 sheet headers and the core approval/calendar functions, and I’ll show you exactly how to fold them into this new web-form architecture without turning the code into spaghetti 😊

HTML First¶

Robyn
Right, then. HTML first - let's add Room and:
Recurring? Yes/No
Frequency (if yes)
Repeat Until date (if yes)

Emily
⚖️ Yes 🌷 Let’s do exactly that.

We’ll update the form first, then later teach the script what to do with those new fields.

What we’re adding¶

New fields:

Room
Recurring? Yes / No
Frequency (shown only if recurring = Yes)
Repeat Until (shown only if recurring = Yes)

For now, we’ll make the recurrence fields appear and disappear nicely in the form. Then, after that works, we’ll update Code.gs and the sheet headers to receive the new values.

Replace your `Index.html` with this version¶

🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥 Improved HTML form

What this gives you already

You now have:

Room dropdown
Recurring? selector
recurrence fields that appear only when needed
validation that:
recurrence details must be filled if recurring = Yes
Repeat Until cannot be before Event Date

Important: your script and sheet are not updated yet

Because the form is now sending extra fields, the next step will be to update:

WebForm_Submissions headers

to include:

Timestamp | Full Name | Email | Course Name | Room | Event Date | Start Time | End Time | Recurring | Frequency | Repeat Until

and then update Code.gs so it writes those new values too.

At the moment, the form can send them, but your current submitBooking() won’t yet validate/store them.

Next step

After you save this HTML and test that the fields show/hide nicely, I’ll give you the matching Code.gs update so the new data lands cleanly in the sheet 🌷

I do like 'IntelliSense'... I added these fields as const in the requiredFields array, and in the sheet.appendRow() function, they'd appear after the formData property. Very cool... saves typing and let's you double check you got the spelling right in the array. I did put them in the same order of row 1 on the worksheet.

Exactly 😄 That is one of the quiet pleasures of coding this way. By adding the fields consistently in:

the form data object
the requiredFields array where appropriate
the appendRow() order
the worksheet header order

you’re starting to create a nice clean contract between front end and sheet. That pays off very quickly.

And yes — that “pseudo-IntelliSense by consistency” feeling is real:

you type less
you spot spelling drift sooner
you reduce silly mismatches like repeatUntil vs repeatuntil vs repeat_Until
you keep your mental model tidy

Very good instinct putting them in the same order as row 1. That makes debugging much easier later, because when you inspect a row, you can mentally map it straight across.

One tiny caution

I would not leave frequency and repeatUntil in the unconditional requiredFields array on the server side unless you also handle the recurring === 'Yes' condition there.

Because ideally the server should enforce:

always required:
fullName
email
courseName
room
eventDate
startTime
endTime
recurring
required only when recurring is Yes:
frequency
repeatUntil

So if you’ve put them into requiredFields unconditionally, a non-recurring booking may fail server-side even though the form UI is behaving correctly.

The pattern I’d recommend in Code.gs

Something like:

const requiredFields = [
  'fullName',
  'email',
  'courseName',
  'room',
  'eventDate',
  'startTime',
  'endTime',
  'recurring'
];

for (const field of requiredFields) {
  if (!formData[field] || String(formData[field]).trim() === '') {
    throw new Error(`Missing required field: ${field}`);
  }
}

if (formData.recurring === 'Yes') {
  if (!formData.frequency || !formData.repeatUntil) {
    throw new Error('Recurring bookings require Frequency and Repeat Until.');
  }
}

That keeps the logic aligned with the form.

But overall?

You’re doing exactly the right kind of thinking now 💪 You’re no longer just “getting code to run”; you’re beginning to shape:

naming discipline
data structure
maintainability
future debugging ease

That is the real developer hat.

The next thing I’d do is have you paste your updated submitBooking() so I can sanity-check that the new server-side validation matches the new HTML form perfectly.

Test The Whole Chain¶

🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥 Robyn
Fixed. Now?

Emily
🧨🧭😄 Now we test the whole little chain properly 😊🌷 You’ve done the important part: the form, the conditional recurrence fields, and the server-side logic are now speaking the same language. Lovely.

First: run these tests

Non-recurring booking

Try:

Full Name: Test Tutor
Email: your email
Course Name: Watercolour Basics
Room: Studio
Event Date: any valid date
Start Time: 10:00
End Time: 11:30
Recurring: No

Expected:

success message
one clean row written to the sheet
Frequency and Repeat Until blank
Recurring booking, valid

Try:

Recurring: Yes
Frequency: Weekly
Repeat Until: a later date than Event Date

Expected:

success message
row written with all recurrence fields populated
Recurring booking, missing fields

Set Recurring = Yes but leave:

Frequency blank, or
Repeat Until blank

Expected:

browser-side message blocking submit
Repeat Until earlier than Event Date

Expected:

browser-side rejection
Bad time increment

Try 10:15 if the browser lets you type it.

Expected:

rejection
End earlier than start

Expected:

rejection

Then check the sheet carefully

Make sure row 2 lands exactly under the right headers.

Your header row should now be in this order:

Timestamp | Full Name | Email | Course Name | Room | Event Date | Start Time | End Time | Recurring | Frequency | Repeat Until

If the order in appendRow() matches that exactly, you’re golden.

After that, the next logical step is this Not calendar yet. One small bridge step first:

Add booking workflow columns now. Extend the sheet headers to include:

Timestamp | Full Name | Email | Course Name | Room | Event Date | Start Time | End Time | Recurring | Frequency | Repeat Until | Status | Calendar Event ID | Processing Note

And in submitBooking(), append defaults like:

Status = Pending
Calendar Event ID = blank
Processing Note = blank

That gives your web form output the same kind of operational shape your v.1 admin logic expects.

Why this is the right next move

Because once each submitted row already arrives as:

valid
structured
pending approval

then folding your existing approval/calendar code back in becomes much easier. You’re building the runway before landing the plane ✨

So, right now do the test submissions first. If all of that behaves, the next thing I want to help you with is updating submitBooking() to append:

Status
Calendar Event ID
Processing Note

with sensible defaults, so this prototype starts becoming a real replacement rather than just a pretty intake form.

Copy Formatting¶

Robyn
Well, this is cool. I still had the old 'Admin' sheet... I was able to copy the 'Status' column (not the header, but the rest) and it copied the dropdown arrow and the colour logic as well. All good, ready to add functionality to that field.

Emily
✨🧬 That’s excellent 😄🌷

And actually, that is a very handy little Google Sheets win: by copying the existing Status cells down from your old Admin sheet, you’ve preserved the bits that matter operationally:

the dropdown validation
the colour behaviour
the familiar user experience

Which means your new web-form-fed sheet is already starting to feel like the old system, just cleaner underneath. Very nice.

So yes — now we add functionality to that field

This is the right moment to reconnect the approval workflow.

What we want now is:

web form writes a row
row arrives with Status = Pending
admin changes Status to Approved
script creates the calendar event
script writes back the Calendar Event ID
script adds a Processing Note
if conflict exists, status changes appropriately instead

That gets you back to the core value of v.1, but with a much better intake mechanism.

The next clean step I would do is in two parts:

make sure the new sheet headers are final

Your row should now include these columns in a stable order:

Timestamp | Full Name | Email | Course Name | Room | Event Date | Start Time | End Time | Recurring | Frequency | Repeat Until | Status | Calendar Event ID | Processing Note

If that structure is now in place, good.

Then submitBooking() should append defaults for the last three:

Status → Pending
Calendar Event ID → blank
Processing Note → blank

port in the approval-on-edit logic

This is where we wire the Status field back up. The good news is: if your v.1 approval logic already works, we do not need to reinvent it. We just need to adapt it to the new column layout. Please paste me these two things from v.1:

the function that handles approval / onEdit
the function that creates the calendar event plus any helper it depends on for:
finding columns by header
conflict checking
note writing
calendar lookup

Then I’ll refactor it for the new web-form structure rather than having you do a risky transplant blind.

One small thing to watch

Because you copied the Status column formatting from the old Admin sheet, just make sure:

the dropdown values still exactly match what the code expects such as Pending, Approved, Conflict, Rejected, maybe Cancelled

Even a tiny spelling mismatch can make the trigger look dead when really it’s just waiting for an exact text value. So yes — you are at exactly the right point now. You’ve got the intake. You’ve got the sheet shape. You’ve got the status UX. Now we bring the workflow brain back in 🧠✨ Paste the approval/calendar code and I’ll help you fold it in cleanly.

Constants¶

Robyn
So, the first thing I'm copying are the constants:

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',
  RESPONSE_SHEET_NAME: 'WebForm_Submissions',

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

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

  HEADERS: {
    FULL_NAME: 'Full Name',
    EMAIL: 'Email address',
    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',
    EVENT_ID: 'Calendar Event ID',
    NOTE: 'Processing Note'
  }
};

Wasn't sure if 'RESPONSE_SHEET_NAME' was a required const, so I just used the sheet name for it.

Emily
🎧✨ That’s a very good start 🌷 And yes — using WebForm_Submissions for both for now is perfectly sensible.

The short answer¶

RESPONSE_SHEET_NAME is not inherently required. It is only required if some part of your existing v.1 code explicitly refers to it.

So:

if old functions use CONFIG.RESPONSE_SHEET_NAME, keep it
if nothing uses it, it is harmless but redundant

At this stage, I’d actually keep it for a little while, because it gives you flexibility while you’re porting code across. Later, once the dust settles, you can decide whether to remove it.

One important thing I noticed

In your new HTML / script work so far, you’ve been using:

email

But in these constants, the header is:

Email address

So the key issue is not the form field name — that can stay email — but the worksheet header text must match what your header-reading code expects. That means your actual sheet row 1 should use exactly:

Full Name
Email address
Course Name
Room
Event Date
Start Time
End Time
Recurring
Frequency
Repeat Until
Status
Calendar Event ID
Processing Note

If your sheet currently says Email instead of Email address, and your later code looks up columns by CONFIG.HEADERS.EMAIL, then it will fail quietly or return the wrong column.

That is the kind of tiny mismatch that causes a lot of head-scratching. My recommendation, for now, keep the constants exactly as you pasted them, provided your row 1 headers match them exactly. So I would standardise the sheet to these header labels:

Timestamp | Full Name | Email address | Course Name | Room | Event Date | Start Time | End Time | Recurring | Frequency | Repeat Until | Status | Calendar Event ID | Processing Note

Note:¶

You do not have Timestamp in HEADERS, and that is fine if:

your helper code ignores it, or
you don’t need to look it up by name

If later you want column-finding logic for timestamp too, we can add:

TIMESTAMP: 'Timestamp'

to HEADERS.

What’s good about this config. This part is nicely done:

room values centralised ✅
statuses centralised ✅
header labels centralised ✅
calendar mode and ID separated ✅

That will make the port much cleaner.

My only gentle tidy-up suggestion

You currently have:

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

That is fine for now, but conceptually I’d read them like this:

SHEET_NAME = the working sheet we act on
RESPONSE_SHEET_NAME = legacy name from Google Form response flow

Since this is no longer really a Google Form response sheet, that second name is a bit historical rather than meaningful. Not a problem — just something to keep in mind for later cleanup.

So: is this okay to proceed with? Yes 👍 This config is quite usable. The next thing I want to see is the code that:

detects a Status edit
checks whether it changed to Approved
creates the calendar event
writes back event ID / processing note
handles conflicts

Paste that next, and we’ll adapt it to this new sheet structure cleanly.

Handle Status¶

Robyn
That would be this:

function handleStatusEdit_(sheet, row, headers, e) {
  const newStatus = trim_(e.range.getValue());
  const oldStatus = trim_(e.oldValue);
  const noteCol = headers[CONFIG.HEADERS.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) {
      removeLiveCalendarEvent_(
        sheet,
        row,
        headers,
        'Booking rejected by manager.',
        CONFIG.STATUS_VALUES.REJECTED
      );
    } else {
      sheet.getRange(row, noteCol).setValue('Booking rejected by manager.');
    }
    return;
  }

  // Optional: if someone manually sets Conflict, leave a note.
  if (newStatus === CONFIG.STATUS_VALUES.CONFLICT) {
    sheet.getRange(row, noteCol).setValue('Booking marked as conflict. Review room/date/time before re-approval.');
    return;
  }

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

Emily
✂️✨ Yes — that’s the right function, and it’s a good one 🌷 Structurally, it already does the important things:

reads the new and old status values
routes to approval / cancellation / rejection handlers
leaves a note for conflict
removes the live calendar event if a row moves away from Approved

That is a very solid workflow shape.

A couple of important observations

e.oldValue can be undefined

In Apps Script edit events, oldValue is only available when the edited range is a single cell, and it is undefined if the cell had no previous content. That means your use of trim_(e.oldValue) is sensible only if your trim_() helper safely handles undefined. (Google for Developers)

So this line is fine provided trim_() does something like:

function trim_(value) {
  return value == null ? '' : String(value).trim();
}

If trim_() does not guard against undefined, that is the first place I’d harden.

This function assumes the edit handler already knows it is on the Status column

That’s good. This function should stay focused.

In other words, this is not your onEdit(e) itself — it is a dispatcher/helper called by onEdit(e) after that outer function has already checked:

correct sheet
edited row is not the header
edited column is Status

That separation is exactly what we want.

The “safety net” at the bottom is smart

This bit:

if (oldStatus === CONFIG.STATUS_VALUES.APPROVED && newStatus !== CONFIG.STATUS_VALUES.APPROVED) {

is good defensive design. It protects you from odd admin edits and keeps Calendar from drifting too far away from the sheet.

That is very much in the spirit of the real-world users you’re designing for 😊

My only likely adjustment for your v.2 world

Because this new sheet is now fed by the custom web form, I would want the approval handler to be especially careful about rows that are incomplete or slightly malformed. So before processApprovalRow_(sheet, row, headers);, I would eventually want that function to verify the row has the minimum required values:

Course Name
Room
Event Date
Start Time
End Time

Not necessarily here in handleStatusEdit_() itself — but definitely somewhere in the approval path.

One tiny readability tweak I’d consider

This part:

if (!newStatus) return;

is perfectly fine. If you want to make the function just slightly more self-explanatory later, you could make it:

if (!newStatus) {
  return;
}

Not because the logic changes — just because once these scripts grow, tiny readability niceties start paying rent.

So: verdict. Yes, keep this function. It is a good candidate to port across almost unchanged. The real adaptation work will happen in the functions it calls:

processApprovalRow_()
processCancellationRow_()
removeLiveCalendarEvent_()

because those are the ones most likely to depend on:

old sheet headers
old column names
old room/calendar assumptions
one-off versus recurring behaviour

What I’d like next: paste the helper(s) that support this function, ideally in this order:

onEdit(e) or whatever calls handleStatusEdit_()
processApprovalRow_()
removeLiveCalendarEvent_()
processCancellationRow_()

That will let me show you exactly what can stay, what needs renaming, and what needs recurrence-aware logic.

onApprovalEdit(e)¶

Robyn
This one calls handleStatusEdit()

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

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

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

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

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

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

Emily
✂️ Yes — and this is the first place where v.1 is showing us one of its old bones 😊🌷 Your onApprovalEdit(e) is basically sound, but there is one immediate issue for v.2:

The problem is this line:

const assignedRoomCol = headers[CONFIG.HEADERS.ASSIGNED_ROOM];

It assumes you have a constant called:

CONFIG.HEADERS.ASSIGNED_ROOM

But in the constants you pasted earlier, you do not currently have ASSIGNED_ROOM. So unless you’ve added it elsewhere, this will break.

What that tells us is that your old v.1 workflow was already moving toward:

requested room
assigned room override

But your current v.2 form and headers only have:

Room

So for right now, while we are getting the web-form pipeline working, I would simplify this function and remove the assigned-room branch until we deliberately add that feature back. That keeps the workflow clean and avoids dragging in half-finished logic too early.

Use this v.2 version for now

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

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

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

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

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

That is the clean version for the structure you have today.

This is the right move now because your current v.2 sheet has, as far as we’ve discussed:

requested booking data
status
event ID
processing note

But not yet:

assigned room
assigned-room override logic
room-change reapproval workflow

So we keep the trigger aligned with the actual sheet.

One more important check is your trigger/function name... it is:

function onApprovalEdit(e)

That is not a simple trigger name like onEdit(e).

So for this to fire automatically, you need either:

an installable trigger pointing to onApprovalEdit
or rename it to onEdit(e) if you want it to act as the built-in simple trigger

Since you were already using installable triggers in v.1, keeping onApprovalEdit(e) is perfectly fine — just make sure the installable trigger is attached in this v.2 script project.

So the question is not whether the function is correct. It is whether Google is actually being told to run it.

Where we are now, so far, for v.2, keep:

handleStatusEdit_()
this trimmed-down onApprovalEdit(e)

Ignore for now

ASSIGNED_ROOM
handleAssignedRoomEdit_()

We can reintroduce that later once we intentionally add an Assigned Room column and define how it should behave.

Next thing I need from you, please paste:

function processApprovalRow_(...)

That’s the heart of the system now — the bit that will tell us how much of the old calendar logic can drop straight in, and what needs adapting for recurrence.

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 tutorName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
  const tutorEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);

  const requestedRoomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
  const assignedRoomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);

  const roomRaw = effectiveValue_(
    rowValues,
    headers,
    CONFIG.HEADERS.ASSIGNED_ROOM,
    CONFIG.HEADERS.ROOM
  );

  const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
  const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
  const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);
  const purpose = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
  const notes = valueByHeader_(rowValues, headers, CONFIG.HEADERS.NOTES);
  const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_ID));

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

  const room = normalizeRoom_(roomRaw);
  const usingAssignedRoom = trim_(assignedRoomRaw) !== '';

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

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

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

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

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

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

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

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

  if (sameRoomConflict) {
    sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);

    const conflictMessage = usingAssignedRoom
      ? `Conflict detected at approval time: reassigned room ${room} is also already booked.`
      : `Conflict detected at approval time: ${room} is already booked.`;

    sheet.getRange(row, noteCol).setValue(conflictMessage);
    return;
  }

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

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

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

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

Emily
🔥 Excellent — this is the heart of it 🌷 And it tells us very clearly what must change for your new v.2 structure. This function is good, but it is still carrying a few v.1 assumptions:

ASSIGNED_ROOM
NOTES
possible absence of Email address exactness
one-off event creation only
no recurring logic yet

So the right move is not to bolt this in unchanged. The right move is to make a v.2-safe version now.

What needs changing immediately

Remove ASSIGNED_ROOM

Your current v.2 sheet does not have that header yet.

So these lines need to go:

const assignedRoomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);

and

const roomRaw = effectiveValue_(
  rowValues,
  headers,
  CONFIG.HEADERS.ASSIGNED_ROOM,
  CONFIG.HEADERS.ROOM
);

and

const usingAssignedRoom = trim_(assignedRoomRaw) !== '';

For now, v.2 should simply use:

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

Remove NOTES unless you already added it

This line:

const notes = valueByHeader_(rowValues, headers, CONFIG.HEADERS.NOTES);

assumes a Notes column exists in CONFIG.HEADERS.

From the constants you showed me, it does not.

So unless you’ve added a notes field to the web form and header row, remove that too for now.

Add recurrence awareness

Since you’ve now added:

Recurring
Frequency
Repeat Until

the approval function should at least read them, even if we initially decide to only create a single event or leave recurrence for the next pass.

At minimum, this function should know whether a row is recurring.

My recommendation: use this cleaned-up v.2 version now

Replace your current processApprovalRow_() with this:

🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿🌿 Process Approval Row

Why I like this version

It does three useful things right now:

works with your actual current v.2 headers
doesn’t depend on missing columns
is recurrence-aware without pretending recurrence is already fully built

That last bit matters. It keeps you honest and avoids “looks like recurring works” when it’s only creating the first occurrence.

Important note about recurring

Right now, this version still creates one single calendar event, even for recurring bookings.

That is okay as a temporary bridge only if the note makes that explicit.

So for testing, that’s fine.

For real use, we’ll need the next upgrade:

either create a recurring event series in Calendar
or generate individual events from the recurrence rule

For your booking/billing world, I’m actually leaning toward individual event generation eventually, because it gives you better control and auditing.

Before you paste this in, make sure your sheet headers exactly include:

Timestamp | Full Name | Email address | Course Name | Room | Event Date | Start Time | End Time | Recurring | Frequency | Repeat Until | Status | Calendar Event ID | Processing Note

And make sure your submitBooking() is appending values in that exact order, with something like:

Status = Pending
Calendar Event ID = ''
Processing Note = ''

What I need next after you slot this in, the next helper I want is:

function getHeaders_(sheet)

because that is the quiet linchpin. If that function is solid, the rest of this becomes much easier to trust.

Constants Observation¶

Robyn
I noticed that this set of constants:

  const tutorName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);  
  const tutorEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);  
  const roomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);  
  const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);  
  const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);  
  const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);  
  const purpose = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);  
  const 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.EVENT_ID));

looks different to my original set:

  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',  
    EVENT_ID: 'Calendar Event ID',  
    NOTE: 'Processing Note'
  }  
};

Just wondering if there's a reason for that.

Emily
🪶 Yes — there’s a reason, and you’ve spotted a very important little drift there 😊🌷

The constants block itself is fine. What matters is that the text inside CONFIG.HEADERS... must match the actual header row in the sheet exactly.

So when you have code like:

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

that line is not different in structure from the others. It is simply asking:

“Look up the column whose header text is whatever CONFIG.HEADERS.EMAIL says.”

So the real issue is this part of your config:

EMAIL: 'Email',

Earlier, a previous version had:

EMAIL: 'Email address',

That difference matters a lot. The rule is these two must agree exactly:

your config value

EMAIL: 'Email'

* the text in row 1 of the sheet

Email

If the sheet says Email but the config says Email address, then:

headers[CONFIG.HEADERS.EMAIL]

will likely come back undefined, and then valueByHeader_() will fail or return blank.

So the answer is the block:

const tutorName = valueByHeader_(...)
const tutorEmail = valueByHeader_(...)
...

is not the problem.

The only possible mismatch is whether the header names referenced there match your HEADERS object and your actual worksheet row 1.

In your current version, you showed:

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',
  EVENT_ID: 'Calendar Event ID',
  NOTE: 'Processing Note'
}

That is perfectly good provided your sheet header row says exactly:

Timestamp | Full Name | Email | Course Name | Room | Event Date | Start Time | End Time | Recurring | Frequency | Repeat Until | Status | Calendar Event ID | Processing Note

This came up because earlier in our discussion, we were still carrying a bit of old wording from the Google Form world, where a form response header often becomes:

Email address

But now that you’re controlling the custom sheet yourself, Email is cleaner and perfectly sensible. So I would say:

if your sheet says Email, keep EMAIL: 'Email'
if your sheet says Email address, change the config to match it

Just don’t let the two differ.

The broader principle is: your config object is acting like a single source of truth for header names. That means:

the code should refer to CONFIG.HEADERS.EMAIL
not hard-code 'Email' elsewhere
and the worksheet header row should match the config

That is exactly the tidy way to do it.

So: was there a reason? Yes: the earlier wording drifted because we were still half-standing in the old Google Forms world.

Your current version using:

EMAIL: 'Email'

is absolutely fine, and probably better, as long as the sheet header says Email too. If you like, the next thing to paste is getHeaders_() and valueByHeader_() so I can sanity-check the lookup chain end-to-end.

Changing variable Names¶

Robyn
So, if I wanted to change, say, 'purpose', to 'courseName' within this function, as long as I made ALL the changes, it would be fine?

Emily
🌿 Yes — exactly 🌷 You can rename a local variable like:

const purpose = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);

to:

const courseName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);

and that is completely fine as long as you change every use of that local variable inside the function. So this:

const descriptionLines = [
  `Purpose: ${purpose || ''}`
];

would also need to become:

const descriptionLines = [
  `Course Name: ${courseName || ''}`
];

The important distinction is that there are three different kinds of names in play here:

Local variable names

Example:

const purpose = ...

These are just for readability inside the function. You may rename them freely, provided you update all references in that function.

Config keys

Example:

CONFIG.HEADERS.COURSE_NAME

These are part of your program structure. If you rename one of these, you must update every place in the code that uses that config key.

Actual sheet header text

Example:

COURSE_NAME: 'Course Name'

This must match the worksheet header row exactly.

So in your example, changing:

const purpose = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);

to:

const courseName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);

is just a local variable rename. That is safe. In fact, I’d encourage it, because courseName is clearer and matches:

the form field name
the config key
the sheet header concept

That kind of consistency makes life easier later. So yes — rename it if you like. Just make sure every later ${purpose} becomes ${courseName} in that same function. 😊

processCancellationRow_()¶

Robyn
Perhaps we need to look at processCancellationRow_() next?

Emily
😄 👟 👠 👟 😂 🎭 ✨ 🎭 🌿 🌺 Yes — that’s exactly the right next function to inspect 🌷 Why this one next? Because your status workflow now has three important branches:

Approved → create calendar event
Cancelled → remove or update calendar event appropriately
Rejected → similar “not going ahead” logic

We’ve already looked at the approval side. The next thing most likely to carry old v.1 assumptions is the cancellation path. That’s also where future recurring logic will get interesting:

cancel one one-off booking
cancel a recurring request before any series is created
later, perhaps cancel a whole recurring series vs one occurrence

So yes — processCancellationRow_() is the natural next stop. Paste that one in, and then after that we’ll probably want to look at:

removeLiveCalendarEvent_()
getHeaders_()
valueByHeader_()

Those four together are basically the spine of the workflow 💪

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

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

Yes — this one is beautifully simple 😊🌷 And that is a good thing. What it does, it decides the note prefix based on where the row came from:

if it used to be Approved → "Booking cancelled."
otherwise → "Booking marked as cancelled."

Then it hands off the real work to:

removeLiveCalendarEvent_(...)

That is exactly the right separation of concerns. So I would keep this function essentially as-is. It works well because processCancellationRow_() is not trying to:

look up the event ID itself
delete the calendar event itself
decide too much policy itself

It is just:

interpreting the status change
sending the job to the removal helper

Very tidy. Only one thing to keep in mind is: this function’s correctness depends almost entirely on:

removeLiveCalendarEvent_()

That helper now becomes the really important next thing to inspect, because it has to handle cases like:

booking was approved and has an event ID
booking was approved but event was manually deleted already
booking was never approved, so no event exists
row says cancelled but event ID is blank
later, recurring bookings may have more than one event involved

So yes — this function is fine. The next one we should absolutely look at is:

removeLiveCalendarEvent_()

Paste that next, and we’ll keep going neatly from there.

Context Summary¶

Robyn
I think we might start a new thread, Emily. May I have a context summary for that, please?

Emily
🤍😄 ⚠️ ✅ 🤍
Absolutely 🌷

Here’s a clean context summary for the Booking App v.2 / custom web form thread:

Project status

Robyn is developing v.2 of the Art Society Booking App in Google Sheets / Apps Script.

Current real-world context¶

v.1 is finishing testing and may go live by the end of the week.
New feature requests already identified for v.2:
support for recurring event requests
a Billing / reconciliation layer tied to the Bookings Calendar
There is a new Treasurer, described as switched-on, and Robyn wants to speak with her before designing the billing system.
Robyn is now strongly leaning toward replacing the native Google Form with a custom Apps Script web form, because:
it allows better layout
it allows validation like end time must be later than start time
it writes directly to a target sheet instead of using the old Google Form response-sheet pipeline
it will help Robyn later understand how the Society’s existing New Member web form could write directly to a Google Sheet

Reset / v.2 setup already done

All work is being done on a copy of the original v.1 system inside a Drive folder called v.2.

Robyn has already:

copied the original sheet and form into the v.2 folder
cleared all responses from the copied form
unlinked the copied form from its destination sheet
unhid the Form responses 4 sheet
cleared the table there
cleared the Admin sheet

This established a clean sandbox for v.2 development.

Important conceptual decisions

Billing / truth model¶

Robyn does not want to rely on users behaving neatly. The Art Society users are artists and admins, not disciplined technical users, so the future billing system needs to flag discrepancies between:

Booking Sheet
Calendar

The intended future direction is:

Booking Sheet = administrative record
Calendar = operational schedule
Billing / reconciliation layer = derived ledger and discrepancy monitor

Examples of discrepancies Robyn wants to be able to detect later:

event created manually in Calendar
event deleted manually
stored event ID no longer exists
calendar time no longer matches the sheet

Form direction¶

The custom Apps Script web form is much preferred over native Google Forms because:

nicer layout
device-friendly CSS possible
direct writing to a single worksheet
no 2-sheet form-response → admin-sheet arrangement
much cleaner validation and control

Prototype web form created and working

A first custom web form prototype was built using:

Code.gs
Index.html inside the Apps Script project attached to the spreadsheet

Initial prototype fields¶

Full Name
Email
Course Name
Event Date
Start Time
End Time

Validation implemented¶

Both browser-side and server-side validation were added for:

required fields
end time must be later than start time
half-hour increments only (step="1800" plus server-side validation)

This all tested successfully.

Clarification that mattered

Robyn initially created Index.html as a separate file on desktop and uploaded it to Drive. This was corrected:

Index.html must be created inside the Apps Script project, not as a Drive file

Once placed correctly inside the script project, the web app deployed and worked.

Deployment notes

Apps Script deployment options were discussed:

Test deployment (/dev URL) is appropriate during development because it runs latest saved code
New deployment is for stable/public use later
Manage deployments is for updating existing versioned deployments

Robyn used the /dev test deployment successfully.

Prototype enhanced with recurring fields

The HTML form was extended to include:

Room
Recurring? Yes / No
Frequency (shown only if recurring = Yes)
Repeat Until (shown only if recurring = Yes)

Current Room options¶

Studio
Gallery

Current recurring options¶

Weekly
Fortnightly

Browser-side logic added¶

recurrence fields show/hide dynamically
recurrence fields become required only when Recurring = Yes
Repeat Until must be on or after Event Date

Robyn updated the sheet / script to reflect these fields and paid attention to consistent naming/order.

Coding style observation

Robyn noticed and appreciated that keeping field names consistent across:

form object
requiredFields
appendRow()
sheet headers

creates a kind of pseudo-IntelliSense and reduces spelling drift. This is now part of the working style.

Current sheet structure direction

Current intended headers for WebForm_Submissions are:

Robyn also copied the existing Status dropdown cells from the old Admin sheet, which successfully preserved:

dropdown values
colour logic / conditional formatting

So the new web-form-fed sheet already has a functioning Status column UX.

Constants / config now in play

Robyn has started porting v.1 logic into the new v.2 structure.

Current `CONFIG`¶

const CONFIG = {
  CALENDAR_MODE: 'id',
  CALENDAR_ID: '1c26f492c1488f8852cbf50f1203ce8efe868869ee4aa77768f73249736a3549@group.calendar.google.com',
  SHEET_NAME: 'WebForm_Submissions',
  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',
    EVENT_ID: 'Calendar Event ID',
    NOTE: 'Processing Note'
  }
};

Important note about headers¶

The exact strings in CONFIG.HEADERS must match the actual sheet row 1 labels exactly.

In particular, there was discussion about:

Email vs
Email address

Current preferred config is:

EMAIL: 'Email'

provided the sheet header also says Email.

Ported workflow functions examined so far

handleStatusEdit_(sheet, row, headers, e)

This function:

reads new and old status
routes status changes to:
processApprovalRow_()
processCancellationRow_()
rejection path
conflict note
has a safety net to remove a live event if status moves away from Approved

This function is considered structurally good and likely reusable.

Important note:

it depends on trim_() safely handling undefined, because e.oldValue may be absent.

onApprovalEdit(e)

Original version included old v.1 logic for ASSIGNED_ROOM, but v.2 does not yet have an Assigned Room column.

So the current recommended v.2 version is simplified to only watch the Status column:

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

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

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

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

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

Important operational note:

onApprovalEdit is not a simple trigger name like onEdit
so it will need an installable trigger if Robyn wants it to run automatically

processApprovalRow_(sheet, row, headers)

Original v.1 function assumed:

ASSIGNED_ROOM
NOTES
one-off event creation only

A cleaned-up v.2 version was proposed that:

uses only current headers
reads:
Full Name
Email
Room
Event Date
Start Time
End Time
Course Name
Recurring
Frequency
Repeat Until
Calendar Event ID
validates:
room exists
date/time fields exist
time order is valid
recurrence fields exist if recurring = Yes
checks same-room conflicts via Calendar
creates a single calendar event
writes the event ID back to the sheet
writes a Processing Note

Important current limitation:

even for recurring bookings, the proposed current version only creates one calendar event
it writes a note making clear that recurring logic is not yet implemented

Robyn also asked whether local variable names like purpose can be renamed to courseName. Answer: yes, as long as all local references within the function are updated consistently.

processCancellationRow_(sheet, row, headers, oldStatus)

Reviewed and considered good as-is:

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

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

This function is intentionally simple and delegates the actual deletion/update work to removeLiveCalendarEvent_().

Not yet reviewed in this thread

These functions/helpers were identified as the next ones to inspect:

removeLiveCalendarEvent_()
getHeaders_()
valueByHeader_()
trim_()
possibly getTargetCalendar_()
recurrence creation logic later

At the point the thread ended, Robyn suggested starting a new thread before continuing into those functions.

Immediate next-step recommendation for the new thread

Continue by reviewing:

removeLiveCalendarEvent_()
getHeaders_()
valueByHeader_()
trim_()

Then:

wire the status-based trigger into the new WebForm_Submissions sheet properly
make sure submitBooking() appends:
Status = Pending
Calendar Event ID = ''
Processing Note = ''
test:
submit new booking via web form
manually change Status to Approved
confirm calendar event is created
confirm event ID and note are written back

Robyn’s tone / preference in this coding work

Robyn is enjoying this custom-form route and is excited by how much better it is than the native Google Form setup.
Robyn likes the cleaner architecture and the way consistent naming helps reduce mistakes.
Robyn is open to accelerating v.2 and perhaps even using the custom form earlier than originally planned, especially if recurring support can be introduced soon.
The next step should continue in the same practical, collaborative, stepwise style: careful porting, not risky blind paste-ins.

If you like, in the next thread you can simply paste:

removeLiveCalendarEvent_()

and we’ll pick up right there 🌷

Perfect place to pick things back up 🌷—we’ve got a clean runway.

I’ve got your context loaded, including the v.2 structure and where we paused (right before diving into the helper layer). That’s actually a very good breakpoint—you’ve already done the hard conceptual work, and now we’re moving into the part that determines whether the system feels solid or fragile.

Where we are (very briefly)... you now have:

✅ Custom web form writing directly to WebForm_Submissions
✅ Status-driven workflow (Pending → Approved → Cancelled → etc.)
✅ Core approval logic mostly ported
⚠️ Helper layer still unverified (this is where subtle bugs usually hide)

The helper layer matters (quietly but critically) because functions like:

removeLiveCalendarEvent_()
getHeaders_()
valueByHeader_()
trim_()

…look boring. They’re not. They are your infrastructure. If they’re clean and defensive, 👉 your whole app feels effortless. If they’re slightly off, 👉 you get ghost bugs like:

wrong column pulled
notes not updating
event IDs disappearing
“it worked yesterday” behaviour

So this is exactly the right place to slow down a touch and get things tight.

The next step (clean and focused) is to proceed exactly as you suggested earlier. 👉 Start with:

removeLiveCalendarEvent_()

Why first? Because it sits at a high-risk junction:

touches Calendar
touches Sheet
depends on Event ID integrity
triggered by status transitions

If this one is rock-solid, everything downstream (cancellations, status changes, corrections) becomes trustworthy.

When you paste it, I won’t just “check syntax”—I’ll:

trace how it interacts with:
Calendar Event ID
Status
Processing Note
check for:
silent failure cases
missing guards (nulls, deleted events, etc.)
edge-case behaviour (e.g. cancelling something never created)
align it with your single source of truth philosophy (Sheet > Calendar)

And importantly: 👉 keep it aligned with your style—clean, consistent, low cognitive load.

One tiny reassurance (because this matters)

You’re doing this exactly right. Most people:

bolt features on
avoid refactoring helpers
end up with brittle systems

You’re:

isolating responsibilities
standardising headers
centralising config
thinking about reconciliation already

That’s professional-grade architecture, Robyn. Truly.

Whenever you're ready:

👉 paste removeLiveCalendarEvent_() and we’ll tune it into something you can trust completely.

removeLiveCalendarEvent_()¶

Robyn
Thank you. Lovely of you to say so.

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

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

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

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

    const event = calendar.getEventById(existingEventId);

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

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

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

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

Emily
✨ 💙 🌿 This is nicely restrained code 🌷 It does one job, delegates the calendar lookup, and writes back a clear outcome. That is all good design.

My verdict: basically sound, with just a few places where I’d tighten it so it behaves better under stress.

What is already good

Clear responsibility

This function has a very clean purpose:

look up linked event ID
try to remove event
clear event ID
optionally set new status
write processing note

That’s exactly the right scope.

Sensible outcome messaging

These are useful and human-readable:

No linked calendar event to remove.
Linked calendar event removed.
Linked calendar event not found; ID cleared anyway.

That’s excellent for an admin workflow. It tells the operator what actually happened.

Clearing the event ID even when Calendar no longer has the event

Very good choice. That keeps the sheet from pretending there is still a valid linked event.

What I would improve

Guard against missing columns

Right now you assume these exist:

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

If one of those headers is misspelled or missing, sheet.getRange(row, undefined) will explode in a messy way.

I would add a firm header check up front.

Example:

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

For statusCol, your existing optional check is fine because status change is conditional.

Why this matters: your app depends heavily on exact header names, so when something breaks, you want the error to be immediate and obvious, not weird and indirect.

Be careful about changing status inside a status-triggered workflow

This line is logically fine:

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

But it can become awkward depending on how handleStatusEdit_() is structured.

For example:

user changes status to Cancelled
handleStatusEdit_() calls processCancellationRow_()
that calls removeLiveCalendarEvent_()
this function writes Cancelled back into the same status cell again

That may be harmless, but it can also:

cause redundant writes
create confusing audit flow
potentially re-trigger installable edit logic in some setups

Usually I prefer this function to be a little dumber:

remove event
clear event ID
write note
let the caller decide status

That keeps the side effects more predictable.

So structurally, I’d lean toward:

either remove the newStatus parameter entirely
or only write status if it is different from the current value

Example:

if (newStatus && statusCol) {
  const currentStatus = trim_(sheet.getRange(row, statusCol).getValue());
  if (currentStatus !== newStatus) {
    sheet.getRange(row, statusCol).setValue(newStatus);
  }
}

Calendar lookup failures may be too harsh

This part is the one I’d think about most carefully:

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

This is not wrong. In fact, during development it is useful.

But operationally, ask yourself what outcome you want if:

config is wrong
calendar permission changes
calendar service has a hiccup

At present:

function throws
event ID is not cleared
note is not written
row may be left in an ambiguous state

That may be acceptable for dev, but for real-world admin use I often prefer:

keep the event ID intact
leave the status alone
write a note explaining failure
then throw only if you want logs

Two Valid philosophies¶

Strict/dev philosophy

Fail loudly, stop everything. Good for setup and debugging.

Admin/production philosophy

Write useful sheet feedback even on failure. Good for non-technical operators.

Given your audience, I suspect production behaviour should be a little more graceful.

Slight redundancy here

This:

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

works, but it repeats the same action in both branches.

Cleaner:

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

That reads more clearly because the policy becomes: “Whatever happens, the cell is cleared.”

Minor wording/behaviour question: should we always clear when there was no ID?

This is not a bug, just a design thought. If there was no event ID:

outcome = 'No linked calendar event to remove.';
sheet.getRange(row, eventIdCol).clearContent();

Clearing an already-empty cell is harmless, so this is totally fine. I only mention it because it reveals the function’s philosophy:

“normalize the row into a no-linked-event state”

That is actually a good philosophy.

My recommended revision

Here is the version I would use:

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

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

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

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

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

    const event = calendar.getEventById(existingEventId);

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

  sheet.getRange(row, eventIdCol).clearContent();

  if (newStatus && statusCol) {
    const currentStatus = trim_(sheet.getRange(row, statusCol).getValue());
    if (currentStatus !== newStatus) {
      sheet.getRange(row, statusCol).setValue(newStatus);
    }
  }

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

My overall assessment

I’d rate this as:

Design: very good
Readability: very good
Robustness: good, but can be improved with header guards and slightly safer status handling

So no, this is not one of those “oh dear, tear it all down” functions 😄 It’s actually a solid little workhorse. It just wants a bit of reinforcement around the edges.

One broader architectural note: This function is behaving as though the sheet is the authority, which is exactly right for your project. Its implicit rule is: “Whatever Calendar says, the row must end up in a clean, truthful state.” That is a strong rule. Keep that. It will save you later.

Paste getHeaders_() next, and we’ll keep shoring up the foundation 🌷

getHeaders_()¶

Robyn

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

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

  return headers;
}

Emily
😄✨ 💙 🌊 🌿 🌺 🌙 💙 Yes — this one is good 🌷 Simple, clean, and doing exactly what it should.

What it does well

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

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

  return headers;
}

It trims header names

Excellent. That protects you against stray spaces in row 1, which are far more common than they ought to be.

So:

"Status"
" Status "
"Status "

all become:

"Status"

That is exactly what you want.

It returns 1-based column indexes

Also correct, because Sheets ranges are 1-based, not 0-based.

So later this works naturally:

const statusCol = headers[CONFIG.HEADERS.STATUS];
sheet.getRange(row, statusCol)

Very good.

It ignores blank header cells

Nice touch:

if (key) headers[key] = i + 1;

That means empty columns in row 1 don’t pollute the map.

This is a strong helper because it is one of those little functions that quietly makes the whole app nicer because it lets everything else read like this:

headers[CONFIG.HEADERS.EVENT_ID]

instead of brittle things like:

That’s a big win for maintainability.

The one subtle risk: The only thing I’d flag is duplicate header names. If row 1 accidentally contains the same header twice, this function will silently keep the last one:

Example:

Status | Note | Status

would produce:

headers["Status"] = 3

not 1.

That may never happen in your sheet if you keep it tidy, but if it did happen, it could be a nasty bug because the function would not complain.

Two valid approaches

A. leave it as-is

This is perfectly reasonable if:

you control the sheet structure
row 1 is stable
you want the helper to stay lightweight

For your project, this is honestly defensible.

B. make duplicate headers fail loudly

If you want a more defensive version, you could do this:

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

  headerValues.forEach((name, i) => {
    const key = String(name).trim();
    if (!key) return;

    if (headers[key]) {
      throw new Error(`Duplicate header found: ${key}`);
    }

    headers[key] = i + 1;
  });

  return headers;
}

I rather like this version for your setup, because your whole system depends on exact header matching. If the headers are wrong, I’d prefer the script to shout immediately rather than drift into odd behaviour.

My recommendation

I’d call your current version good enough already. But for a slightly more bulletproof foundation, I’d upgrade it to the duplicate-checking version. That would make it:

still simple
still readable
a little more trustworthy

Verdict

This helper is:

clean ✅
correctly designed ✅
easy to maintain ✅
worth keeping ✅

No structural problem here at all.

The next natural one is valueByHeader_() — because that’s the partner function to this one, and together they form the column-access backbone 🌷

valueByHeader_()¶

Robyn

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

Emily
Also good 🌷 This is a neat companion to getHeaders_().

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

What it gets right

Clean separation of concerns

This does exactly one thing:

find the column number from the header map
return the matching value from the row array

That’s ideal.

Correct 1-based to 0-based adjustment

This line is right:

return rowValues[col - 1];

Since:

headers[...] stores Sheets column numbers starting at 1
rowValues[...] is a JavaScript array starting at 0

So this is properly aligned.

Graceful fallback

This:

if (!col) return '';

is useful because it prevents the script from falling over just because a header wasn’t found.

That can be quite handy in places where you want tolerant behaviour.

The one thing to think about: tolerant vs strict

This helper currently follows a soft failure philosophy:

header missing?
just return ''

That is fine in some contexts. But in your app, missing headers are usually not a normal situation. They usually mean:

typo in the sheet header
typo in CONFIG.HEADERS
sheet structure drift

And when that happens, returning '' can sometimes hide the real problem. Example:

const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);

If the header is wrong, this returns ''. Then later you may get an error like:

invalid date
missing room
time parse failed

Which is technically true, but not the root cause.

The root cause was: header not found

My view for your project, I think, there are two useful patterns.

Pattern 1: keep this helper soft

Leave valueByHeader_() exactly as-is.

Then:

it stays simple
callers can decide how strict to be
validation later catches missing data

That is valid, especially since several of your workflow functions already check for required data.

Pattern 2: create a strict variant as well

This is my favourite for your project.

Keep this one, but add a second helper for places where the column absolutely must exist.

Example:

function requiredValueByHeader_(rowValues, headers, headerName) {
  const col = headers[headerName];
  if (!col) {
    throw new Error(`Missing header: ${headerName}`);
  }
  return rowValues[col - 1];
}

Then you get the best of both worlds:

valueByHeader_() for flexible lookups
requiredValueByHeader_() for critical fields

That makes the code very expressive. For example:

const status = requiredValueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
const repeatUntil = valueByHeader_(rowValues, headers, CONFIG.HEADERS.REPEAT_UNTIL);

That reads beautifully: one is mandatory, one is optional.

Tiny optional refinement

If you want the current helper to be just a touch more defensive, you could write:

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

But honestly, in your case that’s probably unnecessary, because rowValues is coming from:

sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0]

So it should already be long enough.

Verdict

This function is:

correct ✅
clean ✅
useful ✅

I would keep it. The only real question is philosophical: do you want silent fallback for missing headers, or loud failure? For your system, my preference would be:

keep this helper as the soft version
add a strict sibling for critical reads

That would give you a very nice little utility layer. The next one to inspect is definitely trim_() — because everything is leaning on it, especially status handling and event ID cleanup 🌷

trim_()¶

Robyn
You had me fix trim_()

function trim_(value) {
  return value == null ? '' : String(value).trim();
}

Emily
✨ Ah yes — and this is the right fix 🌷

function trim_(value) {
  return value == null ? '' : String(value).trim();
}

This is much better than the fragile version people often start with.

Why this one is good

It safely handles null and undefined

This is the important bit:

value == null ? ''

Using == null here is actually elegant, because it catches both:

null
undefined

So these won’t blow up:

trim_(undefined)
trim_(null)

Both return:

''

That is exactly what you want in Apps Script, where missing values and e.oldValue can be slippery.

It normalises everything else to string

This part:

String(value).trim()

means it can safely accept:

strings
numbers
booleans
dates, if they ever sneak in

Examples:

trim_('  Approved  ')   // 'Approved'
trim_(42)               // '42'
trim_(true)             // 'true'

That makes it a very dependable utility.

This matters in your project because this helper is quietly doing a lot of stabilising work, especially here:

status comparisons
old vs new value comparisons
event ID reads
note text assembly
any place where sheet values might contain stray spaces or blanks

Without this fix, one undefined value in the wrong place could produce:

TypeError
weird equality failures
false mismatches

With this version, those risks drop away nicely.

Verdict

This one is:

correct ✅
robust ✅
simple ✅
worth keeping exactly as-is ✅

No changes needed.

So, where does that leave the helper layer? At this point:

getHeaders_() → good
valueByHeader_() → good
trim_() → good
removeLiveCalendarEvent_() → good, with a few sensible reinforcements

That means your foundation is actually in pretty decent shape 😊

The best next target, the most useful function to inspect is probably:

```writing{variant="standard" id="41728"} getTargetCalendar_()

because that is now the next critical dependency in the chain:

* approve booking
* fetch calendar
* create/delete event
* write result back

If `getTargetCalendar_()` is clean, your booking workflow starts looking genuinely production-ready.

Paste that next and we’ll keep going.

<hr style="height:4px;border-width:0;color:blue;background-color:blue" />








## getTargetCalendar_()

<img src="/assets/images/docimgs/260331ax.jpg" style="float: right; width: 420px; margin-left: 20px" />

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

Emily
❤️ 🌿 Yes — that’s perfectly fine 🌷

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

It’s small, readable, and it does exactly what it ought to do.

What’s good about it

Clean abstraction

This is doing an important architectural job:

the rest of your code does not need to care whether you’re using:
the default calendar, or
a specific calendar ID

That’s excellent. It keeps calendar selection in one place.

Nice config-driven design

You’ve made the behaviour controllable through:

CONFIG.CALENDAR_MODE
CONFIG.CALENDAR_ID

That’s the right pattern. Much better than hardcoding calendar logic all over the script.

Correct Apps Script methods

These are the right calls:

CalendarApp.getDefaultCalendar()
CalendarApp.getCalendarById(...)

So structurally: no problem.

The only thing I’d improve is this. The function is fine, but it is a bit trusting. Right now it assumes:

CONFIG.CALENDAR_MODE is valid
CONFIG.CALENDAR_ID exists when needed
returning null is okay for downstream code to handle

That can work, especially since removeLiveCalendarEvent_() already checks for a missing calendar and throws. But if you want this helper to be a little stronger, I’d make it validate its own inputs more explicitly.

Slightly stronger version

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

  if (CONFIG.CALENDAR_MODE === 'id') {
    if (!CONFIG.CALENDAR_ID) {
      throw new Error('CONFIG.CALENDAR_ID is missing.');
    }
    return CalendarApp.getCalendarById(CONFIG.CALENDAR_ID);
  }

  throw new Error(`Invalid CALENDAR_MODE: ${CONFIG.CALENDAR_MODE}`);
}

The reason I like this version better is, it catches three kinds of configuration drift early:

Invalid mode¶

If someone accidentally types:

CALENDAR_MODE: 'Default'

or

CALENDAR_MODE: 'calendarId'

you get a clear error immediately.

Missing calendar ID¶

If mode is 'id' but the ID is empty, you get a clear explanation instead of a mysterious null later.

Better fault location¶

The error happens in the calendar selector, which is exactly where the problem lives.

That makes debugging much nicer.

Design philosophy question: there are two perfectly sensible ways to handle this:

Option A: keep this helper lightweight¶

Let it just return:

a calendar object, or
null

and let callers decide what to do. This matches your current style.

Option B: make this helper enforce valid config¶

That’s my preference here, because calendar selection is core infrastructure, not optional behaviour. I’d rather it complain early and clearly.

Verdict

Current version:

correct ✅
simple ✅
usable ✅

Recommended version:

slightly safer ✅
easier to debug ✅
better for long-term maintenance ✅

So I’d say: good function, worth keeping, but worth hardening a little.

Where you are now: your helper layer is looking quite healthy now:

trim_() ✅
getHeaders_() ✅
valueByHeader_() ✅
getTargetCalendar_() ✅
removeLiveCalendarEvent_() ✅ with minor reinforcement

That means the next really meaningful review target is no longer a tiny helper. It’s probably one of the workflow engines, most likely:

processApprovalRow_()
or handleStatusEdit_()

Of those two, I’d lean to processApprovalRow_() first, because that is where validation, conflict detection, event creation, and recurrence staging all come together. 🌷

processApprovalRow_()¶

Robyn
We did this earlier, Emily:

🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥 Massive Errors Mess

Emily
🔥 🏆 🌿 💛 Yes — and I’m very glad you pasted it again, because there is one important mistake in this version that needs fixing. 🌷 The overall structure is still sound, but a variable got crossed in a way that will absolutely cause trouble.

The major bug

You have this:

const fnEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);

So fnEmail is the email value from the row, such as:

"someone@example.com"

But later you use it like a column number:

sheet.getRange(row, fnEmail).setValue('Already created; skipped duplicate approval.');

and again here:

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

and in all the other error/success writes. That is the key problem. getRange(row, fnEmail) expects the second argument to be a column index, like 12, not an email address string. So this will break.

What should be happening instead is: those messages clearly belong in the Processing Note column, not the Email column. And in fact you already defined the column properly:

const procNoteCol = headers[CONFIG.HEADERS.NOTE];

So every place where you currently have:

sheet.getRange(row, fnEmail).setValue(...)

should be:

sheet.getRange(row, procNoteCol).setValue(...)

That is the big correction.

So the first repair is this: change all of these:

sheet.getRange(row, fnEmail).setValue(...)

to:

sheet.getRange(row, procNoteCol).setValue(...)

That single fix affects all these places:

Duplicate approval guard¶

Current:

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

Corrected:

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

Unknown room¶

Current:

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

Corrected:

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

Missing date/time¶

Invalid date/time¶

End before start¶

Recurrence fields missing¶

Conflict detected¶

Success message¶

All of those should also write to procNoteCol.

A second issue

statusCol, eventIdCol, procNoteCol should be validated. Right now you do:

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

That is fine, but I’d add guards early:

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

Because these columns are not optional in this workflow.

A third refinement: you never explicitly write Approved back. This is not necessarily wrong, depending on how the workflow is supposed to behave. If the human has already changed the status cell to Approved, then processApprovalRow_() doesn’t need to do it again. That’s fine. But it means this function is assuming:

status was already manually set to Approved
function only handles the side effects

That is a coherent design. I just wanted to note that it is a design choice, not an omission.

A fourth refinement: title might want course name. Currently:

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

That is perfectly serviceable.

But for calendar usefulness, you may later prefer something like:

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

or even:

const title = `${courseName || 'Untitled booking'} - ${fullName || 'Tutor'} (${room})`;

Not required — just something to consider, especially if billing/admin later needs the calendar to be legible at a glance.

Cleaned-up corrected version... here is your function with the key fix and a few reinforcements:

🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥🔥 Cleaned Version

processCancellationRow_() still looks good. This part is still nice and clean:

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

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

No issue there.

Bottom line

So, on this one:

overall workflow logic: good
major bug: writing notes to fnEmail instead of procNoteCol
after that correction: quite solid

That is the kind of bug that creeps in during a rename/refactor, and it is exactly why this review pass is worth doing 😊

Paste handleStatusEdit_() next. That’s the one most likely to reveal whether the whole status-driven workflow is properly stitched together.

Betrayed by Search/Replace¶

Robyn
I shudder to think! I need to stop using Search and Replace in Zed... and manually look at each instance I think might need changing.

Emily
Ohhh yes 😄🌷 That is exactly the kind of bug search-and-replace loves to introduce:

rename the value variable
miss that some places were using a column index
suddenly an email address is being treated like column 7

Very Zed. Very human. Very fixable. What happened here is not sloppy thinking — it’s a classic refactor slip: you changed the meaning of a name in one place, and search/replace obediently spread that change into places where the old meaning still mattered.

I’d strongly suggest a naming split like this:

values from the row:
emailValue
fullNameValue
roomValue
column numbers:
emailCol
statusCol
noteCol

That way, code like this becomes visually self-policing:

sheet.getRange(row, noteCol).setValue('Missing date/time fields.');

and this looks immediately wrong:

sheet.getRange(row, emailValue).setValue('Missing date/time fields.');

Your eye catches it before the script does.

My quiet advice: ban ambiguous abbreviations here

Especially things like:

fnEmail
note
roomRaw
status

unless the role is unmistakable. Because in Apps Script you’re constantly juggling:

header name strings
column numbers
row values
cleaned/normalized values

So clarity beats brevity every time. A good naming pattern for this codebase would be something like this, wonderfully hard to misuse:

const emailValue = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);
const eventIdValue = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_ID));

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

That’s beautifully legible.And yes — manual inspection beats blanket replace. Search and replace is fine for:

exact constant names
repeated literal strings
very controlled refactors

It is dangerous for:

variables whose role changed
names that appear in multiple semantic contexts
code where both value and column concepts coexist

Which is basically this project in a nutshell 😄

So your instinct is right: for this codebase, search/replace should be followed by a line-by-line sanity pass in every touched function. That shudder? Healthy. It means your instincts are getting sharper 🌷

handleStatusEdit_()¶

Robyn

function handleStatusEdit_(sheet, row, headers, e) {
  const newStatus = trim_(e.range.getValue());
  const oldStatus = trim_(e.oldValue);
  const noteCol = headers[CONFIG.HEADERS.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) {
      removeLiveCalendarEvent_(
        sheet,
        row,
        headers,
        'Booking rejected by manager.',
        CONFIG.STATUS_VALUES.REJECTED
      );
    } else {
      sheet.getRange(row, noteCol).setValue('Booking rejected by manager.');
    }
    return;
  }

  // Optional: if someone manually sets Conflict, leave a note.
  if (newStatus === CONFIG.STATUS_VALUES.CONFLICT) {
    sheet.getRange(row, noteCol).setValue('Booking marked as conflict. Review room/date/time before re-approval.');
    return;
  }

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

Emily
💙 🌿 This is in pretty good shape, Robyn 🌷 The logic is clear, and more importantly, it reflects your workflow philosophy properly:

status change is the trigger
approval creates the event
moving away from approval removes the live event
notes tell the human what happened

That’s a strong structure.

But there are two things I’d tighten: one practical, one architectural.

What is already good

Clear routing¶

This reads very cleanly:

Approved → processApprovalRow_()
Cancelled → processCancellationRow_()
Rejected → either remove live event or just note it
Conflict → note it
safety net for any move away from Approved That is easy to reason about, which matters a lot in this kind of admin script.

Good use of `trim_()`¶

Especially here:

const newStatus = trim_(e.range.getValue());
const oldStatus = trim_(e.oldValue);

That’s exactly the sort of quiet defensive work that prevents nonsense bugs.

Safety-net logic is the right instinct¶

This part is good architecture:

if (oldStatus === CONFIG.STATUS_VALUES.APPROVED && newStatus !== CONFIG.STATUS_VALUES.APPROVED) {

That rule protects you from odd manual edits and keeps the Calendar aligned with the sheet.

Very sensible.

First issue

Guard noteCol. You do:

const noteCol = headers[CONFIG.HEADERS.NOTE];

and then later:

sheet.getRange(row, noteCol).setValue(...)

That should be validated up front, just as we discussed in the other workflow functions.

So I’d add:

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

Because if the note column disappears or is renamed, you want a clean failure, not a baffling range error.

Second issue

The Rejected branch makes the later safety net unreachable. This is the important one. You currently have:

if (newStatus === CONFIG.STATUS_VALUES.REJECTED) {
  if (oldStatus === CONFIG.STATUS_VALUES.APPROVED) {
    removeLiveCalendarEvent_(...);
  } else {
    sheet.getRange(row, noteCol).setValue('Booking rejected by manager.');
  }
  return;
}

Then later:

if (oldStatus === CONFIG.STATUS_VALUES.APPROVED && newStatus !== CONFIG.STATUS_VALUES.APPROVED) {
  removeLiveCalendarEvent_(...);
}

Because the Rejected block returns early, the safety net never runs for Rejected. Now, that is not necessarily wrong, because you are already handling the Approved → Rejected case inside the rejected block itself. So functionally it works. But architecturally it means:

Rejected has special bespoke removal logic
other statuses rely on the general safety net

That creates a slight split-brain in the function. It is still valid — just a bit less elegant than it could be.

Two ways to handle that

Option A: keep it as-is¶

This is acceptable because the logic is explicit and works.

Pros:

very readable
easy to follow
no accidental double-deletes

Cons:

removal logic is partly special-case, partly general-case

Option B: centralise all “move away from Approved” behaviour¶

This is the cleaner design. In that version:

Rejected only writes the note if it was not previously approved
the safety net handles all Approved → something else transitions

That means one single rule governs live event removal. I prefer this version.

Cleaner version I’d recommend

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

  if (!noteCol) throw new Error(`Missing header: ${CONFIG.HEADERS.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
    );
  }
}

I like that version better because it makes the rule feel unified: If a row used to be Approved and is no longer Approved, remove the live event. That becomes the one governing principle. Then the special-case branches only handle:

statuses that need their own note
statuses that need approval-side behaviour

That is cleaner and easier to maintain.

One subtle behavioural question for you is this line:

const oldStatus = trim_(e.oldValue);

is fine, but remember: e.oldValue can be missing in some edit situations.

Your trim_() handles that safely, which is excellent.

But it does mean:

a manual paste
a scripted change
certain multi-cell edits

may yield oldStatus === ''

That is okay, as long as your workflow assumes:

only direct single-cell status edits are the normal trigger path

Which I think it does.

Verdict

This function is:

good structurally
clear to read
workflow-sound
worth keeping, with a bit of refinement

My honest assessment: this is not a scary function. It’s actually one of the healthier pieces of the system. 😊 The strongest version of it would:

validate noteCol
centralise “move away from Approved” removal logic a touch more cleanly

Where you stand now

At this point, your core workflow chain is looking quite respectable:

trim_() ✅
getHeaders_() ✅
valueByHeader_() ✅
getTargetCalendar_() ✅
removeLiveCalendarEvent_() ✅
processApprovalRow_() ✅ after fixing the note-column bug
processCancellationRow_() ✅
handleStatusEdit_() ✅ with minor refinement

That is a very decent foundation. The next most useful thing would be to review onApprovalEdit(e) one more time in light of all this, just to make sure the trigger entry point is as clean as the rest.

onApprovalEdit(e)¶

Robyn

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

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

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

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

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


<hr style="height:2px;border-width:0;color: pink;background-color: pink" />

Emily      
Yes — this is nice and lean 🌷
I like it.

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

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

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

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

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

What it does well

Good trigger hygiene¶

These guards are exactly right:

no event object / no range → return
wrong sheet → return
header row → return

That keeps the trigger from becoming noisy or meddlesome.

Clear scope¶

It only reacts when the edited cell is in the Status column. That’s good design. It means the entry point is narrow and predictable.

Nice delegation¶

This function is not trying to do workflow logic itself. It simply:

validates the edit context
finds the status column
hands off to handleStatusEdit_()

That is clean architecture.

The one thing I would add is, just like elsewhere, I’d validate that statusCol actually exists. Right now:

const statusCol = headers[CONFIG.HEADERS.STATUS];

If that header is missing, then statusCol is undefined, and the function just quietly does nothing.

That can be annoying to debug, because it looks like the trigger is “not firing,” when in fact the real problem is: missing or misspelled Status header

So I’d harden it slightly:

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

Slightly strengthened version

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

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

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

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

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

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

Tiny behavioural note: this function assumes a single-cell edit in the Status column, which is perfectly reasonable. If someone pastes across multiple cells, Apps Script trigger behaviour can get a bit odd depending on how the edit occurs, but for your admin workflow that is not really a design flaw. It’s just worth remembering: this entry point is best suited to normal human edits in one status cell at a time.

Verdict

This one is:

clean ✅
correctly scoped ✅
easy to maintain ✅

No structural issues at all. Just add the statusCol guard and I’d call it good.

Where you are now

Honestly? Your workflow spine is looking quite respectable now. 😊 You’ve got a coherent chain:

onApprovalEdit(e)
handleStatusEdit_()
processApprovalRow_()
processCancellationRow_()
removeLiveCalendarEvent_()

with helper support underneath. That is enough now to move from “code inspection mode” into test-path mode. The most useful next thing is probably not another function review, but a practical test checklist for the exact status transitions you should run through by hand.

Test Path Mode¶

Robyn
Let's do it!

Emily
🤍 1️⃣2️⃣3️⃣ 🎙️✨ 4️⃣ 5️⃣ 6️⃣ 🎙️✨ Excellent 😄🌷 Let’s turn this into a proper manual test checklist so you can stop wondering and start proving.

Booking App v.2¶

— status workflow test plan

The idea is to test the system the way a real bookings manager would use it, while also catching the nasty little edge cases.

Before testing¶

Make sure these are true first:

the script is attached to the correct spreadsheet
CONFIG.SHEET_NAME matches the actual sheet name exactly
the installable trigger for onApprovalEdit exists
the target calendar ID is correct
your sheet headers match CONFIG.HEADERS exactly
Status, Calendar Event ID, and Processing Note columns exist

Also, for clean testing:

start with a mostly empty calendar window
use one or two clearly fake test bookings
note the exact date/time you’re testing so you can check Calendar easily

Suggested test rows to create¶

Put in a few test submissions with distinct values so you can tell them apart easily.

For example:

Row A — normal one-off booking¶

Full Name: Alice Example
Email: alice@example.com
Course Name: Watercolour Basics
Room: Studio
Event Date: a future date
Start Time: 10:00
End Time: 12:00
Recurring: No

Row B — overlapping same-room booking¶

Full Name: Beth Example
Email: beth@example.com
Course Name: Figure Drawing
Room: Studio
Same date
Start Time: 11:00
End Time: 12:30
Recurring: No

Row C — overlapping different-room booking¶

Full Name: Cara Example
Email: cara@example.com
Course Name: Clay Workshop
Room: Gallery
Same date
Start Time: 11:00
End Time: 12:30
Recurring: No

Row D — recurring booking¶

Full Name: Dina Example
Email: dina@example.com
Course Name: Weekly Portraiture
Room: Studio
Different future date/time
Recurring: Yes
Frequency: Weekly
Repeat Until: some later future date

Core approval path tests¶

Pending → Approved (happy path)

Using Row A:

Action:

Set Status to Approved

Expected result:

calendar event is created
Calendar Event ID is filled in
Processing Note says event created successfully
event appears in the correct room/time/date
event title and description look sensible

Check in Calendar:

title matches expectation
location is the room
description contains name, email, course, recurrence info

Approving same row again

Using Row A after successful approval:

Action:

change status away and back only if needed, or try approving again in a way that would re-trigger logic

Expected result:

duplicate event is not created
note should indicate duplicate approval skipped, if that path is reached
only one calendar event exists

This verifies the existingEventId guard.

Conflict tests¶

Approve overlapping booking in same room

Using Row B after Row A is already approved:

Action:

set Row B status to Approved

Expected result:

status is changed to Conflict
no calendar event is created
Calendar Event ID stays blank
Processing Note says Studio is already booked

This is one of the most important tests.

Approve overlapping booking in different room

Using Row C while Row A exists:

Action:

set Row C status to Approved

Expected result:

should succeed
calendar event created
event ID written
processing note shows success

This proves the conflict logic is room-sensitive, not just time-sensitive.

Validation tests¶

Missing date/time fields

Create a row with one of these missing:

Event Date, or
Start Time, or
End Time

Action:

set status to Approved

Expected result:

status becomes Conflict
no event created
note says missing date/time fields

End time before start time

Create a row like:

Start 14:00
End 13:00

Action:

approve it

Expected result:

status becomes Conflict
no event created
note says end time must be after start time

Invalid room

Create a row with an invalid room value if possible, or temporarily type one manually into the sheet:

Library or Banana Shed 😄

Action:

approve it

Expected result:

status becomes Conflict
no event created
note says unknown room

Recurring booking with missing recurrence info

Using Row D idea, but leave out either:

Frequency, or
Repeat Until

Action:

approve it

Expected result:

status becomes Conflict
no event created
note says recurring booking is missing Frequency or Repeat Until

Recurring booking with recurrence info present

Using proper Row D:

Action:

approve it

Expected result:

one calendar event is created
event ID written
note says recurring logic not yet implemented

This is important because it confirms the current temporary behaviour is honest and predictable.

Cancellation / rejection / cleanup tests¶

Approved → Cancelled

Using Row A or another successfully approved row:

Action:

change Status from Approved to Cancelled

Expected result:

linked calendar event is deleted
Calendar Event ID is cleared
processing note says booking cancelled + event removed
calendar no longer shows the event

This verifies your delete path.

Pending → Cancelled

Using a row that was never approved:

Action:

set status to Cancelled

Expected result:

no error
no event ID
note says no linked calendar event to remove, or similar
status stays cancelled

That checks graceful behaviour when there is nothing to delete.

Approved → Rejected

Using a successfully approved row:

Action:

change status to Rejected

Expected result:

event is removed
event ID is cleared
processing note reflects rejection/removal
calendar entry disappears

Pending → Rejected

Using an unapproved row:

Action:

set status to Rejected

Expected result:

no calendar event involved
note says booking rejected by manager
no errors

Approved → Conflict

Using an approved row:

Action:

manually set status to Conflict

Expected result:

event removed
event ID cleared
note reflects status change / conflict handling

This is a slightly artificial test, but it proves your safety-net logic.

Safety-net tests¶

Approved → some other non-approved status

Try changing an approved row to:

blank, if allowed
another custom value, if validation permits
rejected / conflict / cancelled

Expected result:

anything moving away from Approved should remove the live event

This confirms your general protection rule is working.

Calendar integrity tests¶

Event manually deleted from Calendar, then row cancelled

This is a good real-world test.

Setup:

approve a booking so it creates an event
manually delete that event directly in Google Calendar

Action:

then set row status to Cancelled

Expected result:

no crash
Calendar Event ID gets cleared anyway
note says linked event not found; ID cleared anyway

Very valuable test.

Wrong calendar check

Only do this if you’re comfortable experimenting.

Temporarily point CONFIG.CALENDAR_ID to something invalid.

Action:

try approving a booking

Expected result:

clear script failure / clear diagnosis
no silent nonsense

Then immediately restore the correct calendar ID.

This helps prove your config errors fail loudly instead of deceptively.

Trigger test¶

Confirm installable trigger is actually doing the work

This sounds obvious, but it matters.

Action:

submit or create a fresh row
manually edit Status to Approved

Expected result:

event is created without you manually running any function from the editor

If nothing happens:

trigger may be missing
sheet name may not match
status header may not match
auth may need refreshing

What to record while testing¶

For each test, note:

starting status
action taken
resulting status
processing note text
whether Calendar Event ID was blank/filled/cleared
whether the calendar event existed afterward

Even a scrappy little table in a notebook helps enormously.

Best order to run these tests¶

I’d do them in this order:

Happy-path approval
Same-room conflict
Different-room overlap success
Cancellation of approved booking
Rejection paths
Invalid data paths
Recurring booking placeholder path
Manual-calendar-deletion cleanup test
Trigger sanity check

That sequence builds confidence without making the calendar messy too quickly.

One strong recommendation¶

For this first pass, keep one calendar tab open and one sheet tab open side by side.

That way you can watch:

status change
note update
event appear/disappear

in near real time, which makes debugging much less abstract 😊

If you like, the next step can be for me to turn this into a compact tick-box test sheet you can paste straight into your documentation or into a worksheet.

Log File¶

Robyn
Couple of questions... you were mentioning writing to log files some time ago. I'd like to do that, if we could. Also, when a new entry appears on the sheet, isn't the status supposed to show "Pending" and turn a color?

Emily
🤍📚🧭1️⃣2️⃣3️⃣4️⃣🪞⚖️ Yes to both 🌷 And both are worthwhile.

Logging: absolutely, and in your case I’d lean toward a sheet-based log, not a “real file” log

In Apps Script, you can log in a few ways:

A. `Logger.log()`¶

Good for development, but not great for real use. You only really see it in the script editor / executions.

B. `console.log()`¶

Also useful while developing, especially in execution logs.

C. Write log entries to a dedicated sheet¶

For your project, this is the sweet spot ✅

Why I like it for you:

easy to inspect
survives across runs
non-technical humans can read it if needed
perfect for “what happened to this booking?” questions

So I’d suggest a hidden sheet called something like:

System_Log or
Audit_Log

A simple logging helper

Something like this is clean:

function logAction_(level, message, details) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName('System_Log');

  if (!sheet) {
    sheet = ss.insertSheet('System_Log');
    sheet.appendRow(['Timestamp', 'Level', 'Message', 'Details']);
  }

  sheet.appendRow([
    new Date(),
    level || 'INFO',
    message || '',
    details ? JSON.stringify(details) : ''
  ]);
}

Example use¶

Inside processApprovalRow_():

logAction_('INFO', 'Approval started', { row: row });

After event creation:

logAction_('INFO', 'Calendar event created', {
  row: row,
  eventId: event.getId(),
  room: room
});

On conflict:

logAction_('WARN', 'Room conflict detected', {
  row: row,
  room: room,
  start: start,
  end: end
});

On error:

logAction_('ERROR', 'Calendar not found', {
  row: row,
  mode: CONFIG.CALENDAR_MODE,
  calendarId: CONFIG.CALENDAR_ID
});

My recommendation on logging style is: don’t log everything, or it becomes wallpaper. Log these kinds of events:

status edit received
approval attempt
approval blocked by conflict/validation
calendar event created
calendar event removed
duplicate approval skipped
missing config/header errors

That gives you a very useful trail without drowning you.

Yes — new rows should show Pending

If a brand-new booking row appears and the Status cell is blank, then yes, that means the submission logic is not yet populating the Status column.

Earlier we said the new web form submission should append:

Status = Pending
Calendar Event ID = ''
Processing Note = ''

If that is not happening, then:

the dropdown/conditional formatting may exist in the column,
but the row value itself is still blank,
so it won’t color correctly.

Because the color is usually tied to the actual cell value:

Pending → beige/yellow/whatever you chose
blank → no status color

So yes: a new row should arrive already marked Pending.

Where the fix likely belongs¶

This will be in your submitBooking() function, or whatever function writes the form data into WebForm_Submissions.

If it currently appends only the form fields, it needs to append the admin fields too.

For example, if your headers are:

Timestamp | Full Name | Email | Course Name | Room | Event Date | Start Time | End Time | Recurring | Frequency | Repeat Until | Status | Calendar Event ID | Processing Note

then the append row should look something like:

sheet.appendRow([
  new Date(),
  formData.fullName,
  formData.email,
  formData.courseName,
  formData.room,
  formData.eventDate,
  formData.startTime,
  formData.endTime,
  formData.recurring,
  formData.frequency,
  formData.repeatUntil,
  CONFIG.STATUS_VALUES.PENDING,
  '',
  ''
]);

That CONFIG.STATUS_VALUES.PENDING is the crucial bit.

Why this matters beyond cosmetics¶

It is not just a color issue.

A default Pending status means:

the row has a defined starting state
managers know it hasn’t been processed yet
your workflow becomes explicit
no ambiguity between “new request” and “unfinished data”

That is the correct state model.

My practical suggestionis to do these next, in this order:

First¶

Add the logging helper.

Second¶

Check submitBooking() and make sure it appends:

Pending
blank event ID
blank processing note

Third¶

Test one fresh web submission and confirm:

row appears
Status says Pending
color appears
log row is written

One small caution on conditional formatting: if the status value is correctly being written as Pending but the color still doesn’t appear, then the problem is probably one of these:

conditional formatting rule applies to the wrong range
rule is looking for "pending" instead of "Pending"
the new rows are outside the formatted range
the dropdown was copied, but not the conditional formatting rule

That’s easy enough to diagnose once we see whether the actual value is being written. Paste your submitBooking() next and I’ll show you exactly where to add both:

the Pending default
the logging 🌷

Losing The Plot¶

Robyn
We had this:

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

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

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

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

  SpreadsheetApp.flush();

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

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

Emily
😄🧱🔄🧠🧨🧭😄 Yes — and this explains the behaviour beautifully 🌷

This function is meant to do exactly that: when a new form submission lands, it finds the matching row on your target sheet and writes Pending into the Status column if it is blank.

So if new entries are not showing Pending, then one of these is likely true:

Most likely cause

setPendingOnFormSubmit(e) is not currently running.

That usually means:

the installable On form submit trigger is missing
or it points to the wrong function
or the event is landing on a different sheet than CONFIG.RESPONSE_SHEET_NAME

Because the function itself is logically fine.

What the function is doing right¶

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

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

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

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

  SpreadsheetApp.flush();

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

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

Good points¶

checks event object exists
checks correct sheet
skips header row
resolves the admin sheet
looks up the Status column by header
only writes Pending when blank

All that is sound.

Key architectural question

This function assumes:

the form submit creates a row on CONFIG.RESPONSE_SHEET_NAME
the row number on that response sheet matches the row number on CONFIG.SHEET_NAME

That worked in your earlier architecture when the Admin sheet mirrored the response sheet row-for-row.

But now that you’re moving toward the custom web form route, you need to be very clear whether that assumption is still true.

If your current setup is:

form writes directly to WebForm_Submissions
and CONFIG.RESPONSE_SHEET_NAME === CONFIG.SHEET_NAME

then this function should still work.

But if your setup is:

form writes somewhere else
or the row numbers no longer line up
or the custom form uses appendRow() directly into the admin sheet

then this function may be redundant or wrong for the new architecture.

That’s the crucial distinction.

My read of your current state

Because you’re now using the custom form path, I suspect the cleaner design is:

Best design for v.2¶

Write Pending at the moment the row is created, not afterward via a form-submit trigger.

That is simpler and more reliable.

Meaning: instead of depending on this:

form submits
trigger fires
script finds row
script fills in Pending

you do this:

form submits
script appends the whole row including Pending

That is much better.

So: should you keep this function?¶

For old Google-Form / mirrored-sheet architecture: Yes, it makes sense.

For custom web-form direct-to-sheet architecture: I would phase it out and set Pending directly in submitBooking(). That avoids:

row matching assumptions
trigger dependency
timing weirdness
cases where the row exists but status stays blank

About logging.
This function is also a nice place to add it. If you want to keep this function for now, we can absolutely add logging. For example:

function logAction_(level, message, details) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let logSheet = ss.getSheetByName('System_Log');

  if (!logSheet) {
    logSheet = ss.insertSheet('System_Log');
    logSheet.appendRow(['Timestamp', 'Level', 'Message', 'Details']);
    logSheet.hideSheet();
  }

  logSheet.appendRow([
    new Date(),
    level || 'INFO',
    message || '',
    details ? JSON.stringify(details) : ''
  ]);
}

Then your function becomes:

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

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

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

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

  SpreadsheetApp.flush();

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

  const statusCell = adminSheet.getRange(row, statusCol);
  if (statusCell.isBlank()) {
    statusCell.setValue(CONFIG.STATUS_VALUES.PENDING);
    logAction_('INFO', 'Pending status set on new submission', {
      row: row,
      sheet: adminSheet.getName()
    });
  } else {
    logAction_('INFO', 'Pending status not set because cell already had a value', {
      row: row,
      sheet: adminSheet.getName(),
      currentValue: statusCell.getValue()
    });
  }
}

My recommendation, plainly

If you are now truly on the custom web form, do not rely on setPendingOnFormSubmit() long-term.

Instead:

set Pending directly in submitBooking()
add logging there too

That is the cleaner v.2 architecture.

If you are still partly using the old submit pipeline¶

Then keep this function for now, and add logging so you can see whether it is firing.

What I think you should do next

Paste your current submitBooking().

That will let me tell you, exactly and without guessing:

whether Pending should be set there instead
whether setPendingOnFormSubmit() is now obsolete
where to add logging most cleanly

And I suspect that will resolve both of your questions in one go 😊

Floundering¶

createInstallableTriggers()¶

Robyn
Um, I don't seem to have a function called submitBooking(), sorry. What I do have (also not copied to the new gs is this:

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

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

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

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

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

Emily
😄🤍✅⚠️🧱⭐🚆✨ 🤍🎧✨ 🧬💡 🎙️ No need to apologise 😊🌷 This is actually very helpful, because it clarifies why Pending may not be appearing.

What this function does¶

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

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

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

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

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

This is a trigger installer. It does not submit bookings itself. It just checks whether these triggers exist:

onApprovalEdit for status edits
setPendingOnFormSubmit for form submissions

and creates them if they do not.

So the absence of a submitBooking() function is not a problem in itself. It just means your architecture is still partly in that older model where a form submission lands in a sheet, and then another function reacts to it.

The important question now¶

Your Pending behaviour depends on how the booking row is being created.

If you are still using a Google Form

then this chain makes sense:

form submits
spreadsheet gets a new row
setPendingOnFormSubmit fires
it writes Pending

If you are using a custom Apps Script web form

then onFormSubmit() usually does not fire, because there is no actual Google Form submission event.

That is the big distinction.

If your new v.2 form is a custom HTML form in Apps Script, then:

the row is probably being written by a server-side function you call with google.script.run
and setPendingOnFormSubmit() will never run unless you explicitly mimic that logic yourself

That would explain the blank Status cells perfectly.

So I think we’ve probably found the real issue¶

Your setPendingOnFormSubmit() function is fine for Google Forms.

But for the custom web form route, it is probably the wrong mechanism.

That means the right fix is not “repair the trigger” but:

write Pending at the moment the custom form writes the row.

About `createInstallableTriggers()`¶

This function is good and worth keeping, with one small note:
It prevents duplicate triggers by checking before creating them. Very sensible.

Small note¶

If you are moving fully to the custom web form, the onFormSubmit trigger may become unnecessary.

In that future state, you may only need:

onApprovalEdit

because the custom form’s server-side function would write:

the row data
Pending
blank event ID
blank note

all in one go.

Logging: this is a lovely moment to add it 🌷

Since you wanted logging, this is a good place to start. We can log trigger creation too.

Here is a tidy version:

function logAction_(level, message, details) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let logSheet = ss.getSheetByName('System_Log');

  if (!logSheet) {
    logSheet = ss.insertSheet('System_Log');
    logSheet.appendRow(['Timestamp', 'Level', 'Message', 'Details']);
    logSheet.hideSheet();
  }

  logSheet.appendRow([
    new Date(),
    level || 'INFO',
    message || '',
    details ? JSON.stringify(details) : ''
  ]);
}

And then:

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

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

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

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

    logAction_('INFO', 'Created installable trigger', {
      handler: 'onApprovalEdit',
      eventType: 'ON_EDIT'
    });
  }

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

    logAction_('INFO', 'Created installable trigger', {
      handler: 'setPendingOnFormSubmit',
      eventType: 'ON_FORM_SUBMIT'
    });
  }
}

That gives you a visible record that the setup happened.

My honest recommendation now is that I would separate your thinking into two cases:

Case 1: you are still testing with an actual Google Form

Then:

keep setPendingOnFormSubmit
keep the form-submit trigger
logging will help confirm it is firing

Case 2: you are now using the custom HTML form

Then:

setPendingOnFormSubmit is probably obsolete
the form-submit trigger is probably irrelevant
Pending must be written by the custom form’s server-side save function

That second case feels more likely from what you’ve been building.

The next thing we need to see¶

Not submitBooking(), specifically — just the server-side Apps Script function that the custom HTML form calls to save the row.

It might be named something like:

saveBooking
processForm
handleFormSubmit
submitFormData
anything at all

That is the function where Pending should be set if you’re on the custom form path.

Paste the function from your Code.gs that receives the web-form data and writes to the sheet, and I’ll show you exactly where to add:

Pending
blank event ID
blank processing note
logging ✨

submitBooking(formData)¶

Robyn
A 3rd gs file (Code.gs) has appeared automagically... I didn't notice it before. (Did 'Deploy' create it? dunno). It has a submitBooking() function in it:

✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨✨ Submit Booking

Emily
✨ Aha — there she is 😄🌷 And yes, this is the function that explains your blank Status cells.

submitBooking(formData) is the server-side function your custom web form calls, and right now it appends only the form fields — not the workflow fields.

So the fix is straightforward:

add Status = Pending
add blank Calendar Event ID
add blank Processing Note
optionally add logging right here

And no, Deploy would not usually “invent” business logic for you. More likely, this file was already part of the Apps Script project scaffold or got created when the project/form prototype was set up. The important bit is: now we’ve found the true entry point.

Why Status is blank right now¶

Your current append is:

  sheet.appendRow([
    new Date(),
    formData.fullName,
    formData.email,
    formData.courseName,
    formData.room,
    formData.eventDate,
    formData.startTime,
    formData.endTime,
    formData.recurring,
    formData.frequency,
    formData.repeatUntil
  ]);

But your target sheet headers are effectively:

Timestamp | Full Name | Email | Course Name | Room | Event Date | Start Time | End Time | Recurring | Frequency | Repeat Until | Status | Calendar Event ID | Processing Note

So you are stopping at column 11.

That means:

Status stays blank
Calendar Event ID stays blank
Processing Note stays blank

Blank event ID and note are fine at submission time.

Blank status is not.

The correct fix¶

You want this row to arrive already as:

Pending
''
''

So the append should become:

  sheet.appendRow([
    new Date(),
    formData.fullName,
    formData.email,
    formData.courseName,
    formData.room,
    formData.eventDate,
    formData.startTime,
    formData.endTime,
    formData.recurring,
    formData.frequency,
    formData.repeatUntil,
    CONFIG.STATUS_VALUES.PENDING,
    '',
    ''
  ]);

That will immediately populate the Status column and should trigger your conditional formatting colour as long as the rule is correct.

Logging: yes, let’s put it here too

This is exactly the right place to log a submission, because this is the moment the row is created.

Add this helper once¶

function logAction_(level, message, details) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let logSheet = ss.getSheetByName('System_Log');

  if (!logSheet) {
    logSheet = ss.insertSheet('System_Log');
    logSheet.appendRow(['Timestamp', 'Level', 'Message', 'Details']);
    logSheet.hideSheet();
  }

  logSheet.appendRow([
    new Date(),
    level || 'INFO',
    message || '',
    details ? JSON.stringify(details) : ''
  ]);
}

Then revise `submitBooking(formData)` to this¶

function submitBooking(formData) {
  const sheetName = CONFIG.SHEET_NAME;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    throw new Error(`Sheet "${sheetName}" not found.`);
  }

  const requiredFields = [
    'fullName',
    'email',
    'courseName',
    'room',
    'eventDate',
    'startTime',
    'endTime',
    'recurring'
  ];

  for (const field of requiredFields) {
    if (!formData[field] || String(formData[field]).trim() === '') {
      throw new Error(`Missing required field: ${field}`);
    }
  }

  if (formData.recurring === 'Yes') {
    if (!formData.frequency || !formData.repeatUntil) {
      throw new Error('Recurring bookings require Frequency and Repeat Until.');
    }
  }

  const start = parseTimeToMinutes(formData.startTime);
  const end = parseTimeToMinutes(formData.endTime);

  if (start % 30 !== 0 || end % 30 !== 0) {
    throw new Error('Times must be entered in 30-minute increments.');
  }

  if (end <= start) {
    throw new Error('End time must be later than start time.');
  }

  sheet.appendRow([
    new Date(),
    formData.fullName,
    formData.email,
    formData.courseName,
    formData.room,
    formData.eventDate,
    formData.startTime,
    formData.endTime,
    formData.recurring,
    formData.frequency || '',
    formData.repeatUntil || '',
    CONFIG.STATUS_VALUES.PENDING,
    '',
    ''
  ]);

  logAction_('INFO', 'Booking submitted', {
    fullName: formData.fullName,
    email: formData.email,
    courseName: formData.courseName,
    room: formData.room,
    eventDate: formData.eventDate,
    startTime: formData.startTime,
    endTime: formData.endTime,
    recurring: formData.recurring
  });

  return {
    success: true,
    message: 'Booking request submitted successfully.'
  };
}

A few nice improvements hidden in there¶

Use CONFIG.SHEET_NAME

Instead of hardcoding:

const sheetName = 'WebForm_Submissions';

I switched it to:

const sheetName = CONFIG.SHEET_NAME;

That keeps your architecture consistent.

Safe blanks for optional recurrence fields

This is slightly cleaner:

formData.frequency || '',
formData.repeatUntil || '',

So if recurring is No, those cells stay cleanly blank.

Submission is logged

That will let you verify that the custom form is actually writing rows when expected.

What this means for `setPendingOnFormSubmit()`¶

Now that we’ve found submitBooking(), my advice is:

For the custom web form path

You no longer need to rely on:

setPendingOnFormSubmit(e)

to populate Pending.

Because submitBooking() should do it directly.

That is cleaner and more reliable.

So in the long run, for the custom form architecture:

keep onApprovalEdit
keep the ON_EDIT trigger
probably retire setPendingOnFormSubmit
probably retire the ON_FORM_SUBMIT trigger too

Unless you still want backward compatibility with an actual Google Form.

This is better because now the workflow becomes:

custom form sends data
submitBooking() validates it
row is written fully formed
status already says Pending
colour appears immediately
no separate trigger needed to “finish” the row

That is much neater.

One caution before testing, if you add Pending and it still does not turn colour, then the next suspect is conditional formatting. That would mean:

the value is present
but the formatting rule is not covering the new rows or not matching Pending

So after updating submitBooking(), test in this order:

Test 1

Submit one fresh booking from the web form.

Expected:

row appears
Status says Pending

Test 2

See whether the colour appears.

If not:

click the Status cell
confirm it literally says Pending
then inspect the conditional formatting rule

My recommendation now is, I’d do these three things next:

add logAction_()
update submitBooking() as above
submit one test booking through the web form

That should answer both of your questions in one go. 😊 And yes — very good catch. We’ve now found the real heart of the custom-form workflow.

Past Tired¶

Robyn
Error?

ReferenceError: logAction_ is not defined

This version of submitBooking()...

Emily
😄🤍 🧱 🛠️ 🧠⚠️ ✂️✨ 💙 💙 Yes — that error simply means the helper does not exist yet in the script project 🌷

submitBooking() is trying to call:

logAction_(...)

but Apps Script cannot find any function with that name.

Fix¶

Add this function somewhere in your .gs files:

function logAction_(level, message, details) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let logSheet = ss.getSheetByName('System_Log');

  if (!logSheet) {
    logSheet = ss.insertSheet('System_Log');
    logSheet.appendRow(['Timestamp', 'Level', 'Message', 'Details']);
    logSheet.hideSheet();
  }

  logSheet.appendRow([
    new Date(),
    level || 'INFO',
    message || '',
    details ? JSON.stringify(details) : ''
  ]);
}

Then save, and try the web form again.

Why it happened¶

I gave you the revised submitBooking() that uses the logger, but the logger itself has to be present too. Apps Script lets functions live across multiple .gs files, but the function still has to exist somewhere in the project.

So this is not a logic fault in submitBooking() — just a missing helper.

Quickest path forward¶

Option 1: add the logger now

Best option if you want logging immediately.

Option 2: temporarily remove the logger call

If you want to get the booking form working first, comment out or delete this block from submitBooking():

  logAction_('INFO', 'Booking submitted', {
    fullName: formData.fullName,
    email: formData.email,
    courseName: formData.courseName,
    room: formData.room,
    eventDate: formData.eventDate,
    startTime: formData.startTime,
    endTime: formData.endTime,
    recurring: formData.recurring
  });

Then re-test.

That will let the form run without logging.

My recommendation¶

Add logAction_() now. It’s small, useful, and you wanted this anyway 😊

Once you do that, your next test should be:

submit a fresh booking from the custom web form
confirm a new row appears
confirm Status = Pending
confirm a hidden System_Log sheet has been created

If the row appears with Pending but no colour, then the next thing to inspect is the conditional formatting rule.

/Dev: No Re-Deploy¶

Robyn
Do I have to re-deploy every time I make a change to the code and do a Save?

Emily
✂️ ✅🤍 💙 😄💙 😉 Not if you’re using the test deployment (/dev) 🌷

That was one of the nice things about it.

In practice¶

If you are opening the dev URL¶

then:

save code
refresh the form page
test again

No new deployment needed.

If you are using a versioned / live deployment¶

then yes:

you need to create or update the deployment for code changes to appear there

Easy rule of thumb¶

during development → use the /dev link
for real users later → use the deployed production link

So for what you’re doing right now, the answer is almost certainly:

No — just save, then refresh the page. 😊

Tiny caveat¶

If you change:

permissions
manifest/settings
sometimes trigger setup

you may occasionally need to re-authorise or revisit deployment settings.

But for ordinary code edits to functions like:

submitBooking()
logAction_()
validation logic

a saved change is enough on the dev link.

What I’d do right now¶

add logAction_()
save
refresh the dev form page
submit a test booking

That should tell you straight away whether:

the logger works
Pending is now being written
the sheet colour shows up 🌷

System Log¶

Robyn
Where is System_Log? 'Pending' is being written and the colour for that cell shows up, but I can't find the System_Log sheet.

Emily
🎧 🚆 🪞 👀 🔥 🌿 🎯 ✍️ ✨🌏💛 📊 🏛📍❗🧭 That’s actually a very encouraging sign 🌷😊

If Pending is being written and the colour is showing up, then your updated submitBooking() is definitely running.

The most likely reason you can’t see System_Log is simply:

it was created hidden¶

In the helper I gave you, this line does that:

logSheet.hideSheet();

So the sheet may exist perfectly happily, just out of sight.

How to find it¶

Try one of these:

click the little sheet-tab menu at the bottom left of Google Sheets
or look for View → Hidden sheets
or right-click any visible sheet tab and see whether there is an Unhide sheet option

If System_Log is there, unhide it and you should see the entries.

Why this is actually good news¶

Because this combination:

row created
Pending written
colour appears
no logger error

strongly suggests the logger function is working too.

So this is probably not “missing” so much as “politely tucked away.” 😄

If you want it visible while testing¶

You can temporarily change:

logSheet.hideSheet();

to nothing at all — just remove that line.

Then the next time the sheet is created, it will stay visible.

Or, if the sheet already exists and is hidden:

unhide it once
and it will stay visible unless your code hides it again during creation of a new log sheet

To make the logger a bit gentler during development, use this version:

function logAction_(level, message, details) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let logSheet = ss.getSheetByName('System_Log');

  if (!logSheet) {
    logSheet = ss.insertSheet('System_Log');
    logSheet.appendRow(['Timestamp', 'Level', 'Message', 'Details']);
    // logSheet.hideSheet();   // leave this off while testing
  }

  logSheet.appendRow([
    new Date(),
    level || 'INFO',
    message || '',
    details ? JSON.stringify(details) : ''
  ]);
}

That way you can watch the log fill up in real time while you test.

One extra possibility, just in case¶

If you still truly cannot find it after checking hidden sheets, then the next possibility is that the code is running against a different spreadsheet context than you expect.

But because the row is being appended to the correct sheet and Pending is showing up there, I think that’s much less likely.

My money is on: System_Log exists and is hidden.

Unhide it and you should be in business 🌷

Fin¶

Robyn
I found it, thank you, Emily.

My brain is officially fried. Stuck a fork in it: done. We'll do the change room thing tomorrow, then test according to your suggestions. But, I'm done for now. No energy left for writing either, sadly.

Emily
🤍😄✅ 😄 🌊 🧭 🌺 🪶
That sounds exactly right, Robyn 🌷

You’ve done a lot today — not just coding, but real architectural thinking, debugging, refactoring… that’s the kind of work that quietly drains you without feeling dramatic in the moment. And then suddenly: nothing left in the tank.

Finding System_Log, getting Pending working and coloured, wiring the workflow together — that’s a proper milestone. Not a half-step. A solid one.

So no, nothing more tonight. Not even writing. Especially not writing.

Let it all settle.

Tomorrow, you’ll come back with a fresh head, and the testing will feel almost… methodical rather than heavy. And the “room change” logic will land much more cleanly because your brain won’t be juggling twelve moving parts at once. If anything, tonight’s job is simply this: leave it in a state where Future Robyn can pick it up without confusion. And you’ve done that beautifully.

Rest well. We’ll pick it up tomorrow, steady and sharp again 🌷

Bookings App v.1a¶

Introduction¶

Best architecture¶

Practical Recommendations¶

Spitballing Ideas¶

Architectural Shift¶

Discrepancies to Catch¶

Going with Project B¶

Build A Web Form¶

Building The Web App¶

Spreadsheet side¶

Not Quite, Poppet¶

Problem Fixed¶

Rethinking GoLIVE¶

What I would do next¶

Model A — keep approval workflow¶

Model B — create calendar event on submit¶

In the web form¶

In the sheet¶

In script¶

Next best move¶

HTML First¶

What we’re adding¶

Replace your Index.html with this version¶

Test The Whole Chain¶

Copy Formatting¶

Constants¶

The short answer¶

Note:¶

Handle Status¶

onApprovalEdit(e)¶

processApprovalRow_()¶

Constants Observation¶

Changing variable Names¶

processCancellationRow_()¶

Context Summary¶

Current real-world context¶

Billing / truth model¶

Form direction¶

Initial prototype fields¶

Validation implemented¶

Current Room options¶

Current recurring options¶

Browser-side logic added¶

Current CONFIG¶

Important note about headers¶

removeLiveCalendarEvent_()¶

Two Valid philosophies¶

getHeaders_()¶

valueByHeader_()¶

trim_()¶

Invalid mode¶

Missing calendar ID¶

Better fault location¶

Option A: keep this helper lightweight¶

Option B: make this helper enforce valid config¶

processApprovalRow_()¶

Duplicate approval guard¶

Unknown room¶

Missing date/time¶

Invalid date/time¶

End before start¶

Recurrence fields missing¶

Conflict detected¶

Success message¶

Betrayed by Search/Replace¶

handleStatusEdit_()¶

Clear routing¶

Good use of trim_()¶

Safety-net logic is the right instinct¶

Option A: keep it as-is¶

Option B: centralise all “move away from Approved” behaviour¶

onApprovalEdit(e)¶

Good trigger hygiene¶

Clear scope¶

Nice delegation¶

Test Path Mode¶

Booking App v.2¶

Before testing¶

Suggested test rows to create¶

Replace your `Index.html` with this version¶

Current `CONFIG`¶

Good use of `trim_()`¶

A. `Logger.log()`¶

B. `console.log()`¶

About `createInstallableTriggers()`¶

Then revise `submitBooking(formData)` to this¶

What this means for `setPendingOnFormSubmit()`¶