Smart Life US
Google Sheets bulk appointment booking: finish with one LockService and setValues call
Introduction — validation is done, but saving is too slow
When you try to build bulk appointment registration in Google Sheets, you often run into this: “validation works fine, but writing the data to the sheet is way too slow.” In workflows like inbound appointments, where dozens or hundreds of rows accumulate daily, you want to paste the carrier’s appointment list once and process it in bulk. In the previous post, Google Sheets bulk appointment validation: validate on a single paste (final version), we implemented validateBulkSlots() that inspects each pasted candidate row and pre‑filters date, time, capacity, and container duplicates.
The real problem comes next. You must copy only the rows marked “passed” into the APPT_MAIN sheet, but if you call appendRow() for every single row, performance degrades noticeably once you hit even 100 rows. If two people save at the same time, you can even end up with duplicate appointments. In this post, we’ll go over a flow that re‑checks the rows that passed validation on the server, acquires a lock with LockService, and then registers all appointments in one shot with a single setValues call. The core question is: “how do we reduce errors caused by the time gap between validation and saving?”
Why not trust validation results and re‑check on the server?
Once I put bulk registration into real‑world operation, I realized it’s more dangerous than expected to blindly write rows with a green “validation passed” mark to the sheet. Validation is only a snapshot taken at paste time. It can take minutes before the user clicks the Save button; in the meantime, another user might book the same container, or add another appointment in the same time slot and exhaust the remaining capacity. If you simply trust the validation result, you’ll miss the fact that “there was capacity back then, but now we’re over capacity.”
So when designing the bulk booking function bulkBookAppts(rows), I applied two principles. First, we re‑validate the incoming rows on the server. We reuse the validateBulkSlots() function from the previous post, but we only save rows based on the cleaned data that function returns, and we store validation failures/exceptions in a failed list with detailed reasons. Second, the actual write to the sheet is performed only once using setValues inside a LockService.getScriptLock() critical section. If we configure single‑appointment booking (BOOK_bookAppt) in the same script to use the same ScriptLock, then no matter which path is used to create appointments, only “one batch at a time” can be written.
With this structure, we can return results in a clean {total, success, failed} format. Operators can quickly see “how many rows succeeded and why each failed,” and they can pick out only the failed rows to coordinate with the carrier again. Think of it as separating validation and saving, but ensuring the saving step reflects the actual current state one more time.
Designing bulkBookAppts — inputs, outputs, and flow
The core function in this article is bulkBookAppts(rows). We assume rows is an array coming from a web app or sidebar. Each element represents one appointment row, in the exact field format (date, time, type, container, etc.) that our previous validateBulkSlots() understands.
The processing flow can be organised as follows. First, verify that rows is an array and that its length is within a reasonable range to prevent basic input errors. Then, for each row, call validateBulkSlots([raw]) again; if ok is false or cleaned is empty, record that row in the failure list with a reason and skip it. For rows that pass, take cleaned[0] and convert it to an array that matches the column order in the APPT_MAIN sheet. Since the APPT_MAIN layout is fixed for this whole series, it’s safer to manage the positions of date, time, type, door, container, quantity, pallets, etc. with constants.
Next, collect all converted rows in rowsToInsert. After finishing validation, acquire a LockService lock and write them to APPT_MAIN using a single call to setValues(rowsToInsert) starting from the first empty row at the bottom. Finally, return an object {total, success, failed} where total is rows.length, success is rowsToInsert.length, and failed is the failure list. Keeping the return shape fixed like this allows you to reuse the same processing logic whether it’s called from a web app, a button, or somewhere else.
Step 1 — define configuration constants for bulk registration
First, we’ll gather configuration constants needed for Google Sheets Apps Script bulk registration. This project contains multiple series codes in one place, so we’ll prefix constants for this article with BULK_. Managing sheet names and column positions as constants reduces the need to hunt through all code when APPT_MAIN’s structure changes.
1) What this code does
- Defines the APPT_MAIN sheet name, lock wait time, max rows per run, and column indexes in one place.
2) Where to paste it
- In Google Sheets → Extensions → Apps Script → in your project, paste at the top of this series file or at the very top of
Code.gs.
3) What to do after pasting
- Just save; no need to run anything yet.
const BULK_APPT_SHEET_NAME = 'APPT_MAIN'; // → Appointment sheet name
const BULK_APPT_TZ = 'America/New_York'; // → Fixed time zone
const BULK_LOCK_WAIT_MS = 30 * 1000; // → Lock wait max 30 seconds
const BULK_MAX_ROWS_PER_RUN = 500; // → Max rows per single run
// APPT_MAIN column indexes (1-based)
const BULK_COL_DATE = 1; // → Column A: Date
const BULK_COL_START = 2; // → Column B: Start time
const BULK_COL_TYPE = 3; // → Column C: Equipment type
const BULK_COL_DOOR = 4; // → Column D: Door
const BULK_COL_CNTR = 5; // → Column E: Container
const BULK_COL_CARRIER = 6; // → Column F: Carrier
const BULK_COL_CLIENT = 7; // → Column G: Client
const BULK_COL_REMARK = 8; // → Column H: Note
const BULK_COL_END = 9; // → Column I: End time
const BULK_COL_CREATED_AT = 10; // → Column J: Created at
const BULK_COL_QTY = 11; // → Column K: Quantity
const BULK_COL_PALLET = 12; // → Column L: Pallets
const BULK_COL_APPT_ID = 13; // → Column M: Appointment IDHow to check it works: nothing visible yet; as long as subsequent code can reference BULK_... constants without errors, it’s fine.
Step 2 — helper to convert one row into APPT_MAIN format
Next is a helper that transforms one validated row into APPT_MAIN format. We already defined the base appointment structure and column order in the previous series, so this function simply follows those rules. Date and time were already strictly validated and normalized earlier, so here we only need to place values into the right positions.
1) What this code does
- Converts one
cleanedrow fromvalidateBulkSlots()into an array that matches APPT_MAIN’s column order.
2) Where to paste it
- Right below the configuration constants, in the same file.
3) What to do after pasting
- Run
BULK_testMapRow_()once and check if the converted result is logged correctly.
function BULK_mapCleanRowToApptRow_(clean) { // → Map a validated row to an appointment row
const tz = BULK_APPT_TZ; // → Use time zone constant
const dateStr = clean.date; // → 'YYYY-MM-DD'
const startStr = clean.time; // → 'HH:mm'
const endStr = clean.endTime; // → 'HH:mm'
const dateObj = new Date(dateStr + 'T00:00:00'); // → Date object
const createdAt = new Date(); // → Creation timestamp
const apptId = Utilities.getUuid(); // → Generate unique appointment ID
return [
dateObj, // → A Date (Date)
startStr, // → B Start time (string)
clean.type, // → C Equipment type
clean.door || '', // → D Door
clean.containerNo || '', // → E Container
clean.carrier || '', // → F Carrier
clean.client || '', // → G Client
clean.remark || '', // → H Note
endStr, // → I End time
createdAt, // → J Created at
clean.qty || 0, // → K Quantity
clean.pallet || 0, // → L Pallets
apptId // → M Appointment ID
];
}
function BULK_testMapRow_() { // → Mapping test function
const sample = { // → Sample data
date: '2026-08-31', // → Date
time: '09:00', // → Start
endTime: '09:30', // → End
type: 'CONTAINER', // → Type
door: 'D01', // → Door
containerNo: 'TEST123', // → Container
carrier: 'CARRIER', // → Carrier
client: 'CLIENT', // → Client
remark: 'Test', // → Note
qty: 1, // → Quantity
pallet: 2 // → Pallets
};
const row = BULK_mapCleanRowToApptRow_(sample); // → Call mapping
Logger.log(JSON.stringify(row)); // → Log result
}How to check it works: in the Apps Script editor, run BULK_testMapRow_. If the execution log shows an array of length 13 and the first value is displayed as a Date object, it’s working.
Step 3 — saving with one setValues inside LockService
Now we’ll implement bulkBookAppts(rows), which uses Google Sheets LockService to actually write only rows that passed validation into APPT_MAIN. This function is kept simple so it can be called directly from a web app or sidebar.
1) What this code does
- Re‑validates the input array, gathers only passing rows, and writes them to APPT_MAIN using one
setValuescall inside aLockServicelock.
2) Where to paste it
- Right below the helper functions above.
3) What to do after pasting
- Run
BULK_testBulkBookAppts_()and confirm that, from 4 sample rows, only a subset is saved to the sheet and the result is logged.
function bulkBookAppts(rows) { // → Main function for bulk booking
if (!Array.isArray(rows)) { // → Check input type
throw new Error('An array "rows" is required'); // → Prevent incorrect calls
}
if (rows.length === 0) { // → Handle empty array
return { total: 0, success: 0, failed: [] }; // → Return immediately
}
if (rows.length > BULK_MAX_ROWS_PER_RUN) { // → Enforce max row limit
throw new Error('You can process up to ' + BULK_MAX_ROWS_PER_RUN +
' rows at a time'); // → Prevent excessive requests
}
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sheet = ss.getSheetByName(BULK_APPT_SHEET_NAME); // → APPT_MAIN sheet
if (!sheet) { // → Verify sheet exists
throw new Error('Sheet ' + BULK_APPT_SHEET_NAME + ' not found');
}
const failed = []; // → Failure list
const rowsToInsert = []; // → Rows to actually save
// Step 1: re-validate each row.
rows.forEach(function (raw, idx) { // → Iterate each row
try {
const v = validateBulkSlots([raw]); // → Reuse validation from part 1
if (!v.ok || !Array.isArray(v.cleaned) || v.cleaned.length === 0) {
failed.push({ // → Record failure
index: idx,
reason: v.message || 'Validation failed' // → Reason
});
return; // → Next row
}
const clean = v.cleaned[0]; // → One cleaned row
const apptRow = BULK_mapCleanRowToApptRow_(clean); // → Convert to sheet row
rowsToInsert.push(apptRow); // → Add to save targets
} catch (e) { // → Handle exceptions
failed.push({ // → Record failure
index: idx,
reason: e.message || 'Exception occurred' // → Exception message
});
}
});
if (rowsToInsert.length === 0) { // → No rows to save
return { // → Return only validation results
total: rows.length,
success: 0,
failed: failed
};
}
// Step 2: save with a single setValues call inside a lock.
const lock = LockService.getScriptLock(); // → Script-level lock
let lockAcquired = false; // → Whether we acquired the lock
try {
lockAcquired = lock.tryLock(BULK_LOCK_WAIT_MS); // → Wait up to max time
if (!lockAcquired) { // → Failed to acquire lock
throw new Error('Another job is running. Please try again in a moment');
}
const lastRow = sheet.getLastRow(); // → Current last row
const startRow = lastRow + 1; // → First row to write
const numRows = rowsToInsert.length; // → Number of rows to add
const numCols = rowsToInsert[0].length; // → Number of columns
const range = sheet.getRange(startRow, 1, numRows, numCols); // → Target range
range.setValues(rowsToInsert); // → Write in a single call
return { // → Return summary result
total: rows.length,
success: rowsToInsert.length,
failed: failed
};
} finally {
if (lockAcquired) { // → If we had a lock
lock.releaseLock(); // → Release it
}
}
}
function BULK_testBulkBookAppts_() { // → Bulk registration test
const sampleRows = [ // → 4 sample input rows
{
date: '2026-08-31',
time: '09:00',
endTime: '09:30',
type: 'CONTAINER',
door: 'D01',
containerNo: 'BULK001',
carrier: 'CARRIER1',
client: 'CLIENT1',
remark: 'OK1',
qty: 1,
pallet: 1
},
{
date: '2026-08-31',
time: '09:00',
endTime: '09:30',
type: 'CONTAINER',
door: 'D01',
containerNo: 'BULK002',
carrier: 'CARRIER2',
client: 'CLIENT2',
remark: 'OK2',
qty: 1,
pallet: 1
},
{
date: '2026-08-31', // → Intentionally wrong values
time: '99:00', // → Invalid time
endTime: '09:30',
type: 'CONTAINER',
door: 'D01',
containerNo: 'BULK003',
carrier: 'CARRIER3',
client: 'CLIENT3',
remark: 'BAD TIME',
qty: 1,
pallet: 1
},
{
date: '2026-08-31',
time: '09:00',
endTime: '09:30',
type: 'CONTAINER',
door: 'D01',
containerNo: 'BULK001', // → Duplicate container
carrier: 'CARRIER4',
client: 'CLIENT4',
remark: 'DUP CNTR',
qty: 1,
pallet: 1
}
];
const result = bulkBookAppts(sampleRows); // → Call main function
Logger.log(JSON.stringify(result)); // → Check result
}How to check it works: in the Apps Script editor, run BULK_testBulkBookAppts_. If only 2 valid rows are appended to the APPT_MAIN sheet and the log shows something like {"total":4,"success":2,"failed":[...]}, then it’s behaving as intended.
Practical tips — design concurrency and test scenarios together
In real warehouse operations, after running bulk setValues in Google Sheets for a while, I found that “how to make it fail” is more important than the feature itself. During peak times, multiple users create appointments simultaneously, so even with LockService, the perceived quality varies greatly depending on how long you wait for a lock and how many rows you allow per run. In this article we used 500 rows and 30 seconds as an example, but in practice it’s safer to set both the row limit and wait time conservatively based on your workload.
Test scenarios also need to go beyond the “everything succeeds” case. As in the test above, you must intentionally mix in invalid times and duplicate containers to verify that validation and saving results are separated as designed. In real operations, we also mix “dates outside the allowed booking window,” “disallowed equipment types,” and “time slots already at full capacity,” and then tune both the validation and bulk registration functions together to improve stability. Since validation and saving are separated, simply strengthening validateBulkSlots() raises the reliability of the entire bulk registration flow.
Closing — one thing you can try today
The Google Sheets Apps Script bulk registration flow can be split into three stages: “paste → validate → save once with setValues inside LockService.” If you already implemented the validation logic from the previous post, then simply pasting in the code from this article completes the last piece of large‑scale appointment registration.
The simplest thing you can do today is paste this article’s code into your project and run BULK_testBulkBookAppts_(). If two test appointments are added to APPT_MAIN and reasons for the two failed rows are neatly logged, your structure is ready. From there, you can wire the bulkBookAppts(rows) function to a web app button or admin menu and start pasting real carrier appointment files to complete end‑to‑end automation of inbound appointment booking.
