Smart Life US
Google Sheets booking hours blocking: build a booking system
Intro: how to block warehouse bookings outside operating hours
When you run an inbound booking system for a warehouse or logistics site using Google Sheets, you quickly hit a common issue. A coordinator accepts a booking, then later checks and realizes it’s outside operating hours or on a holiday. Then they have to call to reschedule and resend the confirmation email, which is inconvenient for both the coordinator and the driver. To reduce this, this post shows how to use Google Sheets booking hours blocking so you register weekday operating hours and holidays on sheets and automatically prevent bookings outside those windows. The key is a getDateBlockInfo(date) function that calculates the available hours and blocked periods for each date and rejects invalid bookings at the validation step before they’re created.
This is the “step 1: booking registration” based on real-world experience designing a Google Sheets inbound booking automation for an actual warehouse. If you’ve already followed the earlier post to create the SETTINGS sheet, APPT_MAIN, and common config functions, this time we’ll layer operating-hours and holiday blocking rules on top to implement calendar settings that only open bookable time slots. The functions you build here are structured so you can plug them straight into a Web App or form-based booking later.
Designing the CALENDAR_CONFIG and HOLIDAYS sheet structure
To implement operating-hours blocking with Google Apps Script, you first need a sheet structure that’s easy for humans to work with. In real warehouse/logistics environments, the easiest pattern to maintain was splitting this into two sheets: CALENDAR_CONFIG and HOLIDAYS. The idea is that you can change operating-hours policy by editing only these two sheets.
First is the CALENDAR_CONFIG sheet. Since you need to see weekday rules at a glance, create one row per weekday (MON–SUN) and set up columns like this:
DAY_OF_WEEK: weekday code (e.g.,MON,TUE,SAT,SUN). Using three letters makes it convenient in code.OPEN_TIME/CLOSE_TIME: default opening and closing times for that weekday, fixed formatHH:MM.LUNCH_START/LUNCH_END: continuous periods that must be blocked, such as lunch.BLOCKED_SLOTS: any additional blocked times for that weekday, listed like"10:00-11:00;15:00-15:30"separated by semicolons.
Second is the HOLIDAYS sheet. This is where you list date-specific exceptions. You manage all the frequent closures in actual operations—national holidays, inventory counts, maintenance, etc.—on this single sheet. The basic columns are:
DATE: entered as a Google Sheets date value. It must be a “date” data type, not text.TYPE: a code such asFULL(closed all day) orPARTIAL(partially closed). The code will branch on this later.OPEN_TIME/CLOSE_TIME: operating hours only used when TYPE is PARTIAL.NOTE: a memo like “Chuseok holiday” or “Inventory count” you can later use when answering inquiries.
Once you split it like this, a policy change such as “Saturday is no longer short hours, it’s a full closure” can be implemented by editing just one row in CALENDAR_CONFIG. When you want to pre-load next year’s holidays, you just add the dates and TYPE on the HOLIDAYS sheet, and the Apps Script code doesn’t need to change. Designing your Google Sheets booking time limits around sheet-based rules like this lets the operations team manage policies themselves and improves practical efficiency.
Step 1 code — auto-create calendar config sheets
First, create a function to automatically generate the CALENDAR_CONFIG and HOLIDAYS sheets. You only need to run it once at the beginning, but it’s very useful in practice because it reduces mistakes when recreating the structure in a new file.
Paste this code at the very bottom of the “reservation tool” Apps Script project’s Code.gs. After pasting, run the initCalendarConfig() function once from the script editor to create the sheets.
This code does the following:
1) If the CALENDAR_CONFIG sheet doesn’t exist, it creates it and fills in the header and default weekday rows.
2) If the HOLIDAYS sheet doesn’t exist, it creates it and fills in just the header.
3) It inserts sample weekday operating hours so you can simply edit them and start using the system right away.
// Change only this part to match your environment
const BOOK_CAL_CONFIG_SHEET = 'CALENDAR_CONFIG'; // → Calendar config sheet name
const BOOK_HOLIDAYS_SHEET = 'HOLIDAYS'; // → Holidays sheet name
function initCalendarConfig() { // → Function to create calendar config sheets
const ss = SpreadsheetApp.getActive(); // → Get current spreadsheet
// Default weekday codes and example opening hours
const defaultRows = [ // → Default settings array per weekday
['MON', '08:00', '17:00', '12:00', '13:00', ''], // → Monday
['TUE', '08:00', '17:00', '12:00', '13:00', ''], // → Tuesday
['WED', '08:00', '17:00', '12:00', '13:00', ''], // → Wednesday
['THU', '08:00', '17:00', '12:00', '13:00', ''], // → Thursday
['FRI', '08:00', '17:00', '12:00', '13:00', ''], // → Friday
['SAT', '08:00', '12:00', '', '', ''], // → Saturday (short-hours example)
['SUN', '', '', '', '', 'CLOSED'] // → Sunday closure example
];
// Create CALENDAR_CONFIG sheet and set header
let calSheet = ss.getSheetByName(BOOK_CAL_CONFIG_SHEET); // → Check if sheet exists
if (!calSheet) { // → If not, create it
calSheet = ss.insertSheet(BOOK_CAL_CONFIG_SHEET); // → Create sheet
const headers = [ // → Define header row
'DAY_OF_WEEK', // → Weekday code (MON, etc.)
'OPEN_TIME', // → Opening time
'CLOSE_TIME', // → Closing time
'LUNCH_START', // → Lunch start
'LUNCH_END', // → Lunch end
'BLOCKED_SLOTS' // → Extra blocked periods
];
calSheet.getRange(1, 1, 1, headers.length).setValues([headers]); // → Input header
calSheet.getRange(2, 1, defaultRows.length, defaultRows[0].length)
.setValues(defaultRows); // → Input default weekday rows
calSheet.setFrozenRows(1); // → Freeze header row
}
// Create HOLIDAYS sheet and set header
let holidaySheet = ss.getSheetByName(BOOK_HOLIDAYS_SHEET); // → Check holidays sheet
if (!holidaySheet) { // → If not, create it
holidaySheet = ss.insertSheet(BOOK_HOLIDAYS_SHEET); // → Create sheet
const headers = [ // → Define holiday header
'DATE', // → Date
'TYPE', // → FULL / PARTIAL
'OPEN_TIME', // → Partial opening time
'CLOSE_TIME', // → Partial closing time
'NOTE' // → Remarks
];
holidaySheet.getRange(1, 1, 1, headers.length).setValues([headers]); // → Input header
holidaySheet.setFrozenRows(1); // → Freeze header row
}
}How to check it: after running initCalendarConfig from the script editor, if you see CALENDAR_CONFIG and HOLIDAYS tabs, each with headers and default weekday values filled in, it worked.
Step 2 code — read and cache calendar settings
Now create a function to read the contents of CALENDAR_CONFIG and HOLIDAYS and keep them in memory as objects. In the booking validation logic you shouldn’t read sheets on every call; instead, you want to reuse the settings loaded into memory so performance stays stable. In this post we’ll use a simple “in-memory cache” pattern that survives for the duration of the script execution.
Paste this code immediately after the previous snippet in the same Apps Script project’s Code.gs. After pasting, run BOOK_testLoadCalendarConfig_() to see the loaded structure in the execution log.
This code does the following:
1) Reads the CALENDAR_CONFIG sheet and organizes operating hours, lunch, and BLOCKED_SLOTS per weekday code.
2) Reads HOLIDAYS and stores TYPE, OPEN_TIME, CLOSE_TIME, and NOTE in an object keyed by date (yyyy-MM-dd).
3) Stores the read results in the BOOK_calendarCache variable to reuse them in the same execution.
// Cache variable to store calendar settings in memory
let BOOK_calendarCache = null; // → Reuse loaded settings
function loadCalendarConfig_() { // → Read operating-hours and holiday settings from sheets
if (BOOK_calendarCache) { // → If cache already exists
return BOOK_calendarCache; // → Return it as-is
}
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const calSheet = ss.getSheetByName(BOOK_CAL_CONFIG_SHEET); // → Calendar sheet
const holidaySheet = ss.getSheetByName(BOOK_HOLIDAYS_SHEET); // → Holidays sheet
if (!calSheet) { // → If sheet missing
throw new Error('CALENDAR_CONFIG sheet is missing. Run initCalendarConfig() first.'); // → Error message
}
if (!holidaySheet) { // → If sheet missing
throw new Error('HOLIDAYS sheet is missing. Run initCalendarConfig() first.'); // → Error message
}
// Read CALENDAR_CONFIG
const calValues = calSheet.getDataRange().getValues(); // → Read full range
const calHeaders = calValues[0]; // → First row is header
const dayConfigMap = {}; // → Object to store settings per weekday
for (let i = 1; i < calValues.length; i++) { // → Loop through data rows
const row = calValues[i]; // → Current row
const day = String(row[0]).trim(); // → DAY_OF_WEEK value
if (!day) { // → Skip if empty
continue;
}
dayConfigMap[day] = { // → Store settings per weekday
open: String(row[1] || '').trim(), // → OPEN_TIME
close: String(row[2] || '').trim(), // → CLOSE_TIME
lunchStart: String(row[3] || '').trim(), // → LUNCH_START
lunchEnd: String(row[4] || '').trim(), // → LUNCH_END
blockedSlots: String(row[5] || '').trim() // → BLOCKED_SLOTS
};
}
// Read HOLIDAYS
const holidayValues = holidaySheet.getDataRange().getValues(); // → Full range
const holidaysMap = {}; // → Object to store holiday info per date
for (let i = 1; i < holidayValues.length; i++) { // → Loop through data rows
const row = holidayValues[i]; // → Current row
const dateVal = row[0]; // → DATE value
if (!dateVal) { // → Skip if empty
continue;
}
const jsDate = new Date(dateVal); // → Convert to JS Date
if (isNaN(jsDate.getTime())) { // → If invalid date
continue; // → Skip
}
const ymd = Utilities.formatDate(jsDate, Session.getScriptTimeZone(), 'yyyy-MM-dd'); // → Standard format
holidaysMap[ymd] = { // → Store holiday settings per date
type: String(row[1] || '').trim(), // → TYPE
open: String(row[2] || '').trim(), // → OPEN_TIME
close: String(row[3] || '').trim(), // → CLOSE_TIME
note: String(row[4] || '').trim() // → NOTE
};
}
BOOK_calendarCache = { // → Store in cache
days: dayConfigMap, // → Default weekday settings
holidays: holidaysMap // → Date-specific holiday settings
};
return BOOK_calendarCache; // → Return cache
}
// For testing: log to verify settings are read correctly
function BOOK_testLoadCalendarConfig_() { // → Test function
const cfg = loadCalendarConfig_(); // → Read settings
Logger.log(JSON.stringify(cfg, null, 2)); // → Log contents
}How to check it: run BOOK_testLoadCalendarConfig_ in the editor, then open “Execution log.” If you see a JSON structure with days and holidays, with weekday OPEN_TIME values and the dates/TYPE values you entered on HOLIDAYS, it’s working.
Step 3 code — time string comparison utilities
To use weekday operating hours and holidays, you need accurate comparison of time strings like '09:00'. Naive string comparison won’t correctly handle cases like "10:00" vs "9:30", so it’s safest to keep helper functions that convert times into “minutes as a number” and compare those.
Paste this code next in Code.gs. Since this step doesn’t touch booking storage, we won’t use LockService; instead, we validate inputs and throw errors immediately when a bad format is passed.
// Convert 'HH:MM' string to a number of minutes
function BOOK_timeToMinutes_(timeStr) { // → Convert time string to a number
const t = String(timeStr || '').trim(); // → Normalize string
if (!t) { // → If empty
return null; // → Return null
}
const parts = t.split(':'); // → Split into hour and minute
if (parts.length !== 2) { // → Wrong format
return null; // → Return null
}
const h = Number(parts[0]); // → Hour as number
const m = Number(parts[1]); // → Minute as number
if (!Number.isFinite(h) || !Number.isFinite(m)) { // → Validate numbers
return null; // → Invalid values
}
return h * 60 + m; // → Total minutes
}
// Compare two time strings: negative if a < b, 0 if equal, positive if a > b
function compareTime_(a, b) { // → Compare times
const ma = BOOK_timeToMinutes_(a); // → Convert a
const mb = BOOK_timeToMinutes_(b); // → Convert b
if (ma === null || mb === null) { // → Conversion failed
throw new Error('Invalid time format: ' + a + ', ' + b); // → Error
}
return ma - mb; // → Return difference
}
// Check if a time is within a range
function isTimeInRange_(time, start, end) { // → Check for inclusion in range
const mt = BOOK_timeToMinutes_(time); // → Target time
const ms = BOOK_timeToMinutes_(start); // → Start time
const me = BOOK_timeToMinutes_(end); // → End time
if (mt === null || ms === null || me === null) { // → Conversion failed
throw new Error('Invalid time format: ' + time + ', ' + start + ', ' + end); // → Error
}
return mt >= ms && mt < me; // → True if >= start and < end
}The test function below includes expected-value checks so you can validate behavior automatically.
function BOOK_testCompareTime_() { // → Time comparison test
const test1 = compareTime_('09:00', '10:00'); // → Expect negative
if (test1 >= 0) {
throw new Error('FAIL: expected compareTime to be negative, got ' + test1);
}
const test2 = isTimeInRange_('09:30', '09:00', '10:00'); // → Expect true
if (test2 !== true) {
throw new Error('FAIL: expected isTimeInRange to be true, got ' + test2);
}
Logger.log('✓ Tests passed'); // → Worked as expected
}If BOOK_testCompareTime_ runs without errors and the log shows ✓ Tests passed, the utilities are working as intended.
Step 4 code — function to return per-date operating hours and holiday info
Now for the core function of this post: getDateBlockInfo(date). This function gives Web Apps or booking creation logic the information they need to decide whether a requested date/time is bookable. Architecturally, it’s designed to return the operating window along with a list of blocked periods.
This function proceeds in the following order:
1) Converts the Date argument to a yyyy-MM-dd string in the script’s time zone.
2) Calls loadCalendarConfig_() to get weekday base settings (dayConfigMap) and HOLIDAYS info (holidaysMap).
3) First looks up the date in HOLIDAYS. If TYPE is FULL, it treats that as a full-day closure.
- It returns
{open: null, close: null, reason: NOTE}, plus ablocksarray that contains a 00:00–24:00 full block with the closure reason.
4) If it’s not a FULL holiday, it looks up the weekday code (SUN–SAT) in CALENDAR_CONFIG. If OPEN_TIME/CLOSE_TIME are empty, it treats that weekday as closed and again returns a 00:00–24:00 full block.
5) It adds lunch and BLOCKED_SLOTS as “blocked period” entries in the array.
6) If the HOLIDAYS TYPE is PARTIAL, it overrides open and close with the holiday’s values.
Add this code next in the same Code.gs.
// Returns operating-hours and blocked periods for a given date
function getDateBlockInfo(date) { // → Look up bookable info per date
if (!(date instanceof Date)) { // → Validate Date type
throw new Error('getDateBlockInfo expects a Date object.'); // → Invalid argument
}
const tz = Session.getScriptTimeZone(); // → Script time zone
const ymd = Utilities.formatDate(date, tz, 'yyyy-MM-dd'); // → Standard date string
const cfg = loadCalendarConfig_(); // → Load cached settings
const dayConfigMap = cfg.days; // → Weekday settings map
const holidaysMap = cfg.holidays; // → Holiday settings map
// 1. Check for a holiday
const holiday = holidaysMap[ymd]; // → Holiday for this date
if (holiday && holiday.type === 'FULL') { // → Full-day closure
return { // → Return info
date: ymd, // → Date
isHoliday: true, // → Holiday flag
holidayType: 'FULL', // → Holiday type
open: null, // → No operating hours
close: null, // → No operating hours
blocks: [{ // → Full-day block
start: '00:00', // → Start
end: '24:00', // → End
reason: holiday.note || 'Closed' // → Reason (from NOTE)
}]
};
}
// 2. Get weekday base settings
const dayIndex = date.getDay(); // → 0 (Sun) to 6 (Sat)
const dayCodes = ['SUN', 'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT']; // → Weekday codes
const dayCode = dayCodes[dayIndex]; // → Code for current weekday
const base = dayConfigMap[dayCode]; // → Base settings for this weekday
// Treat as closed if weekday has no operating hours
if (!base || !base.open || !base.close) { // → If operating hours are empty
return { // → Treat as closed
date: ymd,
isHoliday: true, // → Effectively a closure
holidayType: 'CLOSED', // → Weekday closure
open: null,
close: null,
blocks: [{
start: '00:00',
end: '24:00',
reason: 'Closed on this weekday'
}]
};
}
let open = base.open; // → Base opening time
let close = base.close; // → Base closing time
const blocks = []; // → Blocked periods array
// 3. Add lunch as a blocked period
if (base.lunchStart && base.lunchEnd) { // → If lunch period exists
blocks.push({ // → Add to block list
start: base.lunchStart, // → Lunch start
end: base.lunchEnd, // → Lunch end
reason: 'Lunch' // → Reason
});
}
// 4. Parse BLOCKED_SLOTS (e.g., "10:00-11:00;15:00-15:30")
if (base.blockedSlots) { // → If extra blocked periods exist
const parts = base.blockedSlots.split(';'); // → Split on semicolons
parts.forEach(part => { // → Process each period
const p = part.trim(); // → Trim whitespace
if (!p) return; // → Skip empty pieces
const range = p.split('-'); // → Split start-end
if (range.length !== 2) return; // → Skip if malformed
blocks.push({ // → Add blocked period
start: range[0].trim(), // → Start
end: range[1].trim(), // → End
reason: 'Extra block' // → Reason
});
});
}
// 5. If partial holiday, override operating hours
if (holiday && holiday.type === 'PARTIAL') { // → If partial holiday defined
if (holiday.open && holiday.close) { // → If times provided
open = holiday.open; // → Override opening time
close = holiday.close; // → Override closing time
}
}
return { // → Final info
date: ymd, // → Date
isHoliday: Boolean(holiday), // → Holiday flag
holidayType: holiday ? holiday.type : null, // → Holiday type
open: open, // → Opening time
close: close, // → Closing time
blocks: blocks // → Blocked-period list
};
}
// Test: check operating info for today
function BOOK_testGetDateBlockInfo() { // → Test function
const today = new Date(); // → Today
const info = getDateBlockInfo(today); // → Lookup info
Logger.log(JSON.stringify(info, null, 2)); // → Log output
}You can verify this as follows:
1) On the HOLIDAYS sheet, enter today’s date in the DATE column and set TYPE to FULL.
2) Run BOOK_testGetDateBlockInfo from the script editor.
3) In the execution log, if you see isHoliday: true, holidayType: "FULL", open: null, close: null, and a blocks entry for 00:00–24:00 with the reason taken from NOTE, your conditions for blocking bookings outside operating hours are being calculated correctly.
4) Delete that HOLIDAYS row and test again; you should see the CALENDAR_CONFIG weekday operating hours plus lunch and BLOCKED_SLOTS reflected.
5) Set TYPE to PARTIAL in HOLIDAYS and put different times in OPEN_TIME / CLOSE_TIME. Run the test again and confirm that the returned open / close values have been overridden by the PARTIAL settings.
Once this works, your Web App or booking save function can call getDateBlockInfo() and only save a booking when the requested time is within open~close and doesn’t fall into any of the blocks. That way, your Apps Script operating-hours configuration is applied consistently across the entire script.
Practical tips from running operating-hours and holiday blocking
Running a real inbound booking automation for a warehouse revealed some common patterns. When you set up Google Apps Script holiday blocking, consider the following:
- Only put “rarely changing rules” into BLOCKED_SLOTS
Besides lunch, use CALENDAR_CONFIG’s BLOCKED_SLOTS only for mostly fixed blocked periods such as peak loading/unloading windows that are common per weekday. Exceptions specific to a particular customer or carrier tend to change often with sales policy; if you mix those into BLOCKED_SLOTS, you’ll later have trouble figuring out “why is this time blocked again?” It’s better to handle customer-specific exceptions as separate conditions in the booking validation logic.
- Use only organization-approved codes for HOLIDAYS TYPE
The code assumes only FULL and PARTIAL for branching. It’s best to establish a rule in your organization that only these two codes are used. Once people begin entering things like full, half, or partial_am, the definitions in sheets and code drift apart and some dates may unintentionally follow “normal weekday rules” and stay open for booking.
- Enforce date/time formats using data validation
In Google Sheets, values often “look” like dates but are actually text, especially in environments with lots of CSV imports and pasting. For the HOLIDAYS DATE column, add data validation to allow “dates only,” and for OPEN_TIME/CLOSE_TIME, validation that only allows an HH:MM pattern. That alone greatly reduces runtime errors.
The code above skips invalid DATE values, but from the operations team’s viewpoint that can become “we added a holiday but it doesn’t actually work,” so it’s worth adding a simple routine to periodically check warning marks on the sheet.
- Keep operating-hours/holiday logic separate from booking logic
The loadCalendarConfig_, getDateBlockInfo, and time comparison utils created here focus solely on “calendar rules.” In the actual booking save step, you’ll also add LockService to prevent concurrency issues, plus logic for overlapping bookings, per-customer slot limits, and so on. If you modularize the calendar-related decisions in their own functions, any major change in next year’s holiday policy requires updating only this module, which makes maintenance easier.
Closing
This post covered the fundamental base for a Google Sheets booking system: operating-hours and holiday blocking. You created CALENDAR_CONFIG and HOLIDAYS sheets to manage weekday operating hours and date-specific closures, and used Apps Script functions loadCalendarConfig_() and getDateBlockInfo() to load that information and compute per-date operating windows and blocked periods. You also implemented utilities like compareTime_ and isTimeInRange_ and test functions so you can automatically verify behavior as you go.
Once this structure is in place, policy changes can be handled by editing sheets instead of code, so on-site teams can adjust operations themselves, which is a major advantage.
The one action to take now is this: open the Apps Script editor, run initCalendarConfig() to create the CALENDAR_CONFIG and HOLIDAYS sheets, and enter your actual warehouse operating hours and holiday schedule. Then run BOOK_testGetDateBlockInfo() and BOOK_testCompareTime_() to see how today’s operating hours, blocked periods, and time comparison utilities behave. Once you’re comfortable with that, you’re ready for the next step: implementing logic to automatically reject booking requests outside operating hours or on holidays.
