Smart Life US
Google Sheets booking capacity: auto slots by time
Introduction: can you see how many trucks fit per slot?
When you run inbound appointment booking for a warehouse yourself, one of the most frustrating issues is that it’s not obvious “how many trucks we can take in the same time slot.” If each planner decides by gut feeling, some days the docks sit empty, and other days too many trucks get bunched into the same time window and operations freeze up. This post tackles that problem with a Google Sheets booking capacity calculation and covers how to automatically display remaining capacity per time slot.
This post is part 3 of the Google Sheets inbound appointment system: booking registration. In Calculate booking availability period in Google Sheets: auto range from today we built the basic structure for operating hours and per-customer booking date ranges. Here, we build on that to calculate capacity and actual usage per time slot, and focus on code that computes remaining capacity (capacity − usage).
The example is built with Google Sheets and Apps Script to a level that’s usable in production: Google Sheets time-slot booking management, Apps Script booking slot capacity, and showing remaining booking capacity in Google Sheets. Save / duplicate / cleanup triggers would be too much to cover here, so this part focuses only on calculation logic and touches on Lock usage at save time at a conceptual level.
Why calculate time-slot booking capacity in code?
In the real world, the first idea that comes to mind is simple: filter the booking sheet by a specific time slot and use a formula like COUNTA to count rows and compare against capacity. When volume is low at the beginning, this works without big operational issues. But once bookings accumulate and more companies and planners get involved, the limits of a formula-based approach show up quickly.
- Performance issues
Once you go from a few dozen bookings a day to a few hundred, the formulas and filters spread across the sheet start slowing the entire document down. Especially when several people have the same sheet open, filter changes and recalculation frequently freeze the screen. Planners are usually juggling phone calls while manipulating the sheet, so even a few seconds of delay is painful in practice.
- Capacity by equipment type is hard to manage
In reality, containers, trailers, and small trucks all have different processing times and dock-usage patterns. To run a realistic Google Sheets inbound appointment system, you often need different capacities per time slot, like “08:00: up to 2 containers and 4 trailers.” If you try to handle that with plain formulas, the columns get very complex, and you’ll keep rewriting formulas every time a new equipment type is added.
Because of this, this series uses an Apps Script function dedicated to calculating booking slot capacity and only shows the result in the sheet or web app. The key idea is: use getValues() to read the booking sheet once, compute usage for each time slot in memory, and quickly return remaining capacity for any requested time/equipment combination. With this structure, even when you reach thousands of rows, showing remaining capacity in Google Sheets stays consistently fast.
Structure for reading per-equipment hourly capacity from settings
The starting point for time-slot booking capacity calculation is “capacity.” If you hard-code capacities directly into code, a developer has to change the code every time the operation changes. In practice that becomes maintenance cost. So in this series we keep all booking-related settings in a SETTINGS sheet and store per-equipment hourly capacity there as well.
For example, if containers are 2 per hour, trailers 4 per hour, and you may add small trucks later, you can store values in the settings sheet as BOOK_CAPACITY_CONTAINER, BOOK_CAPACITY_TRAILER, and so on. On the booking screen, equipment type is a required field, and Apps Script looks up the capacity based on that equipment type.
Another important point is avoiding collisions with other code by establishing a naming convention. In this booking module we prefix constants and function names with BOOK_. That way, if other automations (e.g., inventory management, load planning) are added to the same project, constant names won’t collide and cause errors. We reuse the loadSettings_() function built in the previous part, and in this part we only pull the keys related to booking capacity out of its result.
Let’s start with the code that reads default capacity per equipment type from the SETTINGS sheet.
Step 1 — Function to read base capacity per equipment type
This code reads booking slot capacity from the SETTINGS sheet depending on equipment type (container, trailer, etc.).
- Where to paste: Google Sheets → Extensions → Apps Script → at the very bottom of the existing
Code.gs - Prerequisite: The same project already has
loadSettings_()and a SETTINGS sheet set up. - After pasting: Save (⌘S/CTRL+S), then run
BOOK_testGetCapacityForType_()and check the execution log.
// ===== Step 1: Read capacity settings by equipment type =====
// Change only this section to fit your own environment
const BOOK_CAPACITY_CONFIG = { // → Booking capacity settings
SETTING_KEY_PREFIX: 'BOOK_CAPACITY_', // → SETTINGS key prefix
DEFAULT_PER_HOUR: 2, // → Default capacity when missing
VALID_TYPES: ['CONTAINER', 'TRAILER'] // → Allowed equipment types
}; // →
/**
* Get hourly capacity per equipment type
* @param {string} equipType Equipment type (e.g., 'CONTAINER', 'TRAILER')
* @return {number} Capacity per hour
*/
function BOOK_getCapacityForType_(equipType) { // → Lookup capacity per equipment type
if (!equipType) { // → Check if equipment value is empty
throw new Error('Equipment type is empty'); // → Abort with error
} // →
const upperType = String(equipType).toUpperCase(); // → Normalize to uppercase
if (!BOOK_CAPACITY_CONFIG.VALID_TYPES.includes(upperType)) { // → Check allowed list
throw new Error('Disallowed equipment type: ' + equipType); // → Reject invalid value
} // →
const settings = loadSettings_(); // → Reuse SETTINGS loader from previous part
const key = BOOK_CAPACITY_CONFIG.SETTING_KEY_PREFIX + upperType; // → SETTINGS key name
const raw = settings[key]; // → Read value for that key
if (raw === undefined || raw === '') { // → If no value
return BOOK_CAPACITY_CONFIG.DEFAULT_PER_HOUR; // → Return default capacity
} // →
const capacity = Number(raw); // → Convert to number
// Capacity is a count of vehicles. 2.5 must not pass — isFinite alone lets it through.
if (!Number.isSafeInteger(capacity) || capacity <= 0) { // → Positive integers only
throw new Error('Capacity must be a positive integer: ' + raw); // → Settings error
} // →
return capacity; // → Return capacity
} // →
/**
* Test function for BOOK_getCapacityForType_
*/
function BOOK_testGetCapacityForType_() { // → Test helper
const types = BOOK_CAPACITY_CONFIG.VALID_TYPES; // → Allowed equipment list
const result = {}; // → Object to hold results
types.forEach(function(t) { // → Iterate per equipment type
result[t] = BOOK_getCapacityForType_(t); // → Lookup capacity
}); // →
Logger.log(JSON.stringify(result)); // → Output result to log
} // →How to verify
In the Apps Script editor, choose BOOK_testGetCapacityForType_, run it, and check the execution log. If you see JSON like { "CONTAINER": 2, "TRAILER": 4 } it’s working. If there’s no value in SETTINGS, DEFAULT_PER_HOUR will be used. If you intentionally put bad values like ABC, -1, or 0 in the settings, it should throw an error for that key so you can spot configuration issues quickly.
Step 2 — Calculate actual usage per date and time slot in one pass
With capacity in place, next you need to calculate “how many have already been booked.” This is where performance really diverges in Google Sheets time-slot booking management. If you call getValue() row by row or keep changing filters and using COUNTA per time slot, it may feel fine at the beginning, but once you pass a certain row count, perceived speed drops sharply.
For robust use in practice, it’s much better to “read all booking rows for a given date with a single getValues() and count usage per time slot and equipment type in JavaScript arrays.” This way the network roundtrip happens once, and most work is done in memory, which is fast even with thousands of records.
In this post, we assume bookings are stored in an APPT_MAIN sheet, where
- Column A: booking date (
Datetype) - Column B: booking time (
Datetype, only hour and minute matter) - Column C: equipment type (string, e.g.,
CONTAINER,TRAILER)
We assume there are already helper functions in the project from previous parts: APPT_ymd_() and APPT_hm_() that convert date/time to strings (yyyymmdd, HH:MM) for comparison. These functions are not redefined here; we reuse them as-is.
The following code is the core function that “counts bookings for all time/equipment combinations for a specific date and returns an object.” Values not in the allowed equipment list are silently skipped and not included in the aggregate. This is intentional so that rows with bad equipment codes don’t affect capacity calculations. Just keep in mind that out-of-list equipment values are ignored in usage counts.
Step 2 code — Calculate time-slot usage for a given date
- Where to paste: Same Apps Script project, in
Code.gs, directly under the Step 1 code - After pasting: Save and run
BOOK_testGetTimeUsageForDateCore_(); inspect the log.
// ===== Step 2: Aggregate usage per time and equipment by date =====
// Change these to fit your sheet structure
const BOOK_USAGE_CONFIG = { // → Usage calculation settings
APPT_SHEET_NAME: 'APPT_MAIN', // → Booking main sheet name
COL_DATE: 1, // → Date column index (A=1)
COL_TIME: 2, // → Time column index (B=2)
COL_EQUIP_TYPE: 3 // → Equipment type column index (C=3)
}; // →
/**
* Calculate bookings per time slot and equipment type for a given date
* @param {Date} targetDate Target date (Date object)
* @return {Object} Object in the form {"HH:MM|EQUIP": count}
*/
function BOOK_getTimeUsageForDateCore_(targetDate) { // → Time-slot usage per date
if (!(targetDate instanceof Date)) { // → Check Date type
throw new Error('You must pass a Date object'); // → Reject invalid input
} // →
const sheet = SpreadsheetApp // →
.getActive() // → Current spreadsheet
.getSheetByName(BOOK_USAGE_CONFIG.APPT_SHEET_NAME); // → Booking sheet
if (!sheet) { // → Check sheet existence
throw new Error('Booking sheet not found'); // → Configuration error
} // →
const lastRow = sheet.getLastRow(); // → Last data row
if (lastRow < 2) { // → Header only
return {}; // → Return empty result
} // →
const height = lastRow - 1; // → Number of data rows
// Adjust width so only needed columns are read (recommended for production)
const width = BOOK_USAGE_CONFIG.COL_EQUIP_TYPE; // → Number of columns to read
const range = sheet.getRange(2, 1, height, width); // → From row 2 to last row
const values = range.getValues(); // → Read in one call
const ymdTarget = APPT_ymd_(targetDate); // → Target date string
const usage = {}; // → Result object
const badRows = []; // → Rows that could not be counted
values.forEach(function(row, i) { // → Loop through each row
const rowNumber = i + 2; // → Actual sheet row number
const rowDate = row[BOOK_USAGE_CONFIG.COL_DATE - 1]; // → Date cell
const rowTime = row[BOOK_USAGE_CONFIG.COL_TIME - 1]; // → Time cell
const equipType = row[BOOK_USAGE_CONFIG.COL_EQUIP_TYPE - 1]; // → Equipment cell
if (!rowDate && !rowTime && !equipType) { // → All three cells empty
return; // → Only truly blank rows are skipped
} // →
// From here we do **not** skip quietly. A row we fail to count drops out of usage,
// which reads as "seats still available" and lets an over-capacity booking through.
if (!(rowDate instanceof Date) || !(rowTime instanceof Date)) { // → Stored as text
badRows.push(rowNumber); // → Record it
return; // → Cannot count this row
} // →
const upperType = String(equipType).toUpperCase(); // → Uppercase equipment type
if (!BOOK_CAPACITY_CONFIG.VALID_TYPES.includes(upperType)) { // → Not an allowed type
badRows.push(rowNumber); // → Also an uncounted row
return; // →
} // →
const ymd = APPT_ymd_(rowDate); // → Row date string
if (ymd !== ymdTarget) { // → Different date?
return; // → Skip (normal)
} // →
const hm = APPT_hm_(rowTime); // → HH:MM string
const key = hm + '|' + upperType; // → Time+equipment key
if (!usage[key]) { // → First time seen?
usage[key] = 0; // → Initialize to 0
} // →
usage[key] += 1; // → Add one booking
}); // →
if (badRows.length > 0) { // → Any row we could not count
throw new Error( // → The number cannot be trusted
'Some booking rows could not be counted (rows ' + badRows.slice(0, 10).join(', ') +
'). Dates/times are stored as text, or the equipment type is not in the allowed ' +
'list. Leaving them out reads as "seats available" and lets over-capacity ' +
'bookings through. Clean them up with APPT_normalizeDateFormats() from Part 5.'
); // →
} // →
return usage; // → Time/equipment usage
} // →
/**
* Test function for BOOK_getTimeUsageForDateCore_
*/
function BOOK_testGetTimeUsageForDateCore_() { // → Test helper
const today = new Date(); // → Today
const usage = BOOK_getTimeUsageForDateCore_(today); // → Calculate usage
Logger.log(JSON.stringify(usage)); // → Log result
} // →How to verify
Enter several bookings in APPT_MAIN with today’s date using multiple time/equipment combinations, then run BOOK_testGetTimeUsageForDateCore_. In the log you should see something like {"08:00|CONTAINER":2,"09:00|TRAILER":1}. Confirm it counts correctly up to the last data row and that equipment types not in the allowed list are excluded from the result.
Step 3 — Calculate remaining capacity for a slot and prevent overbooking
Now that we can calculate usage per time and equipment, we still need a way to answer “how many spots are left in this specific slot” when taking a booking. Showing remaining booking capacity in Google Sheets boils down to capacity - currentUsage. There are two practical details to watch:
- Remaining capacity should not go negative
In practice, you might have some temporarily over-filled slots, but showing negative remaining capacity confuses both planners and customers. For display purposes, it’s safer to clamp the value with Math.max(capacity − usage, 0).
- Prevent overbooking when multiple users save at once
Several planners might try to grab the last spot in a time slot at the same time. Just calculating remaining capacity is not enough to prevent overbooking, because another booking could slip in between the time you calculated remaining capacity and the time you save the new row. To handle this, the save logic should use LockService to
lock → recalculate latest remaining capacity → save or reject → unlock.
(A conceptual Lock example is shown in Step 4.)
The function below is the core that calculates usage, capacity, and remaining spots for a given date/time/equipment combination. Use this result in your web app, sidebar, or a separate sheet to make time-slot booking status clear.
Step 3 code — Slot-level remaining capacity calculation
- Where to paste: Same project,
Code.gs, directly under Step 2 - After pasting: Save and run
BOOK_testGetSeqUsageForSlotCore_().
// ===== Step 3: Calculate remaining capacity for an individual slot (time+equipment) =====
/**
* Calculate usage, capacity, and remaining spots for one slot (date+time+equipment)
* @param {Date} slotDate Booking date (Date, date part only)
* @param {Date} slotTime Booking time (Date, using hour and minute)
* @param {string} equipType Equipment type
* @return {Object} {time, equipType, used, capacity, remaining}
*/
function BOOK_getSeqUsageForSlotCore_(slotDate, slotTime, equipType) { // → Slot usage and remaining capacity
if (!(slotDate instanceof Date) || !(slotTime instanceof Date)) { // → Check date/time types
throw new Error('Date and time must be of type Date'); // → Reject invalid input
} // →
const upperType = String(equipType).toUpperCase(); // → Uppercase equipment type
if (!BOOK_CAPACITY_CONFIG.VALID_TYPES.includes(upperType)) { // → Validate allowed equipment
throw new Error('Disallowed equipment type: ' + equipType); // → Error notification
} // →
const usageByKey = BOOK_getTimeUsageForDateCore_(slotDate); // → Usage for the date
const hm = APPT_hm_(slotTime); // → HH:MM string
const key = hm + '|' + upperType; // → Time+equipment key
const used = usageByKey[key] || 0; // → Existing usage
const capacity = BOOK_getCapacityForType_(upperType); // → Lookup capacity
const remaining = Math.max(capacity - used, 0); // → Remaining spots (no negatives)
return { // → Return result object
time: hm, // → Time string
equipType: upperType, // → Equipment type
used: used, // → Used count
capacity: capacity, // → Capacity
remaining: remaining // → Remaining spots
}; // →
} // →
/**
* Test function for BOOK_getSeqUsageForSlotCore_
*/
function BOOK_testGetSeqUsageForSlotCore_() { // → Test helper
const today = new Date(); // → Today
const time = new Date( // → 08:00 time object
today.getFullYear(), // →
today.getMonth(), // →
today.getDate(), // →
8, 0, 0 // →
); // →
const info = BOOK_getSeqUsageForSlotCore_(today, time, 'CONTAINER'); // → 08:00 container info
Logger.log(JSON.stringify(info)); // → Log result
} // →How to verify
Enter several bookings in APPT_MAIN for today at 08:00 with equipment CONTAINER, then run BOOK_testGetSeqUsageForSlotCore_. In the log, used should match the actual number of bookings, and remaining should be capacity − used and never go below 0. When you fill the slot up to capacity and run the test again, remaining should show 0.
Step 4 — Pattern for using Lock when saving bookings (concept example)
Steps 1–3 focused on “calculation.” In a real system, you’ll have a function that saves new bookings, and within that function you must “do a final remaining-capacity check” to prevent overbooking. Here we don’t implement the full save logic; we only provide a sample skeleton that shows the core structure. Adjust sheet names and column layout to your own project.
Step 4 code — Example save pattern with LockService
- Where to paste: Same
Code.gs, under Step 3 - Note: Function names, sheet names, and column layout here are examples. Integrate this pattern into your real booking-save function.
// ===== Step 4: Lock pattern for preventing overbooking (example) =====
/**
* Example: single booking save logic (capacity check + Lock)
* Modify sheet name, column layout, etc., to fit your actual environment.
* @param {Object} payload {date, time, equipType, shipper, memo ...}
* @return {Object} {ok: boolean, message: string}
*/
function BOOK_saveSingleBooking_(payload) {
// 1) Basic input validation (simple example)
if (!payload || !payload.date || !payload.time || !payload.equipType) {
return { ok: false, message: 'Required fields are missing.' };
}
// Passing '2026-08-03' straight into new Date() parses as midnight UTC (a day early),
// and '09:00' becomes Invalid Date. Validate with the Part 5 helpers, then build them.
let slotDate;
let slotTime;
try {
const ymdText = APPT_ymd_(payload.date); // validates format and real date
const hmText = APPT_hm_(payload.time); // reads '09:00' safely
const dp = ymdText.split('-');
const tp = hmText.split(':');
slotDate = new Date(Number(dp[0]), Number(dp[1]) - 1, Number(dp[2]));
slotTime = new Date(1899, 11, 30, Number(tp[0]), Number(tp[1]));
} catch (e) {
return { ok: false, message: 'Invalid date/time format: ' + e.message };
}
const equipType = String(payload.equipType).toUpperCase();
// 2) Acquire script-level lock
const lock = LockService.getScriptLock();
try {
lock.waitLock(30 * 1000); // Wait up to 30 seconds (tune for production)
// 3) While holding the lock, recalculate latest remaining capacity
const slotInfo = BOOK_getSeqUsageForSlotCore_(slotDate, slotTime, equipType);
if (slotInfo.remaining <= 0) {
return {
ok: false,
message: Utilities.formatString(
'[Capacity exceeded] %s %s %s is already fully booked.',
APPT_ymd_(slotDate),
slotInfo.time,
equipType
)
};
}
// 4) If at least one spot remains, actually save the booking
const sheet = SpreadsheetApp
.getActive()
.getSheetByName(BOOK_USAGE_CONFIG.APPT_SHEET_NAME);
if (!sheet) {
return { ok: false, message: 'Booking sheet not found.' };
}
// Example: append a new booking row at the end (adjust columns per project)
const lastRow = sheet.getLastRow();
const newRow = lastRow + 1;
// Assume: A: date, B: time, C: equipment, D: shipper, E: memo
// Writing column by column leaves a **half-filled row** if anything throws midway.
// Build the row as an array and write it with one setValues call.
const rowValues = [
slotDate, // A: date
slotTime, // B: time
equipType, // C: equipment
payload.shipper || '', // D: shipper
payload.memo || '' // E: memo
];
sheet.getRange(newRow, 1, 1, rowValues.length).setValues([rowValues]);
sheet.getRange(newRow, BOOK_USAGE_CONFIG.COL_DATE).setNumberFormat('yyyy-MM-dd');
sheet.getRange(newRow, BOOK_USAGE_CONFIG.COL_TIME).setNumberFormat('HH:mm');
return {
ok: true,
message: Utilities.formatString(
'%s %s %s booking saved. (remaining: %d → %d)',
APPT_ymd_(slotDate),
slotInfo.time,
equipType,
slotInfo.remaining,
slotInfo.remaining - 1
)
};
} catch (e) {
// Log for developer/operator investigation
console.error('BOOK_saveSingleBooking_ error:', e);
return { ok: false, message: 'An error occurred while saving the booking. Please try again in a moment.' };
} finally {
// 5) Release the lock
try {
lock.releaseLock();
} catch (e) {
// Failure to release is usually non-critical; log quietly
console.warn('Failed to release lock:', e);
}
}
}This code is only an example. In a production system you’ll also need to align:
- The exact column layout of your booking sheet (which column stores what)
- Duplicate booking prevention (e.g., block a second booking for the same shipper/PO in the same time slot)
- A public wrapper function that your web app/sidebar can call
But it should be enough to understand how to wire LockService in and how to use the result of BOOK_getSeqUsageForSlotCore_().
Step 5 — Practical tips: design locks, performance, and error handling together
Just pasting in Google Sheets booking capacity code won’t automatically make it production-ready. Based on actual warehouse/hub deployments, these operational tips help:
- Always apply LockService in save functions
As shown in the example BOOK_saveSingleBooking_(), your production save code should generally follow this pattern:
1) Acquire lock with const lock = LockService.getScriptLock(); lock.waitLock(30000);
2) While the lock is held, call BOOK_getSeqUsageForSlotCore_() for a fresh remaining-capacity check
3) If remaining ≥ 1, append the booking; if 0, return a “fully booked” message to the user
4) In finally, call lock.releaseLock(); to release the lock
This pattern ensures that even if several planners try to grab the last spot in the same slot, the capacity does not get violated.
- Restrict
getValues()to only the columns you really need
In the example we fixed the width for clarity, but in production you should read only necessary columns for best performance. Once your booking sheet structure is stable, double-check BOOK_USAGE_CONFIG.COL_DATE, COL_TIME, and COL_EQUIP_TYPE to minimize unnecessary reads. The bigger your dataset (thousands or tens of thousands of rows), the more this optimization matters.
- Separate developer logs from user-facing messages
Messages inside throw new Error('Disallowed equipment type…') are for developers and ops to diagnose issues quickly. For actual users, it’s better to show more user-friendly text like “Please check the equipment selection again.”
- Apps Script / Stackdriver logs: detailed technical messages for debugging
- Web app / sidebar UI: short, clear guidance that non-technical users can understand
- Keep test functions even after going live
The three test helpers introduced here
BOOK_testGetCapacityForType_BOOK_testGetTimeUsageForDateCore_BOOK_testGetSeqUsageForSlotCore_
are worth keeping even in production. When someone reports a problem, you can quickly run them to see current capacity, usage, and remaining values for today. Because they use the BOOK_ prefix, they won’t conflict with other modules in the same project.
Closing
By implementing Google Sheets booking capacity with Apps Script, everyone on the planning team sees the same answer to “how many trucks can we accept in this time slot” and “how many spots are left right now.” In this post we walked through:
- A function that reads per-equipment capacity from the SETTINGS sheet
- A function that uses a single
getValues()to compute per-time-slot usage for a date - A function that combines those to get remaining capacity for a specific slot
- An example
LockServicepattern to prevent overbooking at save time
A practical next step is to open your existing booking sheet, confirm which columns hold date, time, and equipment type, then adjust the sheet name and column indices in the code above to match and paste it in. Once you run the three test functions and the capacity/usage/remaining numbers line up with your actual bookings, you can connect these calculations to your web app or Sheet UI to give planners a clear “remaining spots per time slot” view on the floor.
