← All posts · 2026-08-14 · Sophia Excel & Automation

Google Sheets booking window: auto limit from today

Intro: when you want to block past dates and far‑future bookings

When you run an inbound booking system on Google Sheets, you quickly run into a problem: people make bookings into already-closed time slots or way too far in the future. Especially if carriers or partners are using a web app booking screen themselves, you’ll repeatedly see cases where they keep choosing time slots on the same day even after today’s cutoff time has passed, or they reserve dates months ahead. These bookings have to be adjusted on-site, and the coordinator ends up spending time calling around to reconfirm schedules.

This post walks through how to automate Google Sheets booking window calculation with Apps Script. The goal is to open bookings only up to N days from today, and to make sure once today’s booking cutoff time has passed, today is automatically excluded from the available-date list. If you already have a Google Sheets inbound booking system running, adding just this one piece of logic will greatly reduce both “bookings after today’s cutoff” and “bookings too far in the future.”

Why you should calculate the booking window in code

When you manage available dates manually, several issues appear. Typically you start by listing dates down a sheet and coloring or marking “X” on days you don’t accept bookings. After cutoff, the coordinator hides or deletes today’s date from the list. It sounds simple, but with shift changes, handovers, and night work in the mix, this often doesn’t happen on time.

When bookings are made through a web app or a Google Sheets sidebar, there’s another problem. The date list displayed on screen is often treated as a “static list” built once, and hard to refresh daily. Since coordinators can’t readily touch the screen code, you end up with days where the on-screen availability and actual operating rules don’t match.

To cut these issues down, it’s efficient to create a single common function that calculates the booking window. That function should:

• Use the current date and time as reference
• Read “booking window (N days)” from the SETTINGS sheet
• Check today’s booking cutoff time
• Return the result as a date range (start–end) and a client-facing list

Once you have this, any UI — web app, sidebar, or sheet dropdown — can call the same function. The coordinator only has to manage the number of days and cutoff time on the SETTINGS sheet, and the repetitive work of manually editing the date list goes away. The code isn’t complicated, and because it automatically drops today from the list after cutoff, even if someone forgets to update it, it’s a practical pattern for real operations.

Core of the window logic: today, cutoff time, and N days

The Google Sheets booking window logic we’ll implement here can be summarized with three rules:

First, the reference is always today. The script runs on the server side, and anything before today is treated as unavailable. Even if you sometimes need to reopen past dates, it’s safer to exclude them by default in this logic, and handle exceptions separately.

Second, today’s booking cutoff determines whether today is included. In the previous post we assumed you already stored a “booking cutoff time (e.g., 16:00)” in the SETTINGS sheet and built a helper like BOOK_timeToMinutes_() for time comparisons.

• If the current time is earlier than the cutoff → today is bookable
• If the current time is past the cutoff → today is removed from the list

Third, you manage the maximum booking window (N days) via settings. For example, if BOOKING_MAX_DAYS = 7:

• Today is August 14
• If you are before cutoff → window is Aug 14–Aug 20
• If you are after cutoff → window is Aug 15–Aug 21

We’ll bundle these three rules into a single internal function that calculates the start and end dates, and then transform that into a screen-friendly array. You can plug this straight into a Google Sheets inbound booking system or Apps Script booking date range logic. In the examples below, we standardize date formats using Utilities.formatDate and use a combination of yyyy-MM-dd and MM/dd(E). Keeping backend and frontend on the same date format is important.

Step 1 — Booking window calculator: getBookingWindow_()

First, we build an internal function that calculates “from which date to which date bookings are allowed.” It returns the start and end dates (both as Date objects) plus flags like whether today is included. This is the core piece that automates “today plus N days” in Google Sheets.

Paste this code at the very bottom of the existing Code.gs file of your booking project under “Extensions → Apps Script” in Google Sheets. We assume you already have helper functions like loadCalendarConfig_() and BOOK_timeToMinutes_() from the previous post. After pasting, save the project (⌘S or Ctrl+S).

First, define constants for booking window and cutoff settings.

// Settings constants for booking window calculation → change these to match your system
const BOOK_WINDOW_CONFIG = {                               // → Booking window settings object
  SETTINGS_SHEET_NAME: 'SETTINGS',                         // → Settings sheet name
  KEY_MAX_DAYS: 'BOOKING_MAX_DAYS',                        // → Key for max booking days
  KEY_CUTOFF_TIME: 'BOOKING_CUTOFF_TIME',                  // → Key for booking cutoff time (HH:MM)
  DEFAULT_MAX_DAYS: 7                                      // → Default 7 days when not set
};                                                         
                                                          
// Internal function that calculates the booking window (start/end dates).
// It uses the loadCalendarConfig_() and BOOK_timeToMinutes_() functions from the previous post.
function getBookingWindow_() {                             // → Start booking window calculation
  const today = new Date();                                // → Get current time
  const todayYmd = Utilities.formatDate(                   // → Format today as yyyy-MM-dd
    today, Session.getScriptTimeZone(), 'yyyy-MM-dd'       // → Use script time zone
  );                                                       
                                                          
  const config = loadCalendarConfig_();                    // → Load settings from previous post
  const maxDaysSetting = config[BOOK_WINDOW_CONFIG.KEY_MAX_DAYS]; // → Read max days setting
  let maxDays = Number(maxDaysSetting);                    // → Convert to number
  if (!Number.isFinite(maxDays) || maxDays <= 0) {         // → If invalid value
    maxDays = BOOK_WINDOW_CONFIG.DEFAULT_MAX_DAYS;         // → Use default 7 days
  }                                                        
                                                          
  const cutoffStr = config[BOOK_WINDOW_CONFIG.KEY_CUTOFF_TIME];   // → Cutoff time HH:MM
  let includeToday = true;                                 // → Default is to include today
                                                          
  if (cutoffStr) {                                         // → When cutoff time is configured
    const cutoffMinutes = BOOK_timeToMinutes_(cutoffStr);  // → Convert HH:MM to minutes
    const nowMinutes = today.getHours() * 60 + today.getMinutes(); // → Current time in minutes
    if (Number.isFinite(cutoffMinutes) &&                  // → If numeric, compare
        nowMinutes > cutoffMinutes) {                      // → Passed cutoff time
      includeToday = false;                                // → Exclude today
    }                                                      
  }                                                        
                                                          
  const startDate = new Date(today);                       // → Copy start date from today
  if (!includeToday) {                                     // → If today should be excluded
    startDate.setDate(startDate.getDate() + 1);            // → Shift start to tomorrow
  }                                                        
                                                          
  const endDate = new Date(startDate);                     // → Copy end date from start
  endDate.setDate(endDate.getDate() + (maxDays - 1));      // → Last date in N-day window
                                                          
  return {                                                 // → Return window info
    startDate,                                             // → Start date
    endDate,                                               // → End date
    includeToday,                                          // → Whether today is included
    todayYmd                                               // → Today as string
  };                                                       
}                                                          

To make sure this code works, add a simple test function at the bottom of the same file and run it.

// Test function to log the booking window result.
function BOOK_testGetBookingWindow_() {                    // → Test-only function
  const win = getBookingWindow_();                         // → Get window info
  Logger.log(JSON.stringify(win));                         // → Log the entire object
}                                                          

In the Apps Script editor, choose BOOK_testGetBookingWindow_ from the function dropdown, click Run, then open the execution log to confirm that startDate, endDate, includeToday, and todayYmd look as expected. Try changing BOOKING_CUTOFF_TIME on the SETTINGS sheet to times earlier and later than now; includeToday should flip between true and false. That confirms your booking window automation is behaving as intended.

Step 2 — When you only need the last available date: getBookingMaxDate_()

In practice, when building a Google Sheets inbound booking system, you often only need the last available booking date, not the entire window. For example, you can give this as maxDate to a datepicker widget in the frontend so users simply can’t select later dates. You can implement this easily by reusing the window function from Step 1.

Paste the following code directly under getBookingWindow_().

// Returns the last date of the booking window as a yyyy-MM-dd string.
function getBookingMaxDate_() {                            // → Calculate last available date
  const win = getBookingWindow_();                         // → Get window info
  const endDate = win.endDate;                             // → End date object
  const tz = Session.getScriptTimeZone();                  // → Get time zone
  return Utilities.formatDate(endDate, tz, 'yyyy-MM-dd');  // → Return as yyyy-MM-dd
}                                                          
                                                          
// Test helper: log only the last available date.
function BOOK_testGetBookingMaxDate_() {                   // → Test-only function
  const maxDate = getBookingMaxDate_();                    // → Get last available date
  Logger.log('Max booking date: ' + maxDate);              // → Log result
}                                                          

Run BOOK_testGetBookingMaxDate_ from the editor. Check that the log shows something like Max booking date: 2026-08-21. Change BOOKING_MAX_DAYS on the SETTINGS sheet from 7 to, say, 5 or 10 and run again — the date should shift accordingly. If it does, your “today plus N days” logic is correctly wired up.

Step 3 — A screen-friendly date list: getBookingWindowForClient()

Now we’ll convert the booking window into a format that UI code can consume directly. When a web app, sidebar, or any other client function calls this, it should receive data that can immediately populate a dropdown or a list of date buttons. You can also reuse it as a Google Sheets date list generator script.

We’ll use this return shape:

• ymd: storage/transport date string (e.g., 2026-08-14)
• label: display string (e.g., 08/14(Thu))
• isToday: whether this entry represents today (true/false)

In a web app, you would typically show label in the dropdown and store ymd as the actual value. isToday is handy for tagging today or picking a default selection.

Paste this directly under the two functions you just added.

// Returns a booking date list in a format suitable for clients (web app, sidebar, etc.).
function getBookingWindowForClient() {                     // → Screen-facing list builder
  const win = getBookingWindow_();                         // → Get window info
  const tz = Session.getScriptTimeZone();                  // → Time zone
  const result = [];                                       // → Result array
                                                          
  const cur = new Date(win.startDate);                     // → Copy start date
  const end = new Date(win.endDate);                       // → Copy end date
                                                          
  while (cur.getTime() <= end.getTime()) {                 // → Loop from start to end
    const ymd = Utilities.formatDate(                      // → yyyy-MM-dd format
      cur, tz, 'yyyy-MM-dd'                               
    );                                                     
    const mmdd = Utilities.formatDate(                     // → MM/dd format
      cur, tz, 'MM/dd'                                     
    );                                                     
    const dow = Utilities.formatDate(                      // → Day-of-week abbreviation
      cur, tz, 'E'                                         
    );                                                     
                                                          
    result.push({                                          // → Add one date item
      ymd: ymd,                                            // → Internal date
      label: mmdd + '(' + dow + ')',                       // → Display label
      isToday: (ymd === win.todayYmd)                      // → Flag if today
    });                                                    
                                                          
    cur.setDate(cur.getDate() + 1);                        // → Move to next date
  }                                                        
                                                          
  return result;                                           // → Return full list
}                                                          
                                                          
// Test helper: log the booking date list.
function BOOK_testGetBookingWindowForClient() {            // → Test-only function
  const list = getBookingWindowForClient();                // → Get list
  Logger.log(JSON.stringify(list));                        // → Log everything
}                                                          

Run BOOK_testGetBookingWindowForClient in the editor. The execution log should show an array similar to:

[
  {"ymd":"2026-08-14","label":"08/14(Thu)","isToday":true},
  {"ymd":"2026-08-15","label":"08/15(Fri)","isToday":false},
  ...
]

Before cutoff, the first element should be today; after cutoff, today should disappear and the list should start from tomorrow. Once this looks correct, your web app can call google.script.run.getBookingWindowForClient() to get this array and automatically populate the date selector area.

Practical tips: settings, testing, and extensions

When you move this logic into a live environment, keep a few extra points in mind.

First, align configuration values with what you tell the field teams. If you tell everyone “bookings are accepted up to 5 days in advance,” but BOOKING_MAX_DAYS is set to 7, users will see 7 days on screen. Always check that your announcement to booking teams, warehouse staff, and partners matches the values on the SETTINGS sheet to avoid confusion.

Second, be clear about what the cutoff time actually means. In this post, BOOKING_CUTOFF_TIME is an “input cutoff time” for creating or changing bookings. It’s often different from the last inbound arrival time. Adding a notes column in the settings sheet with something like “Booking input cutoff (e.g., 16:00)” makes handovers and onboarding much smoother.

Third, test by simulating different times. You don’t have to wait for the real cutoff to see how it behaves:

• Set BOOKING_CUTOFF_TIME to one hour before the current time and run BOOK_testGetBookingWindow_() to confirm includeToday becomes false.
• Then set it to a later time and confirm it flips back to true.

After running through this, you can be confident that “today is automatically excluded after cutoff” is correctly implemented.

Fourth, unify the date/time concept between frontend and backend. Here we use yyyy-MM-dd for storage and MM/dd(E) for display. On different devices and locales, dates may render differently, so in frontend code it’s safer to store and transmit the ymd string as-is and treat display formatting as a separate concern. For more on aligning date formats, you may also find this related post helpful: How to standardize date formats in Google Sheets: inbound booking basics 5.

Finally, plan a separate structure for non-bookable days (holidays, inventory counts, etc.). The functions we built only apply a “today plus N days” filter. In actual operations, you’ll usually add a “closed days list” and have getBookingWindowForClient() skip those dates. Keeping the window logic as-is and layering rules like holidays or excluded weekdays on top later makes the system easier to maintain long term.

Wrap-up

One of the most common mistakes in booking systems is leaving both closed days and far‑future dates open. By combining Google Sheets with Apps Script, you can automatically calculate a booking window of N days from today and hide today after cutoff time passes, without manual edits. The three functions covered here — getBookingWindow_(), getBookingMaxDate_(), and getBookingWindowForClient() — keep the structure simple while still being robust enough for real-world use.

There’s one straightforward action you can take now: create a SETTINGS sheet in your current booking spreadsheet, add BOOKING_MAX_DAYS and BOOKING_CUTOFF_TIME, then copy the code from this post into your Apps Script project and run the tests. Once you see which dates are open from today and confirm that today drops out automatically after cutoff, the rest of your booking UI automation becomes much easier to build.