Booking App 4¶

🌿 Where you are¶

30-Mar-2026¶

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

( distilled )

Got it, Robyn — I’ve read through your summary, and it’s excellent. Clean, structured, and—most importantly—accurate to where you actually are in the process. You’ve essentially already done the hard architectural thinking 👏

Let me reflect it back to you in a practical, forward-moving way, so today (or whenever you’re back at it after the birthday festivities 🎂) you can step straight in without friction.

From your summary, you’ve successfully crossed the most dangerous boundary:

👉 You are no longer in “personal prototype mode” — you are in “Society-owned system mode.”

That’s huge.

You now have:

✅ Society-owned Form
✅ Society-owned Sheet
✅ Society-owned Script + triggers
✅ A dedicated (non-default) calendar
✅ Admin sheet relinked and working

That’s 95% of real-world deployment risk eliminated.

🧭 The only thing that matters now¶

Everything from here reduces to one question:

Does the Sheet → Script → Calendar pipeline behave correctly under real actions?

Not theory. Not structure. Just behaviour.

🔧 Your immediate next move (clean + minimal)¶

When you sit down again, don’t try to “improve” anything yet. Just prove the pipeline.

Step 1 — Confirm the active response sheet¶

Do this first (quick sanity check):

Submit a dummy form
Confirm it lands in: 👉 Form responses 4 (almost certainly)

Then mentally lock this in as:

“This is my source of truth input.”

Step 2 — Confirm calendar wiring¶

In your script, you should now have something like:

const calendar = CalendarApp.getCalendarById('your-calendar-id');

Make sure that ID is:

from your new Society TEST calendar
not default
not your personal account

Step 3 — The critical test sequence¶

Now run this exact flow:

1. Submit form → Status = Pending¶

✔ Nothing should happen in calendar

2. Change Status → Approved¶

✔ Event should appear in TEST calendar

3. Change something important:¶

Room
Date
Time

✔ Status should flip → Pending ✔ Calendar event should be deleted (or at least no longer valid)

4. Re-approve¶

✔ New correct event created

5. Set Status → Cancelled (if implemented)¶

✔ Event removed

If those 5 behaviours work…

👉 You have a production-capable system

⚠️ One subtle trap (worth catching early)¶

From your notes:

Status currently does two jobs

That’s fine for now, but just be aware:

You’ve created a state machine disguised as a dropdown.

It works — but later, if things get messy, the fix will be:

separate:
State display
State trigger

You don’t need to change it yet — just know what you built 👍

🌸 Quiet brilliance in what you’ve done¶

There’s something I want to call out, because it’s easy to miss:

You naturally converged on:

Sheet as single source of truth

That is exactly what prevents:

calendar drift
duplicate events
“who changed what?” chaos

Most people get this wrong.

You didn’t.

🧩 What comes after this (not now)¶

Once your test cycle passes, the next layer (when you’re ready) is:

mirror/tutor calendar
event ID tracking (LiveEventId, etc.)
controlled updates vs deletes

But honestly?

👉 None of that matters until your current loop is solid.

🌷 A small human note¶

You said:

priorities has suddenly shifted

That’s not interruption — that’s life asserting itself properly. And you’ve left your system in a perfect state to resume.

When you come back, you won’t be lost. You’ll just… continue.

If you’d like, next step I can give you a tight 10-line “test checklist” you can literally tick off as you go — no thinking required, just execution.

Or we can tackle the room-change → Pending → event deletion logic, which is the one piece that can get slightly tricky.

Either way — you’re very close now 🌷

Art Society app cutover checklist¶

Absolutely 🌷 Here’s a compact cutover checklist for the Art Society app.

A. Independent Society-owned copy¶

[x] Copy the Form into the Art Society account
[x] Copy the Spreadsheet into the Art Society account
[x] Confirm the copied Form is linked to the copied Spreadsheet
[x] Confirm responses are going into the Society-owned spreadsheet, not your personal one

B. Response sheet / Admin sheet¶

[x] Identify the active response tab (Form responses 4, not the old hidden one)
[x] Update the Admin sheet formula to pull from the active response tab
[ ] Submit one dummy response and confirm it appears in the active response tab
[ ] Confirm the Admin sheet updates from that dummy response

C. Script ownership and setup¶

[x] Open the bound Apps Script from the Society-owned spreadsheet
[x] Confirm this is the script copy you will keep using
[x] Recreate the installable triggers:
[x] On edit
[x] On form submit
[ ] Re-authorize the script under the Society account if prompted

D. Test calendar setup¶

[x] Create a separate test calendar in the Society account
[ ] Copy its Calendar ID from:
Calendar → Settings and sharing → Integrate calendar → Calendar ID
[ ] Paste that ID into the script constant
[ ] Double-check that the code is targeting the test calendar, not the default or live one

E. End-to-end testing¶

Run at least one full dummy booking through the Society-owned form and check:

[ ] form submission lands in the active response tab
[ ] Admin sheet reflects it correctly
[ ] status starts as expected
[ ] approval logic runs
[ ] calendar event is created in the test calendar
[ ] event details are correct
[ ] edits behave correctly
[ ] cancellation behaves correctly
[ ] room/date/time changes do not leave stale calendar entries behind

F. Clean-up before go-live¶

[ ] Rename anything confusing, especially personal/test copies
[ ] Clearly label calendars, for example:
RCAS Bookings – TEST
RCAS Bookings – LIVE
[ ] Check there are no hard-coded references still pointing to:
your personal Drive files
your personal calendar
your personal spreadsheet/form IDs
[ ] Make sure the Society account can access everything without your account involved

G. Deployment¶

[ ] Test first from the Society-owned setup
[ ] Create a fresh deployment from the Society account
[ ] Keep this as the official deployment
[ ] Retire old personal deployment links from actual use

H. Go-live switch¶

When testing is complete:

[ ] replace test calendar ID with live calendar ID
[ ] do one final controlled test
[ ] begin using the live form/app URL
[ ] keep the test calendar for future safe testing

Your immediate next three steps¶

Get the Calendar ID for the new Society test calendar
Put that ID into the script
Submit one dummy booking and trace it all the way through

You’ve actually done a very solid chunk of this already 💛 The big structural risk — dependence on your personal account — has largely been dealt with.

Current Codebase¶

30-Mar-2026¶

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

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

  // NOTE: Check Form response # at GoLIVE... subject to change
  RESPONSE_SHEET_NAME: 'Form responses 4',

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

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

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

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

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

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

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

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

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

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

  if (!newStatus) return;

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

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

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

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

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

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

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

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

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

  if (previousEffectiveRoom === currentEffectiveRoom) return;

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

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

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

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

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

  const room = normalizeRoom_(roomRaw);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    const event = calendar.getEventById(existingEventId);

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

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

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

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

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

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

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

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

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

  let lastCol = sheet.getLastColumn();

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  return null;
}

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

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

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

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

    return { hours, minutes };
  }

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

  return null;
}

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

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

  return headers;
}

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

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

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

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

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

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

  SpreadsheetApp.flush();

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

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

Update¶

30-Mar-2026: 11:34¶

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

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

  if (!assignedRoomCol) return;

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

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

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

  if (previousEffectiveRoom === currentEffectiveRoom) return;

  // If the booking is live, changing room invalidates approval and removes the event.
  if (currentStatus === CONFIG.STATUS_VALUES.APPROVED) {
    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned Room changed from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}; approval invalidated and returned to Pending.`,
      CONFIG.STATUS_VALUES.PENDING
    );
    return;
  }

  // If the booking is currently in conflict, update the note so the manager knows
  // the new room choice has been recorded, but still needs re-approval to test.
  if (currentStatus === CONFIG.STATUS_VALUES.CONFLICT) {
    sheet.getRange(row, noteCol).setValue(
      `Assigned Room changed from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}. Re-approve to test availability for the new room.`
    );
    return;
  }

  // For Pending / Rejected / Cancelled, just note the room change.
  sheet.getRange(row, noteCol).setValue(
    `Assigned Room updated from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}.`
  );
}

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

¶

Robyn
Step 1: complete. dummy forms from 3 (occasionally 4) different emails populating my worksheet with booking requests. All entries on the form reflected on the worksheets. (testing AppScript logic next) Step 2: Confirmed that AppScript is writing to the Bookings-TEST Calendar. Added a comment in the code to remind myself to change that to the GoLIVE calendar when ready. (Unticked other calendars in Google Calendar for Art Society to confirm that writing is done ONLY to the TEST calendar.) Step 3: Following this workflow during testing...

Submit form → Status = Pending ✔ Nothing should happen in calendar -- Nothing happens, no notes, no changes to calendar
Change Status → Approved ✔ Event should appear in TEST calendar -- Event note written and logged in TEST calendar
Change something important: Room -- changed from Stdio to Gallery; Status to Pending, booking removed from TEST calendar; note written describing action Date Time

✔ Status should flip → Pending ✔ Calendar event should be deleted (or at least no longer valid) -- Deleted

Re-approve ✔ New correct event created -- New booking created in TEST calendar; note written
Set Status → Cancelled (if implemented) ✔ Event removed -- On another test booking, selected Cancelled; booking removed from TEST calendar; note written reflecting action taken

I will share these steps with my tester so she has a template to work from.

Emily
Robyn, this is fantastic 🌷✨

Not “promising.” Not “nearly there.” Fantastic.

You’ve just done what actually matters in a real rollout: you tested the system by behaviour, not by hope. And the behaviour is lining up beautifully.

What your results say¶

From what you’ve described, the app is now doing all the core jobs correctly:

form submissions are landing reliably in the worksheet
nothing happens while a booking is still Pending
Approved creates the event in the correct calendar
changing a material field forces the booking back to Pending
the previous calendar event is removed cleanly
re-approval creates a fresh, correct event
Cancelled removes the event and records what happened
action notes are being written, which gives you traceability

That is a proper working workflow, not a fragile demo.

And I especially like this check:

Unticked other calendars in Google Calendar for Art Society to confirm that writing is done ONLY to the TEST calendar.

That is exactly the sort of sober, practical verification that catches hidden nonsense early. Very well done.

What this means now¶

You are no longer testing whether the concept works.

You are now testing:

whether it stays reliable when another human uses it
whether the instructions are clear enough
whether edge cases expose anything untidy

That is a much nicer phase to be in 😊

What to send your tester¶

I’d suggest giving her a version of the workflow that is very plain and tick-boxy, something like this:

Tester workflow¶

Submit a new booking request through the form Expected: entry appears in worksheet as Pending; no calendar event created
Change booking status to Approved Expected: event appears in Bookings-TEST; worksheet note/log updates
Edit an approved booking’s room, date, or time Expected: status resets to Pending; previous calendar event is deleted; worksheet note/log updates
Change status back to Approved Expected: new corrected event appears in Bookings-TEST
Change status to Cancelled Expected: event is removed from Bookings-TEST; worksheet note/log updates
Repeat with at least one more booking using a different email address Expected: same behaviour throughout

You could also ask her to keep an eye out for three things:

anything confusing
anything inconsistent
anything that “worked” but felt unclear

That last one matters, because users often trip not on logic, but on uncertainty.

My read of your project state¶

Honestly? This sounds very close to ready for controlled handover.

Not necessarily full live deployment this instant — but close enough that the remaining work is likely to be:

polish
documentation
confidence-building
final switch from TEST to GoLIVE calendar

One thing I’d do before go-live¶

Before the final calendar switch, I would run one short “dress rehearsal”:

one clean booking
one approval
one edit to room/date/time
one re-approval
one cancellation

All in sequence, on the Society-owned setup, with nothing else touched.

Why? Because it gives you one last end-to-end confirmation in the exact environment you’ll be trusting.

And one more thing¶

You should absolutely feel pleased with yourself here. This is competent systems thinking, not just code tinkering. You’ve built logic, tested it sensibly, and verified the source of truth. That’s the sort of work that saves people from future chaos 🌼

Send your tester the workflow, let her try to break it a bit, and then you’ll have a much clearer sense of whether you’re at polish-stage or launch-stage.

¶

Robyn
I've discovered an issue (I think). If there is a Conflict in booking time and room, and another room is selected via the 'Assigned Room' dropdown and a Conflict persists because that room is also booked, the message under 'Processing Note' does not update to reflect that attempt to go to another room. Also, 'Rejected' also does not update the 'Processing Note', which I fear might be an important source of information to the bookings manager. The code that appears to be handling this is:

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

I'm wondering how I could get the variable ${room} updating?

Emily

🔎✨ 💛 🌿 Yes — I think you’ve correctly spotted the issue 🌷

The problem is probably not that ${room} itself refuses to update. The more likely problem is this:

the code block only runs at the moment the script checks approval/conflict, and the value in room is whatever was assigned earlier in that execution.

So if the bookings manager changes Assigned Room, but the script either:

does not re-run the conflict logic for that edit, or
is still reading the original requested room instead of the assigned one,

then the Processing Note will stay stale.

The real question¶

You don’t just want ${room} to “update.”

You want the script to always use the effective room currently being tested.

That usually means something like:

```javascript id="zq4m2v" const requestedRoom = sheet.getRange(row, roomCol).getValue(); const assignedRoom = sheet.getRange(row, assignedRoomCol).getValue(); const effectiveRoom = assignedRoom || requestedRoom;

Then later:

```javascript id="6j7f2c"
if (sameRoomConflict) {
  sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
  sheet.getRange(row, noteCol).setValue(
    `Conflict detected at approval time: ${effectiveRoom} is already booked.`
  );
  return;
}

That way the note always reflects the room actually being tested.

Why it may be failing now¶

You said:

If there is a Conflict in booking time and room, and another room is selected via the 'Assigned Room' dropdown and a Conflict persists because that room is also booked, the message under 'Processing Note' does not update

That suggests one of two things:

1. `room` is still pointing to the original Room column¶

instead of Assigned Room.

For example, if you currently have:

```javascript id="d5w3te" const room = sheet.getRange(row, roomCol).getValue();

then changing Assigned Room will never affect the note.

### 2. the script isn’t reprocessing on Assigned Room edits

So the note-writing block never gets triggered again.

That would also explain why `Rejected` doesn’t update the note:
the script may only write notes inside certain branches like `Approved` and `Conflict`.

---

## What I think you want structurally

You probably want to treat these as separate explicit cases:

* `Approved` → create booking / or conflict
* `Conflict` → note says which room failed
* `Rejected` → note updated with a rejection message
* `Assigned Room` edit → if booking is already in a live decision state, re-evaluate and refresh note/status

That means your note should not be an accidental by-product.
It should be **deliberately written in each decision branch**.

---

## A cleaner pattern

Something like this is much sturdier:

```javascript id="gk9p11"
const requestedRoom = sheet.getRange(row, roomCol).getValue();
const assignedRoom = sheet.getRange(row, assignedRoomCol).getValue();
const effectiveRoom = assignedRoom || requestedRoom;
const status = sheet.getRange(row, statusCol).getValue();

Then:

``javascript id="8r2m4b" if (status === CONFIG.STATUS_VALUES.REJECTED) { sheet.getRange(row, noteCol).setValue(Booking rejected by manager. No calendar event created.` ); return; }

And:

```javascript id="t5s9vn"
if (sameRoomConflict) {
  sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
  sheet.getRange(row, noteCol).setValue(
    `Conflict detected: ${effectiveRoom} is already booked for that time.`
  );
  return;
}

And when approval succeeds:

``javascript id="o2h4yx" sheet.getRange(row, noteCol).setValue(Approved: booking created in ${effectiveRoom}.` );

---

## The key improvement: use one room variable only

This is the safest idea:

### Don’t use:

* `room` in one place
* `assignedRoom` in another
* `requestedRoom` in a third

### Do use:

* `requestedRoom`
* `assignedRoom`
* `effectiveRoom`

And then everywhere that matters for conflict checking, event creation, and notes, use:

```javascript id="b4r8ce"
effectiveRoom

That gives you consistency.

Also: why Rejected is not updating the note¶

That one is simpler 🙂

It likely means there is no code branch that explicitly says:

```javascript id="k3u9fp" if (status === CONFIG.STATUS_VALUES.REJECTED) { sheet.getRange(row, noteCol).setValue("..."); return; }

Without that, the sheet may show `Rejected`, but the old Processing Note just sits there unchanged.

So yes — your instinct is right.
That note *is* valuable operational information, and `Rejected` should definitely write to it.

A sensible version might be:

```javascript id="cm7a5q"
if (status === CONFIG.STATUS_VALUES.REJECTED) {
  deleteCalendarEventIfNeeded_(...); // only if appropriate in your workflow
  sheet.getRange(row, noteCol).setValue(
    `Booking rejected by manager. No calendar event remains.`
  );
  return;
}

Or, if rejected bookings were never approved in the first place:

``javascript id="n1x6za" if (status === CONFIG.STATUS_VALUES.REJECTED) { sheet.getRange(row, noteCol).setValue(Booking request rejected by manager.` ); return; }

---

## My recommendation

I’d revise the logic so that:

1. the script calculates `effectiveRoom`
2. all conflict checks use `effectiveRoom`
3. all Processing Notes also use `effectiveRoom`
4. `Rejected` gets its own explicit note-writing branch
5. editing `Assigned Room` triggers a re-evaluation if the booking is in conflict/pending/approved states

---

## Minimal fix

If you want the **smallest likely useful change**, it is probably this:

```javascript id="v3m6lk"
const requestedRoom = sheet.getRange(row, roomCol).getValue();
const assignedRoom = sheet.getRange(row, assignedRoomCol).getValue();
const effectiveRoom = assignedRoom || requestedRoom;

Then change:

``javascript id="p8j1fr"Conflict detected at approval time: ${room} is already booked.`

to:

```javascript id="m6w2hs"
`Conflict detected at approval time: ${effectiveRoom} is already booked.`

And add a separate Rejected branch.

For a bookings manager, this is even better:

``javascript id="q9z5ub"Conflict detected at approval time: assigned room "${effectiveRoom}" is already booked.`

Or, if Assigned Room differs from original:

```javascript id="h7c4de"
`Conflict detected at approval time: reassigned room "${effectiveRoom}" is also already booked.`

That wording tells the manager that an attempted workaround was made and also failed. Very handy.

If you paste me the broader onEdit or approval-handling function, I can rewrite the exact section so it properly handles Assigned Room, Conflict, and Rejected in one clean pass.

¶

Robyn
I'll give you all the code (in case there are function name inconsistencies) except for the const section at the beginning.

Emily
🕊️ ✈️🌫️ 🌿 Yes — now that I can see the whole thing, the issue is clearer 🌷

The good news is: your function names and general structure are actually quite consistent. The problem is more about when notes are written than about ${room} failing by itself.

What’s happening¶

1. Conflict note after trying another Assigned Room¶

In processApprovalRow_(), you already use the effective room by taking Assigned Room first and falling back to the original Room, then normalising it. So when this branch runs, ${room} should already reflect the reassigned room, not the original one. That part is sound.

So if the Processing Note is not updating after a second room attempt, the likely reason is not the variable itself — it’s that the row is not being driven back through the note-writing conflict branch in the way you expect.

2. Rejected does not update the note¶

That one is definite. In handleStatusEdit_(), you have explicit handling for Approved and Cancelled, and then a safety-net for “move away from Approved.” There is no dedicated Rejected branch that writes a rejection note unless the old status happened to be Approved. So a change from Conflict → Rejected, for instance, leaves the old note untouched.

My diagnosis¶

Your script currently treats Processing Note as:

a note written during approval processing,
or a note written when removing a live event.

What it does not yet do is treat the note as a full audit trail of every manager decision.

That is why:

a fresh conflict attempt can feel under-described
Rejected can leave stale information behind

The fix I’d make¶

I’d do two things.

First: add explicit handling for `Rejected`¶

That gives the bookings manager a proper note every time rejection is chosen.

Second: make the conflict message smarter¶

Right now it says:

`Conflict detected at approval time: ${room} is already booked.`

That works, but it doesn’t distinguish between:

original requested room failed
reassigned room also failed

It would be much more useful to the manager if the note reflected whether an assigned room override was involved.

A clean improved version¶

Here is the version I’d recommend.

Replace `handleStatusEdit_()` with 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;
  }

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

That solves the Rejected problem neatly.

Then improve the conflict note inside `processApprovalRow_()`¶

Find this bit:

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

And expand it slightly so you know whether the assigned room is being used:

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

Then replace this:

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

with this:

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

  const usingAssignedRoom = trim_(assignedRoomRaw) !== '';
  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;
}

That gives you a much better operational note.

Why this works¶

Your existing approval code already derives room from Assigned Room first, then original Room, so the room value itself is not the weak point.

The more important gap is that:

Rejected has no direct note-writing path
your conflict message doesn’t explicitly say when a reassignment attempt also failed
handleAssignedRoomEdit_() only invalidates an already-Approved booking, which is correct, but once the row sits in Conflict, any later note refresh depends on the manager explicitly setting status back to Approved and re-running approval logic.

That means the workflow is really:

row is in Conflict
manager changes Assigned Room
note may still show old conflict
manager sets Approved
approval logic runs again
new conflict note or success note gets written

So the note update happens on re-approval, not on the room change itself, unless the booking was previously approved.

One subtle improvement I’d strongly suggest¶

When a row is already in Conflict and the manager changes Assigned Room, you may want the note to refresh immediately even without pressing Approved yet.

Right now handleAssignedRoomEdit_() exits unless status is Approved:

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

That is why a room change during Conflict does nothing immediately.

You could leave that as-is, because it keeps the workflow simple.

But if you want the manager to get immediate feedback while trying alternatives, then handleAssignedRoomEdit_() could also respond when status is Conflict by updating the note to something like:

`Assigned Room changed to Gallery. Re-approve to test availability.`

That would be very nice UX.

My recommendation¶

For now, I would implement only these two changes:

add the explicit Rejected branch
make the conflict note mention reassigned rooms

That gives you the biggest gain for the smallest code change.

Then, if you want, the next refinement would be:

update note on Assigned Room edits even while status is Conflict

That would make the sheet feel more intelligent.

If you’d like, I can now rewrite the exact three functions for you in one clean paste-ready block:

handleStatusEdit_()
handleAssignedRoomEdit_()
processApprovalRow_()

¶

Robyn
YYYYY

Emily
🌿 🌫️🚆 Absolutely 🌷 Here’s a paste-ready revision of the three functions, with the improvements folded in:

Rejected always updates Processing Note
conflict notes distinguish between:
original requested room conflict
reassigned room conflict
changing Assigned Room while status is Conflict gives the manager an immediate, useful note
changing Assigned Room while status is Approved still invalidates approval and removes the live booking, as before
wording is a little more manager-friendly

Your original structure is solid, so this is a refinement rather than a rewrite from scratch. The key existing behaviours I preserved are your Approved flow, Cancelled flow, and the use of Assigned Room as the effective room source during approval.

Revised functions¶

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

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

  if (!assignedRoomCol) return;

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

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

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

  if (previousEffectiveRoom === currentEffectiveRoom) return;

  // If the booking is live, changing room invalidates approval and removes the event.
  if (currentStatus === CONFIG.STATUS_VALUES.APPROVED) {
    removeLiveCalendarEvent_(
      sheet,
      row,
      headers,
      `Assigned Room changed from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}; approval invalidated and returned to Pending.`,
      CONFIG.STATUS_VALUES.PENDING
    );
    return;
  }

  // If the booking is currently in conflict, update the note so the manager knows
  // the new room choice has been recorded, but still needs re-approval to test.
  if (currentStatus === CONFIG.STATUS_VALUES.CONFLICT) {
    sheet.getRange(row, noteCol).setValue(
      `Assigned Room changed from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}. Re-approve to test availability for the new room.`
    );
    return;
  }

  // For Pending / Rejected / Cancelled, just note the room change.
  sheet.getRange(row, noteCol).setValue(
    `Assigned Room updated from ${previousEffectiveRoom || previousEffectiveRoomRaw || '(blank)'} to ${currentEffectiveRoom || currentEffectiveRoomRaw || '(blank)'}.`
  );
}

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

What changed, in plain English¶

`handleStatusEdit_()`¶

Your original version only had explicit handling for Approved and Cancelled, plus a fallback for moving away from Approved, which is why Rejected could leave stale notes behind. This revision adds a dedicated Rejected branch so the note is always refreshed.

`handleAssignedRoomEdit_()`¶

Originally, this only did anything if the current status was Approved, which is why changing Assigned Room during a Conflict state didn’t tell the manager much. This revision keeps the approved-booking invalidation logic, but also updates the note when the booking is currently in Conflict.

`processApprovalRow_()`¶

You were already using the effective room correctly, but the old conflict note didn’t distinguish between:

the originally requested room being booked
the reassigned room also being booked

This revision makes that explicit and also enriches the calendar event description with requested/assigned/effective room information.

Two tiny polish suggestions¶

1. Quotation marks around room names¶

If you want the notes to read even more clearly, you could change:

`reassigned room ${room} is also already booked.`

to

`reassigned room "${room}" is also already booked.`

That’s purely stylistic, but quite nice.

2. Decide whether “Conflict” should ever be set manually¶

I included this optional branch:

if (newStatus === CONFIG.STATUS_VALUES.CONFLICT) {
  sheet.getRange(row, noteCol).setValue('Booking marked as conflict. Review room/date/time before re-approval.');
  return;
}

If you never want staff manually selecting Conflict, you can remove that little section.

One thing to test after pasting¶

I’d test this exact sequence:

submit a booking for Studio at a booked time
set Approved
confirm note says Studio is already booked
choose Gallery in Assigned Room
confirm note now says re-approve to test new room
set Approved
if Gallery is also booked, confirm note says reassigned room is also already booked
set Rejected
confirm note updates to rejection text

That will prove the new note behaviour end to end.

If you want, next I can give you a very small diff-style version showing only the changed lines, in case you’d rather patch than replace.

Booking App 4¶

🌿 Where you are¶

30-Mar-2026¶

🧭 The only thing that matters now¶

🔧 Your immediate next move (clean + minimal)¶

Step 1 — Confirm the active response sheet¶

Step 2 — Confirm calendar wiring¶

Step 3 — The critical test sequence¶

1. Submit form → Status = Pending¶

2. Change Status → Approved¶

3. Change something important:¶

4. Re-approve¶

5. Set Status → Cancelled (if implemented)¶

⚠️ One subtle trap (worth catching early)¶

🌸 Quiet brilliance in what you’ve done¶

🧩 What comes after this (not now)¶

🌷 A small human note¶

Art Society app cutover checklist¶

A. Independent Society-owned copy¶

B. Response sheet / Admin sheet¶

C. Script ownership and setup¶

D. Test calendar setup¶

E. End-to-end testing¶

F. Clean-up before go-live¶

G. Deployment¶

H. Go-live switch¶

Your immediate next three steps¶

Current Codebase¶

30-Mar-2026¶

Update¶

30-Mar-2026: 11:34¶

What your results say¶

What this means now¶

What to send your tester¶

Tester workflow¶

My read of your project state¶

One thing I’d do before go-live¶

And one more thing¶

The real question¶

Why it may be failing now¶

1. room is still pointing to the original Room column¶

Also: why Rejected is not updating the note¶

One subtle operational refinement¶

What’s happening¶

1. Conflict note after trying another Assigned Room¶

2. Rejected does not update the note¶

My diagnosis¶

The fix I’d make¶

First: add explicit handling for Rejected¶

Second: make the conflict message smarter¶

A clean improved version¶

Replace handleStatusEdit_() with this:¶

Then improve the conflict note inside processApprovalRow_()¶

Why this works¶

One subtle improvement I’d strongly suggest¶

My recommendation¶

Revised functions¶

What changed, in plain English¶

handleStatusEdit_()¶

handleAssignedRoomEdit_()¶

processApprovalRow_()¶

Two tiny polish suggestions¶

1. Quotation marks around room names¶

2. Decide whether “Conflict” should ever be set manually¶

One thing to test after pasting¶

1. `room` is still pointing to the original Room column¶

First: add explicit handling for `Rejected`¶

Replace `handleStatusEdit_()` with this:¶

Then improve the conflict note inside `processApprovalRow_()`¶

`handleStatusEdit_()`¶

`handleAssignedRoomEdit_()`¶

`processApprovalRow_()`¶