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

Google Sheets booking status automation: create TODAY view — Dashboard part 1

Intro: you want to see only today’s bookings, but you keep redoing filters

When you manage inbound dock appointments in Google Sheets, there are constant moments when you want to pull out only the trucks coming in today. Every time you do that, you end up re-applying the date filter, changing the sort order to start time, and hiding yesterday’s and tomorrow’s bookings. Repeating this eats up more time than you’d think. It’s the screen you look at most often on the floor, yet it takes far too much manual work.

This post is a focused extract of the Google Sheets Apps Script booking status automation method I actually run at a physical warehouse. Here we only cover the first step: “Automatically generating the Today booking status sheet.” We leave the original booking sheet (APPT_MAIN) as is, and build a structure where a read-only view sheet (with a name like TODAY) containing only today’s bookings sorted by time is automatically refreshed via Apps Script. Once you set it up, you can always keep the “What’s coming in today?” screen up to date with a single click in a custom menu.

This is Part 1 of the inbound booking system dashboard series. If you’re curious about the overall structure and background, it’s worth first reading Overall structure of the inbound booking system in Google Sheets: why I built it and how the parts connect. In this part, we’ll dive into the practical implementation of the “Today booking status” sheet.

Why you need a dedicated “Today bookings” view sheet

Once you’ve used a booking sheet for a bit, you quickly realize that the screen for entering bookings and the screen operations need are different. The APPT_MAIN sheet for data entry has many columns: date, start time, equipment type, door, container, carrier, customer, notes, booking ID, and so on. That’s great for recording and searching. But operators mainly want to see “today,” “right now,” and “which door” at a glance.

The questions operators frequently ask look like this:

• How many containers are scheduled today?
• At what time slots is the volume concentrated?
• Which doors, which carriers are coming in?

If you keep changing filters and sort orders directly on APPT_MAIN, multiple users end up clashing: sort order gets messed up, or someone accidentally hides a column that should stay visible. To avoid this, it’s much more stable to treat the main sheet as a database and create an automatically generated, dedicated “today status” sheet that only shows today’s data.

After I switched to this structure, I left the “today” view sheet pinned on the floor monitors at all times, and limited booking creation and edits to APPT_MAIN only. Edits happen in one place; viewing and monitoring are done through multiple view sheets and dashboards. This made operations, training, and permissions much easier.

The two key functions we’ll implement in this post are:

• DASH_getApptDailyViewSheet_(): prepares today’s view sheet and sets the info message, headers, and formatting.
• DASH_updateApptDailyView(): reads only today’s bookings from APPT_MAIN, sorts them by start time, and writes to the view sheet in one batch with setValues.

With just these two in place, you can also reuse the same data later when you attach a web dashboard, for example in something like How to password-protect a Google Sheets web app: Dashboard part 5.

Designing the Today bookings view sheet: minimal info, read-only

First, decide what information you want to show in the Today booking status sheet. As an example, let’s assume the APPT_MAIN columns are structured as follows:

• A Date
• B Start time
• C Equipment type
• D Door
• E Container
• F Carrier
• G Customer
• H Notes
• I End time
• J Created at
• K Quantity
• L Pallets
• M Booking ID

From this, for the Today view sheet, I recommend pulling only the fields that are frequently needed in operations, for example:

• Date
• Start time
• End time
• Door
• Container
• Carrier
• Customer
• Quantity
• Pallets
• Booking ID

There are two important principles here.

First, the view sheet must be a read-only result. If you edit values directly in the TODAY sheet, your changes will be overwritten next time you refresh from APPT_MAIN. To minimize confusion, put an info message at the top in A1 like: “This sheet is an automatically generated view of today’s inbound bookings. Any manual edits will be overwritten on the next refresh based on the booking sheet (APPT_MAIN).”

Second, decide on a consistent sort key. Since the Today view sheet always focuses on a single day, the sort key is almost always column B, start time. I recommend storing start time as a Date/Time value and just formatting it as HH:mm in Google Sheets. If you store time as text, it becomes harder to catch errors later when sorting or calculating times in Apps Script.

Based on this structure, let’s implement the Google Sheets booking status automation in code.

Step 1 — Prepare the Today view sheet: DASH_getApptDailyViewSheet_()

In this step, we prepare the sheet (TODAY) that will hold today’s booking status. If the sheet doesn’t exist, we create it; if it does, we clear its contents and reset the info message, header, and formatting. This way, even if DASH_updateApptDailyView() runs multiple times, the structure stays consistent.

1) What this code does

• Looks for or creates the TODAY sheet in the current spreadsheet, and sets the info message on the first row and the header and formatting on the second row.

2) Where to paste it

• In Google Sheets: Extensions → Apps Script → paste at the bottom of the existing Code.gs.
• To avoid name collisions with code from other posts, we prefix constants and functions with DASH_.

3) What to do after pasting

• Save (Ctrl+S or ⌘S), then in the editor’s function list select DASH_getApptDailyViewSheet_ and run it once, then grant the requested permissions.

// Today booking dashboard settings → adjust this part to match your sheet structure
const DASH_VIEW_SHEET_NAME = 'TODAY';               // → Name of the Today status sheet
const DASH_TZ = 'America/New_York';                 // → Fixed time zone
const DASH_APPT_MAIN_SHEET_NAME = 'APPT_MAIN';      // → Name of the source booking sheet

function DASH_getApptDailyViewSheet_() {            // → Prepare Today view sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // → Get current spreadsheet
  let sheet = ss.getSheetByName(DASH_VIEW_SHEET_NAME); // → Find TODAY sheet

  if (!sheet) {                                     // → If it doesn't exist
    sheet = ss.insertSheet(DASH_VIEW_SHEET_NAME);   // → Create new sheet
  }

  sheet.clear();                                    // → Clear all existing contents

  // Write info message and header
  const headerValues = [                            // → Values for the first two rows
    [
      '※ This sheet automatically generates today\'s inbound booking status. ' +
      'Even if you edit it manually, it will be overwritten on the next refresh based on the booking sheet (APPT_MAIN).'
    ],
    [
      'Date', 'Start time', 'End time',
      'Door', 'Container', 'Carrier', 'Customer',
      'Quantity', 'Pallets', 'Booking ID'
    ]
  ];

  sheet.getRange(1, 1, headerValues.length, headerValues[1].length) // → A1~ header range
    .setValues(headerValues);                   // → Write info message and header

  sheet.getRange('A1').setFontColor('#888888'); // → Make info message gray
  sheet.getRange('A2:J2').setFontWeight('bold'); // → Make header bold

  sheet.setFrozenRows(2);                        // → Freeze top two rows

  // Set column widths and formats
  sheet.setColumnWidths(1, 3, 90);              // → Date and time column widths
  sheet.setColumnWidth(4, 80);                  // → Door column width
  sheet.setColumnWidths(5, 3, 120);             // → Container–Customer column widths
  sheet.setColumnWidths(8, 2, 70);              // → Quantity and Pallets column widths
  sheet.setColumnWidth(10, 100);                // → Booking ID column width

  sheet.getRange('A3:A').setNumberFormat('yyyy-mm-dd'); // → Date display format
  sheet.getRange('B3:C').setNumberFormat('HH:mm');      // → Time display format

  return sheet;                                 // → Return prepared sheet
}

How to check it worked: run DASH_getApptDailyViewSheet_ from the script editor, then go back to the spreadsheet. If a TODAY sheet exists with the info message in A1 and headers in A2:J2, it’s set up correctly.

Step 2 — Select only today’s bookings and render them: DASH_updateApptDailyView()

Now for the core function: we’ll read only today’s bookings and redraw the TODAY sheet. For real-world use, the important part is to avoid repeatedly copying/deleting row by row; instead, filter and sort in arrays and then write once with setValues. This makes it fast and stable, even when multiple people trigger it at the same time.

This function works as follows:

1. Reads the full data range from APPT_MAIN in one go.
2. Filters rows whose date is “today.”
3. Excludes rows without a start time.
4. Sorts by start time.
5. Overwrites TODAY sheet from row 3 downward.
6. Regardless of errors, always releases the LockService lock at the end.

1) What this code does

• Reads only today’s bookings from APPT_MAIN, sorts them by start time, and overwrites the TODAY sheet.

2) Where to paste it

• Paste directly under the DASH_getApptDailyViewSheet_() function.

3) What to do after pasting

• Save, then run DASH_updateApptDailyView from the script editor and grant the requested permissions.

function DASH_updateApptDailyView() {                // → Fully refresh Today view
  const lock = LockService.getScriptLock();          // → Script lock object
  lock.waitLock(30000);                              // → Wait up to 30 seconds

  try {                                              // → Start try block
    const ss = SpreadsheetApp.getActiveSpreadsheet();// → Spreadsheet
    const apptSheet = ss.getSheetByName(DASH_APPT_MAIN_SHEET_NAME); // → APPT_MAIN sheet

    if (!apptSheet) {                                // → If sheet not found
      throw new Error('Cannot find booking sheet (APPT_MAIN).'); // → Throw error
    }

    const lastRow = apptSheet.getLastRow();          // → Last data row
    if (lastRow < 2) {                               // → If there is no data
      const viewSheet = DASH_getApptDailyViewSheet_(); // → Initialize view sheet
      viewSheet.getRange('A3:J').clearContent();     // → Clear data area
      return;                                        // → Exit
    }

    const range = apptSheet.getRange(2, 1, lastRow - 1, 13); // → Range A2:M
    const values = range.getValues();                // → Fetch all booking data

    const today = new Date();                        // → Current time
    const todayYmd = Utilities.formatDate(today, DASH_TZ, 'yyyy-MM-dd'); // → Today ymd

    const filtered = [];                             // → Array to hold today’s bookings

    for (let i = 0; i < values.length; i++) {        // → Loop through each row
      const row = values[i];                         // → One row of data
      const dateVal = row[0];                        // → Column A: date
      const startTime = row[1];                      // → Column B: start time
      const endTime = row[8];                        // → Column I: end time
      const type = row[2];                           // → Column C: equipment type
      const door = row[3];                           // → Column D: door
      const cntr = row[4];                           // → Column E: container
      const carrier = row[5];                        // → Column F: carrier
      const client = row[6];                         // → Column G: customer
      const qty = row[10];                           // → Column K: quantity
      const pallet = row[11];                        // → Column L: pallets
      const apptId = row[12];                        // → Column M: booking ID

      if (!dateVal) {                                // → If date is empty
        continue;                                    // → Skip this row
      }

      // Safely compare dates as 'yyyy-MM-dd'
      let rowYmd;
      try {                                          // → Try date conversion
        rowYmd = APPT_ymd_(dateVal);                 // → Use helper from previous part
      } catch (e) {                                  // → If conversion fails
        continue;                                    // → Skip this row
      }

      if (rowYmd !== todayYmd) {                     // → If not today
        continue;                                    // → Skip this row
      }

      // Exclude rows without start time from Today view
      if (!startTime) {                              // → If no start time
        continue;                                    // → Skip this row
      }

      // Verify quantity/pallets are numeric; otherwise set to empty
      const safeQty = Number.isFinite(Number(qty)) ? Number(qty) : '';      // → Quantity
      const safePallet = Number.isFinite(Number(pallet)) ? Number(pallet) : ''; // → Pallets

      filtered.push([
        dateVal,       // Date
        startTime,     // Start time
        endTime,       // End time
        door,          // Door
        cntr,          // Container
        carrier,       // Carrier
        client,        // Customer
        safeQty,       // Quantity
        safePallet,    // Pallets
        apptId         // Booking ID
      ]);
    }

    // Sort by start time
    filtered.sort(function (a, b) {                  // → Sort function
      const t1 = a[1];                               // → Start time of a
      const t2 = b[1];                               // → Start time of b

      // If time values are Date objects, sort by getTime
      if (t1 instanceof Date && t2 instanceof Date) { // → If both are Date
        return t1.getTime() - t2.getTime();          // → Earlier time first
      }

      // If strings, compare as 'HH:mm'
      const s1 = String(t1);                         // → Convert to string
      const s2 = String(t2);                         // → Convert to string
      if (s1 < s2) return -1;                        // → s1 earlier
      if (s1 > s2) return 1;                         // → s2 earlier
      return 0;                                      // → Same time
    });

    const viewSheet = DASH_getApptDailyViewSheet_(); // → Prepare view sheet
    const dataRange = viewSheet.getRange(3, 1, Math.max(filtered.length, 1), 10); // → A3:J

    if (filtered.length === 0) {                     // → If no bookings for today
      dataRange.clearContent();                      // → Clear data area
      return;                                        // → Exit
    }

    dataRange.setValues(filtered);                   // → Write values in one batch
  } finally {                                        // → Regardless of error
    lock.releaseLock();                              // → Release lock
  }
}

How to check it worked: enter 1–2 test bookings with today’s date into APPT_MAIN, then run DASH_updateApptDailyView. On the TODAY sheet, rows from A3 onward should show only today’s bookings sorted by start time. Yesterday’s and tomorrow’s bookings should not appear.

This code uses APPT_ymd_(), a helper function that normalizes dates to 'YYYY-MM-DD'. If you created it in a previous part, just reuse it. If you’re only following this post, add a helper with the same behavior. Normalizing date format like this helps not just for “Today booking status” but also for later analytics and KPIs.

Step 3 — Add a menu button: onOpen and dashboard menu

On the shop floor, it’s much more convenient to click a menu button than to open the script editor and run functions manually. In this step, we’ll create a menu called “Booking tools” in the sheet’s top menu bar and add an item “[Refresh Today bookings]” under it.

To leave room for other posts in this project, we’ll structure onOpen() to call multiple helper functions. In this part we’ll just add the helper DASH_addDashboardMenu_(menu) and add more helpers from later parts when needed.

1) What this code does

• When the spreadsheet is opened, adds a “Booking tools” menu with a “[Refresh Today bookings]” item that runs DASH_updateApptDailyView() when clicked.

2) Where to paste it

• Paste below the functions from the previous step.
• If you already have an onOpen() from another post, do not paste this whole block; instead, just add one line DASH_addDashboardMenu_(menu); inside your existing onOpen.

3) What to do after pasting

• Save and refresh the spreadsheet.

function onOpen() {                                   // → Automatically runs when sheet opens
  const ui = SpreadsheetApp.getUi();                  // → UI object
  const menu = ui.createMenu('Reservation Tool');        // → Create top menu

  // If you have helper functions from other parts, call them here as well.
  // Example: addApptBaseMenu_(menu); APPT_addSettingsMenu_(menu); etc.

  DASH_addDashboardMenu_(menu);                       // → Add dashboard menu for this part

  menu.addToUi();                                     // → Attach menu to UI
}

function DASH_addDashboardMenu_(menu) {               // → Dashboard menu helper
  menu.addItem('Refresh Today bookings', 'DASH_updateApptDailyView'); // → Function to run on click
}

How to check it worked: reopen the spreadsheet. If a “Booking tools” menu appears at the top with a “Refresh Today bookings” item, it’s working. Clicking this menu item should refresh the TODAY sheet and redraw today’s bookings from row 3 downward.

Common real-world issues and how to prevent them

Handling inconsistent date/time formats

In real operations, bookings are often typed in manually or pasted from various systems, so date formats get mixed: 2026-08-21, 8/21/2026, plain text, etc. If you compare these naively, you’ll often miss some of today’s bookings. Using a helper like APPT_ymd_() to enforce a 'YYYY-MM-DD' format greatly reduces such issues. In the code above, rows that fail conversion are quietly skipped so that a single bad date doesn’t stop the entire refresh.

Staying clean when there are no bookings today

It’s perfectly possible that there are no bookings for a given day. Passing an empty array directly to setValues will throw an error. That’s why the code uses Math.max(filtered.length, 1) to ensure at least one row in the target range, and if filtered.length === 0, it just calls clearContent() and exits. The TODAY sheet keeps its structure, and only the data area is emptied, so users can naturally understand that there are “no bookings today.”

Using LockService when multiple people refresh at once

Dashboard sheets are often displayed on monitors, and several people may click the “Refresh Today bookings” button around shift change. Without LockService, two clear() and setValues() calls can interleave, briefly showing an empty or partially filled screen. Wrapping the whole function with LockService.getScriptLock() and releasing the lock in finally keeps behavior stable. This habit will also help later when you schedule “auto-refresh booking status” triggers on a time-based schedule.

Conclusion

In this post, we built the most basic yet crucial part of the Google Sheets booking status automation: a TODAY sheet that automatically gathers and shows only today’s bookings.

We implemented:

• DASH_getApptDailyViewSheet_() to prepare the TODAY view sheet and set the info message, headers, and formatting.
• DASH_updateApptDailyView() to filter today’s bookings from APPT_MAIN, sort them by start time, and write them in a single setValues call.
• DASH_addDashboardMenu_() to add a “Booking tools → Refresh Today bookings” menu item so the floor can refresh with one click.

Once this is in place, you no longer need to manually fiddle with filters and sort orders to build a “today” view. This TODAY view becomes a solid base on which you can later build dashboards (e.g., inbound volume by time slot, volume by carrier, etc.).

Here’s one concrete next step:

If you already have a booking sheet, update the constants (DASH_APPT_MAIN_SHEET_NAME, DASH_VIEW_SHEET_NAME) to match your sheet names, then run DASH_updateApptDailyView() once. As soon as you see a clean, dedicated sheet showing only today’s bookings, you’ll likely get immediate ideas about which metrics and charts you want to stack on top of it.