Booking App 3¶

Initial Calendar script¶

24-Mar-2026¶

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

Absolutely 🌷 Here’s a clean context summary of this thread.

Thread context summary¶

Art Society app cutover, calendar ownership, and testing

Robyn is in the process of moving the Art Society booking app out of a personal/testing environment and into a fully Society-owned Google account context so the app will not depend on Robyn’s personal Drive, calendar, or deployment.

1. Calendar-link behaviour¶

Robyn noticed that a Calendar link placed at the top of the booking form opened the viewer’s own Google Calendar shell, not the Society’s calendar. We established that this is normal Google behaviour: a generic Calendar link opens Calendar for the signed-in user, not the app’s target calendar. The correct pattern is to use:

a dedicated Society calendar,
a proper share/view/subscription link if needed,
and in Apps Script, CalendarApp.getCalendarById(...) rather than assuming a default calendar.

2. Testing with a separate calendar¶

Robyn wanted to test against a non-production calendar first. We discussed using:

a separate Google account initially,
then preferably a test calendar inside the Society account once access became available.

We concluded that the cleanest setup is:

Art Society Bookings – TEST
Art Society Bookings – LIVE
optionally also a tutor-facing mirror calendar.

This keeps the real bookings calendar clean while testing.

3. Mirror calendar¶

Tutor-facing read-only view

Robyn wanted tutors to have read-only access only, not editing rights. We discussed using a calendar mirror webpage or tutor-facing availability calendar rather than exposing the real working calendar.

Recommended structure:

real internal calendar = authoritative booking calendar
separate tutor-facing mirror calendar = read-only display of availability
booking form = request entry point

The mirror calendar should contain generic blocking events such as:

Booked – Studio
or simply busy slots, so tutors can view availability without seeing sensitive booking details.

4. Dual-calendar sync logic¶

We sketched a workflow where the sheet remains the source of truth, and the script writes to:

the real calendar
and the mirror calendar

Suggested hidden columns:

LiveEventId
MirrorEventId

Behaviour discussed:

Pending → no events created
Approved → create live event + mirror event
Cancelled → delete both events
Approved booking changed in date/time/room → revert to Pending, delete both events, clear stored IDs
minor metadata changes → optionally update existing events

The mirror calendar is downstream only, never the source of truth.

5. Access to the Society account¶

Robyn was then given access to the Art Society Google account, including Calendar and likely Drive. This changed the recommended strategy significantly for the better.

We concluded that the correct long-term structure is:

Society-owned Form
Society-owned Sheet
Society-owned script
Society-owned triggers
Society-owned calendars
Society-owned deployment

This avoids future breakage or dependency on Robyn’s own account.

6. Ownership and cutover concept¶

Robyn correctly identified that merely sharing a file from a personal Drive is not the same as giving the Society ownership. We clarified that:

shared access is not operational ownership,
Apps Script deployments do not transfer ownership cleanly,
and the Society needs its own completely independent copy.

We introduced the idea of a cutover, meaning: the move from Robyn’s personal/testing environment to a Society-owned production-capable environment.

7. Independent Society-owned copy¶

We worked through how to create a fully independent copy of the form and sheet:

make a copy of the spreadsheet while signed into the Society account
make a copy of the form while signed into the Society account
reconnect the copied form to the copied Society-owned spreadsheet

This succeeded. Robyn confirmed that the copied form/sheet setup was a completely new copy, which was exactly the desired result.

8. Form response destination issue¶

While reconnecting the copied Form to the copied spreadsheet, Google created a new sheet tab:

Form responses 4

Robyn had earlier hidden:

Form responses 3

We determined that:

Google often creates a new response tab when linking/relinking,
Form responses 4 is likely now the live destination,
and it should not be deleted immediately.

Recommended approach:

keep Form responses 4
send a dummy form submission
confirm which tab receives the response
then treat that tab as the active response source

9. Admin sheet relinking¶

Robyn needed to relink the Admin sheet. We clarified that if the Admin sheet is just another tab inside the same spreadsheet, there is no special file-level relink; only formulas need to point to the correct response tab.

Robyn showed this formula:

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

We identified it as Excel structured-table syntax, not valid Google Sheets syntax.

Recommended replacement formulas included:

='Form responses 4'!A:Z

or:

=ARRAYFORMULA('Form responses 4'!A:Z)

or better:

=QUERY('Form responses 4'!A:Z,"select * where A is not null",1)

Robyn updated this successfully.

10. Trigger recreation¶

Robyn asked about which triggers needed recreating. The relevant pair turned out to be:

one On edit trigger
one On form submit trigger

Robyn found them and recreated the triggers in the Society-owned script context.

11. New calendar ID¶

Later, Robyn created a new secondary calendar and needed the calendar ID for use in Apps Script. We established that in Google Calendar, the path is:

My calendars
hover calendar
⋮
Settings and sharing
Integrate calendar
copy Calendar ID

This is the ID needed for:

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

12. Present state at end of thread¶

By the end of the thread, Robyn had:

a Society-owned copy of the form and sheet
the Admin sheet pulling data again
triggers recreated
a new non-default calendar created for testing
and was preparing to continue testing, though family birthday obligations temporarily interrupted progress

13. Practical next steps implied by the thread¶

The next likely tasks after this thread are:

obtain the new calendar’s ID from Calendar settings
update the script constants to use the Society test calendar
submit one or more dummy form bookings
verify:
responses land in the active response tab
Admin workflow behaves correctly
approval/edit/cancellation logic behaves correctly
calendar events go to the new test calendar, not a default one
later switch from test to live calendar IDs when ready

If you’d like, I can also turn this into a compact cutover checklist you can paste into your notes and work through step by step.

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.

Code 2nd Go¶

25-Mar-2026¶

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

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

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

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

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

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

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

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

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

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

  processApprovalRow_(sheet, row, headers);
}

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

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

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

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

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

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

  const room = normalizeRoom_(roomRaw);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  return '';
}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  return null;
}

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

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

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

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

    return { hours, minutes };
  }

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

  return null;
}

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

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

  return headers;
}

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

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

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

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

  SpreadsheetApp.flush();

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

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


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

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

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

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

  SpreadsheetApp.flush();

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

2nd Update¶

25-Mar-2026¶

//  One-calendar, two-room booking workflow

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

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

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

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

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

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

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

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

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

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

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

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

  if (!newStatus) return;

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

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

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

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

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

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

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

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

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

  if (previousEffectiveRoom === currentEffectiveRoom) return;

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

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

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

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

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

  const room = normalizeRoom_(roomRaw);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    const event = calendar.getEventById(existingEventId);

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

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

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

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

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

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

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

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

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

  let lastCol = sheet.getLastColumn();

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  return null;
}

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

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

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

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

    return { hours, minutes };
  }

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

  return null;
}

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

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

  return headers;
}

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

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

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

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

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

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

  SpreadsheetApp.flush();

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

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