Booking App v0.1a¶
Original¶
05.Apr.2026¶
Main Script¶
/**
* 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);
}
}
// Updated 30-Mar-2026 to improve 'Processing Note' output (col K)
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
);
}
}
// Updated 30-Mar-2026 to improve 'Processing Note' output (col K)
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)'}.`
);
}
// Updated 30-Mar-2026 to improve 'Processing Note' output (col K)
function processApprovalRow_(sheet, row, headers) {
const calendar = getTargetCalendar_();
if (!calendar) {
throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
}
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const fullName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
const 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 courseName = 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 - ${fullName || 'Tutor'} (${room})`;
const descriptionLines = [
`Full Name: ${fullName || ''}`,
`Email: ${tutorEmail || ''}`,
`Requested Room: ${requestedRoomRaw || ''}`,
`Assigned Room: ${assignedRoomRaw || ''}`,
`Effective Room: ${room}`,
`Course Name: ${courseName || ''}`,
'',
'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);
}
}
Edited¶
05.Apr.2026¶
/**
* One-calendar, two-room booking workflow
*/
// ** Constants Set ** //
const CONFIG = {
CALENDAR_MODE: 'id', // 'default' or 'id'
// NOTE: This ID is to a TEST calendar - change at GoLIVE
CALENDAR_ID: '1c26f492c1488f8852cbf50f1203ce8efe868869ee4aa77768f73249736a3549@group.calendar.google.com',
// only used if CALENDAR_MODE = 'id'
SHEET_NAME: 'WebForm_Submissions',
// 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.
*/
// ** onApprovalEdit(e) ** //
function onApprovalEdit(e) {
if (!e || !e.range) return;
const sheet = e.range.getSheet();
if (sheet.getName() !== CONFIG.SHEET_NAME) return;
const row = e.range.getRow();
const col = e.range.getColumn();
if (row < 2) return;
const 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);
}
}
// ** handleStatusEdit_(sheet, row, headers, e) ** //
// Updated 30-Mar-2026 to improve
// 'Processing Note' output (col K)
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
);
}
}
// ** handleAssignedRoomEdit_(sheet, row, headers, e) ** //
// Updated 30-Mar-2026 to improve
// 'Processing Note' output (col K)
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)'}.`
);
}
// ** processApprovalRow_(sheet, row, headers) ** //
// Updated 30-Mar-2026 to improve
// 'Processing Note' output (col K)
function processApprovalRow_(sheet, row, headers) {
const calendar = getTargetCalendar_();
if (!calendar) {
throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
}
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const fullName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
const 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 courseName = 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 - ${fullName || 'Tutor'} (${room})`;
const descriptionLines = [
`Full Name: ${fullName || ''}`,
`Email: ${tutorEmail || ''}`,
`Requested Room: ${requestedRoomRaw || ''}`,
`Assigned Room: ${assignedRoomRaw || ''}`,
`Effective Room: ${room}`,
`courseName: ${courseName || ''}`,
'',
'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}.`);
}
// ** processCancellationRow_(sheet, row, headers, oldStatus) ** //
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
);
}
// ** removeLiveCalendarEvent_(sheet, row, headers, baseNote, newStatus) ** //
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());
}
// ** getTargetCalendar_() ** //
function getTargetCalendar_() {
if (CONFIG.CALENDAR_MODE === 'default') {
return CalendarApp.getDefaultCalendar();
}
return CalendarApp.getCalendarById(CONFIG.CALENDAR_ID);
}
// ** normalizeRoom_(value) ** //
function normalizeRoom_(value) {
const v = trim_(value).toLowerCase();
const match = CONFIG.ROOM_VALUES.find(room => room.toLowerCase() === v);
return match || '';
}
// ** effectiveValue_(rowValues, headers, overrideHeader, originalHeader) ** //
function effectiveValue_(rowValues, headers, overrideHeader, originalHeader) {
const overrideValue = valueByHeader_(rowValues, headers, overrideHeader);
const originalValue = valueByHeader_(rowValues, headers, originalHeader);
return trim_(overrideValue) ? overrideValue : originalValue;
}
// ** ensureColumnsExist() ** //
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);
}
// ** createInstallableTriggers() ** //
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();
}
}
// ** combineDateAndTime_(datePart, timePart) ** //
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
);
}
// ** parseDatePart_(value) ** //
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;
}
// ** parseTimePart_(value) ** //
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;
}
// ** getHeaders_(sheet) ** //
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;
}
// ** valueByHeader_(rowValues, headers, headerName) ** //
function valueByHeader_(rowValues, headers, headerName) {
const col = headers[headerName];
if (!col) return '';
return rowValues[col - 1];
}
// ** trim_(value) ** //
function trim_(value) {
return value == null ? '' : String(value).trim();
}
// ** setPendingOnFormSubmit(e) ** //
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);
}
}
Constants¶
So, the first thing I'm copying are the constants:
const CONFIG = {
CALENDAR_MODE: 'id', // 'default' or 'id'
// NOTE: This ID is to a TEST calendar - change at GoLIVE
CALENDAR_ID: '1c26f492c1488f8852cbf50f1203ce8efe868869ee4aa77768f73249736a3549@group.calendar.google.com',
// only used if CALENDAR_MODE = 'id'
SHEET_NAME: 'WebForm_Submissions',
RESPONSE_SHEET_NAME: 'WebForm_Submissions',
ROOM_VALUES: ['Studio', 'Gallery'],
STATUS_VALUES: {
PENDING: 'Pending',
APPROVED: 'Approved',
REJECTED: 'Rejected',
CANCELLED: 'Cancelled',
CONFLICT: 'Conflict'
},
HEADERS: {
TIMESTAMP: 'Timestamp',
FULL_NAME: 'Full Name',
EMAIL: 'Email',
COURSE_NAME: 'Course Name',
ROOM: 'Room',
EVENT_DATE: 'Event Date',
START_TIME: 'Start Time',
END_TIME: 'End Time',
RECURRING: 'Recurring',
FREQUENCY: 'Frequency',
REPEAT_UNTIL: 'Repeat Until',
STATUS: 'Status',
EVENT_ID: 'Calendar Event ID',
NOTE: 'Processing Note'
}
};
Wasn't sure if 'RESPONSE_SHEET_NAME' was a required const, so I just used the sheet name for it.
Status Edit Logic¶
Process Row via Approval¶
function processApprovalRow_(sheet, row, headers) {
const calendar = getTargetCalendar_();
if (!calendar) {
throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
}
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const fullName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
const fnEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);
const courseName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
const roomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);
const recurring = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.RECURRING));
const frequency = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.FREQUENCY));
const repeatUntil = valueByHeader_(rowValues, headers, CONFIG.HEADERS.REPEAT_UNTIL);
const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_ID));
const statusCol = headers[CONFIG.HEADERS.STATUS];
const eventIdCol = headers[CONFIG.HEADERS.EVENT_ID];
const procNoteCol = headers[CONFIG.HEADERS.NOTE];
const room = normalizeRoom_(roomRaw);
if (existingEventId) {
sheet.getRange(row, fnEmail).setValue('Already created; skipped duplicate approval.');
return;
}
if (!room) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).setValue(`Unknown room: ${roomRaw}`);
return;
}
if (!bookingDate || !startTime || !endTime) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).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, fnEmail).setValue('Invalid date/time values.');
return;
}
if (end <= start) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).setValue('End time must be after start time.');
return;
}
if (recurring === 'Yes') {
if (!frequency || !repeatUntil) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).setValue('Recurring booking is missing Frequency or Repeat Until.');
return;
}
}
const overlapping = calendar.getEvents(start, end);
const sameRoomConflict = overlapping.some(event => {
const eventRoom = normalizeRoom_(event.getLocation());
return eventRoom === room;
});
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).setValue(
`Conflict detected at approval time: ${room} is already booked.`
);
return;
}
const title = `${fullName || 'Tutor'} (${room})`;
const descriptionLines = [
`Full Name: ${fullName || ''}`,
`Email: ${fnEmail || ''}`,
`Room: ${room}`,
`Course Name: ${courseName || ''}`,
`Recurring: ${recurring || 'No'}`,
`Frequency: ${frequency || ''}`,
`Repeat Until: ${repeatUntil || ''}`
];
const event = calendar.createEvent(title, start, end, {
location: room,
description: descriptionLines.join('\n')
});
sheet.getRange(row, eventIdCol).setValue(event.getId());
if (recurring === 'Yes') {
sheet.getRange(row, fnEmail).setValue(
`Calendar event created successfully in ${room}. Recurring logic not yet implemented.`
);
} else {
sheet.getRange(row, fnEmail).setValue(`Calendar event created successfully in ${room}.`);
}
}
Process Approval Row v2¶
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 = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);
const purpose = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
const recurring = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.RECURRING));
const frequency = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.FREQUENCY));
const repeatUntil = valueByHeader_(rowValues, headers, CONFIG.HEADERS.REPEAT_UNTIL);
const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_ID));
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;
}
if (recurring === 'Yes') {
if (!frequency || !repeatUntil) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, noteCol).setValue('Recurring booking is missing Frequency or Repeat Until.');
return;
}
}
const overlapping = calendar.getEvents(start, end);
const sameRoomConflict = overlapping.some(event => {
const eventRoom = normalizeRoom_(event.getLocation());
return eventRoom === room;
});
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, 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 || ''}`,
`Recurring: ${recurring || 'No'}`,
`Frequency: ${frequency || ''}`,
`Repeat Until: ${repeatUntil || ''}`
];
const event = calendar.createEvent(title, start, end, {
location: room,
description: descriptionLines.join('\n')
});
sheet.getRange(row, eventIdCol).setValue(event.getId());
if (recurring === 'Yes') {
sheet.getRange(row, noteCol).setValue(
`Calendar event created successfully in ${room}. Recurring logic not yet implemented.`
);
} else {
sheet.getRange(row, noteCol).setValue(`Calendar event created successfully in ${room}.`);
}
}
Process Approval Row v3¶
Note: this is throwing lots of errors
function processApprovalRow_(sheet, row, headers) {
const calendar = getTargetCalendar_();
if (!calendar) {
throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
}
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const fullName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
const fnEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);
const courseName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
const roomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);
const recurring = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.RECURRING));
const frequency = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.FREQUENCY));
const repeatUntil = valueByHeader_(rowValues, headers, CONFIG.HEADERS.REPEAT_UNTIL);
const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_ID));
const statusCol = headers[CONFIG.HEADERS.STATUS];
const eventIdCol = headers[CONFIG.HEADERS.EVENT_ID];
const procNoteCol = headers[CONFIG.HEADERS.NOTE];
const room = normalizeRoom_(roomRaw);
if (existingEventId) {
sheet.getRange(row, fnEmail).setValue('Already created; skipped duplicate approval.');
return;
}
if (!room) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).setValue(`Unknown room: ${roomRaw}`);
return;
}
if (!bookingDate || !startTime || !endTime) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).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, fnEmail).setValue('Invalid date/time values.');
return;
}
if (end <= start) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).setValue('End time must be after start time.');
return;
}
if (recurring === 'Yes') {
if (!frequency || !repeatUntil) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).setValue('Recurring booking is missing Frequency or Repeat Until.');
return;
}
}
const overlapping = calendar.getEvents(start, end);
const sameRoomConflict = overlapping.some(event => {
const eventRoom = normalizeRoom_(event.getLocation());
return eventRoom === room;
});
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, fnEmail).setValue(
`Conflict detected at approval time: ${room} is already booked.`
);
return;
}
const title = `${fullName || 'Tutor'} (${room})`;
const descriptionLines = [
`Full Name: ${fullName || ''}`,
`Email: ${fnEmail || ''}`,
`Room: ${room}`,
`Course Name: ${courseName || ''}`,
`Recurring: ${recurring || 'No'}`,
`Frequency: ${frequency || ''}`,
`Repeat Until: ${repeatUntil || ''}`
];
const event = calendar.createEvent(title, start, end, {
location: room,
description: descriptionLines.join('\n')
});
sheet.getRange(row, eventIdCol).setValue(event.getId());
if (recurring === 'Yes') {
sheet.getRange(row, fnEmail).setValue(
`Calendar event created successfully in ${room}. Recurring logic not yet implemented.`
);
} else {
sheet.getRange(row, fnEmail).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
);
}
Process Approval Row v4¶
function processApprovalRow_(sheet, row, headers) {
const calendar = getTargetCalendar_();
if (!calendar) {
throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
}
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const fullName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
const fnEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);
const courseName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
const roomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);
const recurring = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.RECURRING));
const frequency = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.FREQUENCY));
const repeatUntil = valueByHeader_(rowValues, headers, CONFIG.HEADERS.REPEAT_UNTIL);
const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_ID));
const statusCol = headers[CONFIG.HEADERS.STATUS];
const eventIdCol = headers[CONFIG.HEADERS.EVENT_ID];
const procNoteCol = headers[CONFIG.HEADERS.NOTE];
if (!statusCol) throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
if (!eventIdCol) throw new Error(`Missing header: ${CONFIG.HEADERS.EVENT_ID}`);
if (!procNoteCol) throw new Error(`Missing header: ${CONFIG.HEADERS.NOTE}`);
const room = normalizeRoom_(roomRaw);
if (existingEventId) {
sheet.getRange(row, procNoteCol).setValue('Already created; skipped duplicate approval.');
return;
}
if (!room) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue(`Unknown room: ${roomRaw}`);
return;
}
if (!bookingDate || !startTime || !endTime) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue('Missing date/time fields.');
return;
}
const start = combineDateAndTime_(bookingDate, startTime);
const end = combineDateAndTime_(bookingDate, endTime);
if (
!(start instanceof Date) || isNaN(start.getTime()) ||
!(end instanceof Date) || isNaN(end.getTime())
) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue('Invalid date/time values.');
return;
}
if (end <= start) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue('End time must be after start time.');
return;
}
if (recurring === 'Yes') {
if (!frequency || !repeatUntil) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue(
'Recurring booking is missing Frequency or Repeat Until.'
);
return;
}
}
const overlapping = calendar.getEvents(start, end);
const sameRoomConflict = overlapping.some(event => {
const eventRoom = normalizeRoom_(event.getLocation());
return eventRoom === room;
});
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue(
`Conflict detected at approval time: ${room} is already booked.`
);
return;
}
const title = `${fullName || 'Tutor'} (${room})`;
const descriptionLines = [
`Full Name: ${fullName || ''}`,
`Email: ${fnEmail || ''}`,
`Room: ${room}`,
`Course Name: ${courseName || ''}`,
`Recurring: ${recurring || 'No'}`,
`Frequency: ${frequency || ''}`,
`Repeat Until: ${repeatUntil || ''}`
];
const event = calendar.createEvent(title, start, end, {
location: room,
description: descriptionLines.join('\n')
});
sheet.getRange(row, eventIdCol).setValue(event.getId());
if (recurring === 'Yes') {
sheet.getRange(row, procNoteCol).setValue(
`Calendar event created successfully in ${room}. Recurring logic not yet implemented.`
);
} else {
sheet.getRange(row, procNoteCol).setValue(
`Calendar event created successfully in ${room}.`
);
}
}
Submit Booking¶
function submitBooking(formData) {
const sheetName = 'WebForm_Submissions';
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
if (!sheet) {
throw new Error(`Sheet "${sheetName}" not found.`);
}
const requiredFields = [
'fullName',
'email',
'courseName',
'room',
'eventDate',
'startTime',
'endTime',
'recurring'
];
for (const field of requiredFields) {
if (!formData[field] || String(formData[field]).trim() === '') {
throw new Error(`Missing required field: ${field}`);
}
}
if (formData.recurring === 'Yes') {
if (!formData.frequency || !formData.repeatUntil) {
throw new Error('Recurring bookings require Frequency and Repeat Until.');
}
}
const start = parseTimeToMinutes(formData.startTime);
const end = parseTimeToMinutes(formData.endTime);
if (start % 30 !== 0 || end % 30 !== 0) {
throw new Error('Times must be entered in 30-minute increments.');
}
if (end <= start) {
throw new Error('End time must be later than start time.');
}
sheet.appendRow([
new Date(),
formData.fullName,
formData.email,
formData.courseName,
formData.room,
formData.eventDate,
formData.startTime,
formData.endTime,
formData.recurring,
formData.frequency,
formData.repeatUntil
]);
return {
success: true,
message: 'Booking request submitted successfully.'
};
}
handleAssignedRoomEdit()¶
function handleAssignedRoomEdit_(e, sheet, row, headers) {
const oldValue = String(e.oldValue || '').trim();
const newValue = String(e.value || '').trim();
// If nothing materially changed, do nothing
if (oldValue === newValue) return;
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const oldEffectiveRoom = oldValue || requestedRoom;
const newEffectiveRoom = newValue || requestedRoom;
// If effective room did not actually change, do nothing
if (oldEffectiveRoom === newEffectiveRoom) return;
// Approved booking: remove live event and reset to Pending
if (status === CONFIG.STATUS_VALUES.APPROVED) {
removeLiveCalendarEvent_(
sheet,
row,
headers,
`Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
CONFIG.STATUS_VALUES.PENDING
);
return;
}
// Conflict booking: re-check whether the new effective room still conflicts
if (status === CONFIG.STATUS_VALUES.CONFLICT) {
const statusCol = findHeaderColumn_(headers, CONFIG.HEADERS.STATUS);
const noteCol = findHeaderColumn_(headers, CONFIG.HEADERS.PROCESSING_NOTE);
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 start = combineDateAndTime_(bookingDate, startTime);
const end = combineDateAndTime_(bookingDate, endTime);
if (!(start instanceof Date) || isNaN(start) || !(end instanceof Date) || isNaN(end)) {
sheet.getRange(row, noteCol).setValue(
'Assigned room changed, but date/time could not be evaluated. Please review booking details.'
);
return;
}
const sameRoomConflict = hasRoomConflict_(
sheet,
row,
headers,
newEffectiveRoom,
start,
end
);
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, noteCol).setValue(
`Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}, but conflict remains: ${newEffectiveRoom} is already booked for that time.`
);
} else {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.PENDING);
sheet.getRange(row, noteCol).setValue(
`Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Conflict cleared. Booking reset to Pending for re-approval.`
);
}
return;
}
}
handleAssignedRoomEdit() v2¶
function handleAssignedRoomEdit_(e, sheet, row, headers) {
const oldValue = String(e.oldValue || '').trim();
const newValue = String(e.value || '').trim();
// If nothing materially changed, do nothing
if (oldValue === newValue) return;
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const currentStatus = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS));
const requestedRoomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const noteCol = findHeaderColumn_(headers, CONFIG.HEADERS.PROCESSING_NOTE);
const oldEffectiveRoomRaw = oldValue || requestedRoomRaw;
const newEffectiveRoomRaw = newValue || requestedRoomRaw;
const oldEffectiveRoom = normalizeRoom_(oldEffectiveRoomRaw);
const newEffectiveRoom = normalizeRoom_(newEffectiveRoomRaw);
const oldEffectiveRoomDisplay = oldEffectiveRoom || oldEffectiveRoomRaw || '(blank)';
const newEffectiveRoomDisplay = newEffectiveRoom || newEffectiveRoomRaw || '(blank)';
// If effective room did not actually change, do nothing
if (oldEffectiveRoom === newEffectiveRoom) 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 ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}; approval invalidated and returned to Pending.`,
CONFIG.STATUS_VALUES.PENDING
);
return;
}
// If the booking is currently in conflict, automatically re-test the new room
if (currentStatus === CONFIG.STATUS_VALUES.CONFLICT) {
const statusCol = findHeaderColumn_(headers, CONFIG.HEADERS.STATUS);
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 start = combineDateAndTime_(bookingDate, startTime);
const end = combineDateAndTime_(bookingDate, endTime);
if (!(start instanceof Date) || isNaN(start) || !(end instanceof Date) || isNaN(end)) {
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}, but the booking date/time could not be evaluated. Please review and re-approve manually.`
);
return;
}
if (end <= start) {
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}, but the booking has an invalid time range. Please review and re-approve manually.`
);
return;
}
const sameRoomConflict = hasRoomConflict_(
sheet,
row,
headers,
newEffectiveRoom,
start,
end
);
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}, but a conflict still exists for that room and time.`
);
} else {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.PENDING);
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}. Conflict cleared; booking returned to Pending for re-approval.`
);
}
return;
}
// For Pending / Rejected / Cancelled, just note the room change
sheet.getRange(row, noteCol).setValue(
`Assigned Room updated from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}.`
);
}
Emily said: Absolutely 🌷 Keeping it as-is is a perfectly sensible choice. The current behaviour is conservative, understandable, and less likely to produce surprises for the bookings manager. But here’s the full automatic re-test version so you can inspect it and keep it in reserve.
What this version does is this. When Assigned Room changes and if the booking is Approved:
- remove the live calendar event
- reset to
Pending
If the booking is Conflict:
- immediately re-test the new effective room against existing bookings
- if the clash is gone, set
StatustoPending - if the clash remains, keep
StatusasConflict - update
Processing Noteeither way
If the booking is Pending / Rejected / Cancelled:
- just update the note
Full automatic re-test version¶
function handleAssignedRoomEdit_(e, sheet, row, headers) {
const oldValue = String(e.oldValue || '').trim();
const newValue = String(e.value || '').trim();
// If nothing materially changed, do nothing
if (oldValue === newValue) return;
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const currentStatus = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS));
const requestedRoomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const noteCol = findHeaderColumn_(headers, CONFIG.HEADERS.PROCESSING_NOTE);
const oldEffectiveRoomRaw = oldValue || requestedRoomRaw;
const newEffectiveRoomRaw = newValue || requestedRoomRaw;
const oldEffectiveRoom = normalizeRoom_(oldEffectiveRoomRaw);
const newEffectiveRoom = normalizeRoom_(newEffectiveRoomRaw);
const oldEffectiveRoomDisplay = oldEffectiveRoom || oldEffectiveRoomRaw || '(blank)';
const newEffectiveRoomDisplay = newEffectiveRoom || newEffectiveRoomRaw || '(blank)';
// If effective room did not actually change, do nothing
if (oldEffectiveRoom === newEffectiveRoom) 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 ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}; approval invalidated and returned to Pending.`,
CONFIG.STATUS_VALUES.PENDING
);
return;
}
// If the booking is currently in conflict, automatically re-test the new room
if (currentStatus === CONFIG.STATUS_VALUES.CONFLICT) {
const statusCol = findHeaderColumn_(headers, CONFIG.HEADERS.STATUS);
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 start = combineDateAndTime_(bookingDate, startTime);
const end = combineDateAndTime_(bookingDate, endTime);
if (!(start instanceof Date) || isNaN(start) || !(end instanceof Date) || isNaN(end)) {
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}, but the booking date/time could not be evaluated. Please review and re-approve manually.`
);
return;
}
if (end <= start) {
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}, but the booking has an invalid time range. Please review and re-approve manually.`
);
return;
}
const sameRoomConflict = hasRoomConflict_(
sheet,
row,
headers,
newEffectiveRoom,
start,
end
);
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}, but a conflict still exists for that room and time.`
);
} else {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.PENDING);
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}. Conflict cleared; booking returned to Pending for re-approval.`
);
}
return;
}
// For Pending / Rejected / Cancelled, just note the room change
sheet.getRange(row, noteCol).setValue(
`Assigned Room updated from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}.`
);
}
The one thing you’d need to check is this line:
as it uses a helper name I cannot guarantee matches your project exactly.
You should replace hasRoomConflict_ with whatever helper processApprovalRow_() already uses to check room clashes.
Common possibilities are things like:
So the logic is right, but the helper name may need to be swapped to your real one.
Why I would not rush to adopt it
This version is cleverer, but also a little less obvious to a human user because:
- changing Assigned Room can silently alter Status
- the manager may not realise the row has been re-evaluated automatically
- debugging later becomes a touch more abstract
Your current approach is more old-fashioned, but very transparent: “change room, then re-approve explicitly.”
That’s often nicer in admin tools 😊
My recommendation is to keep your current version live. Save this automatic-retest version in a notes file or comment block for later, in case someone eventually asks for a smoother workflow. That way you don’t introduce extra moving parts before v1 settles.
The Booking Form¶
06-Apr-2026¶
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
body {
font-family: Arial, sans-serif;
margin: 0;
padding: 24px;
background: #f7f7f7;
}
.container {
max-width: 850px;
margin: 0 auto;
background: white;
padding: 24px;
border-radius: 12px;
box-shadow: 0 2px 10px rgba(0,0,0,0.08);
}
h1 {
margin-top: 0;
font-size: 1.6rem;
}
.grid {
display: grid;
grid-template-columns: 1fr 1fr;
gap: 16px 20px;
}
.field {
display: flex;
flex-direction: column;
}
.field.full {
grid-column: 1 / -1;
}
label {
font-weight: 600;
margin-bottom: 6px;
}
input,
select {
padding: 10px;
border: 1px solid #ccc;
border-radius: 8px;
font-size: 14px;
background: white;
}
.actions {
margin-top: 24px;
}
button {
background: #1a73e8;
color: white;
border: none;
padding: 12px 18px;
border-radius: 8px;
font-size: 14px;
cursor: pointer;
}
button:hover {
background: #155ec4;
}
.message {
margin-top: 16px;
padding: 12px;
border-radius: 8px;
display: none;
}
.message.success {
background: #e6f4ea;
color: #137333;
display: block;
}
.message.error {
background: #fce8e6;
color: #c5221f;
display: block;
}
.hidden {
display: none;
}
@media (max-width: 700px) {
.grid {
grid-template-columns: 1fr;
}
}
</style>
</head>
<body>
<div class="container">
<div class="logo-wrap">
<img src="https://celestesgrl.github.io/rcas/images/Logo-RCASsm.png" alt="Art Society logo" class="logo" style="width=150px;">
</div>
<h1>Booking Request - Test App</h1>
<form id="bookingForm">
<div class="grid">
<div class="field">
<label for="fullName">Full Name</label>
<input type="text" id="fullName" name="fullName" required>
</div>
<div class="field">
<label for="email">Email</label>
<input type="email" id="email" name="email" required>
</div>
<div class="field full">
<label for="courseName">Class Name</label>
<input type="text" id="courseName" name="courseName" required>
</div>
<div class="field">
<label for="room">Room</label>
<select id="room" name="room" required>
<option value="">Select a room</option>
<option value="Studio">Studio</option>
<option value="Gallery">Gallery</option>
</select>
</div>
<div class="field">
<label for="eventDate">Event Date</label>
<input type="date" id="eventDate" name="eventDate" required>
</div>
<div class="field">
<label for="startTime">Start Time</label>
<input type="time" id="startTime" name="startTime" step="1800" required>
</div>
<div class="field">
<label for="endTime">End Time</label>
<input type="time" id="endTime" name="endTime" step="1800" required>
</div>
<div class="field">
<label for="recurring">Recurring?</label>
<select id="recurring" name="recurring" required>
<option value="No" selected>No</option>
<option value="Yes">Yes</option>
</select>
</div>
<div class="field hidden" id="frequencyField">
<label for="frequency">Frequency</label>
<select id="frequency" name="frequency">
<option value="">Select frequency</option>
<option value="Weekly">Weekly</option>
<option value="Fortnightly">Fortnightly</option>
</select>
</div>
<div class="field hidden" id="repeatUntilField">
<label for="repeatUntil">Repeat Until</label>
<input type="date" id="repeatUntil" name="repeatUntil">
</div>
</div>
<div class="actions">
<button type="submit">Submit Booking Request</button>
</div>
<div id="message" class="message"></div>
</form>
</div>
<script>
document.addEventListener('DOMContentLoaded', function () {
const form = document.getElementById('bookingForm');
const messageBox = document.getElementById('message');
const recurringSelect = document.getElementById('recurring');
const frequencyField = document.getElementById('frequencyField');
const repeatUntilField = document.getElementById('repeatUntilField');
const frequencyInput = document.getElementById('frequency');
const repeatUntilInput = document.getElementById('repeatUntil');
const eventDateInput = document.getElementById('eventDate');
function showMessage(text, type) {
messageBox.textContent = text;
messageBox.className = `message ${type}`;
}
function clearMessage() {
messageBox.textContent = '';
messageBox.className = 'message';
}
function timeToMinutes(timeStr) {
const [hours, minutes] = timeStr.split(':').map(Number);
return hours * 60 + minutes;
}
function setMinimumEventDate() {
const today = new Date();
const yyyy = today.getFullYear();
const mm = String(today.getMonth() + 1).padStart(2, '0');
const dd = String(today.getDate()).padStart(2, '0');
eventDateInput.min = `${yyyy}-${mm}-${dd}`;
}
function toggleRecurringFields() {
const isRecurring = recurringSelect.value === 'Yes';
frequencyField.classList.toggle('hidden', !isRecurring);
repeatUntilField.classList.toggle('hidden', !isRecurring);
frequencyInput.required = isRecurring;
repeatUntilInput.required = isRecurring;
if (!isRecurring) {
frequencyInput.value = '';
repeatUntilInput.value = '';
}
}
// Initialise UI state
recurringSelect.addEventListener('change', toggleRecurringFields);
toggleRecurringFields();
setMinimumEventDate();
form.addEventListener('submit', function (e) {
e.preventDefault();
clearMessage();
const formData = {
fullName: form.fullName.value.trim(),
email: form.email.value.trim(),
courseName: form.courseName.value.trim(),
room: form.room.value,
eventDate: form.eventDate.value,
startTime: form.startTime.value,
endTime: form.endTime.value,
recurring: form.recurring.value,
frequency: form.frequency.value,
repeatUntil: form.repeatUntil.value
};
// Required fields
if (
!formData.fullName ||
!formData.email ||
!formData.courseName ||
!formData.room ||
!formData.eventDate ||
!formData.startTime ||
!formData.endTime ||
!formData.recurring
) {
showMessage('Please complete all required fields.', 'error');
return;
}
// Prevent past dates
const today = new Date();
today.setHours(0, 0, 0, 0);
const selectedDate = new Date(formData.eventDate + 'T00:00:00');
if (selectedDate < today) {
showMessage('Please choose today or a future date.', 'error');
return;
}
// 30-minute increments
if (
timeToMinutes(formData.startTime) % 30 !== 0 ||
timeToMinutes(formData.endTime) % 30 !== 0
) {
showMessage('Please use 30-minute increments only.', 'error');
return;
}
// End after start
if (timeToMinutes(formData.endTime) <= timeToMinutes(formData.startTime)) {
showMessage('End time must be later than start time.', 'error');
return;
}
// Recurring validation
if (formData.recurring === 'Yes') {
if (!formData.frequency || !formData.repeatUntil) {
showMessage('Please complete the recurring booking fields.', 'error');
return;
}
if (formData.repeatUntil < formData.eventDate) {
showMessage('Repeat Until must be on or after the Event Date.', 'error');
return;
}
}
// Submit to Apps Script
google.script.run
.withSuccessHandler(function (response) {
showMessage(response.message, 'success');
form.reset();
toggleRecurringFields();
setMinimumEventDate();
})
.withFailureHandler(function (error) {
showMessage(error.message || 'Something went wrong.', 'error');
})
.submitBooking(formData);
});
});
</script>
</body>
</html>
Process Approval Row Function¶
function processApprovalRow_(sheet, row, headers) {
const calendar = getTargetCalendar_();
if (!calendar) {
throw new Error('Calendar not found. Check CALENDAR_MODE / CALENDAR_ID.');
}
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const fullName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.FULL_NAME);
const fnEmail = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EMAIL);
const courseName = valueByHeader_(rowValues, headers, CONFIG.HEADERS.COURSE_NAME);
const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const assignedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ASSIGNED_ROOM);
const effectiveRoomRaw = trim_(assignedRoom) || trim_(requestedRoom);
const room = normalizeRoom_(effectiveRoomRaw);
const bookingDate = valueByHeader_(rowValues, headers, CONFIG.HEADERS.EVENT_DATE);
const startTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.START_TIME);
const endTime = valueByHeader_(rowValues, headers, CONFIG.HEADERS.END_TIME);
const recurring = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.RECURRING));
const frequency = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.FREQUENCY));
const repeatUntil = valueByHeader_(rowValues, headers, CONFIG.HEADERS.REPEAT_UNTIL);
const existingEventId = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.CALENDAR_EVENT_ID));
const statusCol = headers[CONFIG.HEADERS.STATUS];
const eventIdCol = headers[CONFIG.HEADERS.CALENDAR_EVENT_ID];
const procNoteCol = headers[CONFIG.HEADERS.PROCESSING_NOTE];
if (!statusCol) throw new Error(`Missing header: ${CONFIG.HEADERS.STATUS}`);
if (!eventIdCol) throw new Error(`Missing header: ${CONFIG.HEADERS.CALENDAR_EVENT_ID}`);
if (!procNoteCol) throw new Error(`Missing header: ${CONFIG.HEADERS.PROCESSING_NOTE}`);
if (existingEventId) {
sheet.getRange(row, procNoteCol).setValue('Already created; skipped duplicate approval.');
return;
}
if (!room) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue(`Unknown room: ${effectiveRoomRaw}`);
return;
}
if (!bookingDate || !startTime || !endTime) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue('Missing date/time fields.');
return;
}
const start = combineDateAndTime_(bookingDate, startTime);
const end = combineDateAndTime_(bookingDate, endTime);
if (
!(start instanceof Date) || isNaN(start.getTime()) ||
!(end instanceof Date) || isNaN(end.getTime())
) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue('Invalid date/time values.');
return;
}
const now = new Date();
if (start < now) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue('Booking start time is in the past.');
return;
}
if (end <= start) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue('End time must be after start time.');
return;
}
if (recurring === 'Yes') {
if (!frequency || !repeatUntil) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue(
'Recurring booking is missing Frequency or Repeat Until.'
);
return;
}
if (repeatUntil < bookingDate) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue(
'Repeat Until must be on or after the Event Date.'
);
return;
}
}
const overlapping = calendar.getEvents(start, end);
const sameRoomConflict = overlapping.some(event => {
const eventRoom = normalizeRoom_(event.getLocation());
return eventRoom === room;
});
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, procNoteCol).setValue(
`Conflict detected at approval time: ${room} is already booked.`
);
return;
}
const title = `${fullName || 'Tutor'} (${room})`;
const descriptionLines = [
`Full Name: ${fullName || ''}`,
`Email: ${fnEmail || ''}`,
`Room: ${room}`,
`Course Name: ${courseName || ''}`,
`Recurring: ${recurring || 'No'}`,
`Frequency: ${frequency || ''}`,
`Repeat Until: ${repeatUntil || ''}`
];
const event = calendar.createEvent(title, start, end, {
location: room,
description: descriptionLines.join('\n')
});
sheet.getRange(row, eventIdCol).setValue(event.getId());
if (recurring === 'Yes') {
sheet.getRange(row, procNoteCol).setValue(
`Calendar event created successfully in ${room}. Recurring logic not yet implemented.`
);
} else {
sheet.getRange(row, procNoteCol).setValue(
`Calendar event created successfully in ${room}.`
);
}
}
Corrected javascript in HTML¶
<script>
const form = document.getElementById('bookingForm');
const messageBox = document.getElementById('message');
const recurringSelect = document.getElementById('recurring');
const frequencyField = document.getElementById('frequencyField');
const repeatUntilField = document.getElementById('repeatUntilField');
const frequencyInput = document.getElementById('frequency');
const repeatUntilInput = document.getElementById('repeatUntil');
const eventDateInput = document.getElementById('eventDate');
function showMessage(text, type) {
messageBox.textContent = text;
messageBox.className = `message ${type}`;
}
function clearMessage() {
messageBox.textContent = '';
messageBox.className = 'message';
}
function timeToMinutes(timeStr) {
const [hours, minutes] = timeStr.split(':').map(Number);
return hours * 60 + minutes;
}
function setMinimumEventDate() {
const today = new Date();
const yyyy = today.getFullYear();
const mm = String(today.getMonth() + 1).padStart(2, '0');
const dd = String(today.getDate()).padStart(2, '0');
eventDateInput.min = `${yyyy}-${mm}-${dd}`;
}
function toggleRecurringFields() {
const isRecurring = recurringSelect.value === 'Yes';
frequencyField.classList.toggle('hidden', !isRecurring);
repeatUntilField.classList.toggle('hidden', !isRecurring);
frequencyInput.required = isRecurring;
repeatUntilInput.required = isRecurring;
if (!isRecurring) {
frequencyInput.value = '';
repeatUntilInput.value = '';
}
}
recurringSelect.addEventListener('change', toggleRecurringFields);
toggleRecurringFields();
setMinimumEventDate();
form.addEventListener('submit', function (e) {
e.preventDefault();
clearMessage();
const formData = {
fullName: form.fullName.value.trim(),
email: form.email.value.trim(),
courseName: form.courseName.value.trim(),
room: form.room.value,
eventDate: form.eventDate.value,
startTime: form.startTime.value,
endTime: form.endTime.value,
recurring: form.recurring.value,
frequency: form.frequency.value,
repeatUntil: form.repeatUntil.value
};
if (
!formData.fullName ||
!formData.email ||
!formData.courseName ||
!formData.room ||
!formData.eventDate ||
!formData.startTime ||
!formData.endTime ||
!formData.recurring
) {
showMessage('Please complete all required fields.', 'error');
return;
}
const today = new Date();
today.setHours(0, 0, 0, 0);
const selectedDate = new Date(formData.eventDate + 'T00:00:00');
if (selectedDate < today) {
showMessage('Please choose today or a future date.', 'error');
return;
}
if (
timeToMinutes(formData.startTime) % 30 !== 0 ||
timeToMinutes(formData.endTime) % 30 !== 0
) {
showMessage('Please use 30-minute increments only.', 'error');
return;
}
if (timeToMinutes(formData.endTime) <= timeToMinutes(formData.startTime)) {
showMessage('End time must be later than start time.', 'error');
return;
}
if (formData.recurring === 'Yes') {
if (!formData.frequency || !formData.repeatUntil) {
showMessage('Please complete the recurring booking fields.', 'error');
return;
}
if (formData.repeatUntil < formData.eventDate) {
showMessage('Repeat Until must be on or after the Event Date.', 'error');
return;
}
}
google.script.run
.withSuccessHandler(function (response) {
showMessage(response.message, 'success');
form.reset();
toggleRecurringFields();
setMinimumEventDate();
})
.withFailureHandler(function (error) {
showMessage(error.message || 'Something went wrong.', 'error');
})
.submitBooking(formData);
});
</script>
Final Entire Script¶
<script>
document.addEventListener('DOMContentLoaded', function () {
const form = document.getElementById('bookingForm');
const messageBox = document.getElementById('message');
const recurringSelect = document.getElementById('recurring');
const frequencyField = document.getElementById('frequencyField');
const repeatUntilField = document.getElementById('repeatUntilField');
const frequencyInput = document.getElementById('frequency');
const repeatUntilInput = document.getElementById('repeatUntil');
const eventDateInput = document.getElementById('eventDate');
function showMessage(text, type) {
messageBox.textContent = text;
messageBox.className = `message ${type}`;
}
function clearMessage() {
messageBox.textContent = '';
messageBox.className = 'message';
}
function timeToMinutes(timeStr) {
const [hours, minutes] = timeStr.split(':').map(Number);
return hours * 60 + minutes;
}
function setMinimumEventDate() {
const today = new Date();
const yyyy = today.getFullYear();
const mm = String(today.getMonth() + 1).padStart(2, '0');
const dd = String(today.getDate()).padStart(2, '0');
eventDateInput.min = `${yyyy}-${mm}-${dd}`;
}
function toggleRecurringFields() {
const isRecurring = recurringSelect.value === 'Yes';
frequencyField.classList.toggle('hidden', !isRecurring);
repeatUntilField.classList.toggle('hidden', !isRecurring);
frequencyInput.required = isRecurring;
repeatUntilInput.required = isRecurring;
if (!isRecurring) {
frequencyInput.value = '';
repeatUntilInput.value = '';
}
}
// Initialise UI state
recurringSelect.addEventListener('change', toggleRecurringFields);
toggleRecurringFields();
setMinimumEventDate();
form.addEventListener('submit', function (e) {
e.preventDefault();
clearMessage();
const formData = {
fullName: form.fullName.value.trim(),
email: form.email.value.trim(),
courseName: form.courseName.value.trim(),
room: form.room.value,
eventDate: form.eventDate.value,
startTime: form.startTime.value,
endTime: form.endTime.value,
recurring: form.recurring.value,
frequency: form.frequency.value,
repeatUntil: form.repeatUntil.value
};
// Required fields
if (
!formData.fullName ||
!formData.email ||
!formData.courseName ||
!formData.room ||
!formData.eventDate ||
!formData.startTime ||
!formData.endTime ||
!formData.recurring
) {
showMessage('Please complete all required fields.', 'error');
return;
}
// Prevent past dates
const today = new Date();
today.setHours(0, 0, 0, 0);
const selectedDate = new Date(formData.eventDate + 'T00:00:00');
if (selectedDate < today) {
showMessage('Please choose today or a future date.', 'error');
return;
}
// 30-minute increments
if (
timeToMinutes(formData.startTime) % 30 !== 0 ||
timeToMinutes(formData.endTime) % 30 !== 0
) {
showMessage('Please use 30-minute increments only.', 'error');
return;
}
// End after start
if (timeToMinutes(formData.endTime) <= timeToMinutes(formData.startTime)) {
showMessage('End time must be later than start time.', 'error');
return;
}
// Recurring validation
if (formData.recurring === 'Yes') {
if (!formData.frequency || !formData.repeatUntil) {
showMessage('Please complete the recurring booking fields.', 'error');
return;
}
if (formData.repeatUntil < formData.eventDate) {
showMessage('Repeat Until must be on or after the Event Date.', 'error');
return;
}
}
// Submit to Apps Script
google.script.run
.withSuccessHandler(function (response) {
showMessage(response.message, 'success');
form.reset();
toggleRecurringFields();
setMinimumEventDate();
})
.withFailureHandler(function (error) {
showMessage(error.message || 'Something went wrong.', 'error');
})
.submitBooking(formData);
});
});
</script>
Handle Assigned Room v2¶
function handleAssignedRoomEdit_(e, sheet, row, headers) {
const oldValue = String(e.oldValue || '').trim();
const newValue = String(e.value || '').trim();
// If nothing materially changed, do nothing
if (oldValue === newValue) return;
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const status = valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS);
const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const oldEffectiveRoom = oldValue || requestedRoom;
const newEffectiveRoom = newValue || requestedRoom;
// If effective room did not actually change, do nothing
if (oldEffectiveRoom === newEffectiveRoom) return;
// Approved booking: remove live event and reset to Pending
if (status === CONFIG.STATUS_VALUES.APPROVED) {
removeLiveCalendarEvent_(
sheet,
row,
headers,
`Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Booking reset to Pending for re-approval.`,
CONFIG.STATUS_VALUES.PENDING
);
return;
}
// Conflict booking: re-check whether the new effective room still conflicts
if (status === CONFIG.STATUS_VALUES.CONFLICT) {
const statusCol = findHeaderColumn_(headers, CONFIG.HEADERS.STATUS);
const noteCol = findHeaderColumn_(headers, CONFIG.HEADERS.PROCESSING_NOTE);
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 start = combineDateAndTime_(bookingDate, startTime);
const end = combineDateAndTime_(bookingDate, endTime);
if (!(start instanceof Date) || isNaN(start) || !(end instanceof Date) || isNaN(end)) {
sheet.getRange(row, noteCol).setValue(
'Assigned room changed, but date/time could not be evaluated. Please review booking details.'
);
return;
}
const sameRoomConflict = hasRoomConflict_(
sheet,
row,
headers,
newEffectiveRoom,
start,
end
);
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, noteCol).setValue(
`Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}, but conflict remains: ${newEffectiveRoom} is already booked for that time.`
);
} else {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.PENDING);
sheet.getRange(row, noteCol).setValue(
`Assigned room changed from ${oldEffectiveRoom} to ${newEffectiveRoom}. Conflict cleared. Booking reset to Pending for re-approval.`
);
}
return;
}
}
Handle Assigned Room v3¶
function handleAssignedRoomEdit_(e, sheet, row, headers) {
const oldValue = String(e.oldValue || '').trim();
const newValue = String(e.value || '').trim();
// If nothing materially changed, do nothing
if (oldValue === newValue) return;
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const status = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS));
const requestedRoom = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const noteCol = findHeaderColumn_(headers, CONFIG.HEADERS.PROCESSING_NOTE);
const oldEffectiveRoom = normalizeRoom_(oldValue || requestedRoom);
const newEffectiveRoom = normalizeRoom_(newValue || requestedRoom);
const oldEffectiveRoomDisplay = oldEffectiveRoom || oldValue || requestedRoom || '(blank)';
const newEffectiveRoomDisplay = newEffectiveRoom || newValue || requestedRoom || '(blank)';
// If effective room did not actually change, do nothing
if (oldEffectiveRoom === newEffectiveRoom) return;
// If the booking is live, changing room invalidates approval and removes the event
if (status === CONFIG.STATUS_VALUES.APPROVED) {
removeLiveCalendarEvent_(
sheet,
row,
headers,
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}; approval invalidated and returned to Pending.`,
CONFIG.STATUS_VALUES.PENDING
);
return;
}
// If the booking is currently in conflict, keep Conflict but update the note
if (status === CONFIG.STATUS_VALUES.CONFLICT) {
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}. 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 ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}.`
);
}
Handle Assigned Room v4¶
function handleAssignedRoomEdit_(e, sheet, row, headers) {
const oldValue = String(e.oldValue || '').trim();
const newValue = String(e.value || '').trim();
// If nothing materially changed, do nothing
if (oldValue === newValue) return;
const rowValues = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
const currentStatus = trim_(valueByHeader_(rowValues, headers, CONFIG.HEADERS.STATUS));
const requestedRoomRaw = valueByHeader_(rowValues, headers, CONFIG.HEADERS.ROOM);
const noteCol = findHeaderColumn_(headers, CONFIG.HEADERS.PROCESSING_NOTE);
const oldEffectiveRoomRaw = oldValue || requestedRoomRaw;
const newEffectiveRoomRaw = newValue || requestedRoomRaw;
const oldEffectiveRoom = normalizeRoom_(oldEffectiveRoomRaw);
const newEffectiveRoom = normalizeRoom_(newEffectiveRoomRaw);
const oldEffectiveRoomDisplay = oldEffectiveRoom || oldEffectiveRoomRaw || '(blank)';
const newEffectiveRoomDisplay = newEffectiveRoom || newEffectiveRoomRaw || '(blank)';
// If effective room did not actually change, do nothing
if (oldEffectiveRoom === newEffectiveRoom) 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 ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}; approval invalidated and returned to Pending.`,
CONFIG.STATUS_VALUES.PENDING
);
return;
}
// If the booking is currently in conflict, automatically re-test the new room
if (currentStatus === CONFIG.STATUS_VALUES.CONFLICT) {
const statusCol = findHeaderColumn_(headers, CONFIG.HEADERS.STATUS);
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 start = combineDateAndTime_(bookingDate, startTime);
const end = combineDateAndTime_(bookingDate, endTime);
if (!(start instanceof Date) || isNaN(start) || !(end instanceof Date) || isNaN(end)) {
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}, but the booking date/time could not be evaluated. Please review and re-approve manually.`
);
return;
}
if (end <= start) {
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}, but the booking has an invalid time range. Please review and re-approve manually.`
);
return;
}
const sameRoomConflict = hasRoomConflict_(
sheet,
row,
headers,
newEffectiveRoom,
start,
end
);
if (sameRoomConflict) {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.CONFLICT);
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}, but a conflict still exists for that room and time.`
);
} else {
sheet.getRange(row, statusCol).setValue(CONFIG.STATUS_VALUES.PENDING);
sheet.getRange(row, noteCol).setValue(
`Assigned Room changed from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}. Conflict cleared; booking returned to Pending for re-approval.`
);
}
return;
}
// For Pending / Rejected / Cancelled, just note the room change
sheet.getRange(row, noteCol).setValue(
`Assigned Room updated from ${oldEffectiveRoomDisplay} to ${newEffectiveRoomDisplay}.`
);
}