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

Create a Google Sheets status dashboard — Dashboard 2

Creating a reservation dashboard by status in Google Sheets ultimately means this: seeing at a glance how many reservations are Waiting / Arrived / In Progress / Done right now. In this post we’ll add an inbound reservation status dashboard using Google Sheets and Apps Script. Once you finish this part, the TODAY sheet will automatically summarize counts by status and show the detailed list on the same screen.

In the previous post, Google Sheets reservation dashboard automation: build the TODAY sheet — Dashboard 1, we built a “daily view” that copies reservations for a target date into the TODAY sheet. This post is the second stage on top of that TODAY data: a view that shows status counts and detailed rows at the same time.

Why you need a status-based dashboard

In a real warehouse, “how many reservations do we have today?” is not enough to plan operations. Even with the same 40 reservations:

20 Waiting / 10 Arrived / 5 In Progress / 5 Done versus
most of them already Done

call for completely different responses. Decisions about assigning workers, switching docks, and adjusting yard waiting space are all made by looking at the current distribution of statuses.

Inbound bookings typically flow like this: Booked → Arrived (Check-in) → At Dock (In Progress) → Done (Check-out). If you cannot see where in that flow the bottleneck is, the site will only feel that “there are a lot of trucks,” but won’t know where to intervene. That’s why an inbound reservation dashboard must show not just total counts, but also the distribution by status. The code in this post automatically calculates that status breakdown based on the TODAY sheet.

What we’ll build in this part

Here we’ll build the “status view layout” inside the TODAY sheet. We are not adding another sheet; instead, think of it as adding three extra layers to TODAY.

Reference date area

At the top of TODAY, cell A2 will show the execution date in the format Reference date: YYYY-MM-DD.
We’ll use a helper function called DASH_getViewDateKey_() for this.
No matter which date’s data is currently in TODAY, this “reference date” will represent when this status view was last refreshed.

Status summary area

Starting from row A4, we’ll place a fixed layout block for the status summary.
Example status labels: Waiting / Arrived / In Progress / Done / Other
A count will be displayed next to each status.
You could use COUNTIF formulas on the sheet, but whenever the TODAY structure changes, you would have to keep updating formulas. To avoid that, we’ll compute everything at once with Apps Script.

Detail list area

Starting from row A10, we’ll redraw TODAY’s original header and rows as a read-only detail section.
The “original data zone” of TODAY is left as-is; only the “read-only detail view zone” below it will be overwritten each time.
This prevents the already-computed summary and detail areas from being mixed back into the next aggregation when you run DASH_updateApptStatusView() multiple times.

With this structure in place, we’ll implement DASH_updateApptStatusView(), which reads TODAY and updates the summary and detail views together. The goal is a re-runnable-safe structure: running the same function multiple times should produce stable, predictable output.

Assumptions and design for status aggregation

To aggregate by status, the TODAY sheet needs one “Current Status” column. The check‑in / status management logic itself is assumed to have been built already as in Automatically update reservation status on check‑in with Google Sheets Apps Script — Check-in 4. The end result is that each reservation in TODAY has a status string similar to:

PENDING or SCHEDULED → Waiting
ARRIVED or CHECKED_IN → Arrived
IN_PROGRESS, LOADING, UNLOADING → In Progress
COMPLETED, DONE → Done

In a real project you may have more status codes, but three things matter for the aggregation logic:

Put a STATUS_MAP at the top of the code to explicitly define “raw status value → internal aggregation key.”
Anything not in the map (typos, new values, temporary values) must be grouped into OTHER. If you silently drop them, “total rows ≠ sum of status counts” and the dashboard becomes untrustworthy.
Rows with an empty status must also count toward OTHER. Empty values are easy to miss.

Another assumption is which column in TODAY stores the status, i.e. the column index. In the example code we’ll use the 7th column (column G) as the status column and set STATUS_COLUMN_INDEX: 7.

In your environment you must adjust this value to match your actual sheet layout. If you don’t, you’ll see all zeros or incorrect values for the status counts — a very common mistake.

Define configuration constants for the status view

Now we’ll add the actual Apps Script code. This goes into the same project as the code from Dashboard Part 1, Google Sheets reservation dashboard automation: build the TODAY sheet — Dashboard 1. To keep the code for this series from colliding within one project, constants and functions added in this part use the DASH_ prefix.

Where to paste

In Google Sheets: Extensions → Apps Script
Near the top of the dashboard-related code file

Apps Script (JavaScript)

// Configuration for the status view.                       // → Defines where to place summary and details
const DASH_STATUS_CONFIG = {                     // → Dashboard 2 specific settings
  TODAY_SHEET_NAME: 'TODAY',                    // → Name of the daily status sheet
  HEADER_ROW: 1,                                 // → Header row number in TODAY
  STATUS_COLUMN_INDEX: 7,                        // → Example column index for status (7 if status is in column G)
  VIEW_INFO_CELL: 'A2',                          // → Cell where the reference date is displayed
  SUMMARY_START_ROW: 4,                          // → Row where the status summary starts
  SUMMARY_START_COL: 1,                          // → Column where the status summary starts (column A)
  DETAIL_START_ROW: 10,                          // → Row where the detail list starts
  STATUS_LABELS: [                               // → Status names to show on the sheet
    { key: 'WAITING', label: 'Waiting' },           // → Internal key and label
    { key: 'ARRIVED', label: 'Arrived' },           // → Arrived status
    { key: 'IN_PROGRESS', label: 'In Progress' },     // → Work in progress
    { key: 'DONE', label: 'Done' },              // → Work completed
    { key: 'OTHER', label: 'Other' }              // → All other statuses
  ],
  STATUS_MAP: {                                  // → Mapping from raw status values to internal keys
    'PENDING': 'WAITING',                        // → Booked but not yet arrived
    'SCHEDULED': 'WAITING',                      // → Same meaning, grouped together
    'ARRIVED': 'ARRIVED',                        // → Arrived
    'CHECKED_IN': 'ARRIVED',                     // → Check-in completed
    'IN_PROGRESS': 'IN_PROGRESS',                // → In progress
    'LOADING': 'IN_PROGRESS',                    // → Loading in progress
    'UNLOADING': 'IN_PROGRESS',                  // → Unloading in progress
    'COMPLETED': 'DONE',                         // → Completed
    'DONE': 'DONE'                               // → Completed
  }
};

Practical tips:

At this point, first confirm the exact column index where the status is stored in TODAY, and then update STATUS_COLUMN_INDEX.
Applying data validation (dropdown) on the status column will help reduce excessive OTHER counts due to typos.

Function to record the reference date

Next we’ll write a string at the top of TODAY showing what date this status view is based on. Here we use the current date at execution time as the reference. This is intentionally separated from the date stored in TODAY. From an operations viewpoint it’s important to know which day’s data you are looking at, but it’s often even more useful to know “when this screen was last refreshed.”

Where to paste

In the same Apps Script file, directly below DASH_STATUS_CONFIG

Apps Script (JavaScript)

// Calculates and writes the status view reference date key.          // → Records reference date text
function DASH_getViewDateKey_() {                // → Internal helper function
  const ss = SpreadsheetApp.getActive();        // → Current spreadsheet
  const sheet = ss.getSheetByName(              // → Find TODAY sheet
    DASH_STATUS_CONFIG.TODAY_SHEET_NAME
  );
  if (!sheet) {                                 // → If the sheet does not exist
    throw new Error('Cannot find TODAY sheet.'); // → Throw an error
  }

  const today = new Date();                     // → Current time
  const tz = 'America/New_York';                // → Fixed time zone
  const y = Utilities.formatDate(               // → Extract year
    today, tz, 'yyyy'
  );
  const m = Utilities.formatDate(               // → Extract month
    today, tz, 'MM'
  );
  const d = Utilities.formatDate(               // → Extract day
    today, tz, 'dd'
  );
  const key = y + '-' + m + '-' + d;            // → YYYY-MM-DD string

  sheet.getRange(DASH_STATUS_CONFIG.VIEW_INFO_CELL) // → Select reference cell
    .setValue('Reference date: ' + key);                // → Write as text
  return key;                                   // → Return key
}

Practical tips:

In this series we always use the fixed time zone 'America/New_York'. We intentionally do not use Session.getScriptTimeZone(), which varies by project settings.
The reference date is written as a string instead of a date type so that the format does not break even if locale settings change.

Implementing the main status aggregation function

Now we’ll build the core function, DASH_updateApptStatusView(). It reads the original data area of TODAY, counts rows by status, and redraws both the summary at the top (from A4) and the detail section at the bottom (from A10) on each run.

Because the summary and detail areas are overwritten inside the same TODAY sheet, two simultaneous executions could partially overwrite each other’s results. To avoid that, we use LockService to serialize runs.

Where to paste

Directly below DASH_getViewDateKey_()

Apps Script (JavaScript)

// Reads the TODAY sheet and builds the status summary and detail view. // → Main function for Dashboard 2
function DASH_updateApptStatusView() {          // → For menu and manual execution
  const lock = LockService.getScriptLock();     // → Lock object
  lock.waitLock(30000);                         // → Wait up to 30 seconds
  try {
    const ss = SpreadsheetApp.getActive();      // → Current spreadsheet
    const sheet = ss.getSheetByName(            // → Find TODAY sheet
      DASH_STATUS_CONFIG.TODAY_SHEET_NAME
    );
    if (!sheet) {                               // → If not found
      throw new Error('Cannot find TODAY sheet.'); // → Throw error
    }

    const lastRow = sheet.getLastRow();         // → Last row number
    const headerRow = DASH_STATUS_CONFIG.HEADER_ROW; // → Header row
    if (lastRow <= headerRow) {                 // → No data rows
      clearStatusViewArea_(sheet);              // → Clear existing view
      DASH_getViewDateKey_();                   // → Only record reference date
      return 0;                                 // → 0 rows processed
    }

    const lastCol = sheet.getLastColumn();      // → Last column number
    const dataRange = sheet.getRange(           // → Data range
      headerRow + 1, 1,                         // → From row after header
      lastRow - headerRow,                      // → Number of data rows
      lastCol                                   // → All columns
    );
    const values = dataRange.getValues();       // → Read as 2D array

    const statusCol = DASH_STATUS_CONFIG.STATUS_COLUMN_INDEX; // → Status column index
    const counts = initStatusCounts_();         // → Initialize status counters
    const detailRows = [];                      // → Array for detail rows

    for (let i = 0; i < values.length; i++) {   // → Loop through each row
      const row = values[i];                    // → Current row

      if (row.join('').trim() === '') {         // → If the row is completely empty
        continue;                               // → Skip
      }

      const rawStatus = String(row[statusCol - 1] || '') // → Read status value
        .trim()
        .toUpperCase();

      const key = mapStatusKey_(rawStatus);     // → Convert to internal key
      if (!counts.hasOwnProperty(key)) {        // → If not defined in counters
        counts.OTHER++;                         // → Increase Other
      } else {
        counts[key]++;                          // → Increase corresponding status
      }

      detailRows.push(row);                     // → Add to detail list
    }

    DASH_getViewDateKey_();                     // → Record reference date
    writeStatusSummary_(sheet, counts);         // → Update summary area
    writeStatusDetails_(sheet, dataRange, detailRows); // → Update detail area

    return detailRows.length;                   // → Number of processed rows
  } finally {
    lock.releaseLock();                         // → Release lock
  }
}

// Initializes the status counter object.            // → Start all counts at 0
function initStatusCounts_() {                  // → Internal helper
  return {
    WAITING: 0,                                 // → Waiting
    ARRIVED: 0,                                 // → Arrived
    IN_PROGRESS: 0,                             // → In progress
    DONE: 0,                                    // → Done
    OTHER: 0                                    // → Other
  };
}

// Maps raw status values to internal status keys.       // → Uses STATUS_MAP
function mapStatusKey_(rawStatus) {             // → Internal helper
  if (!rawStatus) {                             // → If empty string
    return 'OTHER';                             // → Treat as Other
  }
  const map = DASH_STATUS_CONFIG.STATUS_MAP;    // → Mapping object
  if (map.hasOwnProperty(rawStatus)) {          // → If defined
    return map[rawStatus];                      // → Return mapped key
  }
  return 'OTHER';                               // → Everything else is Other
}

Practical tips:

You can lower lock.waitLock(30000) depending on your environment, but if it’s too short, a user double‑clicking the menu quickly might see frequent failures.
The condition row.join('').trim() === '' filters “completely blank rows,” so manually inserted spacer rows don’t affect the statistics.

Clearing and writing the status view areas

Finally, we’ll build helper functions to reset and output the status view. The key point here is to clearly separate the original TODAY data area from the status view area.

Original TODAY data: header row at row 1 + data rows below
Status summary: from row A4
Detail view: from row A10

This separation ensures that, on each rerun, the summary and detail areas are not mistakenly treated as input data and counted into OTHER.

Where to paste

Directly below mapStatusKey_()

Apps Script (JavaScript)

// Initializes the status view summary and detail areas.        // → Deletes previous results
function clearStatusViewArea_(sheet) {          // → Internal helper
  const startRow = DASH_STATUS_CONFIG.SUMMARY_START_ROW; // → First summary row
  const lastRow = sheet.getMaxRows();          // → Max rows in sheet
  const lastCol = sheet.getMaxColumns();       // → Max columns in sheet

  if (lastRow >= startRow) {                   // → If there are rows to clear
    const range = sheet.getRange(              // → Range for summary + detail
      startRow,                                // → Starting row
      1,                                       // → From column A
      lastRow - startRow + 1,                  // → Number of rows
      lastCol                                  // → All columns
    );
    range.clearContent();                      // → Clear values
  }
}

// Writes the status counts to the summary area.            // → Builds top summary table
function writeStatusSummary_(sheet, counts) {   // → Internal helper
  const startRow = DASH_STATUS_CONFIG.SUMMARY_START_ROW; // → Starting row
  const startCol = DASH_STATUS_CONFIG.SUMMARY_START_COL; // → Starting column
  const labels = DASH_STATUS_CONFIG.STATUS_LABELS; // → List of labels

  const output = [];                            // → 2D array to write to sheet
  output.push(['Status', 'Count']);                // → Header row

  labels.forEach(function(item) {               // → Loop through each status
    const key = item.key;                       // → Internal key
    const label = item.label;                   // → Label text
    const count = counts[key] || 0;             // → Count value
    output.push([label, count]);                // → Add one row
  });

  const range = sheet.getRange(                 // → Output range
    startRow,                                   // → Starting row
    startCol,                                   // → Starting column
    output.length,                              // → Number of rows
    output[0].length                            // → Number of columns
  );
  range.setValues(output);                      // → Write values
}

// Writes detailed rows read from TODAY into the view area. // → Builds bottom detail list
function writeStatusDetails_(sheet, sourceRange, detailRows) { // → Internal helper
  const detailStartRow = DASH_STATUS_CONFIG.DETAIL_START_ROW; // → First detail row
  const headerRow = DASH_STATUS_CONFIG.HEADER_ROW; // → TODAY header row
  const lastCol = sourceRange.getNumColumns(); // → Number of columns

  // First, clear only the existing detail area.             // → Protect other zones
  const maxRows = sheet.getMaxRows();           // → Total rows in sheet
  if (maxRows >= detailStartRow) {              // → If detail area exists
    const clearRange = sheet.getRange(          // → Entire detail range
      detailStartRow,                           // → Starting row
      1,                                        // → Column A
      maxRows - detailStartRow + 1,             // → Number of rows
      lastCol                                   // → Number of columns
    );
    clearRange.clearContent();                  // → Clear contents
  }

  // Copy header from TODAY header.           // → Keep header consistent
  const headerRange = sheet.getRange(           // → TODAY header range
    headerRow,                                  // → Header row
    1,                                          // → Column A
    1,                                          // → 1 row
    lastCol                                     // → All columns
  );
  const headerValues = headerRange.getValues(); // → Read header values

  const totalRows = detailRows.length + 1;      // → Header + data rows
  if (totalRows <= 1) {                         // → No data
    const destHeader = sheet.getRange(          // → Header destination
      detailStartRow,                           // → Starting row
      1,                                        // → Column A
      1,                                        // → 1 row
      lastCol                                   // → Number of columns
    );
    destHeader.setValues(headerValues);         // → Write header only
    return;                                     // → Exit
  }

  const destRange = sheet.getRange(             // → Output range for header + data
    detailStartRow,                             // → Header row in view
    1,                                          // → Column A
    totalRows,                                  // → Number of rows
    lastCol                                     // → Number of columns
  );

  const output = [];                            // → Output array
  output.push(headerValues[0]);                 // → Put header in first row
  for (let i = 0; i < detailRows.length; i++) { // → Copy each detail row
    output.push(detailRows[i]);                 // → Add row
  }

  destRange.setValues(output);                  // → Write all at once
}

Practical tips:

Because the detail area from row 10 down is cleared as a block, it’s safer not to store other notes or formulas in this region.
If needed, you can adapt this approach to put the “status detail” on a separate sheet (e.g. TODAY_VIEW). In that case, just change the sheet acquisition to sheet.getSheetByName('TODAY_VIEW').

Integrating menus without duplicate onOpen functions

Across this series (Basics, Registration, Check‑in, Dashboard Google Sheets reservation dashboard automation: build the TODAY sheet — Dashboard 1, etc.), each post has been declaring its own onOpen. In Apps Script, only the last-declared onOpen in the project actually runs, so if you keep multiple onOpen functions across files, the menus from earlier ones will disappear as soon as a later one is added.

The checker also warned about “duplicate onOpen declarations in the series,” so in this post we’ll unify onOpen into a single common entry point and have it call the menu-adding functions from each post.

Previous parts already use this pattern:

Inbound booking basics Part 1: addApptBaseMenu_(menu)
Inbound booking basics Part 2: APPT_addSeedMenu_(menu)
Inbound booking basics Part 3: APPT_addSettingsMenu_(menu)
Inbound booking basics Part 4: APPT_addWebAppMenu_(menu)
Dashboard Part 1: DASH_addDashboardMenu_(menu)

These functions are already defined in those posts, so do not redefine them here.

In this part we only decide how to call them together from a single onOpen.

1) Common onOpen pattern

Place exactly one onOpen in the entire project and have it call all the add○○Menu_ functions from each module:

Apps Script (JavaScript)

// Common onOpen that runs once when the spreadsheet is opened.
// Note: this must be the only onOpen in the entire project;
//       delete or comment out any other onOpen declarations in other files.
function onOpen(e) {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('Reservation Tool'); // Common top-level menu name

  // 1) Base and seed data menus (Inbound basics Parts 1 & 2)
  if (typeof addApptBaseMenu_ === 'function') {
    addApptBaseMenu_(menu);        // /en/posts/20260811-q218-906ac0
  }
  if (typeof APPT_addSeedMenu_ === 'function') {
    APPT_addSeedMenu_(menu);       // /en/posts/20260812-q219-2e1150
  }

  // 2) Settings and web app menus (Inbound basics Parts 3 & 4)
  if (typeof APPT_addSettingsMenu_ === 'function') {
    APPT_addSettingsMenu_(menu);   // /en/posts/20260812-q220-4bcc4c
  }
  if (typeof APPT_addWebAppMenu_ === 'function') {
    APPT_addWebAppMenu_(menu);     // /en/posts/20260813-q221-120eba
  }

  // 3) Dashboard menu (Dashboard Part 1)
  if (typeof DASH_addDashboardMenu_ === 'function') {
    DASH_addDashboardMenu_(menu);  // /en/posts/20260821-q233-f92dcd
  }

  // 4) Add this post’s status dashboard menu (Dashboard Part 2).
  //    It appears under the same top-level menu as the others.
  menu.addSeparator()
      .addItem('Refresh status dashboard', 'DASH_updateApptStatusView');

  menu.addToUi();                  // Add menu to actual UI
}

Advantages of this structure:

There is only one onOpen, so you fully comply with Apps Script rules.
Menu extension functions from each post (addApptBaseMenu_, APPT_addSeedMenu_, APPT_addSettingsMenu_, APPT_addWebAppMenu_, DASH_addDashboardMenu_) are reused as-is.
The DASH_updateApptStatusView introduced in this post appears under the same “Reservation Tools” top-level menu, grouped with the rest of the reservation system menu items.

2) Steps to consolidate existing onOpen functions

In the project, open all files and search for function onOpen.
If there are multiple, move their logic into the common pattern above, and leave only this onOpen definition.
Comment out or delete onOpen declarations in other files.
Save the script, reopen the spreadsheet, and confirm that the top menu shows “Reservation Tools” with the existing menu items plus “Refresh status dashboard.”

What to check when testing in production

To see whether the status dashboard is reliable in the real world, it’s enough to verify these three points:

Handling of typos and empty statuses

In TODAY, set the status for some rows to something like ABC (a value not in the map) or leave them blank.
After running DASH_updateApptStatusView(), confirm that the Other count includes all those rows.
The number of TODAY data rows should exactly match the sum of (WAITING + ARRIVED + IN_PROGRESS + DONE + OTHER).

Consistency across reruns

Prepare 5–10 test rows in TODAY.
Run DASH_updateApptStatusView().
Run it once more immediately.
Without touching the original TODAY data between the two runs, the summary and detail areas should remain identical.
Then change one row’s status, e.g. PENDING → ARRIVED, run again, and confirm that the Waiting count decreases by 1 and Arrived increases by 1.

Verifying STATUS_COLUMN_INDEX

Double‑check which column index really stores the status in TODAY, and confirm that STATUS_COLUMN_INDEX matches that value.
If in doubt, test on a copy of the spreadsheet while changing the index to see which setting produces correct counts.

Conclusion

In this second part of the Google Sheets logistics dashboard series, we built a reservation status dashboard based on the TODAY sheet.

DASH_STATUS_CONFIG centralizes the status column, summary/detail locations, and status mappings.
With a single DASH_updateApptStatusView() call, you can update the reference date, status summary, and detail list all at once.
By consolidating onOpen into one common entry point, the entire series’ menus stay conflict‑free under a single menu tree.

To try this in your own sheet, the minimum steps are:

In TODAY, identify the column index that stores the “current status.”
Set DASH_STATUS_CONFIG.STATUS_COLUMN_INDEX to that value.
Paste the code from this post into Apps Script and save.
Reopen the spreadsheet and click “Reservation Tools ▸ Refresh status dashboard.”

Even with just a handful of rows, being able to see at a glance how many are Waiting, Arrived, In Progress, Done, and Other makes the operational picture feel completely different.

Create a Google Sheets status dashboard — Dashboard 2

Why you need a status-based dashboard

What we’ll build in this part

Assumptions and design for status aggregation

Define configuration constants for the status view

Function to record the reference date

Implementing the main status aggregation function

Clearing and writing the status view areas

Integrating menus without duplicate onOpen functions

1) Common onOpen pattern

2) Steps to consolidate existing onOpen functions

What to check when testing in production

Conclusion

More in this category

Google Sheets bulk appointment booking: finish with one LockService and setValues call

Google Sheets bulk booking validation: check everything in one paste (final)

Google Sheets Late-Arrival Alert Auto Email: Inbound KPI 5

Google Sheets weekly KPI email: KPI Pt.4 final code