Smart Life US
Google Sheets dock doors auto-create: 30 doors, 50 yard slots
Introduction: Why are dock doors and yard slots still typed by hand?
When you build an inbound appointment system in Google Sheets, you always need a list of dock doors and yard slots. Yet on the floor, this “simple” list is still often entered manually, one by one. With just 30 doors and 50 yard slots, typing all the numbers makes it easy to skip or mistype something. This post walks through a Google Sheets dock door auto-generation method to remove that repetitive work.
In the previous post, Build the sheet structure for a Google Sheets inbound appointment system — Basics, Part 1, we created the base sheet structure for appointments and a settings sheet. In this Part 2, on top of that structure we’ll use Apps Script to auto-generate 30 dock doors (D01–D30) and 50 yard slots (Y01–Y50) and prepare functions that read only “ACTIVE” doors/slots plus test functions you can later reuse in your appointment logic. With a single run you seed the data, and the test logs let you verify the result immediately.
There’s one more important point. The scripts in this series are meant to be used inside a single Apps Script project in the end. In Build the sheet structure for a Google Sheets inbound appointment system — Basics, Part 1 we already had an onOpen, so if you simply paste this post’s code into the same project, the onOpen function names will collide. To avoid that:
- Move the actual menu-adding code into a helper function called
APPT_addSeedMenu_(menu), and - Keep only one
onOpenin the whole project, and inside it callAPPT_addSeedMenu_(menu)along with the other menu helpers from each part.
When you see the onOpen code below, if you already created onOpen in Part 1, do not add a new function declaration; just merge in a single line in the body: APPT_addSeedMenu_(menu);. At the end of the post there’s a short paragraph summarizing how to merge the two onOpen functions.
Why you should manage dock doors and yard slots in Google Sheets
In real warehouses running inbound appointments, dock door and yard slot information often lives in separate Excel files, on paper, or in someone’s head. In that state, even if you move appointments into a “system,” it’s easy for the field to get out of sync with the data. Centralizing in Google Sheets is key for stable automation.
First, dock doors and yard slots need to be organized as a table. Typically, door number, description (location, notes, etc.), and active flag are enough for most inbound appointment operations. It’s better for maintainability to start minimal with only what you actually use rather than adding lots of unused columns.
Second, it’s important to fix your numbering convention from day one. If you standardize to two digits like D01–D30 and Y01–Y50, sort, filter, and pivot reports won’t scramble the order. In the field, operations often start with mixed values like D1, D2, D10 and later have to be cleaned up manually. With code-based generation, you can enforce a consistent pattern at once.
Third, you should be able to adjust the active status directly in the sheet. If a certain door is under construction or a yard slot is reserved long-term, you just set its ACTIVE value to FALSE. Appointment logic can then be written to read only active slots. The listActiveDoors_() and listActiveYardSlots_() functions in this post do exactly that, and they’re designed to work even if the ACTIVE column uses plain TRUE/FALSE text instead of checkboxes.
Once your dock and yard data is in Google Sheets, you can reuse the same master data for appointment screens, dashboards, and notifications. Even if your warehouse is already running, defining this structure now will reduce transition costs later when you integrate with a WMS or TMS.
Define sheet structure with APPT-specific constants
All Apps Script code in this series will live in a single project. To avoid constant and function name collisions, we’ll manage them with an APPT_ prefix. We’ll assume you’re already using the shared helpers from the previous part (like getOrCreateSheet_()), and in this post we’ll only define the constants needed for the dock door and yard slot sheets.
These constants are referenced repeatedly in the “build inbound appointment system with Google Sheets” posts. If the sheet names or headers ever change, you only need to edit the constants in one place, which makes production maintenance much easier.
1) This code groups the sheet names and header definitions for dock doors and yard slots.
2) Paste location: Google Sheets → Extensions → Apps Script → near the top of Code.gs, next to your other settings.
3) After pasting: just Save (⌘S or Ctrl+S).
const APPT_SHEET_NAME_DOORS = 'DOORS'; // → Dock door sheet name
const APPT_SHEET_NAME_YARD = 'YARD'; // → Yard slot sheet name
const APPT_DOOR_HEADERS = [ // → Dock door header list
'DOOR_ID', // → Door code (D01 etc.)
'DESCRIPTION', // → Description / location notes
'ACTIVE' // → Active flag
];
const APPT_YARD_HEADERS = [ // → Yard slot header list
'YARD_ID', // → Yard code (Y01 etc.)
'DESCRIPTION', // → Description / location notes
'ACTIVE' // → Active flag
];How to check: There’s no visible change yet because we haven’t added functions. As long as the script saves without errors, move on.
Auto-create 30 dock doors: APPT_seedDockDoors30
Now we’ll create an Apps Script function that generates 30 dock doors (D01–D30) at once. This function creates the DOORS sheet if needed, initializes it with a fixed structure, then fills in IDs and ACTIVE values. Even if there’s existing data, it calls both clearContents() and clearFormats() so everything is wiped and rewritten; any previous descriptions or ACTIVE values will be overwritten. In production, keep that in mind before rerunning it.
Because the function edits a whole sheet, it uses LockService to serialize runs so that if multiple users click the menu at once, execution happens in sequence instead of overlapping. Note that locking doesn’t block repeated runs; it just prevents them from running simultaneously.
1) This code creates/initializes the DOORS sheet and writes D01–D30 with ACTIVE=true.
2) Paste location: Apps Script editor → Code.gs → under the constants you just defined.
3) After pasting: save, then in the function dropdown choose APPT_seedDockDoors30 → Run → grant permissions the first time.
function APPT_seedDockDoors30() { // → Create 30 doors
const lock = LockService.getScriptLock(); // → Script lock
lock.waitLock(30000); // → Wait up to 30 sec
try { // → Error handling
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sheet = getOrCreateSheet_( // → Create sheet if missing
ss,
APPT_SHEET_NAME_DOORS, // → DOORS sheet name
APPT_DOOR_HEADERS // → Header row definition
);
sheet.clearContents(); // → Clear all contents
sheet.clearFormats(); // → Clear all formats
sheet.getRange(1, 1, 1, APPT_DOOR_HEADERS.length) // → Header row range
.setValues([APPT_DOOR_HEADERS]); // → Write headers
const rowCount = 30; // → 30 doors
const data = []; // → Data array
for (let i = 1; i <= rowCount; i++) { // → Loop 1–30
const id = 'D' + String(i).padStart(2, '0'); // → Code like D01
data.push([id, '', true]); // → ID, description, active
}
sheet.getRange(2, 1, data.length, data[0].length) // → Data range
.setValues(data); // → Write data
sheet.autoResizeColumns(1, APPT_DOOR_HEADERS.length);// → Auto-fit columns
} finally { // → Regardless of errors
lock.releaseLock(); // → Release lock
}
}How to check: Back in the spreadsheet, confirm that a DOORS sheet exists. Row 1 should show DOOR_ID / DESCRIPTION / ACTIVE, and rows 2–31 should contain D01–D30 with ACTIVE all TRUE. Later, if you want to temporarily remove a door from use, just change that row’s ACTIVE to FALSE.
Auto-create 50 yard slots: APPT_seedYardSlots50
Next we’ll auto-generate 50 yard slots (Y01–Y50). The pattern is the same as for doors; only the sheet name and header constants differ. This function also wipes the YARD sheet and rewrites it, so any existing descriptions or ACTIVE values will be lost on rerun; back them up if needed.
1) This code creates/initializes the YARD sheet and writes Y01–Y50 with ACTIVE=true.
2) Paste location: Apps Script editor → Code.gs → right under APPT_seedDockDoors30.
3) After pasting: save, then run APPT_seedYardSlots50 from the function dropdown.
function APPT_seedYardSlots50() { // → Create 50 yard slots
const lock = LockService.getScriptLock(); // → Script lock
lock.waitLock(30000); // → Wait up to 30 sec
try { // → Error handling
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sheet = getOrCreateSheet_( // → Create sheet if missing
ss,
APPT_SHEET_NAME_YARD, // → YARD sheet name
APPT_YARD_HEADERS // → Header row definition
);
sheet.clearContents(); // → Clear all contents
sheet.clearFormats(); // → Clear all formats
sheet.getRange(1, 1, 1, APPT_YARD_HEADERS.length) // → Header row range
.setValues([APPT_YARD_HEADERS]); // → Write headers
const rowCount = 50; // → 50 yard slots
const data = []; // → Data array
for (let i = 1; i <= rowCount; i++) { // → Loop 1–50
const id = 'Y' + String(i).padStart(2, '0'); // → Code like Y01
data.push([id, '', true]); // → ID, description, active
}
sheet.getRange(2, 1, data.length, data[0].length) // → Data range
.setValues(data); // → Write data
sheet.autoResizeColumns(1, APPT_YARD_HEADERS.length);// → Auto-fit columns
} finally { // → Regardless of errors
lock.releaseLock(); // → Release lock
}
}How to check: In the spreadsheet, confirm that a YARD sheet exists. Row 1 should show YARD_ID / DESCRIPTION / ACTIVE, and rows 2–51 should contain Y01–Y50 with ACTIVE all TRUE. To temporarily exclude a slot, set its ACTIVE to FALSE.
Read only “ACTIVE” doors: listActiveDoors_ and test function
Once you’ve seeded door and yard lists, you need a way to read only the usable entries in appointment logic. In practice, doors are often pulled out for construction, repair, or long-term use. If you only toggle ACTIVE in the sheet and keep the code rule as “only pick rows where ACTIVE is true,” operations stay simple.
listActiveDoors_() is a utility that reads the DOORS sheet and returns an array of door IDs where ACTIVE is considered TRUE. Because ACTIVE might be a checkbox (boolean TRUE/FALSE) or a text value like 'TRUE', it normalizes both. You don’t call this directly from a menu; instead, we’ll add a APPT_testListActiveDoors test function so you can inspect the results via logs.
1) This code returns an array of DOOR_ID values where ACTIVE is TRUE.
2) Paste location: Apps Script editor → Code.gs → under the two seed functions.
3) After pasting: save, then run APPT_testListActiveDoors and check the execution logs.
function listActiveDoors_() { // → List active doors
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sheet = ss.getSheetByName(APPT_SHEET_NAME_DOORS);// → Find DOORS sheet
if (!sheet) { // → If sheet is missing
throw new Error('The DOORS sheet does not exist.'); // → Error message
}
const lastRow = sheet.getLastRow(); // → Last row number
if (lastRow < 2) { // → No data
return []; // → Return empty array
}
const range = sheet.getRange(2, 1, lastRow - 1, 3); // → ID–ACTIVE range
const values = range.getValues(); // → Read values
const activeIds = []; // → Result array
for (let i = 0; i < values.length; i++) { // → Loop rows
const row = values[i]; // → Current row
const id = row[0]; // → DOOR_ID
const active = row[2]; // → ACTIVE value
if (!id) { // → If no ID
continue; // → Skip
}
const normalized = (typeof active === 'boolean') // → Boolean check
? active // → Use as-is
: String(active).toUpperCase() === 'TRUE'; // → Handle 'TRUE' text
if (normalized) { // → If active
activeIds.push(id); // → Add ID
}
}
return activeIds; // → Active door list
}
function APPT_testListActiveDoors() { // → Test function
const doors = listActiveDoors_(); // → Get active doors
Logger.log(JSON.stringify(doors)); // → Log result
}How to check: In the Apps Script editor, select APPT_testListActiveDoors and run it. Open the execution log; you should see something like ["D01","D02",...,"D30"]. Change some ACTIVE values in the DOORS sheet to FALSE or to text 'TRUE'/'true', run again, and confirm that only doors evaluated as TRUE appear.
Read only “ACTIVE” yard slots: listActiveYardSlots_ and test
Yard slots are handled just like doors. listActiveYardSlots_() reads the YARD sheet and returns an array of YARD_IDs where ACTIVE evaluates to TRUE. Later you’ll use this for things like Google Sheets yard slot auto-fill for dropdowns and auto-assignment logic in your appointment UI.
1) This code returns an array of YARD_ID values where ACTIVE is TRUE.
2) Paste location: Apps Script editor → Code.gs → under listActiveDoors_().
3) After pasting: save, then run APPT_testListActiveYardSlots and check the logs.
function listActiveYardSlots_() { // → List active yard slots
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sheet = ss.getSheetByName(APPT_SHEET_NAME_YARD); // → Find YARD sheet
if (!sheet) { // → If sheet is missing
throw new Error('The YARD sheet does not exist.'); // → Error message
}
const lastRow = sheet.getLastRow(); // → Last row number
if (lastRow < 2) { // → No data
return []; // → Return empty array
}
const range = sheet.getRange(2, 1, lastRow - 1, 3); // → ID–ACTIVE range
const values = range.getValues(); // → Read values
const activeIds = []; // → Result array
for (let i = 0; i < values.length; i++) { // → Loop rows
const row = values[i]; // → Current row
const id = row[0]; // → YARD_ID
const active = row[2]; // → ACTIVE value
if (!id) { // → If no ID
continue; // → Skip
}
const normalized = (typeof active === 'boolean') // → Boolean check
? active // → Use as-is
: String(active).toUpperCase() === 'TRUE'; // → Handle 'TRUE' text
if (normalized) { // → If active
activeIds.push(id); // → Add ID
}
}
return activeIds; // → Active yard list
}
function APPT_testListActiveYardSlots() { // → Test function
const slots = listActiveYardSlots_(); // → Get active yard slots
Logger.log(JSON.stringify(slots)); // → Log result
}How to check: Run APPT_testListActiveYardSlots and open the execution log; you should see ["Y01","Y02",...,"Y50"]. Change some ACTIVE values to FALSE or text 'TRUE', rerun, and confirm that only active ones appear.
Run everything from a menu button
Shared onOpen menu + APPT_addSeedMenu_ helper
In day-to-day use, it’s much more natural to click a menu in the sheet than to open the script editor and pick functions. In this post we’ll create a shared top-level menu called “예약 도구” (“Appointment tools”) and add menu items for auto-generating dock doors and yard slots.
One important caveat:
- Part 1 already defined an
onOpen, and - This post also shows an
onOpen.
If you put both into the same Apps Script project as-is, you’ll get an onOpen duplicate definition conflict.
To avoid that, use this structure:
- Have exactly one shared
onOpenfunction in the whole project. - Each post (module) provides a helper like
APPT_addSeedMenu_(menu)instead of its ownonOpen. - In the shared
onOpen, call all helpers in order — includingAPPT_addSeedMenu_(menu)from this part.
If you already have an onOpen from Part 1, do not paste the full onOpen below. Just add the APPT_addSeedMenu_(menu) function, and inside your existing onOpen, insert a call to APPT_addSeedMenu_(menu);. If you don’t have any onOpen yet, you can use the full example below as-is.
1) This code adds a “예약 도구” menu on open with items to auto-create dock doors and yard slots.
2) Paste location: Apps Script editor → Code.gs
- If a shared
onOpenalready exists: only addAPPT_addSeedMenu_(menu)and call it from your existingonOpen. - If no shared
onOpenexists yet: add bothonOpenandAPPT_addSeedMenu_(menu)below.
3) After pasting: save and reload the sheet to see the menu.
// Shared onOpen example (there must be only ONE in the project):
function onOpen() { // → Runs when sheet opens
const ui = SpreadsheetApp.getUi(); // → UI object
const menu = ui.createMenu('Reservation tool'); // → Appointment tools menu
// Call sub-menu helpers from each part in the series
// Example: if Part 1 defined a common menu helper, call it here as well
// APPT_addCommonMenu_(menu); // ← Example from Part 1 (use actual function name)
APPT_addSeedMenu_(menu); // → Add this part’s menu
menu.addToUi(); // → Show menu
}
// Menu helper for this post (door/yard seeding)
function APPT_addSeedMenu_(menu) { // → Define seeding menu
menu.addItem('Create 30 doors', 'APPT_seedDockDoors30'); // → Generate 30 doors
menu.addItem('Create 50 yards', 'APPT_seedYardSlots50'); // → Generate 50 yards
}Summary: How to merge with Part 1’s onOpen
- If Part 1 already has
function onOpen() { ... }, do not add anotheronOpenfrom this post.
Delete the duplicate from this file and keep only APPT_addSeedMenu_(menu).
- Then, inside the existing
onOpenfrom Part 1, where you’re building themenuobject, add one line:
APPT_addSeedMenu_(menu);.
- Example structure:
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('Reservation tool');
APPT_addCommonMenu_(menu); // Common menu from Part 1
APPT_addSeedMenu_(menu); // Door/yard seeding menu from Part 2
menu.addToUi();
}How to check: After reloading the sheet, you should see the “예약 도구” menu with “도어30개 생성” and “야드50개 생성” items. Clicking them should create or reset the DOORS and YARD sheets and refill the lists.
Practical tips: handling reseeding, concurrent runs, and errors safely
When these scripts are used in a live warehouse, unintentional reruns or concurrent runs can cause surprises. A few precautions help avoid disruptions:
First, be very clear that reseeding wipes existing descriptions and ACTIVE values.
APPT_seedDockDoors30 and APPT_seedYardSlots50 call clearContents() on the entire sheet and rewrite it. That means doors or yard slots manually set to inactive and any notes in DESCRIPTION will be lost. Even if you only want to fix numbering, consider the impact. If notes are important, copy them to another sheet before reseeding.
Second, Locks keep data from being corrupted when two people click the menu at once.
Both seed functions use LockService.getScriptLock().waitLock(30000). That ensures that even if two users trigger the same function at nearly the same time, they’ll run sequentially. The second run still executes and rewrites the sheet; the lock only prevents simultaneous execution, not repeated seeding.
Third, keep ACTIVE values consistent as TRUE/FALSE, whether via checkbox or text.
These examples default to boolean true and, when reading, treat both booleans and the text 'TRUE' as active. In real data where plain text may already exist, this allows you to gradually convert to checkboxes without breaking logic.
Fourth, use error messages to verify initial setup.
If the DOORS or YARD sheet is missing and you run APPT_testListActiveDoors or APPT_testListActiveYardSlots, the code will explicitly throw an error. In that case, just run the seed function first to create the sheet, then rerun the test. For operators new to Apps Script, these small test functions help show clearly “how far the setup has gone.”
Once you’re comfortable with Apps Script, LockService, and basic error handling, you can extend the same pattern to appointment records, history logging, and automatic backups. For more on those topics, it helps to read it alongside something like Google Sheets Apps Script error handling and backups | LockService, try/catch, DriveApp backup.
Conclusion
This post showed how to use Apps Script in Google Sheets to auto-generate 30 dock doors and 50 yard slots, and how to read only active doors and yard slots with testable helper functions. Turning the task of entering dozens of door/slot IDs from manual typing into a one-click action makes both initial setup and later layout changes much faster.
By separating onOpen into a shared menu function and the APPT_addSeedMenu_(menu) helper in this post, you can safely merge it with the code from Part 1 in a single project without name collisions. Keep just one shared onOpen, and add APPT_add○○Menu_(menu) helpers from each part; that pattern will keep things manageable as you add Parts 3, 4, and beyond.
The next actionable step is simple: open the Google Sheets workbook you’re using for inbound appointments, paste in APPT_seedDockDoors30 and APPT_seedYardSlots50, and run them from the menu. Once you see DOORS and YARD populate automatically, you’ll have a solid base to design your appointment UI and auto-assignment logic. In the next part, we’ll use these door and yard lists to add logic that auto-selects and validates doors/yard slots when an appointment is entered.
