Smart Life US
Google Sheets dock door auto assignment with Apps Script – Booking 5
Introduction – inbound bookings, door assignment is always a pain
When you use Google Sheets to take inbound appointments, there’s always one manual task left at the end: someone has to look at each booking and decide which door to put it on. If you’re manually choosing doors based on appointment time, equipment type, and priority, it’s very easy during busy hours to overload D01 or accidentally assign two loads to the same door at the same time. Out on the warehouse floor, “we took the appointments fine, but the door schedule got tangled” is something that can easily throw off the whole day.
This post covers a Google Sheets dock door auto-assignment method to reduce that kind of problem. We’ll build an Apps Script that automatically assigns inbound bookings to doors in Google Sheets, and then wire it cleanly into the existing booking-save flow, step by step.
This is part 5 of the “Build a warehouse booking system in Google Sheets – booking module” series. In Prevent double-booking in Google Sheets inbound appointments with LockService: save safely and earlier, we implemented business hours & holidays, valid booking window calculation, capacity per time slot, input validation and saving, and overbooking prevention. Now on top of that, we’ll add an Apps Script dock door auto-allocation feature so that “even if multiple bookings come in for the same time, they get attached to D01, D02, D03… in order without overlaps.”
Designing auto door assignment – on what basis do we pick a door?
The core idea for auto-assigning dock doors in Google Sheets is simple. First, for the booking you’re trying to make, you look at date, time slot, and equipment type and compute which doors are already in use in that slot. Then you take your full door list, subtract the doors already in use, and from the remaining doors you assign the lowest-numbered door first.
From actual DC operations, assigning doors in random order is less helpful than just filling them in numeric order (e.g., D01→D02→D03). It’s better for travel paths and communication with the floor. It’s also easier for the ops team to tell at a glance which time blocks are heavily loaded on which stretch of doors, and to use that for next-day staffing.
The flow we’ll implement here looks like this:
BOOK_getDoorUsageForSlotCore_()calculates the list of doors already occupied for a given date, time, and equipment type.autoAssignDoor_()uses that info to pick the lowest-numbered door from the list of available doors.- In the existing booking-save function
BOOK_bookAppt(), if the door field is empty we callautoAssignDoor_()to try an auto-assignment. On success column D gets the door; on failure the row is saved with column D left blank. - After saving,
notifyBookingConfirmed_()sends a confirmation email including the booking details and door assignment.
From experience, if all doors are full, it’s more realistic to save the booking in a “door pending” status rather than reject it outright, and let a supervisor tidy things up manually before the shift starts or during a slow time. The auto-assignment logic here is built with that in mind.
Door usage calculation function – implementing BOOK_getDoorUsageForSlotCore_
The first capability we need for auto door assignment is to accurately count “which doors are already in use in this time slot.” To do this, we’ll build a core function called BOOK_getDoorUsageForSlotCore_(). It uses the same main booking sheet APPT_MAIN from earlier parts, filters rows by matching date, time, and equipment type, and then aggregates the door values from those rows.
In practice, when you test this, three mistakes show up the most. First, selecting the wrong column range and ending up reading the wrong values. Second, if the door column is formatted as a number, you get numeric types instead of strings and some doors silently get skipped when checking usage. Third, typos or blanks in the equipment type column slip through, making a slot appear to have “zero usage” when it actually doesn’t. The code below is designed to guard against all three.
From here on we’ll write real Apps Script. We assume you already have helper functions like APPT_ymd_() and APPT_hm_() from earlier parts, which produce string keys for dates and times. If you’re only using this post on its own, you’ll need to create functions with the same interface yourself.
Step 1 — declare config constants for door usage calculation
- What it does: centralizes the sheet name and column positions needed to compute door usage.
- Where: at the very top of
Code.gs, right under your existingBOOK_constants. - After pasting: Save (⌘S/Ctrl+S) and make sure there are no errors.
// → Config constants dedicated to auto door assignment
// These indices must mean **exactly what part 4's `BOOK_APPT_COLS` fixed**:
// A date | B start time | C equipment type | D door | E container | F carrier | G client | H remark
// I end time | J created at | K qty | L pallets | M booking ID - **there is no status column.**
const BOOK_DOOR_CONFIG = { // → Settings for this part
APPT_SHEET_NAME: 'APPT_MAIN', // → Main booking sheet name
COL_DATE: 1, // → Column A: booking date
COL_TIME: 2, // → Column B: start time
COL_EQUIP_TYPE: 3, // → Column C: equipment type
COL_DOOR: 4, // → Column D: door code
HEADER_ROWS: 1 // → Number of header rows
}; //How to check it: as long as saving doesn’t complain about a “duplicate identifier,” you’re good. It’s fine if values overlap with other constants as long as the names are unique.
Step 2 — BOOK_getDoorUsageForSlotCore_ and a test helper
- What it does: computes the list of doors already assigned in a given date/time/equipment-type slot.
- Where: paste this near the bottom of
Code.gs, right after your existingBOOK_functions. - After pasting: run the test helper
BOOK_testGetDoorUsageForSlotCore_()and inspect the log.
// → Calculate which doors are in use for a specific slot
function BOOK_getDoorUsageForSlotCore_(dateObj, timeObj, equipType) { // → Core calculation
if (!(dateObj instanceof Date) || isNaN(dateObj)) { // → Validate date
throw new Error('Invalid date'); // → Block bad input
} //
if (!(timeObj instanceof Date) || isNaN(timeObj)) { // → Validate time
throw new Error('Invalid time'); // → Block bad input
} //
if (typeof equipType !== 'string' || !equipType.trim()) { // → Validate equipment type
throw new Error('Equipment type is empty'); // → Required field
} //
// Building a second list here creates values part 3's capacity function does not know,
// and those bookings drop out of the count entirely.
const allowedEquipTypes = BOOK_CAPACITY_CONFIG.VALID_TYPES; // → Part 3's shared list
const cleanEquipType = equipType.trim().toUpperCase(); // → Normalize case
if (!allowedEquipTypes.includes(cleanEquipType)) { // → Check whitelist
throw new Error('Unsupported equipment type: ' + equipType); // → Block typos/unregistered
} //
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sheet = ss.getSheetByName(BOOK_DOOR_CONFIG.APPT_SHEET_NAME); // → Booking sheet
if (!sheet) { // → Sheet missing
throw new Error('APPT_MAIN sheet not found'); // → Config error
} //
const lastRow = sheet.getLastRow(); // → Last row index
if (lastRow <= BOOK_DOOR_CONFIG.HEADER_ROWS) { // → No data rows
return { // → Return empty result
dateKey: APPT_ymd_(dateObj), // → Date key
timeKey: APPT_hm_(timeObj), // → Time key
equipType: cleanEquipType, // → Equipment type
usedDoors: [] // → No doors in use
}; //
} //
const startRow = BOOK_DOOR_CONFIG.HEADER_ROWS + 1; // → First data row
const numRows = lastRow - BOOK_DOOR_CONFIG.HEADER_ROWS; // → Row count
const numColumns = BOOK_DOOR_CONFIG.COL_DOOR - BOOK_DOOR_CONFIG.COL_DATE + 1; // → columns A-D
const range = sheet.getRange( // → Full data range
startRow, // → Start row
BOOK_DOOR_CONFIG.COL_DATE, // → Start column
numRows, // → Number of rows
numColumns // → Number of columns
); //
const values = range.getValues(); // → 2D array of data
const targetDateKey = APPT_ymd_(dateObj); // → Date key for comparison
const targetTimeKey = APPT_hm_(timeObj); // → Time key for comparison
const usedDoorSet = new Set(); // → For deduplication
const badRows = []; // → Rows with a broken door code
for (let i = 0; i < values.length; i++) { // → Iterate rows
const row = values[i]; //
const dt = row[BOOK_DOOR_CONFIG.COL_DATE - BOOK_DOOR_CONFIG.COL_DATE]; // → Date value
const tm = row[BOOK_DOOR_CONFIG.COL_TIME - BOOK_DOOR_CONFIG.COL_DATE]; // → Time value
const type = row[BOOK_DOOR_CONFIG.COL_EQUIP_TYPE - BOOK_DOOR_CONFIG.COL_DATE]; // → Equipment type
const doorRaw = row[BOOK_DOOR_CONFIG.COL_DOOR - BOOK_DOOR_CONFIG.COL_DATE]; // → Raw door
// getLastRow() includes rows whose values were cleared. Feeding those straight into
// APPT_ymd_ throws and **stops the whole assignment**. Skip fully empty rows; collect
// partially filled ones so a person can look at them.
if (!dt && !tm && !type && !doorRaw) { // → Fully empty row
continue; //
} //
let rowDateKey; // → Row date key
let rowTimeKey; // → Row time key
try { // → Read date/time
rowDateKey = APPT_ymd_(dt); // → 'YYYY-MM-DD'
rowTimeKey = APPT_hm_(tm); // → 'HH:mm'
} catch (e) { // → Unreadable row
badRows.push(i + BOOK_DOOR_CONFIG.HEADER_ROWS + 1); // → Record row number
continue; //
} //
if (rowDateKey !== targetDateKey) { // → Different date
continue; //
} //
const rowType = String(type || '').trim().toUpperCase(); // → Row equipment type
if (rowType !== cleanEquipType) { // → Different type
continue; //
} //
if (rowTimeKey !== targetTimeKey) { // → Different time
continue; //
} //
const door = String(doorRaw || '').trim(); // → Normalize door
if (!door) { // → Skip blank door
continue; //
} //
// → Validate door code format (e.g. only accept D01, D02)
// Silently skipping a malformed door makes it look free and the same door gets
// assigned twice. Stop and let a person fix the sheet instead.
const doorPattern = /^D\d{2}$/; // → Door format regex
if (!doorPattern.test(door)) { // → Non-matching pattern
badRows.push(i + BOOK_DOOR_CONFIG.HEADER_ROWS + 1); // → Record row number
continue; // → Don’t treat as in use
} //
usedDoorSet.add(door); // → Add to used doors
} //
if (badRows.length > 0) { // → Any row we could not read
throw new Error('Some booking rows could not be read (rows ' + // → Stop assigning
badRows.slice(0, 10).join(', ') +
'). Check the date, start time and door code (D01 format), then run again.');
} //
return { // → Return result object
dateKey: targetDateKey, // → Date key
timeKey: targetTimeKey, // → Time key
equipType: cleanEquipType, // → Equipment type
usedDoors: Array.from(usedDoorSet).sort() // → Sorted door list
}; //
} //Test helper:
// → Test door usage calculation
function BOOK_testGetDoorUsageForSlotCore_() { // → Test function
const today = new Date(); // → Today’s date
const time = new Date(); // → Base for time
time.setHours(9, 0, 0, 0); // → Fix at 09:00
const equipType = 'CONTAINER'; // → Sample equipment
const result = BOOK_getDoorUsageForSlotCore_(today, time, equipType); // → Run function
Logger.log(JSON.stringify(result)); // → Log result
if (!Array.isArray(result.usedDoors)) { // → Validate usedDoors type
throw new Error('usedDoors is not an array'); // → Fail fast
} //
} //How to check it: in the Apps Script editor, select and run BOOK_testGetDoorUsageForSlotCore_, then look at the execution log for something like {"dateKey":"...","timeKey":"...","usedDoors":[...]}. Pick a specific time slot in your sheet and manually count which doors are used; make sure that matches the output.
Auto door assignment – finding a free door with autoAssignDoor_
Now we’ll build the function that actually decides which door to assign. It will take the door usage result above, filter the active door list, and pick the lowest-numbered door that isn’t already in use.
Here we assume you already have a listActiveDoors_() function from a previous part, which reads from a config sheet and returns only doors marked as “in use (Y/N)”. If you’re only using this article, you’ll need to create a helper that returns an array like ['D01','D02',...] with the same interface.
If there are no doors defined at all, or all of them are used, the auto-assignment function won’t try to force anything. Instead, it returns isPending: true, and the higher-level logic will treat that as “door assignment pending.”
Step 3 — autoAssignDoor_ and its test
- What it does: takes the active door list, subtracts used doors, and picks the lowest-numbered available door.
- Where: paste this right under
BOOK_getDoorUsageForSlotCore_(). - After pasting: run
BOOK_testAutoAssignDoor_()and verify you get at least one assigned door for a free slot, and pending status when all doors are full.
// → Automatically choose a free door
function autoAssignDoor_(dateObj, timeObj, equipType) { // → Auto door assignment
const usage = BOOK_getDoorUsageForSlotCore_(dateObj, timeObj, equipType); // → Get used doors
const usedDoors = new Set(usage.usedDoors); // → For fast lookups
const activeDoors = listActiveDoors_(); // → List of active doors
if (!Array.isArray(activeDoors) || activeDoors.length === 0) { // → No doors configured
return { // → Return pending
assignedDoor: null, // → No door
isPending: true // → Mark as pending
}; //
} //
const allDoors = activeDoors // → Full door list
.map(d => String(d || '').trim()) // → Normalize strings
.filter(d => d); // → Drop blanks
// Validate the candidate list too. A typo like 'D1' in the DOORS sheet is filtered out
// of the usage count but survives here - and gets assigned on top of a door already in use.
const badDoors = allDoors.filter(d => !/^D\d{2}$/.test(d)); // → Malformed doors
if (badDoors.length > 0) { // → Any at all
throw new Error('The active door list contains malformed values: ' + // → Stop assigning
badDoors.slice(0, 10).join(', ') +
'. Fix the DOORS sheet to the D01 format and run again.');
} //
const available = allDoors // → Candidate doors
.filter(d => !usedDoors.has(d)) // → Exclude used ones
.sort(); // → Sort by code
if (available.length === 0) { // → No doors left
return { // → Return pending
assignedDoor: null, // → No door
isPending: true // → Mark as pending
}; //
} //
return { // → Return success
assignedDoor: available[0], // → First (lowest) door
isPending: false // → Normal assignment
}; //
} //Test helper:
// → Test auto door assignment
function BOOK_testAutoAssignDoor_() { // → Test function
const today = new Date(); // → Today
const time = new Date(); // → Base time
time.setHours(10, 0, 0, 0); // → Assume 10:00 slot
const equipType = 'CONTAINER'; // → Sample equipment
const result = autoAssignDoor_(today, time, equipType); // → Run assignment
Logger.log(JSON.stringify(result)); // → Log result
if (result.assignedDoor && result.isPending) { // → Sanity check
throw new Error('assignedDoor and isPending are contradictory'); // → Guard logic
} //
} //How to check it: run it and look for a log like {"assignedDoor":"D01","isPending":false}. If you have no active doors configured, you should see {"assignedDoor":null,"isPending":true}. To verify the sequence, create multiple test bookings for the same slot in APPT_MAIN and confirm the doors advance in order.
Wiring auto-assignment and email into the booking-save flow
Once door usage and auto-select functions are ready, we need to wire them into the main booking-save function BOOK_bookAppt() to make them useful. In practice, the natural sequence looks like this:
- The user submits booking info via a form or sidebar.
- Inside
BOOK_bookAppt(data),BOOK_validateBookingInput_(data)validates the input and returns avalidatedobject. Its fields are exactly what part 4 fixed:date,time,endTime,type,door,containerNo. There is nodateObj,timeObj, orequipType. - If
validated.dooris empty, callautoAssignDoor_()to pick a door. - If no door is available, save the row with column D (door) left blank. The appointment sheet has only columns A-M and no status column, so an empty door cell is the pending state. A separate
doorStatusvalue has nowhere to be written and simply disappears. - After writing the row to the sheet, we call
notifyBookingConfirmed_()to send a confirmation email.
One crucial point is locking for concurrent execution. If multiple users can book the same slot at nearly the same time, BOOK_bookAppt() as a whole should be wrapped in LockService.getScriptLock() so that only one request at a time can perform “door assignment + capacity check + duplicate check + write.” If you already implemented a LockService pattern in the previous part, just keep using the same structure.
Step 4 — insert auto-assignment snippet inside BOOK_bookAppt
This step doesn’t rewrite your entire function; you just plug in a small block at the right place.
- What it does: when the door is empty, auto-assigns one; if none is free, saves with the door cell blank.
- Do this first: part 4's
BOOK_validateBookingInput_()rejects an empty door. Until you relax those two checks, execution never reaches auto-assignment. - Where: inside
BOOK_bookAppt(payload), right after all validations and just before you actually write to the sheet. - After pasting: create several test bookings with the same date/time/equipment and check that doors are assigned in sequence.
// In part 4's BOOK_validateBookingInput_() - change these two places.
// (1) Required-door check -> delete it, or relax it as below
// if (!door) {
// throw new Error('A door (or yard slot) is required.');
// }
// -> treat an empty door as a request for auto-assignment
// (2) Door format check -> only validate when a value was supplied
// if (!/^D\d{2}$/.test(door)) {
// throw new Error('Door format is invalid.');
// }
// ->
if (door && !/^D\d{2}$/.test(door)) {
throw new Error('Door format is invalid.');
}
// (3) Add the confirmation e-mail address to the return object - without this,
// no mail is ever sent from a real booking. In the cleanup section add:
const email = String(data.email || '').trim();
// and one more line in the returned object:
// pallet: pallet,
email: emailThe third change matters: part 4's return object has no email field, so without it the notification function below simply returns without sending anything.
Then add this right after the validation call inside BOOK_bookAppt().
// Inside BOOK_bookAppt(data) - right after BOOK_validateBookingInput_().
const validated = BOOK_validateBookingInput_(data); // <- already in part 4
// Auto-assign the door. validated uses date/time/type - not dateObj/timeObj/equipType.
if (!validated.door) { // → No door supplied
// autoAssignDoor_ takes two Date objects - same conversion part 4's save uses.
const dp = validated.date.split('-'); // → year/month/day
const tp = validated.time.split(':'); // → hour/minute
const slotDate = new Date(Number(dp[0]), Number(dp[1]) - 1, Number(dp[2]));
const slotTime = new Date(1899, 11, 30, Number(tp[0]), Number(tp[1]));
const auto = autoAssignDoor_(slotDate, slotTime, validated.type);
validated.door = auto.assignedDoor || ''; // → blank = pending
}
BOOK_validateBusinessRules_(validated); // <- already in part 4
BOOK_checkDuplicateContainer_(validated); // <- already in part 4
BOOK_checkCapacityAndSave_(validated); // <- already in part 4
// Mail only after the row is saved. Never roll a booking back over a mail failure.
try {
notifyBookingConfirmed_(validated);
} catch (mailErr) {
Logger.log('Confirmation mail failed (booking was saved): ' + mailErr.message);
}How to check it: in APPT_MAIN, create several bookings for the same date, time slot, and equipment type with the door left blank. Column D should fill D01, D02, D03 in order, and once you run out of active doors the rows should save with column D empty. To review pending bookings, filter for a blank column D - no status column needed.
Booking confirmation emails – basic skeleton for notifyBookingConfirmed_
Finally, after a booking is saved, we’ll add a function to send a confirmation email. In day-to-day operations, this message is what gets forwarded to the carrier or driver, telling them when to show up and which door to use. Here we’ll build a minimal structure you can expand as needed.
The notification function accepts a booking object and composes a simple text email with date, time, equipment type, and door status. If the email field is blank, it quietly exits without doing anything, so you don’t have to use dummy email addresses in test environments.
Step 5 — add notifyBookingConfirmed_ and a test function
- What it does: sends a confirmation email including booking and door info.
- Where: paste this underneath
autoAssignDoor_(). - After pasting: run
BOOK_testNotifyBookingConfirmed_()and verify that you receive a test email.
// → Send booking confirmation email
function notifyBookingConfirmed_(booking) { // → Email sender
// booking: the object part 4's BOOK_validateBookingInput_() returns, as-is.
// { date:'YYYY-MM-DD', time:'HH:mm', type, door, containerNo, carrier, client ... }
// An empty door string *is* the pending state - there is no separate status field.
if (!booking || typeof booking !== 'object') { // → Validate input
throw new Error('booking object is required'); // → Required value
} //
// email arrives only if you added it in step (3) above. With no address we send
// nothing and return quietly - the booking is already saved, so this is not a failure.
const to = String(booking.email || '').trim(); // → Recipient email
if (!to) { // → No email
return; // → Exit quietly
} //
const dateStr = APPT_ymd_(booking.date) ; // → Date string
const timeStr = APPT_hm_(booking.time) ; // → Time string
const doorLabel = booking.door || '(Door assignment pending)'; // → Door label
const subject = '[Inbound booking confirmed] ' + dateStr + ' ' + timeStr; // → Subject
let body = ''; // → Email body
body += 'Your inbound booking has been received.\n\n'; // → Intro
body += 'Date: ' + dateStr + '\n'; // → Date
body += 'Time: ' + timeStr + '\n'; // → Time
body += 'Equipment type: ' + (booking.type || '') + '\n'; // → Equipment
body += 'Container: ' + (booking.containerNo || '') + '\n'; // → Container
body += 'Door: ' + doorLabel + '\n'; // → Door info
if (!booking.door) { // → Door still blank
body += '\nNote: a door has not been assigned yet. We will confirm before arrival.\n';
} //
body += '\nIf you need to change or cancel this booking, please contact your warehouse representative.\n'; // → Closing
MailApp.sendEmail({ // → Send email
to: to, // → Recipient
subject: subject, // → Subject
body: body // → Body
}); //
} //Test helper:
// → Test confirmation email
function BOOK_testNotifyBookingConfirmed_() { // → Test function
const today = new Date(); // → Today
const time = new Date(); // → Now
time.setHours(11, 30, 0, 0); // → Set to 11:30
const dummy = { // → Sample booking object
email: Session.getActiveUser().getEmail() || '[email protected]', // → Current user
date: APPT_ymd_(today), // → 'YYYY-MM-DD'
time: APPT_hm_(time), // → 'HH:mm'
type: 'CONTAINER', // → Equipment type
containerNo: 'TEST1234567', // → Sample container
door: 'D01' // → Sample door (blank = pending)
}; //
notifyBookingConfirmed_(dummy); // → Send test email
} //How to check it: run BOOK_testNotifyBookingConfirmed_ from the editor, then look for an email in your inbox with a subject like “[Inbound booking confirmed] …”. Depending on your org’s settings, Session.getActiveUser().getEmail() may be empty, in which case the email goes to [email protected] as coded.
Practical tips – running safely when multiple bookings hit at once
Once you roll out a Google Sheets booking system with auto door assignment in a live warehouse, how you handle concurrency and edge cases matters more than the feature itself. From experience, these practices keep operations stable:
First, wrap the entire BOOK_bookAppt() function in LockService.getScriptLock(). The lock should cover door auto-assignment, capacity calculation, duplicate checks, and the actual write. If you don’t, two users hitting the same slot at the same time can end up with the same door. A wait time of about 20–30 seconds has been workable in real use.
Second, always whitelist string fields like equipment type and door code. A single typo in the equipment type column can make code think a slot has “zero door usage” and allow far too many bookings. Using a constant list of allowed values and rejecting anything else, as shown here, pays for itself in reduced troubleshooting.
Third, use “door assignment pending” proactively. Instead of forcing the system to magically find a door when they're all full, let the booking in and leave the door cell blank. A shift lead can then clean up the backlog in one pass. In this sheet structure a blank column D is the pending state, so a single blank-D filter gives you the pending view - no status column required.
Fourth, be clear that this series has no cancellation flow yet. The appointment sheet (columns A-M) has no status column, so there is no way to mark a cancelled booking as having released its door. For now, handle cancellations by deleting the row or moving it to the archive sheet - otherwise the door-usage count stays wrong. Adding a real status column means changing the sheet headers, the save function, the capacity count and this part's usage count all at once, which belongs in its own article.
Finally, always simulate on a test spreadsheet first. If you change code against the live sheet, that day’s door stats and reports will be inconsistent. Clone your live structure into a test sheet, seed it with a day’s worth of dummy bookings across hours and equipment types, and run multiple scenarios there before touching production.
Wrap-up – one check you can run today
Google Sheets dock door auto-assignment might look complex from a distance, but at its core it’s just “count which doors are already occupied in this slot, then pick one from the remaining doors” turned into code. Combined with overbooking prevention and locking, it can significantly reduce the time humans spend opening spreadsheets and hand-assigning doors.
One concrete thing you can do today: pick a specific date/time slot in your live APPT_MAIN sheet, manually count the used doors, then run BOOK_testGetDoorUsageForSlotCore_() for that same slot and compare. If the two match exactly, you’re ready to layer auto-assignment and email notifications on top. Passing this single check makes it much easier to confidently evolve the system later when you add doors, extend operating hours, or change capacity rules.
