Smart Life US

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

Google Sheets Container Check-In: Find Driver Appointments

Google Sheets Container Check-In: Find Driver Appointments

Intro: Replace calls and radios with container-number lookup

If you’re trying to build a driver check-in screen in Google Sheets, you’ve likely already automated inbound appointment booking to some degree, but still rely on phone calls and radios for the actual driver arrival. As you keep asking “What’s the container number?” “What time is your appointment?” the line grows, and the front desk ends up opening the appointment sheet and typing numbers into the search box all day long.

Container check-in lookup flow

To reduce that pain, this post walks through building a backend lookup function in Google Sheets + Apps Script that, given a container number, automatically finds today’s appointment. In other words, we’re building the core logic of the arrival check-in screen that will run on the driver’s phone. The code is structured so you can copy–paste and run it as-is, and I’ll explain where to put it and how to test it in a realistic workflow.

This post assumes you already have the basic inbound appointment structure set up (appointment sheet, SETTINGS, APPT_ymd_, APPT_hm_, loadSettings_, etc.). If not, you should first set up Create the Google Sheets Inbound Appointment System Structure — Basics Part 1 and then come back.

What the check-in lookup function needs to do

From the driver’s perspective, a Google Sheets–based warehouse check-in system is simple: enter the container number and tap [Search]. Everything after that should be handled by the system. The Apps Script check-in lookup logic we’ll build here does the following:

  • Reads the appointment sheet (APPT_MAIN) for today’s date
  • Performs a first-pass validation on the container number format (empty, absurdly short, etc.)
  • Searches today’s appointments for a row that has that container number
  • Returns one of these cases:
  • Exactly 1 match: returns appointment time, door/slot, equipment type, status, etc.
  • 0 matches: returns a reason and message like “No appointment for this number today”
  • 2+ matches: returns a “duplicate appointment” status and message

We’ll also create getInitialDataForCheckIn(), which sends the initial information the check-in screen needs when it first loads (today’s date, notice text, business hours, time zone) in one shot. The actual web UI will be implemented separately with HTML + client-side code; this post focuses on the backend API-style Apps Script functions that UI will call.

Group check-in-related settings: constants for sheet/column positions

In real operations, sheet structure and notices change more often than script logic. If you move a column, rename a sheet, or change the notice text and have to touch code in multiple places each time, bugs are inevitable. To prevent that, we’ll group the values used by check-in into a CHK_CONFIG constant object.

Once that’s in place, if you add warehouses or change the sheet layout later, you’ll only need to update a few lines of config at the top and the entire lookup logic will keep working.

Step 1 — Define check-in config constants

This code collects the sheet name, column indices, and SETTINGS keys used by check-in in one place.

Paste it at the very top of Code.gs in Google Sheets → Extensions → Apps Script, under your existing constants.

After pasting, just save (⌘S or Ctrl+S). No need to run it.

Apps Script (JavaScript)
// These column indices must mean **exactly what the appointment series fixed for APPT_MAIN**:
// A date | B start time | C equipment type | D door | E container | F carrier | G client | H remark
// I end time | J created at | K qty | L pallets | M booking ID - **there is no status column yet.**
// If even one index is off, the lookup searches for a container number in the equipment column.
const CHK_CONFIG = {                               // → Check-in configuration group
  APPT_SHEET_NAME: 'APPT_MAIN',                    // → Appointment main sheet name
  COL_DATE: 1,                                     // → A: appointment date
  COL_TIME: 2,                                     // → B: start time
  COL_EQUIP_TYPE: 3,                               // → C: equipment type
  COL_DOOR: 4,                                     // → D: door / yard slot
  COL_CNTR: 5,                                     // → E: container number
  COL_CARRIER: 6,                                  // → F: carrier
  COL_CLIENT: 7,                                   // → G: client
  SETTINGS_CHECKIN_NOTICE_KEY: 'CHECKIN_NOTICE',   // → Notice text settings key
  SETTINGS_BUSINESS_HOURS_KEY: 'CHECKIN_HOURS'     // → Business hours settings key
};                                                 //

// Do **not** write a time zone string here. Reuse `APPT_TZ` from basics part 5 (America/New_York).
// Otherwise the screen reports one zone while the date is computed in another - and 'today'
// silently becomes the wrong day.

To sanity-check: visually compare APPT_MAIN and these column definitions. If they match your actual layout and the functions that reference this config don’t error, you’re good.

Send initial data: getInitialDataForCheckIn

Instead of re-computing today’s date and fetching notice text every time the check-in screen needs it, it’s cleaner to fetch everything once from the server and reuse it on the front end. That’s what getInitialDataForCheckIn() does.

This function returns four things at once:

  • todayYmd: today’s date in YYYY-MM-DD, via APPT_ymd_()
  • notice: the check-in notice text from the SETTINGS sheet (empty string if not set)
  • businessHours: business hours string (e.g., 09:00~18:00, empty if not set)
  • timezone: the time zone used for check-in - the APPT_TZ value from basics part 5 (America/New_York)

Details like holiday logic are better handled earlier in the reservation/calendar part. In this post, the scope is just basic info to show at the top of the screen.

Step 2 — Initial data provider function

This code returns today’s date, notice text, and business hours in one shot for the check-in web app when it first opens.

Paste it at the very bottom of Code.gs, after your existing functions.

Then save, choose getInitialDataForCheckIn from the dropdown, run it once, and grant permissions.

Apps Script (JavaScript)
function getInitialDataForCheckIn() {                         // → Provide initial data for check-in
  const tz = APPT_TZ;                                         // → Series time zone (America/New_York)
  const now = new Date();                                     // → Current time
  const todayYmd = APPT_ymd_(now);                            // → Convert to 'YYYY-MM-DD'
  const settings = loadSettings_();                           // → Read from SETTINGS sheet

  const noticeKey = CHK_CONFIG.SETTINGS_CHECKIN_NOTICE_KEY;   // → Notice text key
  const hoursKey = CHK_CONFIG.SETTINGS_BUSINESS_HOURS_KEY;    // → Business hours key
  const notice = settings[noticeKey] || '';                   // → Empty string if no notice
  const hours = settings[hoursKey] || '';                     // → Empty string if no hours

  const result = {                                            // → Object to send to UI
    todayYmd: todayYmd,                                       // → Today’s date
    notice: notice,                                           // → Notice text
    businessHours: hours,                                     // → Business hours string
    timezone: tz                                              // → Time zone info
  };

  return result;                                              // → Return to web app
}

To verify: run getInitialDataForCheckIn in the Apps Script editor. If it finishes without error, that’s a first pass. For more detail, add and run this test helper:

Apps Script (JavaScript)
function CHK_testInitialData() {                 // → Test initial data function
  const data = getInitialDataForCheckIn();       // → Call function
  Logger.log(JSON.stringify(data));              // → Log contents
}

Run CHK_testInitialData and check the execution log. If you see something like {"todayYmd":"2026-08-16","notice":...}, it’s working as intended.

Find today’s appointment by container: getApptInfoByCntr

Now for the core of this Google Sheets arrival check-in Apps Script: the lookup function. It takes a container number entered by a driver or floor staff and returns today’s appointment from APPT_MAIN.

The logic:

  1. Trim whitespace and uppercase the input.
  2. If empty or too short, immediately return a format error.
  3. Read the appointment sheet in one batch and loop rows 2 through last.
  4. Normalize the date cell with APPT_ymd_() whether it is a Date, a serial number, or a string, then compare.
  5. Among rows matching today’s date, collect those whose container equals the input.
  6. Based on the number of matches:
  • 0: NOT_FOUND_TODAY
  • 1: OK + details
  • 2+: DUPLICATE_CNTR

In production, you might enforce a stricter pattern (e.g., 4 letters + 7 digits) with regex. Here we keep it to length checks so you can extend it to your own rules later.

Step 3 — Appointment lookup by container number

This code searches for today’s appointment by container number and returns a structured result/reason object.

Paste it directly under getInitialDataForCheckIn in Code.gs.

Then save and use the test function CHK_testGetApptInfoByCntr_ below to verify with real data.

Apps Script (JavaScript)
function getApptInfoByCntr(cntrRaw) {                         // → Find today’s appointment by container number
  const ss = SpreadsheetApp.getActiveSpreadsheet();           // → Current spreadsheet
  const sheet = ss.getSheetByName(CHK_CONFIG.APPT_SHEET_NAME);// → Appointment main sheet
  if (!sheet) {                                               // → Sheet not found
    throw new Error('Could not find the appointment sheet.'); // → Configuration error
  }

  const todayYmd = APPT_ymd_(new Date());                     // → Today as a string

  const cntr = String(cntrRaw || '').trim().toUpperCase();   // → Normalize input
  if (!cntr) {                                                // → Empty input
    return {
      found: false,
      reason: 'EMPTY_CNTR',
      message: 'Please enter a container number.'
    };
  }
  if (cntr.length < 4) {                                      // → Too short to be valid
    return {
      found: false,
      reason: 'INVALID_CNTR',
      message: 'The container number format is not valid.'
    };
  }

  const lastRow = sheet.getLastRow();                         // → Last row index
  if (lastRow < 2) {                                          // → No data rows
    return {                                                  // → Result object
      found: false,                                           // → Not found
      reason: 'NO_DATA_TODAY',                                // → No appointments today
      message: 'There are no appointments registered for today.'
    };
  }

  const lastCol = sheet.getLastColumn();                      // → Last column index
  const rng = sheet.getRange(2, 1, lastRow - 1, lastCol);     // → Read from row 2 through last
  const values = rng.getValues();                             // → 2D array of values

  const matches = [];                                         // → Matching rows for today
  let hasOtherDate = false;                                   // → Exists on other date flag
  const dataErrorRows = [];                                   // → Rows we could not read a date from

  for (let i = 0; i < values.length; i++) {                   // → Loop through rows
    const row = values[i];                                    // → Current row
    const dateCell = row[CHK_CONFIG.COL_DATE - 1];            // → Date cell
    const cntrCell = String(row[CHK_CONFIG.COL_CNTR - 1] || '').trim().toUpperCase(); // → Container
    if (!cntrCell) {                                          // → No container on this row
      continue;                                               // → Nothing to compare
    }
    // **Match the number first.** Reading the date first and skipping on failure means a
    // corrupted date on the very row we want is reported as 'no booking today'.
    if (cntrCell !== cntr) {                                  // → Different container
      continue;                                               // → Skip
    }

    // From here this **is** the row we were looking for. Never skip it silently.
    let rowYmd;                                               // → Row date string
    try {                                                     // → Normalize
      rowYmd = (dateCell instanceof Date)                     // → Date, serial or string
        ? APPT_ymd_(dateCell)                                 // → 'YYYY-MM-DD'
        : APPT_ymd_(String(dateCell).trim());                 // → handles padding too
    } catch (e) {                                             // → Not a date
      dataErrorRows.push(i + 2);                              // → A person must look at it
      continue;                                               //
    }                                                         //

    if (rowYmd === todayYmd) {                                // → This row is for today
      matches.push({                                          // → Add to matches
        rowIndex: i + 2,                                      // → Actual row index in sheet
        row: row                                              // → Entire row data
      });
    } else {                                                  // → Same container, other date
      hasOtherDate = true;                                    // → Mark as existing on other date
    }
  }

  // If exactly one booking was found today, that is definitive. Otherwise, if any row
  // for this container had an unreadable date, do not claim there is no booking.
  if (matches.length !== 1 && dataErrorRows.length > 0) {     // → Cannot decide
    return {
      found: false,
      reason: 'DATA_ERROR',
      message: 'The date on this container\'s booking row could not be read (rows ' +
               dataErrorRows.slice(0, 10).join(', ') + '). Please contact the office.'
    };
  }

  if (matches.length === 0) {                                 // → No match for today
    if (hasOtherDate) {                                       // → But exists on other dates
      return {
        found: false,
        reason: 'OTHER_DATE',
        message: 'This container number has an appointment, but on a different date. Please check the appointment date.'
      };
    }
    return {
      found: false,
      reason: 'NOT_FOUND_TODAY',
      message: 'There is no appointment for today with the entered container number.'
    };
  }

  if (matches.length > 1) {                                   // → Multiple matches found
    return {
      found: false,
      reason: 'DUPLICATE_CNTR',
      message: 'There are multiple appointments for today with this container number. Please check with the office.'
    };
  }

  const match = matches[0];                                   // → Single matching row
  const row = match.row;                                      // → Row data

  const timeCell = row[CHK_CONFIG.COL_TIME - 1];              // → Time cell
  const equipType = row[CHK_CONFIG.COL_EQUIP_TYPE - 1] || ''; // → Equipment type
  const door = row[CHK_CONFIG.COL_DOOR - 1] || '';            // → Door/slot
  const carrier = row[CHK_CONFIG.COL_CARRIER - 1] || '';       // → Carrier
  const client = row[CHK_CONFIG.COL_CLIENT - 1] || '';         // → Client

  let timeStr = '';                                           // → Time as string
  if (timeCell instanceof Date) {                             // → If Date object
    timeStr = APPT_hm_(timeCell);                             // → Convert to 'HH:mm'
  } else if (timeCell) {                                      // → If there is a value
    timeStr = String(timeCell);                               // → Use as string
  }

  return {                                                    // → Success result
    found: true,                                              // → Found
    reason: 'OK',                                             // → Normal
    message: 'Appointment found.',                            // → Message
    data: {                                                   // → Details
      rowIndex: match.rowIndex,                               // → Row index
      date: todayYmd,                                         // → Date
      time: timeStr,                                          // → Time
      container: cntr,                                        // → Container number
      equipType: equipType,                                   // → Equipment type
      door: door,                                             // → Door/slot
      carrier: carrier,                                       // → Carrier
      client: client                                          // → Client
    }
  };
}

To verify, plug in a real container number that exists, plus ones that don’t, using the test helper below. Check that the reason and message fit each case.

Apps Script (JavaScript)
function CHK_testGetApptInfoByCntr_() {         // → Test container lookup
  const existingCntr = 'TESTCNTR1';             // → Replace with a real existing number
  const missingCntr = 'NO_SUCH_CNTR';           // → Non-existent number
  const otherDateCntr = 'OTHER_DATE_CNTR';      // → Number that exists only on another date

  const res1 = getApptInfoByCntr(existingCntr); // → Lookup existing
  const res2 = getApptInfoByCntr(missingCntr);  // → Lookup non-existent
  const res3 = getApptInfoByCntr(otherDateCntr);// → Lookup other-date-only

  Logger.log('EXISTING: ' + JSON.stringify(res1));  // → Log results
  Logger.log('MISSING: ' + JSON.stringify(res2));   // → Log results
  Logger.log('OTHER_DATE: ' + JSON.stringify(res3));// → Log results
}

Run CHK_testGetApptInfoByCntr_ and check the logs:

EXISTING should show found:true, reason:"OK",

MISSING should show found:false, reason:"NOT_FOUND_TODAY",

OTHER_DATE should show found:false, reason:"OTHER_DATE".

If so, it’s behaving as designed.

Common real-world mistakes and checkpoints

From rolling out similar Google Sheets container-number lookup automations at several warehouses, the same issues keep coming up:

First, forgetting the date condition. If you search for a container across the entire sheet without limiting to today, past and future appointments get pulled in, and trucks that shouldn’t be here today look “checked in.” That’s why this code only adds rows to matches when rowYmd === todayYmd, and uses hasOtherDate just to flag that the number exists on other dates.

Second, not validating container format at all. If you allow blanks, single letters like A, or 123 to go through, every “no result” case turns into back-and-forth between driver and desk to re-read the number. Tailor stricter checks (minimum length, alphabet/number pattern) to your local rules to cut down on that friction.

Third, mixed date formats. It’s common to have both true date-type cells and hand-typed string dates. That’s why we branch on instanceof Date and normalize via APPT_ymd_(), while string cells are compared as-is. Ideally, you also standardize how dates are saved in the appointment creation flow. For that, see How to Standardize Date Formats in Google Sheets: Inbound Appointment System Basics Part 5.

Fourth, no policy for duplicate appointments. In principle, a single container should have only one appointment. You should ideally block duplicates at save time. Using LockService and duplicate checks on save is covered in Preventing Duplicate Appointments in Google Sheets: Safely Saving with LockService. Still, legacy data or manual bookings can create duplicates, so the lookup layer explicitly returns DUPLICATE_CNTR so staff can recognize the situation immediately.

Wrap-up: Stabilize the lookup logic first, then build the UI

In this post, we implemented two core backend pieces for Google Sheets container-number check-in:

getInitialDataForCheckIn() and getApptInfoByCntr() in Apps Script.

  • One function is called once when the check-in screen loads, to get today’s date, notice text, and business hours.
  • The other takes a container number from the driver or staff, finds today’s appointment, and returns both structured data and a clear reason code.

Once these are stable, wiring them into a doGet(e)-based web app, recording check-in time/agent/status, and managing concurrent writes with LockService becomes much easier.

A concrete next step: pick a real container number from APPT_MAIN, plug it into CHK_testGetApptInfoByCntr_, and run it. Once you see the correct time, door, and status printed in the logs, you can move on to building the web UI with much more confidence.

More in this category

Google Sheets dock door auto assignment with Apps Script – Booking 5
2026-08-16 Excel & Automation

Google Sheets dock door auto assignment with Apps Script – Booking 5

If you’re looking for a way to automatically assign dock doors in Google Sheets, this post walks through it. When you use Google Sheets to take inbound appointments, there’s always one last manual step left: someone has to look at each booking and assign it to a door. When you manually pick doors based on appointment time, equipment type, and priority, it’s easy to overload D01 during busy hours or double-book the same door at the same time. In real warehouses, “appointments were taken fine, but the door schedule is a mess” is something that can derail an entire day.

Google Sheets inbound booking duplicates: safe saving with LockService
2026-08-15 Excel & Automation

Google Sheets inbound booking duplicates: safe saving with LockService

When you build an inbound booking system on Google Sheets, it works fine up to a point, but once the number of users grows and time slots start overlapping, your data can quickly get messy. In particular, preventing duplicate inbound bookings in Google Sheets is one of the most‑searched topics, and it’s almost mandatory at the production stage. This post builds on the earlier structure and focuses on the final step: how to block duplicates and capacity overruns at save time…

Google Sheets booking capacity: auto slots by time
2026-08-15 Excel & Automation

Google Sheets booking capacity: auto slots by time

If you’re looking for a way to calculate booking capacity in Google Sheets, this post organizes everything in one place. When you run inbound appointment booking for a warehouse, the most frustrating part is that it’s hard to see “how many trucks can we take in the same time window.” If each planner decides by gut feeling, you end up with empty docks on some days and complete gridlock when too many trucks pile into one time slot. This post shows how to solve that with a Google Sheets booking capacity calculator and automatically display remaining capacity by time slot.

Google Sheets booking window: auto limit from today
2026-08-14 Excel & Automation

Google Sheets booking window: auto limit from today

If you’re looking for how to calculate a booking window in Google Sheets, this post lays it out. When you run an inbound booking system on Google Sheets, you quickly hit a problem: people book into already-closed time slots or too far in the future. Especially when carriers or partners use a web app booking screen themselves, they keep choosing time slots after today’s cutoff, or they book dates months ahead. These bookings have to be adjusted on-site, and staff end up spending time on calls to reconfirm schedules.