Smart Life US
Google Sheets inbound booking duplicates: safe saving with LockService
Introduction: what happens when two bookings hit the same time slot
As you build an inbound appointment system in Google Sheets, it usually runs fine up to a certain scale. But once more people start using it and time slots overlap, the data can easily become chaotic. Among them, the topic of preventing duplicate inbound bookings in Google Sheets is heavily searched and practically essential once you go into real operations. This post assumes you have the basic structure from the previous parts and focuses on the last step: “how to prevent duplicates and capacity overruns at the moment you actually save.”
You’re currently reading part 4 of Building a Google Sheets inbound booking system — booking entry, based on a design that was actually used at a logistics center. In parts 1–3 and in
How to calculate booking capacity in Google Sheets: auto‑show open spots by time slot, we’ll assume you’ve already implemented:
- Blocking non‑operating hours like lunch or overnight
- Calculating booking windows per customer
- Calculating remaining capacity by time slot and door
What’s left now is to take a single booking coming from a web app or sidebar and handle it consistently inside Apps Script with LockService: validate → check duplicates → recheck capacity → save. This post walks through that full flow and the code you need, step by step.
Two things you must always block when saving: duplicates and capacity overrun
An inbound booking might look like “just a row in a sheet,” but there are two problems you absolutely must prevent in production.
First is duplicate container (or trailer) bookings. If two bookings are made for the same number on the same date, the dock team won’t know which one to follow. A typical pattern is: the carrier requests a time change but doesn’t cancel the original, and the busy operator adds only the new one without deleting the old one. These duplicates accumulate and ultimately you end up with one physical container in the yard but two separate loads and doors scheduled.
Second is overbooking the door/yard slots beyond capacity. Even if you’ve already calculated “how many we can take in this time slot,” if two people see the last remaining spot at almost the same time and both click save, you’re in trouble. Each one sees “1 remaining” and finishes its calculation, then both saves reach the sheet and you end up with two rows. At that point you can’t trust the number on the screen; you must look again at the actual rows in the sheet right before saving.
You can’t realistically prevent these two issues just by telling people to “be careful.” That’s why you need both an Apps Script duplicate booking check and capacity overrun prevention code, and you must wrap the entire save section with LockService. Only then will the result stay consistent even when two saves happen “almost at the same time.”
Why use LockService, and the basic structure
In Google Apps Script, the tool for handling concurrency is LockService. In this setup we’ll use the script‑wide lock (ScriptLock), not a custom key lock. In other words, we’ll make it so that “within this script project, only one person at a time can run the booking‑save routine.”
The basic structure of the inbound booking save function looks like this:
- Get a script lock with
LockService.getScriptLock(). - Use
waitLock(…)to wait for a set time (e.g., 5 seconds) to obtain the lock. - Only after acquiring the lock:
- Validate required fields and formats
- Recheck operating hours and allowed booking window
- Query for existing bookings for the same container
- Recalculate capacity at this moment to confirm there’s still room
- If all clear, actually append a row to the sheet
- Release the lock in
finallyregardless of any errors intry.
The crucial point is that capacity recheck and the actual save must be done together inside the lock. If you calculate capacity outside and then only write inside, there’s still a window for two bookings to slip through. Also, when you archive old bookings to another sheet, you should similarly wrap the whole “read → paste → delete” sequence in a lock to avoid duplicate archiving or double deletion.
Main booking save function: BOOK_bookAppt
Now let’s go through the actual code in stages. Since this code will be used together with other scripts in the series, every constant and function name is prefixed with BOOK_. We’ll keep using the same APPT_MAIN sheet from part 3. That part’s usage function reads columns A (date), B (time) and C (equipment), so if you change this sheet or those columns here, that function can’t count anything and will return 0. A 0 is interpreted as “spots remaining,” and over‑capacity bookings will slip through. For archiving we’ll add a new APPT_ARCHIVE sheet.
Step 1 — Skeleton of the main booking save function
This function is the main entry point that saves a single booking received from a web app or sidebar.
Where to paste: Google Sheets top menu → Extensions → Apps Script → paste at the bottom of the code file where your booking‑related scripts live.
What to do after pasting: Save and run the BOOK_testBookAppt_ function once from the editor to see success/failure messages in the log.
// Only change this section to match your own system
// We use the **same APPT_MAIN sheet and columns A~C** as in part 3. The usage function
// in part 3 reads A (date), B (time), and C (equipment). If you change the name or order
// here, that function won’t be able to count anything and will return 0.
// 0 is interpreted as 'capacity available', letting over-capacity bookings through.
const BOOK_ARCHIVE_SHEET_NAME = 'APPT_ARCHIVE'; // → Archive sheet name
const BOOK_APPT_COLS = { // → Column numbers (1-based)
DATE: 1, // A: Booking date ← same as part 3
TIME: 2, // B: Start time ← same as part 3
TYPE: 3, // C: Equipment type ← same as part 3
DOOR: 4, // D: Door/Yard slot
CONTAINER: 5, // E: Container number
CARRIER: 6, // F: Carrier
CLIENT: 7, // G: Customer
REMARK: 8, // H: Note
END_TIME: 9, // I: End time
CREATED: 10, // J: Created timestamp
QTY: 11, // K: Quantity
PALLET: 12, // L: Pallet count
STATUS: 13 // M: Status (blank = active, ARCHIVED = archived)
}; // →
/**
* Main function to save a single booking
* @param {Object} data Booking data coming from the web app
* @returns {Object} Save result and message
*/
function BOOK_bookAppt(data) { // → Booking save entry point
const lock = LockService.getScriptLock(); // → Script-wide lock
let locked = false; // → Indicates lock success
try {
lock.waitLock(5000); // → Wait up to 5 seconds
locked = true; // → Lock acquired
const validated = BOOK_validateBookingInput_(data); // → Required/format check
BOOK_validateBusinessRules_(validated); // → Operating rules check
BOOK_checkDuplicateContainer_(validated); // → Container duplicate check
BOOK_checkCapacityAndSave_(validated); // → Recheck capacity, then save
return { // → Success response
success: true, // → Success flag
message: 'The booking has been saved successfully.' // → User message
};
} catch (e) {
return { // → Failure response
success: false, // → Failure flag
message: e.message || 'An error occurred while saving the booking.' // → Error message
};
} finally {
if (locked) { // → Only if we actually locked
lock.releaseLock(); // → Release lock
}
}
}
/**
* Simple test helper
*/
function BOOK_testBookAppt_() { // → Test-only function
// If you hard-code a date here, all tests will start failing as soon as the year changes
// and the date falls outside the allowed booking window. Use the helper from part 2
// to get an **actually bookable date**.
const window = getBookingWindowForClient(); // → [{ymd, label, isToday}, ...]
if (!window || window.length === 0) { // → No bookable dates
Logger.log('No bookable dates. Check operating hours and booking window settings first.');
return;
}
const dummy = { // → Sample data
date: window[0].ymd, // → First bookable day
time: '09:00', // → Start time
endTime: '09:30', // → End time (required)
type: 'CONTAINER', // → Equipment type (allowed in part 3)
door: 'D01', // → Door or slot
containerNo: 'TEST1234567', // → Container number
carrier: 'Test carrier', // → Carrier name
client: 'Test customer', // → Customer name
remark: 'This is a test booking.', // → Note
qty: '10', // → Quantity (string is OK)
pallet: '2' // → Pallet count
};
const result = BOOK_bookAppt(dummy); // → Run booking save
Logger.log(JSON.stringify(result)); // → Log result
}How to confirm it’s working: When you run BOOK_testBookAppt_ from the editor, if you see a log starting with {"success":true and a sample row added to the APPT_MAIN sheet, the basic skeleton is working.
Input validation: check required fields, formats and numbers in one place
Once you put a booking system into actual use, missing or malformed inputs show up more often than you’d expect. You’ll see all kinds of date formats, text in time fields, or notes like “a lot” typed into the quantity column. If such values are saved as‑is, they may cause NaN to creep into capacity calculations and break totals.
The following code checks:
- Required: date, start time, end time, type, door, container number
- Formats: date (YYYY‑MM‑DD), time (HH:MM)
- Basic allowlists for type and door (examples)
- That quantity and pallet count are numeric and not
NaN
Step 2 — Input validation function
This function validates required fields, formats and numeric values and returns a cleaned booking object.
Where to paste: Right below BOOK_bookAppt.
What to do after pasting: Purposely put bad values for date/time/quantity in BOOK_testBookAppt_ and confirm the error messages.
/**
* Input validation — checks not just format but whether the date/time actually exists
* @param {Object} data Raw data from the UI
* @returns {Object} Normalized booking data
*/
function BOOK_validateBookingInput_(data) { // → Input validation
if (!data) { // → Entire object check
throw new Error('No booking data was provided.'); // → Error message
}
const type = String(data.type || '').trim().toUpperCase(); // → Equipment type (upper)
const door = String(data.door || '').trim(); // → Door/slot
// We normalize container numbers to uppercase and trim spaces before saving.
// Otherwise 'test1234567' and 'TEST1234567' are treated as different containers
// and the duplicate check can be bypassed.
const containerNo = String(data.containerNo || '').trim().toUpperCase();
const carrier = String(data.carrier || '').trim(); // → Carrier name
const client = String(data.client || '').trim(); // → Customer name
const remark = String(data.remark || '').trim(); // → Note
if (!door) { // → Door/slot check
throw new Error('Door (or yard slot) is a required field.'); // → Message
}
if (!containerNo) { // → Container check
throw new Error('Container (or trailer) number is a required field.'); // → Message
}
// For date/time, we use the helpers from part 5. A simple regex would let
// `2026-02-31` or `99:99` pass, creating bookings on impossible dates/times.
let date;
let time;
let endTime;
try {
date = APPT_ymd_(String(data.date || '').trim()); // → Valid calendar date
} catch (e) {
throw new Error('Booking date is invalid: ' + e.message);
}
try {
time = APPT_hm_(String(data.time || '').trim()); // → 00–23 hours, 00–59 minutes
} catch (e) {
throw new Error('Start time is invalid: ' + e.message);
}
try {
endTime = APPT_hm_(String(data.endTime || '').trim()); // → End time
} catch (e) {
throw new Error('End time is invalid: ' + e.message);
}
// End time must be strictly after the start time for the booking to make sense.
if (compareTime_(endTime, time) <= 0) { // → Time compare helper from part 1
throw new Error('End time must be later than start time.');
}
// For equipment types we reuse the **allowed list defined in part 3**.
// If you define a separate list here, part 3’s capacity function won’t recognize it
// and that booking will be excluded from usage counts.
if (!BOOK_CAPACITY_CONFIG.VALID_TYPES.includes(type)) { // → Allowlist check
throw new Error('Unknown equipment type: ' + type +
' (Allowed: ' + BOOK_CAPACITY_CONFIG.VALID_TYPES.join(', ') + ')');
}
if (!/^D\d{2}$/.test(door)) { // → Door pattern (D01, etc.)
throw new Error('Door format is invalid.'); // → Message
}
const qty = BOOK_toCount_(data.qty, 'Quantity'); // → ≥0 integer or null
const pallet = BOOK_toCount_(data.pallet, 'Pallet count'); // → ≥0 integer or null
return { // → Normalized result
date: date, // → 'YYYY-MM-DD'
time: time, // → Start 'HH:mm'
endTime: endTime, // → End 'HH:mm'
type: type, // → Equipment type (upper)
door: door, // → Door/slot
containerNo: containerNo, // → Container (upper)
carrier: carrier, // → Carrier
client: client, // → Customer
remark: remark, // → Note
qty: qty, // → Quantity
pallet: pallet // → Pallet count
};
}
/**
* Convert quantity-like input to a non-negative integer (blank → null)
*/
function BOOK_toCount_(raw, label) { // → Count conversion helper
if (raw === undefined || raw === null || raw === '') { // → No value
return null; // → Leave as empty
}
const n = Number(raw); // → Convert to number
if (!Number.isSafeInteger(n) || n < 0) { // → Integer and non-negative
throw new Error(label + ' must be a non-negative integer: ' + raw);
}
return n; // → Valid integer
}How to confirm it’s working: Change endTime in BOOK_testBookAppt_ to an empty string, or set qty: 'ten', then run it. You should get success:false with clear messages like “End time is a required field” or “Quantity must be a number.”
Enforcing operating rules, duplicate checks, capacity checks and save
Even if input validation passes, you still can’t save right away. There are three more layers tied directly to real operations:
- Operating rules: per‑customer booking window, blocked days and time ranges
- Duplicates: block multiple bookings for the same date/container (and optionally customer)
- Capacity: recalculate usage at this moment and only append when there’s still room
Step 3 — Rechecking operating rules
This function calls the booking window and operating‑hours helpers from earlier posts to enforce your business rules.
Where to paste: Below BOOK_validateBookingInput_.
What to do after pasting: Use a date in BOOK_testBookAppt_ that’s outside your configured window and test.
/**
* Operating rule checks — calls helper functions using their original contracts
* @param {Object} booking Validated booking data
*/
function BOOK_validateBusinessRules_(booking) { // → Rule checks
// ① Booking window — part 2’s `getBookingWindow_()` returns {startDate, endDate}.
// Don’t confuse it with `getBookingWindowForClient()`, which returns an array.
const win = getBookingWindow_(); // → Window info
const tz = Session.getScriptTimeZone(); // → Script time zone
const minYmd = Utilities.formatDate(win.startDate, tz, 'yyyy-MM-dd'); // → Start date
const maxYmd = Utilities.formatDate(win.endDate, tz, 'yyyy-MM-dd'); // → End date
if (booking.date < minYmd || booking.date > maxYmd) { // → Outside window
throw new Error('The date is outside the booking window (' + minYmd + ' ~ ' + maxYmd + ').');
}
// ② Holidays and operating hours — part 1’s `getDateBlockInfo(Date)` returns
// whether the day is open, with open/close times and blocked segments.
const dp = booking.date.split('-'); // → Y/M/D components
const dateObj = new Date(Number(dp[0]), Number(dp[1]) - 1, Number(dp[2])); // → Local Date
const info = getDateBlockInfo(dateObj); // → Daily schedule
if (info.isHoliday || !info.open || !info.close) { // → Closed day
throw new Error(booking.date + ' is not a day that accepts bookings.');
}
// ③ Within operating hours — part 1’s `isTimeInRange_(time, start, end)` takes 3 args.
if (!isTimeInRange_(booking.time, info.open, info.close)) { // → Start time
throw new Error('Start time is outside operating hours (' + info.open + '~' + info.close + ').');
}
if (!isTimeInRange_(booking.endTime, info.open, info.close)) { // → End time
throw new Error('End time is outside operating hours (' + info.open + '~' + info.close + ').');
}
// ④ Check overlap with blocked segments such as lunch or maintenance
const blocks = info.blocks || []; // → Blocked list
for (let i = 0; i < blocks.length; i++) { // → For each segment
const b = blocks[i]; // → Segment
const overlap = compareTime_(booking.time, b.end) < 0
&& compareTime_(booking.endTime, b.start) > 0; // → Any overlap?
if (overlap) { // → Reject if overlap
throw new Error('This period is blocked (' + b.start + '~' + b.end +
(b.reason ? ', ' + b.reason : '') + ').');
}
}
}How to confirm it’s working: Set a date outside the allowed window in BOOK_testBookAppt_ and run it. You should see a message along the lines of “The date is outside the booking window.” The way operating hours are configured is explained in more detail in
Google Sheets booking operating hours lockdown: building a Google Sheets booking system.
Step 4 — Container duplicate booking check
This function scans the APPT sheet to see if the same date/container pair is already booked. You can add conditions like customer, type or status if needed.
Where to paste: Below BOOK_validateBusinessRules_.
What to do after pasting: Add a row to APPT_MAIN with the same date/container and then test.
/**
* Check whether the same container is already booked on the same date
* @param {Object} booking Validated booking data
*/
function BOOK_checkDuplicateContainer_(booking) { // → Duplicate check
const sheet = SpreadsheetApp.getActive() // → Same sheet as part 3
.getSheetByName(BOOK_USAGE_CONFIG.APPT_SHEET_NAME); // → APPT_MAIN
if (!sheet) { // → Sheet missing
throw new Error('Booking sheet not found.'); // → Message
}
const lastRow = sheet.getLastRow(); // → Last row
if (lastRow < 2) { // → Only header
return; // → No duplicates
}
const width = BOOK_APPT_COLS.STATUS; // → Read through column M
const values = sheet.getRange(2, 1, lastRow - 1, width).getValues(); // → Data range
const tz = Session.getScriptTimeZone(); // → Time zone
for (let i = 0; i < values.length; i++) { // → Each row
const row = values[i]; // → Row data
const status = String(row[BOOK_APPT_COLS.STATUS - 1] || '').trim(); // → Status
if (status === 'ARCHIVED') { // → Already archived
continue; // → Ignore
}
const rowDate = row[BOOK_APPT_COLS.DATE - 1]; // → Column A
// Normalize same way as on save. If we only uppercase one side,
// 'test1234567' is treated as a different container and duplicates slip through.
const rowContainer = String(row[BOOK_APPT_COLS.CONTAINER - 1] || '')
.trim().toUpperCase(); // → Column E
if (!rowDate || !rowContainer) { // → Skip blank rows
continue;
}
const rowDateStr = (rowDate instanceof Date)
? Utilities.formatDate(rowDate, tz, 'yyyy-MM-dd') // → Date format
: String(rowDate).trim(); // → As string
if (rowDateStr === booking.date && rowContainer === booking.containerNo) {
throw new Error('A booking for this container on the same date already exists. (Row ' +
(i + 2) + ')'); // → Message
}
}
}How to confirm it’s working: Manually add a row to APPT_MAIN with a bookable future date and container TEST1234567 (matching your test function), then run BOOK_testBookAppt_. You should get success:false and a message like “A booking for this container on the same date already exists.”
Step 5 — Rechecking capacity and actually saving
This function recalculates current usage and capacity for the target slot inside the lock and appends a row only when there’s still room. It uses the capacity‑related helpers you implemented earlier.
Where to paste: Below BOOK_checkDuplicateContainer_.
What to do after pasting: Configure a door/time combination with capacity 1 and try saving twice.
/**
* Recheck capacity and save a row to the booking sheet
* @param {Object} booking Validated booking data
*/
function BOOK_checkCapacityAndSave_(booking) { // → Capacity check + save
const sheet = SpreadsheetApp.getActive() // → Same sheet as part 3
.getSheetByName(BOOK_USAGE_CONFIG.APPT_SHEET_NAME); // → APPT_MAIN
if (!sheet) { // → Sheet missing
throw new Error('Booking sheet not found.'); // → Message
}
// Part 3’s function takes **two Date objects and an equipment type** and returns
// the remaining capacity in one go. No need to query capacity and usage separately;
// that function calculates both.
const dp = booking.date.split('-'); // → Y/M/D
const tp = booking.time.split(':'); // → H/M
const slotDate = new Date(Number(dp[0]), Number(dp[1]) - 1, Number(dp[2])); // → Date
const slotTime = new Date(1899, 11, 30, Number(tp[0]), Number(tp[1])); // → Time
const slot = BOOK_getSeqUsageForSlotCore_(slotDate, slotTime, booking.type);
if (slot.remaining <= 0) { // → No room
throw new Error('The capacity for this time slot (' + slot.time + ' / ' + booking.type +
') is full. Capacity ' + slot.capacity +
', used ' + slot.used); // → Message
}
const nextRow = sheet.getLastRow() + 1; // → Row to write
const rowValues = []; // → Single row data
rowValues[BOOK_APPT_COLS.DATE - 1] = slotDate; // A: Date (Date object)
rowValues[BOOK_APPT_COLS.TIME - 1] = slotTime; // B: Start time (Date)
rowValues[BOOK_APPT_COLS.TYPE - 1] = booking.type; // C: Equipment type
rowValues[BOOK_APPT_COLS.DOOR - 1] = booking.door; // D: Door/slot
rowValues[BOOK_APPT_COLS.CONTAINER - 1] = booking.containerNo; // E: Container
rowValues[BOOK_APPT_COLS.CARRIER - 1] = booking.carrier; // F: Carrier
rowValues[BOOK_APPT_COLS.CLIENT - 1] = booking.client; // G: Customer
rowValues[BOOK_APPT_COLS.REMARK - 1] = booking.remark; // H: Note
rowValues[BOOK_APPT_COLS.END_TIME - 1] = booking.endTime; // I: End time
rowValues[BOOK_APPT_COLS.CREATED - 1] = new Date(); // J: Created timestamp
rowValues[BOOK_APPT_COLS.QTY - 1] = booking.qty; // K: Quantity
rowValues[BOOK_APPT_COLS.PALLET - 1] = booking.pallet; // L: Pallet count
rowValues[BOOK_APPT_COLS.STATUS - 1] = ''; // M: Status (blank=active)
for (let i = 0; i < rowValues.length; i++) { // → Fill gaps
if (rowValues[i] === undefined || rowValues[i] === null) { // → Any holes
rowValues[i] = ''; // → Use empty string
}
}
sheet.getRange(nextRow, 1, 1, rowValues.length).setValues([rowValues]); // → Single write
sheet.getRange(nextRow, BOOK_APPT_COLS.DATE).setNumberFormat('yyyy-MM-dd'); // → Date format
sheet.getRange(nextRow, BOOK_APPT_COLS.TIME).setNumberFormat('HH:mm'); // → Time format
}How to confirm it’s working: Configure the allowed capacity for a specific door/time to be 1, then run BOOK_testBookAppt_ twice, changing only the container number each time. The first should succeed; the second should throw an error like “capacity is full,” and there should be only one row in the sheet. The capacity structure used here is the same one described in
How to calculate booking capacity in Google Sheets: auto‑show open spots by time slot.
Archiving old bookings: safely moving out old rows
As operations continue, thousands of rows will accumulate in your booking sheet. If you leave them, filters and pivots will slow down, so you’ll want to periodically archive bookings that are older than a certain threshold (e.g., N days after the appointment date). Again, you must assume more than one person might click “archive” around the same time.
The archiving function below works as follows:
- Acquire a
ScriptLock. - Mark target rows as
ARCHIVEDfirst, then paste them intoAPPT_ARCHIVE. - Delete those rows from the main sheet (from bottom to top).
- Release the lock in
finally.
Step 6 — Archiving old bookings
This function moves old bookings from APPT_MAIN to APPT_ARCHIVE and then deletes them from the main sheet. Since it writes ARCHIVED into the main sheet first, you won’t double‑archive the same rows even if the script stops midway and you rerun it.
Where to paste: Below the functions above.
What to do after pasting: For testing, temporarily set the cutoff date in the code to a future date, then run and observe what happens.
/**
* Archive old bookings to the archive sheet — safe to rerun
*/
function BOOK_archiveOldAppts_() { // → Archive function
const lock = LockService.getScriptLock(); // → Script lock
let locked = false; // → Lock flag
try {
lock.waitLock(5000); // → Wait up to 5 seconds
locked = true; // → Lock acquired
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const main = ss.getSheetByName(BOOK_USAGE_CONFIG.APPT_SHEET_NAME); // → Main sheet
const archive = ss.getSheetByName(BOOK_ARCHIVE_SHEET_NAME); // → Archive sheet
if (!main || !archive) { // → Missing sheets
throw new Error('Booking or archive sheet not found.'); // → Message
}
const lastRow = main.getLastRow(); // → Last row in main
if (lastRow < 2) { // → No data
return 0; // → Archived rows count
}
const width = Math.max(main.getLastColumn(), BOOK_APPT_COLS.STATUS); // → Through M
const values = main.getRange(2, 1, lastRow - 1, width).getValues(); // → All data
const today = new Date(); // → Today
const cutoff = new Date(today.getFullYear(), today.getMonth(),
today.getDate() - 30); // → 30 days ago
const rowsToArchive = []; // → Rows to archive
const rowIndexes = []; // → Main sheet row indices
for (let i = 0; i < values.length; i++) { // → Each row
const row = values[i]; // → Row data
// Skip rows already marked ARCHIVED. Without this marker, if an error occurs
// between copying and deleting, **the same rows will be copied again**
// the next time this function runs.
if (String(row[BOOK_APPT_COLS.STATUS - 1] || '').trim() === 'ARCHIVED') {
continue;
}
const dateCell = row[BOOK_APPT_COLS.DATE - 1]; // → Column A
if (!(dateCell instanceof Date)) { // → Not a Date
continue; // → Skip, can’t judge
}
const d = new Date(dateCell.getFullYear(), dateCell.getMonth(),
dateCell.getDate()); // → Strip time
if (d <= cutoff) { // → Before or on cutoff
row[BOOK_APPT_COLS.STATUS - 1] = 'ARCHIVED'; // → Mark as archived
rowsToArchive.push(row); // → Add to archive list
rowIndexes.push(i + 2); // → Actual row number
}
}
if (!rowsToArchive.length) { // → Nothing to archive
return 0; // → Archived rows count
}
// Order matters. ①Mark original rows as archived, ②copy them to archive sheet,
// then ③delete them from the main sheet. If the script stops in between,
// the marker prevents duplicate copies on the next run.
for (let k = 0; k < rowIndexes.length; k++) { // → Mark in main sheet
main.getRange(rowIndexes[k], BOOK_APPT_COLS.STATUS).setValue('ARCHIVED');
}
SpreadsheetApp.flush(); // → Commit marks first
const archiveStart = archive.getLastRow() + 1; // → First archive row
archive.getRange(archiveStart, 1, rowsToArchive.length, rowsToArchive[0].length)
.setValues(rowsToArchive); // → Write to archive
rowIndexes.sort(function (a, b) { return b - a; }); // → Delete bottom-up
rowIndexes.forEach(function (rowIndex) { // → For each row
main.deleteRow(rowIndex); // → Delete row
});
return rowsToArchive.length; // → Archived rows count
} finally {
if (locked) { // → If locked
lock.releaseLock(); // → Release
}
}
}How to confirm it’s working: Add a few test rows to APPT_MAIN with appointment dates from a few months ago, then run BOOK_archiveOldAppts_. Those rows should move to APPT_ARCHIVE and disappear from the main sheet. Running the function again should not create duplicate copies in the archive sheet. Even if two people trigger this function at the same time, ScriptLock ensures only one runs at a time.
Practical tips: bringing LockService and duplicate prevention into production
The code above gives you a solid Google Sheets booking save flow with LockService, but here are a few practical lessons from applying it in real‑world operations.
First, write error messages from the user’s point of view. Internally, the code distinguishes between duplicate, capacity overrun, and lock timeout, but the user only cares about why their booking won’t save. Once you know lock timeouts can occur, you might want to show something like “Please try again in a moment. Another booking for the same time slot is being processed.”
Second, enforce allowlists and formats as early as possible. Values like equipment type and door can split your reporting if they differ by even one character. In this post we used sample allowlists like FCL, LTL and a D01 pattern, but in practice you should align them with your site standards, and combine them with data validation in the sheet where possible.
Third, always treat quantity and pallet counts as a pair: numerical conversion plus NaN checks. If someone accidentally enters “10+5”, Number('10+5') becomes NaN, and once that value enters your totals, the entire sum may become NaN. Blocking this here with Number.isFinite or integer checks will keep your later reports and cleanup scripts safe.
Fourth, reserve LockService for mixed read/write operations like saving and archiving. Dashboards that only read booking data should not be locked, while save and cleanup routines should use locks to avoid overlaps. That’s exactly why we applied ScriptLock to the archive function as well: small timing differences in “read → move → delete” can otherwise cause duplicates.
Fifth, define a small set of regression test scenarios and run them regularly. For example:
- Try saving an existing container again → expect duplicate error
- Try saving on a date outside the window → expect window error
- Set capacity to 1 for a slot and save twice → expect only one success
Running 3–4 such tests after any rules or sheet structure change makes it easy to spot problems early.
Conclusion: deliberately try to double‑book the last spot
With everything above in place, your Google Sheets inbound booking system can now programmatically control the most common issues: duplicate bookings and capacity overruns. By wrapping the booking save flow in LockService and handling validation → business rules → container duplicate check → capacity recheck → save as a single sequence, you keep data consistent even when multiple users look at the same time slot.
The next concrete action you can take is this: pick a door/time with capacity 1 and try saving two test bookings with different container numbers at the same time. If one succeeds and the other fails with a “capacity full” or duplicate message, your method for preventing duplicate inbound bookings in Google Sheets with LockService is working as intended.
