Smart Life US

← All posts · 2026-08-24 · Excel & Automation

Google Sheets Yard Trailer Status Dashboard — Part 3 (Final)

Google Sheets Yard Trailer Status Dashboard — Part 3 (Final)

Intro: Manage yard slots on the sheet, not in your head

The inbound appointment system in this series connects reservations, check-in, and yard moves into a single flow. In the previous posts:

Create yard status dashboard

The goal of this post is to build a Google Sheets yard trailer status dashboard. The core idea is to create a logistics dashboard in Google Sheets where you can see on one screen “which trailer (or container) is sitting in which yard slot, and for how many hours.” Using Google Sheets and Apps Script, we’ll read from the YARD sheet and trailer move history (TRAILER_MOVES), calculate each slot’s current occupancy and elapsed time, and visualize it with colors.

On the operations floor, yard slots were often managed for a long time with whiteboards and verbal updates. As the number of units entering the yard increased and turns got faster, it became harder to answer questions like “When did that trailer come in?” or “Is that unit we parked last night still there?” on the spot. To solve this, we turned yard slot occupancy into a Google Sheets dashboard and highlighted risky slots by elapsed time with colors. In this post I’ll document the structure and code currently used in production as closely as possible.

Data structure: how to combine YARD and TRAILER_MOVES

To calculate yard slot occupancy automatically, you first need to define which data plays which role. In this series we’ve already built the following sheets tied into check-in:

Roles of YARD and move history

The YARD sheet is the slot master.

It includes slot ID, description, active flag, etc. You design it to match the physical layout once and then rarely change it. In this post we’ll reuse the existing structure and won’t redefine the column layout. Instead, we’ll use the active flag as the rule to decide “which slots should be shown on the screen.”

The TRAILER_MOVES sheet is the trailer move history.

Every time a trailer’s location changes—check-in, yard slot move, departure—one log row is added. In Check-in Part 5 we used CHK_appendTrailerMove_() to record this, so here we’ll focus on reading that history to build the latest status per slot.

The tasks for Dashboard Part 3 are:

  • From YARD, bring in only the currently active slots.
  • From TRAILER_MOVES, keep only the most recent record per slot.
  • If the last action is "IN", the slot is occupied; otherwise (e.g. "OUT") we consider it empty.
  • Using the occupancy start time and a reference time (execution time), calculate elapsed time in hours.
  • In a defined area of the TODAY sheet, write, per row:
  • Slot ID
  • Slot name (or area description)
  • Current container (or trailer) number
  • Occupancy start time
  • Elapsed time (hours)
  • Status code

and color the rows so that the longer the elapsed time, the stronger the color.

The base design assumes “within 2 hours is normal, up to 4 hours is warning, beyond 4 hours is critical.” These thresholds live in the DASH_YARD_CONFIG constant so you can adjust them anytime to match yard operations. Set up this way, a single monitor lets you quickly see “what’s been sitting too long.”

Main function and config: the skeleton of yard status updates

The main function in this part is named DASH_updateYardStatusView(). In one line:

“Read YARD and TRAILER_MOVES, and draw per-slot current status on the TODAY sheet.”

Preventing concurrent writes: why use LockService

Yard status is often updated on a schedule (time-based triggers, manual runs, calls from other menus, etc.). If multiple executions modify TODAY at the same time, colors and values can overwrite each other or only partially apply. We addressed similar concerns on the appointment dashboard side.

So this main function uses LockService.getDocumentLock() to get a document-level lock, ensuring only one execution updates TODAY at a time. If acquiring the lock takes too long (e.g. 10 seconds), that execution throws an error and exits to prevent overlapping runs from corrupting the sheet.

Step 1 — Define config constants and the main function shell

1) What this code does

  • Gathers sheet names, starting positions, thresholds, and color settings for the yard dashboard into a single config object.
  • Creates the main function that acquires a document lock, reads YARD and TRAILER_MOVES, and updates the TODAY sheet.

2) Where to paste

  • In Google Sheets → Extensions → Apps Script → the same project used in the rest of this series, paste this at the very bottom of the existing code file.
  • If onOpen() is already defined from previous posts, do not create a new one here. We’ll consolidate menus once we wrap up the whole dashboard.
  • Functions used from earlier parts (like DASH_getApptDailyViewSheet_()) are not redefined; we assume they exist. If needed, see “Dashboard Part 1.”

3) What to do after pasting

  • Save, then run DASH_testUpdateYardStatusView() once to grant permissions.
Apps Script (JavaScript)
// Configuration constants for the yard status view.
const DASH_YARD_CONFIG = {
  TZ: 'America/New_York',              // Fixed time zone
  MOVE_SHEET_NAME: 'TRAILER_MOVES',    // Move history sheet name
  YARD_SHEET_NAME: 'YARD',             // Yard slot sheet name
  VIEW_SHEET_NAME: 'TODAY',            // Dashboard display sheet
  VIEW_START_ROW: 30,                  // Starting row of the yard table (adjust as needed)
  VIEW_START_COL: 1,                   // Starting column of the yard table (column A)
  MAX_SLOT_HOURS_OK: 2,                // Up to 2 hours: OK
  MAX_SLOT_HOURS_WARN: 4,              // 2–4 hours: Warning
  COLOR_EMPTY: '#ffffff',              // Color for empty slots
  COLOR_OK: '#e2f0d9',                 // Color for normal occupancy (light green)
  COLOR_WARN: '#fff2cc',               // Warning color (yellowish)
  COLOR_ALERT: '#f4cccc'               // Critical color (reddish)
};

/**
 * Draws current yard slot occupancy status into the TODAY sheet.
 * Uses LockService to prevent concurrent runs from corrupting TODAY.
 */
function DASH_updateYardStatusView() {
  const lock = LockService.getDocumentLock();
  // Wait up to 10 seconds so multiple runs don’t touch TODAY at the same time
  const gotLock = lock.tryLock(10 * 1000);
  if (!gotLock) {
    throw new Error('Could not obtain lock for yard status update. Please try again shortly.');
  }

  try {
    const ss = SpreadsheetApp.getActive();
    const moveSheet = ss.getSheetByName(DASH_YARD_CONFIG.MOVE_SHEET_NAME);
    const yardSheet = ss.getSheetByName(DASH_YARD_CONFIG.YARD_SHEET_NAME);
    const viewSheet = ss.getSheetByName(DASH_YARD_CONFIG.VIEW_SHEET_NAME);

    if (!moveSheet || !yardSheet || !viewSheet) {
      throw new Error('Please create the YARD, TRAILER_MOVES, and TODAY sheets first.');
    }

    const now = new Date();
    const yardSlots = DASH_loadActiveYardSlots_(yardSheet);     // Active slot list
    const latestMoves = DASH_loadLatestMovesBySlot_(moveSheet); // Latest status per slot
    const viewData = DASH_buildYardViewTable_(yardSlots, latestMoves, now);

    DASH_writeYardView_(viewSheet, viewData);   // Write values to TODAY
    DASH_colorYardView_(viewSheet, viewData);   // Apply colors to TODAY
  } finally {
    // Always release the lock regardless of errors
    lock.releaseLock();
  }
}

/**
 * Test wrapper. Runs the update, then logs completion and
 * lightly validates the structure and types of the output data.
 */
function DASH_testUpdateYardStatusView() {
  const ss = SpreadsheetApp.getActive();
  const viewSheet = ss.getSheetByName(DASH_YARD_CONFIG.VIEW_SHEET_NAME);
  if (!viewSheet) {
    throw new Error('TODAY sheet is required.');
  }
  DASH_updateYardStatusView();

  const startRow = DASH_YARD_CONFIG.VIEW_START_ROW;
  const startCol = DASH_YARD_CONFIG.VIEW_START_COL;
  const lastRow = viewSheet.getLastRow();
  const rows = Math.max(0, lastRow - startRow + 1);
  if (rows <= 0) {
    Logger.log('No yard status data found.');
    return;
  }

  const range = viewSheet.getRange(startRow, startCol, rows, 6);
  const values = range.getValues();

  // Basic sanity checks
  if (!Array.isArray(values)) {
    throw new Error('TODAY yard view data is not an array.');
  }
  if (values.length > 0) {
    const row = values[0];
    if (!Array.isArray(row) || row.length !== 6) {
      throw new Error('Each TODAY yard view row must be an array of length 6.');
    }
    if (typeof row[0] !== 'string' && typeof row[0] !== 'number') {
      throw new Error('First column (slot ID) must be a string or number.');
    }
  }

  Logger.log('DASH_updateYardStatusView executed and basic structure verified.');
}

If running DASH_testUpdateYardStatusView() completes without errors, the skeleton is sound. Next we’ll fill in each step function.

Load active yard slots: decide what to display

Next we’ll pull only the slots from the YARD sheet that should appear on the dashboard. If you include inactive slots, you get too much noise and colors mixed in for areas that aren’t really in use.

Step 2 — Read the active yard slot list

In real sites it’s common to have temporary or retired slots still listed in the sheet. I add an “active” flag column in YARD and reflect a slot in the dashboard only when its value is in an allowed list. If you skip the allowed list, blank or odd values can all be interpreted as “active” and clutter the dashboard.

1) What this code does

  • Reads the YARD sheet and returns only active slots as an array of {slotId, name}.
  • Treats the active flag as valid only when it’s in the allowed values (Y, YES, TRUE, 1).

2) Where to paste

  • Directly below the code from Step 1.

3) What to do after pasting

  • Run DASH_testLoadActiveYardSlots_() and check the execution log to see that the active slot list prints correctly.
Apps Script (JavaScript)
/**
 * Reads the list of active slots from the YARD sheet.
 * Returns: [{slotId, name}, ...]
 *
 * Column assumptions: Col A=Slot ID, Col B=Name, Col C=Active flag
 */
function DASH_loadActiveYardSlots_(yardSheet) {
  const lastRow = yardSheet.getLastRow();
  if (lastRow < 2) {
    // Header only
    return [];
  }

  const range = yardSheet.getRange(2, 1, lastRow - 1, 3); // A:C
  const values = range.getValues();

  const slots = [];
  const ACTIVE_VALUES = ['Y', 'YES', 'TRUE', '1'];

  for (let i = 0; i < values.length; i++) {
    const row = values[i];
    const slotId = row[0]; // Col A
    const name = row[1];   // Col B
    const active = row[2]; // Col C

    if (!slotId) {
      // Skip if slot ID is missing (deleted row, etc.)
      continue;
    }

    const activeStr = String(active).trim().toUpperCase();
    if (!ACTIVE_VALUES.includes(activeStr)) {
      // Exclude inactive slots from the dashboard
      continue;
    }

    slots.push({ slotId, name });
  }

  return slots;
}

/**
 * Test for active slots.
 * Validates structure and types, and logs the slot list.
 */
function DASH_testLoadActiveYardSlots_() {
  const ss = SpreadsheetApp.getActive();
  const yardSheet = ss.getSheetByName(DASH_YARD_CONFIG.YARD_SHEET_NAME);
  if (!yardSheet) {
    throw new Error('YARD sheet is required.');
  }
  const slots = DASH_loadActiveYardSlots_(yardSheet);

  // Expected: array, with objects having required fields and proper types
  if (!Array.isArray(slots)) {
    throw new Error('DASH_loadActiveYardSlots_ result is not an array.');
  }
  if (slots.length > 0) {
    const s = slots[0];
    if (typeof s !== 'object' || s === null) {
      throw new Error('Each slot item must be an object.');
    }
    if (!('slotId' in s) || !('name' in s)) {
      throw new Error('Slot object is missing slotId or name field.');
    }
    if (typeof s.slotId !== 'string' && typeof s.slotId !== 'number') {
      throw new Error('slotId must be a string or a number.');
    }
    if (typeof s.name !== 'string' && s.name !== null && s.name !== undefined) {
      throw new Error('name must be a string or an empty value.');
    }
  }

  Logger.log('Active slots: ' + JSON.stringify(slots));
}

After running the test, confirm that only active slots are filtered in and that the object structure matches expectations.

Compute latest status per slot from TRAILER_MOVES

Now we need to extract the “current status” from the move history in TRAILER_MOVES. This is the core of tracking trailer locations with Google Sheets.

Step 3 — Keep only the most recent record per slot from move history

The TRAILER_MOVES sheet contains a mix of actions: check-in, yard move, departure, etc. Here we manage action codes with an allowed list so bad values don’t silently affect the dashboard. If a required value is missing or the timestamp isn’t a Date, we skip that row.

1) What this code does

  • Reads all rows from TRAILER_MOVES and aggregates them so that for each slot, only the most recent record is stored as {slotId: {...}}.
  • Only passes through allowed action codes (IN/OUT/MOVE); others are ignored.

2) Where to paste

  • Directly below the Step 2 code.

3) What to do after pasting

  • Run DASH_testLoadLatestMovesBySlot_() and confirm in the logs that the latest status per slot looks correct and has the right structure.
Apps Script (JavaScript)
/**
 * Computes the latest move record per slot from the TRAILER_MOVES sheet.
 * Returns: {slotId: {slotId, container, status, movedAt}, ...}
 *
 * Column assumptions: Col A=Timestamp, Col B=Slot ID, Col C=Container number, Col D=Action type (IN/OUT/MOVE etc.)
 */
function DASH_loadLatestMovesBySlot_(moveSheet) {
  const lastRow = moveSheet.getLastRow();
  if (lastRow < 2) {
    // No data
    return {};
  }

  const range = moveSheet.getRange(2, 1, lastRow - 1, 4); // A:D
  const values = range.getValues();

  const latestBySlot = {};
  const ALLOWED_ACTIONS = ['IN', 'OUT', 'MOVE'];

  for (let i = 0; i < values.length; i++) {
    const row = values[i];
    const ts = row[0];         // Timestamp
    const slotId = row[1];     // Slot ID
    const container = row[2];  // Container number
    const actionRaw = row[3];  // IN/OUT/MOVE

    if (!slotId || !ts) {
      // Skip if required values are missing
      continue;
    }
    if (!(ts instanceof Date)) {
      // Ignore non-date timestamps (text, etc.)
      continue;
    }

    const action = String(actionRaw || '').toUpperCase();
    if (!ALLOWED_ACTIONS.includes(action)) {
      // Do not reflect unsupported actions in the dashboard
      continue;
    }

    const prev = latestBySlot[slotId];
    if (!prev || prev.movedAt < ts) {
      // Replace if this record is more recent for this slot
      latestBySlot[slotId] = {
        slotId: slotId,
        container: container || '',
        status: action,
        movedAt: ts
      };
    }
  }

  return latestBySlot;
}

/**
 * Test for latest move status per slot.
 * Validates structure and types, and logs the result.
 */
function DASH_testLoadLatestMovesBySlot_() {
  const ss = SpreadsheetApp.getActive();
  const moveSheet = ss.getSheetByName(DASH_YARD_CONFIG.MOVE_SHEET_NAME);
  if (!moveSheet) {
    throw new Error('TRAILER_MOVES sheet is required.');
  }

  const latest = DASH_loadLatestMovesBySlot_(moveSheet);

  // Expected: object keyed by slot ID; each entry has required fields and types
  if (typeof latest !== 'object' || latest === null || Array.isArray(latest)) {
    throw new Error('DASH_loadLatestMovesBySlot_ result must be an object keyed by slot ID.');
  }

  const keys = Object.keys(latest);
  if (keys.length > 0) {
    const example = latest[keys[0]];
    if (typeof example !== 'object' || example === null) {
      throw new Error('Each slot status must be an object.');
    }
    const required = ['slotId', 'container', 'status', 'movedAt'];
    required.forEach(function (k) {
      if (!(k in example)) {
        throw new Error('Slot status object is missing field: ' + k);
      }
    });
    if (!(example.movedAt instanceof Date)) {
      throw new Error('movedAt field must be of Date type.');
    }
    if (typeof example.status !== 'string') {
      throw new Error('status field must be a string.');
    }
  }

  Logger.log('Latest move status per slot: ' + JSON.stringify(latest));
}

Now we have logic to keep only the latest move per slot, with type validation included.

Build the table data: combine slots and moves with elapsed time

With the list of active slots and the latest move per slot, we can construct the 2D array that will be written directly into TODAY. This array includes elapsed time in hours, which we’ll use for coloring later.

Step 4 — Construct the per-slot status table

What matters in yard operations is “where, what, and for how long.” So we calculate elapsed time only for slots whose last status is IN. For OUT or other statuses, we leave the elapsed time blank instead of a number. If the time math goes wrong, NaN can spread and dirty the sheet, so we guard against non-Date values and negative times.

1) What this code does

  • For each active slot, builds a row containing:
  • Slot ID
  • Slot name
  • Current container number
  • Occupancy start time (Date)
  • Elapsed time (hours, 1 decimal place)
  • Status code

2) Where to paste

  • Directly below the Step 3 code.

3) What to do after pasting

  • Run DASH_testBuildYardViewTable_() and confirm in the logs that the table data has the desired shape and valid field types.
Apps Script (JavaScript)
/**
 * Builds a 2D array for the yard slot status table.
 * Each row: [Slot ID, Name, Container, Start time (Date or ''), Elapsed hours, Status]
 */
function DASH_buildYardViewTable_(yardSlots, latestMoves, now) {
  const rows = [];
  const msPerHour = 1000 * 60 * 60;

  for (let i = 0; i < yardSlots.length; i++) {
    const slot = yardSlots[i];
    const slotId = slot.slotId;
    const name = slot.name || '';
    const move = latestMoves[slotId];

    let container = '';
    let startTime = '';
    let hours = '';
    let status = '';

    if (move && move.status === 'IN') {
      // Currently occupied
      container = move.container || '';
      startTime = move.movedAt;

      if (startTime instanceof Date && !isNaN(startTime.getTime())) {
        const diffMs = now.getTime() - startTime.getTime();
        const diffHours = diffMs / msPerHour;
        const safeHours = diffHours < 0 ? 0 : diffHours; // Guard against future timestamps
        const rounded = Math.round(safeHours * 10) / 10; // Round to 1 decimal place
        hours = Number.isFinite(rounded) ? rounded : '';
      }

      status = 'IN';
    } else if (move) {
      // Last action was OUT, MOVE, etc.
      status = move.status;
      // For OUT status we leave elapsed time blank.
    }

    rows.push([slotId, name, container, startTime, hours, status]);
  }

  return rows;
}

/**
 * Test for building the status table.
 * Logs the resulting array and validates structure and types.
 */
function DASH_testBuildYardViewTable_() {
  const ss = SpreadsheetApp.getActive();
  const yardSheet = ss.getSheetByName(DASH_YARD_CONFIG.YARD_SHEET_NAME);
  const moveSheet = ss.getSheetByName(DASH_YARD_CONFIG.MOVE_SHEET_NAME);
  if (!yardSheet || !moveSheet) {
    throw new Error('Please check that YARD and TRAILER_MOVES sheets exist.');
  }

  const slots = DASH_loadActiveYardSlots_(yardSheet);
  const latest = DASH_loadLatestMovesBySlot_(moveSheet);
  const now = new Date();
  const table = DASH_buildYardViewTable_(slots, latest, now);

  // Expected: 2D array with rows of fixed length and correct field types
  if (!Array.isArray(table)) {
    throw new Error('DASH_buildYardViewTable_ result is not an array.');
  }
  if (table.length > 0) {
    const row = table[0];
    if (!Array.isArray(row)) {
      throw new Error('Each yard view row must be an array.');
    }
    if (row.length !== 6) {
      throw new Error('Each yard view row must have 6 fields.');
    }

    const [slotId, name, container, startTime, hours, status] = row;

    if (typeof slotId !== 'string' && typeof slotId !== 'number') {
      throw new Error('Slot ID must be a string or a number.');
    }
    if (typeof name !== 'string' && name !== null && name !== undefined) {
      throw new Error('Slot name must be a string or an empty value.');
    }
    if (typeof container !== 'string' && container !== '') {
      throw new Error('Container number must be a string or an empty string.');
    }
    if (!(startTime === '' || startTime instanceof Date)) {
      throw new Error('Start time must be a Date or an empty string.');
    }
    if (!(hours === '' || typeof hours === 'number')) {
      throw new Error('Elapsed hours must be a number or an empty string.');
    }
    if (typeof status !== 'string') {
      throw new Error('Status code must be a string.');
    }
  }

  Logger.log('Yard view table: ' + JSON.stringify(table));
}

This gives you table data with built-in validation of structure and field types. You can also verify that OUT slots have blank elapsed time.

Write to TODAY and color by elapsed time

Now for the final two tasks of the Google Sheets yard slot status dashboard:

  1. Write viewData into the designated region of the TODAY sheet
  2. Change row colors by elapsed time

For performance, we perform value writes and color updates each in a single batch.

Step 5 — Write the yard status table and set display formats

The TODAY sheet may already be hosting other dashboards (appointment summary, status views, etc.). To keep this yard view safe, we place it as a separate block in an unused area near the bottom. This position is controlled by VIEW_START_ROW.

1) What this code does

  • Clears the yard area in TODAY once, then writes viewData as is.
  • Leaves the occupancy time column as Date values and just sets the display format to "MM-dd HH:mm".

2) Where to paste

  • Directly below the Step 4 code.

3) What to do after pasting

  • Run DASH_testWriteYardView_() and confirm that data appears in the expected location on TODAY and that the written data’s structure is valid.
Apps Script (JavaScript)
/**
 * Writes the yard status table into the yard region of the TODAY sheet.
 */
function DASH_writeYardView_(viewSheet, viewData) {
  const startRow = DASH_YARD_CONFIG.VIEW_START_ROW;
  const startCol = DASH_YARD_CONFIG.VIEW_START_COL;

  const maxRows = Math.max(viewData.length, 1);
  const clearRange = viewSheet.getRange(startRow, startCol, maxRows, 6);
  // Clear previous data and formatting
  clearRange.clearContent();
  clearRange.clearFormat();

  if (viewData.length === 0) {
    // Nothing to show
    return;
  }

  const range = viewSheet.getRange(startRow, startCol, viewData.length, 6);
  range.setValues(viewData);

  // Apply time format to the start time column (4th column).
  const timeRange = viewSheet.getRange(startRow, startCol + 3, viewData.length, 1);
  timeRange.setNumberFormat('MM-dd HH:mm');
}

/**
 * Test for write behavior.
 * Validates viewData structure and actually writes to TODAY.
 */
function DASH_testWriteYardView_() {
  const ss = SpreadsheetApp.getActive();
  const yardSheet = ss.getSheetByName(DASH_YARD_CONFIG.YARD_SHEET_NAME);
  const moveSheet = ss.getSheetByName(DASH_YARD_CONFIG.MOVE_SHEET_NAME);
  const viewSheet = ss.getSheetByName(DASH_YARD_CONFIG.VIEW_SHEET_NAME);
  if (!yardSheet || !moveSheet || !viewSheet) {
    throw new Error('Please verify that YARD, TRAILER_MOVES, and TODAY sheets exist.');
  }

  const slots = DASH_loadActiveYardSlots_(yardSheet);
  const latest = DASH_loadLatestMovesBySlot_(moveSheet);
  const now = new Date();
  const table = DASH_buildYardViewTable_(slots, latest, now);

  // Basic validation of viewData
  if (!Array.isArray(table)) {
    throw new Error('viewData(table) is not an array.');
  }
  if (table.length > 0) {
    const row = table[0];
    if (!Array.isArray(row) || row.length !== 6) {
      throw new Error('Each viewData row must be an array of length 6.');
    }
  }

  DASH_writeYardView_(viewSheet, table);

  // After writing to TODAY, read back and verify length and types.
  const startRow = DASH_YARD_CONFIG.VIEW_START_ROW;
  const startCol = DASH_YARD_CONFIG.VIEW_START_COL;
  const range = viewSheet.getRange(startRow, startCol, table.length || 1, 6);
  const written = range.getValues();
  if (!Array.isArray(written)) {
    throw new Error('Data read back from TODAY is not an array.');
  }

  Logger.log('TODAY yard view write and basic validation complete, row count: ' + written.length);
}

If TODAY shows slot ID, name, container, start time, elapsed hours, and status in columns A–F starting at VIEW_START_ROW, things are working.

Step 6 — Color rows by elapsed time

The last step is visualization. Rows should change color as elapsed time grows so you can immediately spot trailers that have been sitting too long.

1) What this code does

  • Loops through each row of viewData, reads the elapsed hours, and for each:
  • No container or blank elapsed time: empty-slot color
  • MAX_SLOT_HOURS_OK or less: OK color
  • Up to MAX_SLOT_HOURS_WARN: warning color
  • Above that: critical color

and then applies the background color to the entire row.

2) Where to paste

  • Directly below the Step 5 code.

3) What to do after pasting

  • Run DASH_testColorYardView_() and verify that colors on TODAY follow the specified rules; the test also validates the viewData structure.
Apps Script (JavaScript)
/**
 * Applies elapsed-time-based colors to the yard status region in TODAY.
 */
function DASH_colorYardView_(viewSheet, viewData) {
  const startRow = DASH_YARD_CONFIG.VIEW_START_ROW;
  const startCol = DASH_YARD_CONFIG.VIEW_START_COL;
  const rows = viewData.length;
  if (rows === 0) {
    return;
  }

  const colors = [];
  for (let i = 0; i < rows; i++) {
    const row = viewData[i];
    const container = row[2]; // Container
    const hours = row[4];     // Elapsed hours
    let color = DASH_YARD_CONFIG.COLOR_EMPTY;

    if (container && Number.isFinite(hours)) {
      if (hours <= DASH_YARD_CONFIG.MAX_SLOT_HOURS_OK) {
        color = DASH_YARD_CONFIG.COLOR_OK;
      } else if (hours <= DASH_YARD_CONFIG.MAX_SLOT_HOURS_WARN) {
        color = DASH_YARD_CONFIG.COLOR_WARN;
      } else {
        color = DASH_YARD_CONFIG.COLOR_ALERT;
      }
    }

    const rowColors = [];
    for (let c = 0; c < 6; c++) {
      rowColors.push(color);
    }
    colors.push(rowColors);
  }

  const range = viewSheet.getRange(startRow, startCol, rows, 6);
  range.setBackgrounds(colors);
}

/**
 * Test function for color application.
 * Validates the in-memory viewData structure and
 * applies colors over the data already written in TODAY.
 */
function DASH_testColorYardView_() {
  const ss = SpreadsheetApp.getActive();
  const viewSheet = ss.getSheetByName(DASH_YARD_CONFIG.VIEW_SHEET_NAME);
  if (!viewSheet) {
    throw new Error('TODAY sheet is required.');
  }

  const startRow = DASH_YARD_CONFIG.VIEW_START_ROW;
  const startCol = DASH_YARD_CONFIG.VIEW_START_COL;
  const lastRow = viewSheet.getLastRow();
  const rows = Math.max(0, lastRow - startRow + 1);
  if (rows <= 0) {
    Logger.log('No data in the TODAY yard region.');
    return;
  }

  const range = viewSheet.getRange(startRow, startCol, rows, 6);
  const values = range.getValues();

  // Expected: 2D array; each row length and hours type
  if (!Array.isArray(values)) {
    throw new Error('TODAY yard view values are not an array.');
  }
  if (values.length > 0) {
    const row = values[0];
    if (!Array.isArray(row) || row.length !== 6) {
      throw new Error('Each TODAY yard view row must be an array of length 6.');
    }
    const hours = row[4];
    if (!(hours === '' || typeof hours === 'number')) {
      throw new Error('Elapsed hours column must be a number or an empty string.');
    }
  }

  DASH_colorYardView_(viewSheet, values);
  Logger.log('Yard view color application complete.');
}

In the TODAY sheet’s yard region you should see:

  • Slots with no container or blank elapsed time: white (COLOR_EMPTY)
  • Slots with elapsed time ≤ MAX_SLOT_HOURS_OK (default 2 hours): light green (COLOR_OK)
  • Up to MAX_SLOT_HOURS_WARN (default 4 hours): yellow (COLOR_WARN)
  • Above that: red-ish (COLOR_ALERT)

You can freely adjust colors and thresholds in DASH_YARD_CONFIG.

Practical tips and error-prevention points

Tune thresholds and colors to your actual turn time

The 2/4 hour thresholds here are just examples. Realistic values depend on your turn time and customer expectations. Slower yards might prefer 4/8 hours as “OK/warn,” while very fast-turn operations might need 1/2-hour buckets.

The key is:

  • First agree with your team on “what action to take when each color shows up,” then
  • Reflect that agreement in both DASH_YARD_CONFIG and your floor SOPs.

If colors change but there’s no behavior tied to them, operations will get confused.

Avoid layout clashes on the TODAY sheet

This series also puts other dashboards on TODAY:

To avoid overlap:

  1. Check the last row currently used on TODAY.
  2. Leave 5–10 blank rows below that and set VIEW_START_ROW to that new starting line.
  3. On the row right above the yard view, add a note like “Yard Status (auto-generated area, do not edit manually)” so other users don’t overwrite it.

This makes future dashboard expansion less likely to cause layout conflicts.

Use the test functions and wire things up step by step

When first adding the yard status code, it’s better to run the test functions in order from top to bottom rather than jumping straight to the full flow:

  1. DASH_testLoadActiveYardSlots_ — validates YARD structure, active flag, and object shape
  2. DASH_testLoadLatestMovesBySlot_ — confirms TRAILER_MOVES structure, action codes, and types
  3. DASH_testBuildYardViewTable_ — checks table structure and field types
  4. DASH_testWriteYardView_ — confirms write location/format/row count on TODAY
  5. DASH_testColorYardView_ — validates viewData and color rules on real sheet data
  6. Finally, DASH_testUpdateYardStatusView — checks the full flow and TODAY’s final layout

This makes it easy to pinpoint exactly where things are failing and then fix sheet layouts or data formats accordingly.

Wrap-up

In this post we focused on building a Google Sheets yard trailer status dashboard using the existing YARD master and TRAILER_MOVES move history from earlier in the series, and we implemented a per-slot current occupancy and elapsed-time view on TODAY with color coding.

Key points:

  • We used the YARD sheet to collect only active slots as dashboard targets.
  • We read TRAILER_MOVES and reduced it to one latest move per slot to infer current status.
  • We calculated elapsed time in hours from occupancy start time to the current time.
  • We wrote that data into a defined region of TODAY and colored rows by thresholds (2/4 hours) so the longest-staying trailers stand out.
  • We protected TODAY from concurrent updates using LockService so overlapping executions don’t scramble the view.
  • Each test function includes structure and type checks so that if someone changes the sheet layout, you get immediate, explicit errors instead of silent misbehavior.

The next concrete steps you can take:

  1. On the TODAY sheet, pick a blank area near the bottom and set DASH_YARD_CONFIG.VIEW_START_ROW to that starting row.
  2. Paste all the code from this post into the same Apps Script project used for the earlier dashboard parts.
  3. Run DASH_testUpdateYardStatusView().

If you’re already writing move logs from Check-in Part 5, you should immediately see a yard board where each slot shows which container is there and for how many hours, with colors mapping to urgency. After that, just add a time-based trigger to run every 5–10 minutes, and a single monitor will give you continuous visibility into dwell time across the yard.

More in this category

Google Sheets bulk appointment booking: finish with one LockService and setValues call
2026-09-01 Excel & Automation

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

When you look for ways to bulk‑register appointments in Google Sheets, you often hit this: “validation is fast enough, but actually writing to the sheet is painfully slow.” In operations like inbound appointment booking, where dozens or hundreds of rows pile up daily, you really want to paste the carrier’s list once and process everything in one go. In the previous post…

Google Sheets bulk booking validation: check everything in one paste (final)
2026-08-31 Excel & Automation

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

If you’re looking for a way to bulk‑validate bookings in Google Sheets, this post lays it out. When you paste multiple bookings into a sheet, you often burn time just figuring out which row is failing and why. Especially when you paste an inbound booking list copied from Excel: one row with a bad date format can block the whole batch…

Google Sheets Late-Arrival Alert Auto Email: Inbound KPI 5
2026-08-30 Excel & Automation

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

If an inbound truck is late and nobody notices, the on-site plan falls apart immediately. This post walks through how to set up Google Sheets late-arrival alert auto emails, plus an email automation that sends a summary of tomorrow’s inbound appointments in advance. With just Google Sheets and Apps Script, you can bundle late-arrival alert emails and a “tomorrow’s appointments” preview so the manager can see at a glance, from their inbox, which time slots need attention.

Google Sheets weekly KPI email: KPI Pt.4 final code
2026-08-29 Excel & Automation

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

People looking for automatic weekly KPI report emails from Google Sheets are usually in a similar situation: every Monday morning, they copy last week’s inbound appointment KPI into Excel, aggregate carrier scores, and email the report around. It’s something a person can do, but too much of a waste to repeat every week…