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

Google Sheets yard slot auto-assign & move log — Check-in 5

Intro: why you need Google Sheets yard slot auto-assignment

People who search for a Google Sheets yard slot auto-assignment method usually want to manage “which yard slots are empty and how full things are” in near real time when trucks bunch up. This post is the fifth in the Check-in series and continues from Google Sheets check-in status auto-change with Apps Script — Check-in 4, where we built the code that saves check-ins and flips their status. Here we’ll use LockService to automate assigning trailer yard slots in Google Sheets, and in the same flow, record move history as well.

In real operations, yard slots get messy fast. When shifts change, what’s written on the whiteboard and what’s actually parked often don’t match. In many cases there’s no record of who ordered a move or to which slot, so later you can’t calculate dwell time. In this post, based on real operating experience, we’ll walk through how to auto-assign one yard slot with Google Sheets and Apps Script, reduce slot collisions with LockService while saving, and stack movement logs in a TRAILER_MOVES sheet. Due to the code structure, this only mitigates slot duplication: avoiding duplicate container assignments or “blocking” empty numbers still requires separate rules.

Post-check-in flow: why yard slot auto-assignment matters

The overall series flow is “create booking → assign door → save check-in → auto-change status.” If the door is free as planned, you can send a trailer straight there. In reality, door queues often get long. In that case, you park the trailer in the yard first, then pull it to a door later. Even when using a Google Sheets–based check-in system, many sites still manage only this yard segment with whiteboards and radios, and that’s where the data breaks.

At a logistics center I worked with, at first we only systemized door assignment. Yard slots were just “Y01–Y40” printed out and stuck to the wall with magnets. In peak season, it was hard to tell which slots were actually empty or how long a given trailer had been sitting. Every meeting we’d say, “yard dwell is long,” but had no hard data to back it up. To ease this, we wired in a structure that, at check-in, auto-assigns “door if possible, otherwise yard.” As a result, we could analyze slot-level turn, average dwell, and door-wait patterns.

The structure implemented in this post is:

First, keep the slot list, current container number, and active flag in a YARD sheet.

Second, use CHK_pickFreeYardSlot_() to pick the “first free slot from the top” inside a LockService lock and write in the container number.

Third, write “GATE → Yxx” style move logs into a TRAILER_MOVES sheet so later you can reconstruct paths by slot or trailer.

Once you connect this flow with the check-in save logic from the previous post, you get the basic yard visibility the floor needs day to day — alongside your existing WMS, not instead of it.

YARD sheet structure and yard slot selection rules

To implement yard slot auto-assignment in Google Sheets, you first need a clear YARD sheet structure and a definition of “empty slot.” In this post we’ll assume simple rules:

• Column A: Slot ID (Y01, Y02, etc.)
• Column B: Current container/trailer number
• Column C: Active flag (Y/N)
• From row 2, one slot per row

A slot is “assignable” only if the container cell is completely empty and the active flag is blank or Y.

In real sites you may divide slots by zone or by temperature class (refrigerated/ambient). In that case, add more columns for zone codes, temp class, etc., and add conditions in the selection function. But if you try to code every rule from day one, testing becomes painful. It’s better to start with the simplest rule—like “single zone, first-free order”—and iterate. At my site, we started with this simple model and then added conditions as actual issues surfaced.

Another important point is how the team actually uses the YARD sheet. For example, the C-column active flag is often maintained manually. In this code, N means “definitely inactive,” and blank or Y means “active,” so it will be included in assignment candidates. If people start mixing other values, the code and reality diverge. It’s safer to agree as a team that the active flag is strictly Y or N. With that basic agreement, you can actually get the collision reduction benefit from LockService.

Yard slot auto-assignment with LockService

Now for the actual yard slot auto-assignment function, CHK_pickFreeYardSlot_(). It has four jobs:

1. Read the active slots from the YARD sheet and collect candidate indices where the container cell is empty.
2. Use LockService.getScriptLock() to grab a lock and reduce cases where two users pick the same slot at the same time.
3. While holding the lock, read the sheet again, re-check that a slot is still free, and then write in the container number.
4. Return the selected slot ID on success, or null if no suitable slot exists.

Operationally, it’s safest to assume “two trucks can check in at the same time.” LockService doesn’t guarantee 100% elimination of duplicates, but it dramatically reduces chances that two people grab the same slot simultaneously.

Note that this function does not fully validate container numbers or prevent container-level double occupancy. For example, if you call it twice with the same container number, you could end up occupying two different slots. Those constraints—like “a container may only be in one slot/door at a time”—need to be enforced in operational rules or higher-level logic.

Step 1 — define yard-related constants

This code groups yard-related settings and sheet names.

In Google Sheets: Extensions → Apps Script → in your existing check-in project, paste it at the top of Code.gs (next to the other CHK constants).

After pasting, just save.

const CHK_YARD_CONFIG = {                     // → Check-in yard settings
  YARD_SHEET_NAME: 'YARD',                    // → YARD sheet name
  YARD_COL_SLOT: 1,                           // → Slot ID column (A)
  YARD_COL_CONTAINER: 2,                      // → Current container/trailer column (B)
  YARD_COL_ACTIVE: 3,                         // → Active flag column (C)
  MOVE_SHEET_NAME: 'TRAILER_MOVES',           // → Move log sheet name
  MOVE_COL_TS: 1,                             // → Timestamp column (A)
  MOVE_COL_DATE: 2,                           // → Base date column (B)
  MOVE_COL_CNTR: 3,                           // → Container/trailer number column (C)
  MOVE_COL_FROM: 4,                           // → From-location column (D)
  MOVE_COL_TO: 5,                             // → To-location column (E)
  MOVE_COL_NOTE: 6                            // → Note column (F)
};                                            // → End of constants

How to check it: if the Apps Script editor shows no red errors and no “duplicate declaration” error for existing constants, it’s fine.

Step 2 — function to pick one free yard slot

This code finds a free slot in the YARD sheet and writes the container number under a LockService lock.

In Google Sheets: Extensions → Apps Script → paste at the very bottom of Code.gs (under the other CHK functions).

Save, then run CHK_testPickFreeYardSlot_() once to check the result.

function CHK_pickFreeYardSlot_(containerNo) {                // → Pick a free yard slot
  const ss = SpreadsheetApp.getActiveSpreadsheet();          // → Current spreadsheet
  const sheet = ss.getSheetByName(CHK_YARD_CONFIG.YARD_SHEET_NAME);  // → Get YARD sheet
  if (!sheet) {                                              // → If sheet not found
    throw new Error('YARD sheet not found');                 // → Throw error
  }

  const lastRow = sheet.getLastRow();                        // → Last row number
  if (lastRow < 2) {                                         // → Header only
    return null;                                             // → No free slot
  }

  const range = sheet.getRange(2, 1, lastRow - 1, 3);        // → Range A2:C
  const values = range.getValues();                          // → Read data

  const freeIndexes = [];                                    // → Free slot index list
  for (let i = 0; i < values.length; i++) {                  // → For each row
    const row = values[i];                                   // → Current row
    const slotId = String(row[CHK_YARD_CONFIG.YARD_COL_SLOT - 1]).trim();          // → Slot ID
    const currentCntr = String(row[CHK_YARD_CONFIG.YARD_COL_CONTAINER - 1]).trim(); // → Current container
    const activeFlag = String(row[CHK_YARD_CONFIG.YARD_COL_ACTIVE - 1]).trim();     // → Active flag
    if (!slotId) {                                           // → If no slot ID
      continue;                                              // → Skip
    }
    if (activeFlag && activeFlag.toUpperCase() === 'N') {    // → If inactive (N)
      continue;                                              // → Skip
    }
    if (!currentCntr) {                                      // → If container is empty
      freeIndexes.push(i);                                   // → Add as free slot candidate
    }
  }

  if (freeIndexes.length === 0) {                            // → No candidates
    return null;                                             // → No free slot
  }

  const lock = LockService.getScriptLock();                  // → Get script lock
  let slotId = null;                                         // → Selected slot ID
  try {
    lock.waitLock(5000);                                     // → Wait up to 5s
    const freshRange = sheet.getRange(2, 1, lastRow - 1, 3); // → Read range again
    const freshValues = freshRange.getValues();              // → Latest data
    for (let idx of freeIndexes) {                           // → Loop free candidates
      const row = freshValues[idx];                          // → Current row
      const slot = String(row[CHK_YARD_CONFIG.YARD_COL_SLOT - 1]).trim();          // → Slot ID
      const currentCntr = String(row[CHK_YARD_CONFIG.YARD_COL_CONTAINER - 1]).trim(); // → Current container
      const activeFlag = String(row[CHK_YARD_CONFIG.YARD_COL_ACTIVE - 1]).trim();     // → Active flag
      if (!slot) {                                           // → If no slot ID
        continue;                                            // → Skip
      }
      if (activeFlag && activeFlag.toUpperCase() === 'N') {  // → If inactive slot
        continue;                                            // → Skip
      }
      if (currentCntr) {                                     // → Already occupied
        continue;                                            // → Skip
      }
      slotId = slot;                                         // → Choose this slot
      freshValues[idx][CHK_YARD_CONFIG.YARD_COL_CONTAINER - 1] = containerNo || ''; // → Record container
      break;                                                 // → Break loop
    }

    if (!slotId) {                                           // → No slot found in the end
      return null;                                           // → No free slot
    }

    freshRange.setValues(freshValues);                       // → Write changes
    return slotId;                                           // → Return selected slot ID
  } finally {
    lock.releaseLock();                                      // → Release lock
  }
}                                                            // → End of function

function CHK_testPickFreeYardSlot_() {                       // → Test function
  const testContainer = 'TEST-CNTR-001';                     // → Sample container number
  const slotId = CHK_pickFreeYardSlot_(testContainer);       // → Run slot pick
  Logger.log('Selected slot: ' + slotId);                    // → Log result
  if (!slotId) {                                             // → If selection failed
    Logger.log('No free slot');                              // → Log notice
  }
}                                                            // → End of test function

How to check it: with column B (container) of the YARD sheet cleared, run CHK_testPickFreeYardSlot_(). If the log shows Selected slot: Y01 (or similar) and column B of that row is set to TEST-CNTR-001, it’s working.

Move-history sheet and CHK_appendTrailerMove_ implementation

Auto-assigning yard slots tells you “where it is now,” but not “when and where it moved.” The easiest way to automate move history in Google Sheets is to keep a dedicated TRAILER_MOVES sheet and append one row per move. After implementing this at a real site, we could see average move time from yard to door over a period and what times of day yard rotation got clogged.

The TRAILER_MOVES sheet records six basics:

• Timestamp (recorded time)
• Base date (booking date or operating date)
• Container/trailer number
• From-location
• To-location
• Note

Use codes like GATE, Y01, DOCK-03 for from/to locations, and simple tags like CHECKIN, TO_DOOR or a check-in ID in the Note column to make later filters and pivot analysis easy. For dates, we reuse the APPT_ymd_() from the previous post to standardize on YYYY-MM-DD.

Step 3 — create move log sheet and append-one-row function

This code auto-creates the move log sheet and appends one row per move.

In Google Sheets: Extensions → Apps Script → paste at the very bottom of Code.gs.

Save, then run CHK_testAppendTrailerMove_() to confirm that the log is being written.

function CHK_getOrCreateMoveSheet_() {                           // → Get or create move log sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();              // → Current spreadsheet
  let sheet = ss.getSheetByName(CHK_YARD_CONFIG.MOVE_SHEET_NAME); // → Find sheet
  if (!sheet) {                                                  // → If not found
    sheet = ss.insertSheet(CHK_YARD_CONFIG.MOVE_SHEET_NAME);     // → Create new
    const headers = [                                            // → Header array
      'TIMESTAMP',                                               // → Record time
      'DATE',                                                    // → Base date
      'CONTAINER',                                               // → Container/trailer
      'FROM',                                                    // → From-location
      'TO',                                                      // → To-location
      'NOTE'                                                     // → Note
    ];
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]); // → Write headers
    sheet.setFrozenRows(1);                                      // → Freeze header row
  }
  return sheet;                                                  // → Return sheet
}                                                                 // → End of function

function CHK_appendTrailerMove_(dateYmd, containerNo, fromLoc, toLoc, note) { // → Append move log
  if (!dateYmd || !containerNo || !fromLoc || !toLoc) {          // → Validate required fields
    throw new Error('Required fields for move log are missing'); // → Throw error
  }

  const tz = 'America/New_York';                                 // → Fixed time zone
  const now = new Date();                                        // → Current time

  // APPT_ymd_() is the date formatter used in the previous check-in post
  const dateStr = APPT_ymd_(dateYmd);                            // → YYYY-MM-DD string

  const sheet = CHK_getOrCreateMoveSheet_();                     // → Ensure log sheet
  const lastRow = sheet.getLastRow();                            // → Last row
  const targetRow = lastRow + 1;                                 // → Row to write

  const row = [];                                                // → Row data (0-based index)
  row[CHK_YARD_CONFIG.MOVE_COL_TS - 1]   = Utilities.formatDate(
    now,
    tz,
    'yyyy-MM-dd HH:mm:ss'
  );                                                              // → Record time
  row[CHK_YARD_CONFIG.MOVE_COL_DATE - 1] = String(dateStr).trim();              // → Base date
  row[CHK_YARD_CONFIG.MOVE_COL_CNTR - 1] = String(containerNo).trim();          // → Container
  row[CHK_YARD_CONFIG.MOVE_COL_FROM - 1] = String(fromLoc).trim();              // → From-location
  row[CHK_YARD_CONFIG.MOVE_COL_TO - 1]   = String(toLoc).trim();                // → To-location
  row[CHK_YARD_CONFIG.MOVE_COL_NOTE - 1] = note ? String(note).trim() : '';     // → Note

  // Row length will naturally cover up to MOVE_COL_NOTE (=6), so write 6 columns at once
  sheet.getRange(targetRow, 1, 1, 6).setValues([row]);           // → Write to sheet

  return {                                                       // → Return result
    ok: true,                                                    // → Success flag
    row: targetRow                                               // → Written row number
  };
}                                                                 // → End of function

function CHK_testAppendTrailerMove_() {                          // → Test function
  const today = new Date();                                      // → Today
  const dateStr = APPT_ymd_(today);                              // → YYYY-MM-DD
  const res = CHK_appendTrailerMove_(                            // → Add move log
    dateStr,                                                     // → Base date
    'TEST-CNTR-002',                                             // → Container number
    'GATE',                                                      // → From-location
    'Y01',                                                       // → To-location
    'Test move'                                                  // → Note
  );
  Logger.log(JSON.stringify(res));                               // → Log result
}                                                                 // → End of test function

How to check it: when you run CHK_testAppendTrailerMove_() from Apps Script, a TRAILER_MOVES sheet should be created with a row of test data at row 2. If the sheet already exists, a new row should be appended below existing data.

Wire yard auto-assignment and move logs into the check-in save logic

Now we need to connect the check-in save function from the previous post with this yard assignment and move log logic. The idea is simple: at check-in, if the reservation’s door is free, send the trailer to the door. If not, or if you’ve chosen a “yard first” policy, auto-assign a yard slot. It’s handy to bundle this into a helper like CHK_assignYardOnCheckin_() so saveCheckInData() just calls it and looks at the result.

In production we used rules like:

1. If the booking already has a door assigned, skip yard assignment.
2. Only call the helper when the booking has no door or the check-in operator chose a “yard first” option.
3. On successful yard assignment, write FROM='GATE', TO='Yxx' to TRAILER_MOVES.
4. When later moving from yard to door, call the same CHK_appendTrailerMove_() to log Yxx → DOCK-03.

Step 4 — helper to call yard assignment and move logging at check-in

This code takes a validated appt object and, when appropriate, auto-assigns a yard slot and writes a move log.

In Google Sheets: Extensions → Apps Script → paste at the very bottom of Code.gs.

Save, then create a simple test appt object and run CHK_assignYardOnCheckin_(appt, {}).

function CHK_assignYardOnCheckin_(appt, checkinData) {           // → Yard-assign helper at check-in
  const containerNo = String(appt.containerNo || '').trim();     // → Container from booking
  if (!containerNo) {                                            // → If missing
    return { ok: false, reason: 'NO_CONTAINER' };                // → Do nothing
  }

  const currentDoor = String(appt.door || '').trim();            // → Current door
  if (currentDoor) {                                             // → Already has door
    return { ok: false, reason: 'HAS_DOOR' };                    // → Skip yard assignment
  }

  const dateYmd = appt.date;                                     // → Booking date (YYYY-MM-DD)

  const yardSlot = CHK_pickFreeYardSlot_(containerNo);           // → Pick yard slot
  if (!yardSlot) {                                               // → No free slot
    return { ok: false, reason: 'NO_YARD_SLOT' };                // → Return failure
  }

  const note = 'CHECKIN';                                        // → Simple tag in Note
  const moveRes = CHK_appendTrailerMove_(                        // → Write move log
    dateYmd,                                                     // → Base date
    containerNo,                                                 // → Container number
    'GATE',                                                      // → From-location
    yardSlot,                                                    // → To-location (yard slot)
    note                                                         // → Note
  );

  return {                                                       // → Combined result
    ok: true,                                                    // → Success
    yardSlot: yardSlot,                                          // → Assigned slot
    moveRow: moveRes.row                                         // → Move log row number
  };
}                                                                 // → End of function

How to check it: in the script editor, choose CHK_assignYardOnCheckin_ as the run function, then in the debugger, pass appt a value like {"date":"2026-08-21","containerNo":"TEST-CNTR-003","door":""} and execute. If one slot in the YARD sheet is filled with TEST-CNTR-003 and TRAILER_MOVES gets a GATE → that slot row, it’s working as intended.

When you actually wire this into saveCheckInData(), call this helper after the check-in save succeeds, and only show a front-end message like “Send trailer to yard slot Yxx” when ok is true. If the save fails, skip yard assignment.

Practical tips: clearing slots and checking failures

Finally, here are some practical tips from running this “Google Sheets yard slot auto-assignment + move log” in a live warehouse.

1. Define a clear slot-clearing rule

If you only build assignment and never define “when to clear,” slots fill up quickly. In practice, you usually clear column B (container) when moving from yard to door or when the trailer exits. Build a separate “clear slot” function that also uses LockService to “read → validate → clear” in one shot to reduce clashes when two people try to clear the same slot.

1. Validate container numbers at check-in

It’s better to reject blank or invalid container numbers in higher-level check-in logic. CHK_pickFreeYardSlot_() as written just uses whatever you pass. If you pass an empty string, the slot will be recorded as empty again, and on the next call the function may reassign the same slot. Operationally, treat container number as required and block check-in save when it’s missing.

1. Accept small mismatches between logs and sheet state

Even if Apps Script runs without error, results aren’t always “perfect.” For example, if CHK_appendTrailerMove_() fails mid-way, the yard slot might already have the container recorded, but no move log exists. With Google Sheets–based systems, it’s realistic to accept this level of mismatch and build a periodic reconciliation report comparing TRAILER_MOVES and YARD. The key is being explicit about what’s guaranteed automatically and what still relies on manual checks.

1. Reflect site-specific constraints and扩and gradually

This structure is just one example; every yard is different. Large yards with zones or tight security will have extra constraints. Start with a small test spreadsheet, run the functions from this post, and then progressively encode rules your team already agrees on. That’s much safer than trying to model the entire operation from day one.

Wrap-up

We covered how to get yard slot auto-assignment and move logging in Google Sheets by:

• Designing the YARD sheet structure and defining CHK_YARD_CONFIG
• Implementing CHK_pickFreeYardSlot_() for LockService-based slot assignment
• Adding CHK_appendTrailerMove_() and CHK_assignYardOnCheckin_() to log moves

With just this, “check-in → yard slot auto-assign → move log” becomes a single data flow, which is enough foundation to analyze dwell time and slot turn.

To try it right away, first list your actual yard slots in column A of the YARD sheet, paste in the code from this post, and run CHK_testPickFreeYardSlot_(). Once you see a slot auto-selected and the container number written, you can then connect the check-in save logic and TRAILER_MOVES to grow it into your own Google Sheets–based yard management system.