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

Google Sheets inbound appointment sheet structure — basics 1

Intro: Why the warehouse is tidy, but the dock is still a mess

If you came here searching for how to build an inbound appointment system in Google Sheets, you’re probably someone directly in charge of warehouse or logistics operations. You may already have inventory management and outbound automation somewhat systemized, but the system for managing inbound time slots is often still scattered across Excel files, phone calls, and messengers. This post covers how to build, from scratch, a Google Sheets–based inbound appointment sheet structure that you can immediately copy and use in that situation.

In earlier posts, starting with Build a warehouse inventory management system in Google Sheets | Apps Script automation, I documented how to automate core warehouse tasks with Apps Script: auto inventory aggregation, outbound automation, dashboards, and error handling. That got the internal inventory and outbound flow mostly under control, but in the real world another bottleneck appears at the dock because “when the truck arrives” is still not managed in a structured way. So in this series, we’ll build a separate inbound appointment system step by step. This post is the first basics installment, and the goal is to design the Google Sheets inbound appointment sheet structure in one go so it’s ready for later automation.

Series roadmap: What functions are we designing the structure for?

When you build an inbound appointment system with Google Sheets and Apps Script, the feature list grows quickly. To actually use it in operations, you’ll want at minimum appointment creation, arrival check-in, a live status dashboard, KPI reports, and a carrier portal so it behaves like a real system instead of “just another Excel file.” So this series is split into branches so you can take only the pieces you need and still have a working system.

First, in the “basic setup” branch, we prepare the sheet structure, common settings, and initialization functions like in this post. After that, we’ll expand in order with appointment registration, arrival check-in, live dashboard, KPIs, bulk upload, carrier portal, and late/no‑show handling. For each post, the code is written so you can paste it in without conflicts: constants and function names get prefixes, and the menu structure is kept consistent.

In this post, as the very first step, we’ll create six sheets at once — Reservations, Doors, Yard, Settings, Log, and Overview — and establish the shared settings structure that will be reused in every later installment. If you get this part right during the initial Google Sheets warehouse appointment system setup, you’ll avoid having to rip apart the structure when you add features later.

Base structure design: Why six sheets?

When you build an inbound appointment system in Google Sheets, the first question you hit is “How many sheets should I split this into?” Putting everything on one sheet feels simpler at first, but once you go live, permission management, filter complexity, and performance quickly become a problem. On the other hand, splitting too finely makes it confusing where things live. After trying several patterns for mid‑size warehouses, the most manageable minimum structure for an inbound appointment system turned out to be six sheets:

1. APPT_RESERVATIONS (Reservations sheet)

This is the core. It stores the appointment ID, date and time, carrier, truck plate number, pallet count, and status (booked, checked‑in, completed, no‑show, etc.). Most later automation will operate against this sheet.

1. APPT_DOORS (Doors sheet)

Holds static info such as door number, door type (inbound only / mixed), daily capacity, and operating time windows. Used for calculating door‑level available slots and blocking out maintenance downtime.

1. APPT_YARD (Yard sheet)

Manages yard slots where trailers wait before backing into a door. Tiny warehouses may skip this, but for mid‑size and larger sites the yard easily becomes a bottleneck, so it worked better as a separate sheet.

1. APPT_SETTINGS (Settings sheet)

Stores system‑wide settings as key–value pairs. Things like appointment time slot length (e.g., 30 minutes), daily max inbound pallets, and the allowed check‑in window go here so you don’t have to hard‑code numbers in Apps Script. In later “Apps Script inbound appointment automation” posts, we’ll read these values to make behavior dynamic.

1. APPT_LOG (Log sheet)

Chronologically records key events like appointment creation, updates, cancels, and no‑show processing. This is useful later when you need to see “who changed what and when,” or to use as training material. For more advanced history structures, you can directly extend the approach in Automatically log change history in Google Sheets | Apps Script part 9.

1. APPT_OVERVIEW (Overview sheet)

A view that aggregates appointments, completions, and no‑shows for today and this week. Initially this can be built just with PivotTables and QUERY, and in a later installment we’ll add Apps Script to color‑code and auto‑refresh it.

Splitting this way makes permissions and maintenance far simpler. For example, you can give floor supervisors edit access only to APPT_RESERVATIONS and APPT_YARD, while restricting APPT_SETTINGS to one or two admins. It also clarifies ownership per sheet, which makes it easier to reflect site‑specific operating rules.

Auto‑creating sheets and headers with Apps Script

Now let’s write the code for the initial setup of the Google Sheets warehouse appointment system. The goal is simple: with a single initialization function, create all six sheets, set headers in row 1, and automatically pre‑fill required keys in the settings sheet. Once that’s in place, opening a new site is as easy as copying the spreadsheet and running the initialization function.

We’ll structure this in four parts:

1. Define inbound appointment constants (APPT_CONFIG)
2. Use initApptSystem() to create six sheets and set headers
3. Use setupHeaders_() to format headers on each sheet
4. Use ensureApptSettingsKeys_() to auto‑insert required keys in APPT_SETTINGS

We’ll go through each part with the code, where to install it, and how to verify it.

Step 1 — Define inbound appointment config constants

This code centralizes the sheet names, headers, list of required setting keys, and settings sheet headers needed for the inbound appointment system.

Where to put it: In Google Sheets go to Extensions → Apps Script → at the very top of Code.gs, paste this in.

After pasting, just save (⌘S or Ctrl+S).

const APPT_CONFIG = {                                           // → Inbound appointment–only settings
  SHEET_NAMES: {                                                // → Sheet name collection
    RESERVATIONS: 'APPT_RESERVATIONS',                          // → Reservations sheet
    DOORS: 'APPT_DOORS',                                        // → Door master
    YARD: 'APPT_YARD',                                          // → Yard status
    SETTINGS: 'APPT_SETTINGS',                                  // → Settings sheet
    LOG: 'APPT_LOG',                                            // → Activity log
    OVERVIEW: 'APPT_OVERVIEW',                                  // → Status overview
  },
  RESERVATION_HEADERS: [                                        // -> Appointment sheet headers
    'Date',                                                     // -> A: yyyy-mm-dd
    'Start time',                                               // -> B: hh:mm
    'Equipment type',                                           // -> C: CONTAINER/TRAILER
    'Door',                                                     // -> D: assigned door (blank = pending)
    'Container no.',                                            // -> E: container/trailer number
    'Carrier',                                                  // -> F: carrier name
    'Client',                                                   // -> G: client name
    'Remark',                                                   // -> H: note
    'End time',                                                 // -> I: hh:mm
    'Created at',                                               // -> J: created timestamp
    'Qty',                                                      // -> K: inbound quantity
    'Pallets',                                                  // -> L: pallet count
    'Booking ID',                                               // -> M: unique booking id
  ],
  DOOR_HEADERS: [                                               // → Doors sheet headers
    'Door Number',                                                 // → Door ID
    'Type',                                                     // → Inbound/Mixed, etc.
    'Daily Max Pallets',                                           // → Daily capacity
    'Start Time',                                                 // → Operating start time
    'End Time',                                                 // → Operating end time
    'Active',                                                 // → Active/Inactive
    'Notes',                                                     // → Notes
  ],
  YARD_HEADERS: [                                               // → Yard sheet headers
    'Slot ID',                                                   // → Yard slot ID
    'Status',                                                     // → In use/Empty
    'Reservation ID',                                                   // → Linked appointment ID
    'Vehicle Number',                                                 // → Truck plate number
    'Check-in Time',                                               // → Yard check-in time
    'Exit Time',                                                 // → Yard check-out time
    'Notes',                                                     // → Notes
  ],
  SETTINGS_HEADERS: [                                           // → Settings sheet headers
    'KEY',                                                      // → Setting key
    'VALUE',                                                    // → Value
    'Description',                                                     // → Description
  ],
  SETTINGS_REQUIRED_KEYS: [                                     // → Required setting keys
    'TIME_SLOT_MINUTES',                                        // → Appointment slot length (minutes)
    'DAILY_MAX_PALLETS',                                        // → Max pallets per day
    'CHECKIN_EARLY_MINUTES',                                    // → Early check-in allowed (minutes)
    'CHECKIN_LATE_MINUTES',                                     // → Late check-in allowed (minutes)
    'DEFAULT_DOOR_TYPE',                                        // → Default door type
  ],
  LOG_HEADERS: [                                                // → Log sheet headers
    'Time',                                                     // → Log timestamp
    'User',                                                   // → Operator
    'Action',                                                     // → Action (Create/Update/Cancel, etc.)
    'Reservation ID',                                                   // → Target appointment ID
    'Description',                                                     // → Summary
    'Details',                                                 // → Details
  ],
  OVERVIEW_HEADERS: [                                           // → Overview sheet headers
    'Date',                                                     // → Inbound date
    'Total Reservations',                                               // → Total appointments
    'Total Pallets',                                               // → Total pallets
    'Completed',                                                 // → Completed count
    'No-shows',                                                 // → No-show count
    'Avg. Wait (min)',                                               // → Average waiting time (minutes)
  ],
};

How to check it: After saving, if there are no red underlines on APPT_CONFIG in the editor, you’re good. Nothing changes on the sheet yet.

Step 2 — Sheet helper: getOrCreateSheet_()

This helper returns a sheet if it exists, or creates it if it doesn’t. If you don’t already have a similar helper in another post’s code, use this version.

Where to put it: Below APPT_CONFIG, preferably above other functions for easier management.

function getOrCreateSheet_(ss, name) {                          // → Get or create a sheet
  let sheet = ss.getSheetByName(name);                          // → Look up by name
  if (!sheet) {                                                 // → If not found
    sheet = ss.insertSheet(name);                               // → Create new sheet
  }
  return sheet;                                                 // → Return sheet
}

Step 3 — Initialization function: initApptSystem()

This is the “initial installation” function that creates the six sheets, sets headers in row 1 on each, and sets up headers plus required keys in APPT_SETTINGS. If the sheets already exist, it won’t delete data; it only adds or fixes headers and settings keys.

Where to put it: Paste in the middle or bottom of Code.gs.

After pasting, run initApptSystem once to grant permissions.

function initApptSystem() {                                     // → Initial install for inbound appointment system
  const lock = LockService.getScriptLock();                     // → Lock to prevent concurrent runs
  lock.waitLock(30000);                                         // → Wait up to 30 seconds
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();           // → Current spreadsheet
    const names = APPT_CONFIG.SHEET_NAMES;                      // → Sheet name collection

    const resSheet = getOrCreateSheet_(ss, names.RESERVATIONS); // → Ensure Reservations sheet
    const doorSheet = getOrCreateSheet_(ss, names.DOORS);       // → Ensure Doors sheet
    const yardSheet = getOrCreateSheet_(ss, names.YARD);        // → Ensure Yard sheet
    const setSheet = getOrCreateSheet_(ss, names.SETTINGS);     // → Ensure Settings sheet
    const logSheet = getOrCreateSheet_(ss, names.LOG);          // → Ensure Log sheet
    const ovSheet = getOrCreateSheet_(ss, names.OVERVIEW);      // → Ensure Overview sheet

    // Set headers on each sheet
    setupHeaders_(resSheet, APPT_CONFIG.RESERVATION_HEADERS);   // → Set reservation headers
    setupHeaders_(doorSheet, APPT_CONFIG.DOOR_HEADERS);         // → Set door headers
    setupHeaders_(yardSheet, APPT_CONFIG.YARD_HEADERS);         // → Set yard headers
    setupHeaders_(logSheet, APPT_CONFIG.LOG_HEADERS);           // → Set log headers
    setupHeaders_(ovSheet, APPT_CONFIG.OVERVIEW_HEADERS);       // → Set overview headers
    setupHeaders_(setSheet, APPT_CONFIG.SETTINGS_HEADERS);      // → Set settings sheet headers

    // Fill in required settings keys
    ensureApptSettingsKeys_(setSheet);                          // → Populate missing setting keys

    Logger.log('initApptSystem complete');                      // → Leave execution log
  } finally {
    lock.releaseLock();                                         // → Release lock
  }
}

How to check it: In the editor’s function dropdown, select initApptSystem and click Run. The first time, you’ll see a permissions dialog; approve it as prompted. When it finishes, your spreadsheet should have six sheets named APPT_RESERVATIONS, etc., and each should have headers filled in row 1. If so, it worked.

Step 4 — Header helper: setupHeaders_()

This function writes headers into row 1 on a sheet, makes the header bold, and freezes the top row. If there’s existing data, it leaves row 2 and below untouched.

Where to put it: Directly below the initApptSystem function.

function setupHeaders_(sheet, headers) {                        // → Configure sheet headers
  if (!sheet) return;                                           // → Exit if sheet missing

  const headerRow = 1;
  const headerRange = sheet.getRange(headerRow, 1, 1, headers.length);

  headerRange.setValues([headers]);                             // → Write header row
  headerRange.setFontWeight('bold');                            // → Make text bold

  sheet.setFrozenRows(headerRow);                               // → Freeze first row

  // There may be more existing data columns than headers; leave the rest as-is.
}

How to check it: Run initApptSystem again, then make sure the first row on each sheet is bold and stays pinned at the top when you scroll. If you had existing data, confirm that row 2 and below remain unchanged.

Step 5 — Auto‑fill settings keys: ensureApptSettingsKeys_()

This function checks whether APPT_SETTINGS contains all required keys, and if any are missing it appends them as new rows. It doesn’t overwrite any existing keys, so you can safely rerun the initialization function even after updating VALUEs in production.

APPT_SETTINGS assumes the structure row 1 = headers (KEY, VALUE, 설명), row 2+ = data.

Where to put it: Directly below setupHeaders_.

function ensureApptSettingsKeys_(sheet) {                       // → Ensure required settings keys exist
  if (!sheet) return;                                           // → Exit if sheet missing

  const requiredKeys = APPT_CONFIG.SETTINGS_REQUIRED_KEYS;      // → Required key list
  if (!requiredKeys || requiredKeys.length === 0) return;       // → Nothing to do

  const lastRow = sheet.getLastRow() || 1;                      // → At least row 1 (headers)
  // Read existing KEY values from row 2 downward
  const existingKeys = lastRow > 1
    ? sheet.getRange(2, 1, lastRow - 1, 1)                      // → Only column A
        .getValues()
        .map(row => String(row[0] || '').trim())
        .filter(key => key)
    : [];

  const toAppend = [];
  for (const key of requiredKeys) {                             // → Loop required keys
    if (!existingKeys.includes(key)) {                          // → If missing
      toAppend.push([key, '', '']);                             // → KEY / VALUE / Description (blank)
    }
  }

  if (toAppend.length > 0) {
    sheet.getRange(lastRow + (lastRow === 1 ? 0 : 1), 1, toAppend.length, 3)
         .setValues(toAppend);                                  // → Append blank rows
  }
}

How to check it: Run initApptSystem again, then open the APPT_SETTINGS sheet.

• Row 1 should read KEY | VALUE | 설명.
• Starting from row 2, column A should contain one row each for TIME_SLOT_MINUTES, DAILY_MAX_PALLETS, CHECKIN_EARLY_MINUTES, CHECKIN_LATE_MINUTES, and DEFAULT_DOOR_TYPE.

From here, fill in the VALUE column (B) according to your site’s operating rules. The Description column (C) can be filled in later during operations.

Step 6 — Run from a menu: onOpen and “Appointment tools” menu

Having to open the Apps Script editor every time just to run initApptSystem is inconvenient in real operations. You can add an “Appointment tools → Initial install” menu item when the sheet opens, so you can rerun the structure check with a single click later.

Where to put it: At the very bottom of Code.gs. To avoid conflicts, use this onOpen() only in the project dedicated to inbound appointments (don’t duplicate another onOpen() from other scripts).

function onOpen() {                                             // → Runs when the sheet is opened
  const ui = SpreadsheetApp.getUi();                            // → UI object
  const menu = ui.createMenu('Reservation Tool');                      // → Create menu
  addApptBaseMenu_(menu);                                       // → Add base items
  menu.addToUi();                                               // → Show menu
}

function addApptBaseMenu_(menu) {                               // → Base branch menu
  menu.addItem('Initial Setup', 'initApptSystem');                  // → Initial install button
}

How to check it: Save the script, then reopen the spreadsheet and look at the top menu bar. You should see a “예약 도구” menu, and inside it an “초기 설치” item. Clicking “초기 설치” should run initApptSystem and produce the six sheets, headers, and settings keys as described above.

Practical tips: Things to think about while designing the structure

Once you run a Google Sheets inbound appointment system in a real warehouse, you’ll see that structure design causes more problems (or prevents them) than the code itself. That’s why this installment puts so much emphasis on sheet structure and initialization logic. A few points that proved especially useful in practice:

1. Pick header names that are readable but won’t change often

If you think you might later rename “예약일자” to “입고일자,” it’s safer for code to refer to columns by position (column 1, column 2) rather than by header text. In this post’s code, we never read header labels to drive logic; we only write them into row 1. Later, when we need column references in logic, we’ll manage them via separate constants.

1. Create APPT_SETTINGS as early as possible

Slot length, daily max pallets, and check‑in windows tend to change a lot once you go live. If you hard‑code numbers in code, every change requires a developer or power user. That’s why this post puts SETTINGS_HEADERS, SETTINGS_REQUIRED_KEYS, and ensureApptSettingsKeys_() in place from day one.

1. Use LockService by habit to prevent concurrent corruption

Even initialization functions can misbehave if two people somehow trigger them at the same time, causing messed‑up headers or duplicate sheets. Reservation save/cleanup logic will need more careful locking and validation; we’ll cover that in detail in the later “saving appointments” installment along with LockService, try/catch, and backup patterns. For background, see Google Sheets Apps Script error handling and backup | LockService, try/catch, DriveApp backup.

1. Keep sheet names and code constants logically separated

Use literal sheet names like 'APPT_RESERVATIONS', but always refer to them in code via APPT_CONFIG.SHEET_NAMES.RESERVATIONS. Later, when you merge code from other installments in the series, the APPT_ prefix alone will tell you what belongs to the appointment system, reducing conflicts.

Common errors at this stage and how to fix them

Here are issues practitioners frequently hit during this initial setup, along with how to resolve them quickly on site:

1. Error saying getOrCreateSheet_ is not defined

This can happen if you already had another getOrCreateSheet_() in the project from previous work. Compare what each version does; if they’re equivalent, keep just one and delete the duplicate. If the names differ, you can simply update the function name used inside initApptSystem to match your existing helper.

1. Authorization errors

initApptSystem needs permission to create and edit sheets, so you must go through the Google account authorization flow the first time. Run initApptSystem once from the Apps Script editor to finish approval, then use the “예약 도구 → 초기 설치” menu afterward. If you skip that, you’ll keep seeing the same authorization error from the menu.

1. Starting operations while APPT_SETTINGS values are still empty

This often happens when another team copies the spreadsheet to spin up a new site. To avoid it, add a checklist item that at least TIME_SLOT_MINUTES and DAILY_MAX_PALLETS must be filled in APPT_SETTINGS before go‑live.

Wrap‑up: One concrete task for today — run the init function

For an inbound appointment system, the sheet structure and settings frame matter more than the input form itself. In this post, aiming to build the Google Sheets inbound appointment sheet structure, we defined a six‑sheet setup, shared config constants (APPT_CONFIG), an initialization function (initApptSystem), a helper to auto‑fill settings keys (ensureApptSettingsKeys_), a header helper (setupHeaders_), and onOpen menu integration.

There’s one concrete action you can take today: paste this post’s code into Apps Script in order, save, then in the spreadsheet click “예약 도구 → 초기 설치” once. If that gives you six sheets with headers and settings keys neatly laid out, you’ve already built half of the foundation for your inbound appointment system.

In the next installment, we’ll build on this structure by designing the reservation columns properly, adding basic validation logic, and layering in the safety mechanisms in the save function so each new appointment row is stored reliably.