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

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

Intro: when you want to see “which row fails and why” at a glance

When you paste multiple bookings into Google Sheets, you often spend all your time just figuring out which row is failing and why. Especially when you paste an inbound booking list copied from Excel: if one row has a bad date format and that blocks the entire batch, you’ll hear complaints on the floor right away. Guidance like “row 3 is the problem, so check rows 1–50 manually” basically doesn’t work in real operations.

This post tackles that head‑on. Using Apps Script, we’ll implement a Google Sheets bulk booking validation method that, when you paste multiple lines at once, checks errors per row and immediately shows “which row fails and why.” The goal is to keep the inbound booking list copied from Excel in memory only and build a validation layer that checks everything at once before anything is actually written to the sheet.

When you have to paste an entire inbound booking Excel file you received from site operations, a “one‑by‑one form input” approach can’t keep up with constant plan changes. If bad bookings go straight through, dock and manpower planning gets tangled; but you also don’t want a single bad row to force users to re‑enter all the good rows from scratch. That’s why I designed a separate structure where “the system screens everything once after paste, before saving.”

In this part we’ll implement up through:

• Taking multiple rows copied from Excel into an in‑memory rows array
• Having validateBulkSlots(rows):
• check for required‑field omissions
• date/time formats
• business hours range
• capacity (quota) overflow
• container duplicates among the rows just pasted
• and return “which row fails and why” in the shape { ok, errors[], cleanedRows[] }.

The parts that actually write to the sheet and control concurrency with LockService will be covered in the next post. This one focuses on completing just the validation layer.

Paste‑mode settings: decide what and how you’ll validate

Before building bulk validation, you need to decide one thing: if even one row has an error, will you block the entire batch, or will you save only the rows that pass? Different warehouses have different philosophies, but from real‑world use I’ve found the following staged approach is practical:

1. In the validation stage:

• Read all rows through to the end
• Return a list of which rows passed and which have what errors

At this stage, nothing is written to the sheet. The operator can look at this result and decide whether to “fix the error rows and re‑submit everything” or “go ahead and apply only the rows that passed.”

1. In the save stage:

• Acquire a LockService lock
• Re‑check capacity and duplicates
• Actually append rows to the main booking sheet

Only here do you branch behavior like “if any row fails, roll back the entire batch” vs. “save only the rows that pass.”

To express this in code, we’ll gather the column positions and policy values needed for bulk validation into a single config object. To avoid name collisions with other code branches, we’ll prefix them with BULK_. The actual name of your main booking sheet will differ by environment; here we’ll just use tutorial‑style placeholder naming.

One important caveat: the allowed equipment‑type list must exactly match BOOK_CAPACITY_CONFIG.VALID_TYPES from the previous parts. That list has only two values, ['CONTAINER', 'TRAILER'], and if you invent another allowed list here, capacity calculations and aggregates from the earlier parts will no longer line up. So in this post’s config we keep the allowed list exactly as is.

We’ll reuse the same capacity/booking logic from these posts:

• Inbound booking entry, part 3 — capacity calculation and save
• Inbound booking entry, part 4 — booking confirmation and validation

This post follows the same contracts as the already‑created helpers like BOOK_getCapacityForType_() and BOOK_getSeqUsageForSlotCore_().

Step 1 — define bulk validation config constants

This code defines the paste‑mode column mapping and policy values used by bulk validation.

Where to paste: Google Sheets → Extensions → Apps Script → Code.gs (or near the top of your existing file)

After pasting: just save; no need to run anything yet.

// Bulk validation settings. If needed, change only this section to fit your environment.
const BULK_CONFIG = {
  COLUMN_MAP: {                  // Paste column mapping (0-based, array indices)
    date: 0,                     // 0th: date
    startTime: 1,                // 1st: start time
    endTime: 2,                  // 2nd: end time
    type: 3,                     // 3rd: equipment type
    door: 4,                     // 4th: door
    containerNo: 5,              // 5th: container number
    carrier: 6,                  // 6th: carrier
    client: 7,                   // 7th: client
    remark: 8,                   // 8th: remark
    qty: 9,                      // 9th: quantity
    pallet: 10                   // 10th: number of pallets
  },

  // Whether to block the entire batch at the "save" stage if any single row has an error.
  // In the validation stage, this is ignored; we always scan all rows and return the full error list.
  REQUIRE_ALL_OR_NOTHING: true,

  // Allowed equipment types — must be identical to BOOK_CAPACITY_CONFIG.VALID_TYPES from earlier parts.
  // Do not add values other than ['CONTAINER', 'TRAILER'] as an extra allow‑list.
  VALID_TYPES: ['CONTAINER', 'TRAILER']
};

We don’t define a separate constant for the main booking sheet name here. The core series already uses the APPT_MAIN sheet for capacity and booking calculations, so the actual save stage will also use that sheet. This part is validation‑only, so where we save to will be handled in the next part’s “bulk save” function based on APPT_MAIN.

How to check it’s fine: if saving the script doesn’t produce errors, you’re good. Later functions will be able to reference BULK_CONFIG without issues.

Core idea: get per‑row error reasons with validateBulkSlots

Now we’ll build the key function for this post: validateBulkSlots(rows). This function takes a 2D array from outside, performs validation only, and does not write anything to the sheet.

Its role:

1. Receive a 2D rows array (each inner array is one booking)
2. For each row, check:

• required fields
• date/time formats
• holiday vs. business days and business‑hours range
• capacity (quota) overflow
• container duplicates among the rows just pasted

1. Return the final result as { ok, errors[], cleanedRows[] } where:

• ok: whether the batch passed (errors.length === 0)
• errors: an array of { rowIndex, message }
• cleanedRows: an array of only the rows that passed, in cleaned form (used by the next part’s save logic)

It’s important that we reuse the helper functions built in earlier parts exactly as they were:

• APPT_ymd_(value) → returns 'YYYY-MM-DD' as a string.
• Throws an exception for nonexistent dates, time‑only values, and numbers less than 1
• Throws for blank cells and empty strings as well
• Defined in Inbound booking basics, part 5
• BOOK_timeToMinutes_(value) → converts a 'HH:mm' time string to integer minutes.
• Returns null instead of throwing if the format is invalid
• Defined in Inbound booking entry, part 1
• getDateBlockInfo(date) → returns operating/holiday info for the date as {date, isHoliday, holidayType, open, close, blocks[]}.
• Definition and examples are also in Inbound booking entry, part 1
• BOOK_getSeqUsageForSlotCore_(dateObj, timeObj, type) → returns {time, equipType, used, capacity, remaining} with current usage vs. remaining capacity.
• Defined in Inbound booking entry, part 3

If you assume different contracts for these functions, the code may “silently malfunction,” so make sure your definitions match exactly what’s in the earlier published posts. In this post we do not re‑define these helpers; we assume they’re already in your project and call them as‑is.

Step 2 — implement validateBulkSlots

This code validates all pasted booking rows at once, collects per‑row error reasons, and returns only the rows that pass in cleaned form.

Where to paste: at the bottom of Code.gs, under your existing code

After pasting: save, then run the BULK_testValidateSlots_() test function created in the next section.

/**
 * Validates multiple booking rows in one go.
 * @param {Array<Array<*>>} rows 2D array (each inner array is one booking)
 * @return {{ok: boolean, errors: Array<{rowIndex: number|null, message: string}>, cleanedRows: Array<Object>}}
 */
function validateBulkSlots(rows) {
  const result = { ok: true, errors: [], cleanedRows: [] };

  if (!Array.isArray(rows) || rows.length === 0) {
    result.ok = false;
    result.errors.push({
      rowIndex: null,
      message: 'No rows to validate.'
    });
    return result;
  }

  const seenContainers = new Set();       // Check for duplicates only within this paste
  const col = BULK_CONFIG.COLUMN_MAP;

  for (let i = 0; i < rows.length; i++) {
    const row = rows[i];
    const displayRow = i + 1;             // 1-based row number shown to the user

    try {
      if (!row || row.length === 0) {
        // Completely empty rows are skipped; APPT_ymd_ would throw on blanks anyway.
        continue;
      }

      // 1) Extract raw values
      const rawDate = row[col.date];
      const rawStartTime = row[col.startTime];
      const rawEndTime = row[col.endTime];
      const rawType = row[col.type];
      const rawDoor = row[col.door];
      const rawCntr = row[col.containerNo];
      const rawCarrier = row[col.carrier];
      const rawClient = row[col.client];
      const rawRemark = row[col.remark];
      const rawQty = row[col.qty];
      const rawPallet = row[col.pallet];

      // 2) Required field checks
      if (!rawDate) {
        throw new Error(displayRow + ' row: missing date');
      }
      if (!rawStartTime) {
        throw new Error(displayRow + ' row: missing start time');
      }
      if (!rawEndTime) {
        throw new Error(displayRow + ' row: missing end time');
      }
      if (!rawType) {
        throw new Error(displayRow + ' row: missing equipment type');
      }
      if (!rawCntr) {
        throw new Error(displayRow + ' row: missing container number');
      }

      // 3) Normalize date/time formats
      // Reuses APPT_ymd_ from [Inbound booking basics, part 5].
      const ymd = APPT_ymd_(rawDate);   // 'YYYY-MM-DD' string
      if (!ymd) {
        // APPT_ymd_ throws for invalid dates, so this branch rarely executes.
        throw new Error(displayRow + ' row: invalid date format');
      }

      const startTimeStr = String(rawStartTime).trim();
      const endTimeStr = String(rawEndTime).trim();

      // Reuses BOOK_timeToMinutes_ from [Inbound booking entry, part 1].
      const startMin = BOOK_timeToMinutes_(startTimeStr);
      if (!Number.isFinite(startMin)) {
        throw new Error(displayRow + ' row: invalid start time format');
      }

      const endMin = BOOK_timeToMinutes_(endTimeStr);
      if (!Number.isFinite(endMin)) {
        throw new Error(displayRow + ' row: invalid end time format');
      }

      if (endMin <= startMin) {
        throw new Error(displayRow + ' row: end time must be later than start time');
      }

      // 4) Business day / business hours checks
      // Reuses getDateBlockInfo from [Inbound booking entry, part 1].
      const blockInfo = getDateBlockInfo(ymd);
      if (!blockInfo) {
        throw new Error(displayRow + ' row: no operating info for this date');
      }

      // FULL / CLOSED = holiday, PARTIAL = bookable
      if (blockInfo.holidayType === 'FULL' || blockInfo.holidayType === 'CLOSED') {
        throw new Error(displayRow + ' row: bookings are not allowed on holidays');
      }
      if (!blockInfo.open || !blockInfo.close) {
        throw new Error(displayRow + ' row: business hours not set for this date');
      }

      const openMin = BOOK_timeToMinutes_(blockInfo.open);
      const closeMin = BOOK_timeToMinutes_(blockInfo.close);
      if (!Number.isFinite(openMin) || !Number.isFinite(closeMin)) {
        throw new Error(displayRow + ' row: invalid business hours setting');
      }

      // Both start and end must be within business hours.
      if (startMin < openMin || startMin >= closeMin) {
        throw new Error(displayRow + ' row: start time is outside business hours');
      }
      if (endMin <= openMin || endMin > closeMin) {
        throw new Error(displayRow + ' row: end time is outside business hours');
      }

      // 5) Equipment type allow‑list check
      const typeKey = String(rawType).trim().toUpperCase();
      if (!BULK_CONFIG.VALID_TYPES.includes(typeKey)) {
        throw new Error(displayRow + ' row: disallowed equipment type');
      }

      // 6) Capacity check
      // To avoid time‑zone bugs, split 'YYYY-MM-DD' into year/month/day manually.
      const m = /^(\d{4})-(\d{2})-(\d{2})$/.exec(ymd);
      if (!m) {
        throw new Error(displayRow + ' row: date parsing error');
      }
      const year = Number(m[1]);
      const month = Number(m[2]); // 1–12
      const day = Number(m[3]);

      const dateObj = new Date(year, month - 1, day); // local midnight
      // BOOK_getSeqUsageForSlotCore_ only needs a “slot‑level” time; we use start time.
      const timeObj = new Date(year, month - 1, day, Math.floor(startMin / 60), startMin % 60);

      const usage = BOOK_getSeqUsageForSlotCore_(dateObj, timeObj, typeKey);
      if (!usage || !Number.isFinite(usage.remaining)) {
        throw new Error(displayRow + ' row: failed to load capacity info');
      }
      if (usage.remaining <= 0) {
        throw new Error(displayRow + ' row: slot capacity exceeded');
      }

      // 7) Container duplicate check within this paste
      const keyCntr = String(rawCntr).trim().toUpperCase();
      if (seenContainers.has(keyCntr)) {
        throw new Error(displayRow + ' row: duplicate container in pasted list');
      }
      seenContainers.add(keyCntr);

      // 8) Quantity / pallet validity
      const qtyNum = (rawQty === '' || rawQty == null) ? 1 : Number(rawQty);
      if (!Number.isFinite(qtyNum) || qtyNum <= 0) {
        throw new Error(displayRow + ' row: invalid quantity');
      }

      const palletNum = (rawPallet === '' || rawPallet == null) ? 0 : Number(rawPallet);
      if (!Number.isFinite(palletNum) || palletNum < 0) {
        throw new Error(displayRow + ' row: invalid pallet count');
      }

      // 9) Store the passing row in cleaned form
      result.cleanedRows.push({
        // Date/time/type/container use validated/normalized values; other fields are trimmed.
        date: ymd,
        startTime: startTimeStr,
        endTime: endTimeStr,
        type: typeKey,
        door: rawDoor ? String(rawDoor).trim() : '',
        containerNo: keyCntr,
        carrier: rawCarrier ? String(rawCarrier).trim() : '',
        client: rawClient ? String(rawClient).trim() : '',
        remark: rawRemark ? String(rawRemark).trim() : '',
        qty: qtyNum,
        pallet: palletNum
      });

    } catch (e) {
      // Capture row‑level errors and continue with other rows.
      result.ok = false;
      result.errors.push({
        rowIndex: displayRow,
        message: e && e.message ? e.message : String(e)
      });

      // Even if REQUIRE_ALL_OR_NOTHING is true, we do NOT stop here in the “validation” stage.
      // The goal is to show all issues across the batch; we’ll enforce all‑or‑nothing at the “save” stage.
    }
  }

  result.ok = result.errors.length === 0;
  return result;
}

How to check it’s fine: run the test function below and check that deliberately bad values appear in errors as messages like “n row: …”, and that rows that pass end up only in cleanedRows.

Step 3 — verify edge cases with a test function

Validation logic is much safer to verify using actual data than by code inspection. Cases like NaN slipping in from putting characters in a quantity field only show up in production if you don’t test them. So it’s worth building a small sample array with intentionally bad values and running validateBulkSlots against it in one shot.

We’ll try these six cases in one array:

1. Row 1: valid
2. Row 2: empty start time → “2 row: missing start time”
3. Row 3: invalid end time format → “3 row: invalid end time format”
4. Row 4: end time earlier than start time → “4 row: end time must be later than start time”
5. Row 5: disallowed equipment type → “5 row: disallowed equipment type”
6. Row 6: 'ABC' in quantity → “6 row: invalid quantity”

That’s enough to see required fields, time format, time ordering, allow‑list, and quantity validation all working.

Where to paste: right under the validateBulkSlots function

After pasting: in the Apps Script editor, choose BULK_testValidateSlots_ from the function dropdown and run it.

/**
 * Tests validateBulkSlots behavior.
 * - Row 1: valid
 * - Row 2: missing start time → "missing start time"
 * - Row 3: invalid end time format → "invalid end time format"
 * - Row 4: end before start → "end time must be later than start time"
 * - Row 5: invalid equipment type → "disallowed equipment type"
 * - Row 6: non-numeric quantity → "invalid quantity"
 */
function BULK_testValidateSlots_() {
  const sampleRows = [
    // Date         Start    End      Type        Door  Container  Carrier    Client    Remark Quantity Pallets
    ['2026-08-30', '09:00', '10:00', 'CONTAINER', 'D1', 'C001',   'CARRIER1', 'CLIENT1', '', 1,       0],   // Row 1: valid
    ['2026-08-30', '',       '11:00', 'CONTAINER', 'D2', 'C002',   'CARRIER2', 'CLIENT2', '', 1,       0],   // Row 2: missing start time
    ['2026-08-30', '11:00',  'aa:bb', 'CONTAINER', 'D3', 'C003',   'CARRIER3', 'CLIENT3', '', 1,       0],   // Row 3: invalid end time format
    ['2026-08-30', '13:00',  '12:00', 'CONTAINER', 'D4', 'C004',   'CARRIER4', 'CLIENT4', '', 1,       0],   // Row 4: end < start
    ['2026-08-30', '14:00',  '15:00', 'XYZ',       'D5', 'C005',   'CARRIER5', 'CLIENT5', '', 1,       0],   // Row 5: invalid type
    ['2026-08-30', '16:00',  '17:00', 'CONTAINER', 'D6', 'C006',   'CARRIER6', 'CLIENT6', '', 'ABC',   0]    // Row 6: non-numeric quantity
  ];

  const result = validateBulkSlots(sampleRows);
  Logger.log(JSON.stringify(result, null, 2));

  if (result.ok) {
    Logger.log('All rows passed. Number of passing rows: ' + result.cleanedRows.length);
  } else {
    Logger.log('Number of error rows: ' + result.errors.length);
    result.errors.forEach(function (err) {
      Logger.log(err.rowIndex + ' row error: ' + err.message);
    });
  }
}

How to check it’s fine: after running, open Execution log and check that you see messages roughly like:

• 2 row error: 2 row: missing start time
• 3 row error: 3 row: invalid end time format
• 4 row error: 4 row: end time must be later than start time
• 5 row error: 5 row: disallowed equipment type
• 6 row error: 6 row: invalid quantity

Also, only row 1 (the valid case) should appear in cleanedRows.

Practical tips from using this bulk validation in production

Here are some lessons learned from running this structure in real warehouses:

1. Always separate validation from saving.

If you try to validate, save, and roll back all inside a single function, operators can’t see clearly where things failed. Having a function like validateBulkSlots that “only checks in memory and returns results” and a separate function that acquires a LockService lock and actually saves rows is easier to explain and much better for incident analysis.

1. Run capacity and duplicate checks in both validation and save stages.

Another user might book the same slot between your validation and save steps. The validation stage answers “does this data make sense in principle?”, while the save stage re‑answers “is there actually space left right now?” Capacity calculation itself is centralized in [Inbound booking entry, parts 3 and 4], via BOOK_getSeqUsageForSlotCore_() and BOOK_checkCapacityAndSave_(). This post just calls those functions as‑is.

1. For error messages, it’s helpful to standardize on row number + plain‑language reason.

Messages like "5 row: bookings are not allowed on holidays" let on‑site operators see what to fix and where from a single screenshot. Something abstract like INVALID_TIME might be convenient for developers but always needs extra explanation for real users.

Common errors and how to fix them

Some frequent issues when first wiring in the bulk validation code:

1. APPT_ymd_ is not defined

This helper is the date normalization function from earlier parts. You’ll see this if you haven’t pasted that code into the project yet, or if you renamed it somewhere.

• Make sure the APPT_ymd_ definition from [Inbound booking basics, part 5] is present in this project
• Confirm you didn’t change its name

1. Every date says “bookings are not allowed on holidays”

Usually this is because getDateBlockInfo() is getting a date in the wrong format and defaulting to “holiday.”

• Confirm you’re passing APPT_ymd_(rawDate) directly, as in this post
• Check that you’re not converting it into some other format in between
• For testing, use dates that are actually configured as business days

1. Characters in quantity or pallets slip through validation

A naive check like Number(rawQty) <= 0 will quietly let NaN pass. In this code we explicitly use:

   !Number.isFinite(qtyNum) || qtyNum <= 0

so letters, blanks, and mixed formats all become “invalid quantity.” The test function already includes a character quantity case; verify in the log that it’s caught.

1. Subtle bugs from time zones

A common anti‑pattern is new Date(ymd + 'T00:00:00'). Depending on environment, this is parsed in UTC and may shift the date by one day. In this post we strictly use:

   const m = /^(\d{4})-(\d{2})-(\d{2})$/.exec(ymd);
   const dateObj = new Date(year, month - 1, day);
   const timeObj = new Date(year, month - 1, day, hour, minute);

i.e. we split year/month/day into numbers and use the new Date(year, month-1, day, …) constructor.

Wrap‑up

In this post we completed the first step of a Google Sheets bulk booking validation setup: an Apps Script function validateBulkSlots(rows) that checks every row of an inbound booking list copied from Excel before anything touches the sheet.

We now validate, per row:

• required fields
• date/time formats
• start/end relationship
• business hours
• capacity
• container duplicates within the pasted batch
• quantity and pallet validity

and we return error messages in the form “which row fails and why.”

Here’s what you can do right now:

1. Paste the three blocks — BULK_CONFIG, validateBulkSlots, and BULK_testValidateSlots_ — into your Apps Script project
2. Run the test function
3. In the execution log, confirm that you see messages like:

• “2 row: missing start time”
• “3 row: invalid end time format”
• “4 row: end time must be later than start time”
• “5 row: disallowed equipment type”
• “6 row: invalid quantity”

If that all looks correct, you’re ready for the next step: a bulk insert function that actually writes only the passing rows to the main booking sheet (APPT_MAIN). In the next post we’ll cover that save stage, apply LockService, and wire everything up to the real booking sheet.