Google Sheets

Five Google Sheets data-validation traps for tracking columns

You set up a clean dropshipping sheet. Dropdowns for status, a tidy refund column, nice colour rules. Then you run your tracking sync and half the rows just don't update. No error popup, no red flag — the cells stay stubbornly blank or keep their old value. Your automation looks broken. It isn't.

Nine times out of ten the culprit is data validation quietly rejecting the writes. Validation rules are great for humans typing into cells and terrible at being invisible to scripts. Here are the five traps that bite tracking columns the hardest, and exactly how to dodge each one.

Trap 1: “Reject input” turns writes into silent no-ops

When you create a dropdown, Sheets defaults to Show a warning on invalid input — but plenty of people switch it to Reject input to keep the column tidy. The problem: a status string your tool writes (say Awaiting Dispatch) has to match an allowed value exactly. If your tool returns Awaiting dispatch with a lowercase d, the write is rejected and the cell keeps whatever was there before.

Validation compares character-for-character. A trailing space, a different capital letter, or a fancy “curly” apostrophe is enough to make a perfectly valid status disappear into the void.

The fix: either set the rule to Show a warning so writes always land, or make sure every status your automation can emit is listed in the dropdown verbatim. Fetch Order Tracking writes a fixed, documented set of status strings, so you can build your dropdown list once and trust it forever.

Trap 2: Number validation on the tracking-ID column

Tracking IDs look numeric, so it's tempting to validate them as numbers or to let Sheets auto-format them. Don't. Carrier IDs routinely mix letters and digits (LX123456789GB, H00ABC123), and even all-digit IDs lose their leading zeros the moment a cell is treated as a number. A 00123456 becomes 123456 and your eBay upload fails.

• Format the tracking column as Plain text before anything writes to it.
• Never apply “is a valid number” validation to ID or order-ID columns.
• Watch out for 16- vs 19-digit AliExpress order IDs — Sheets will happily mangle the long ones into scientific notation.

Trap 3: Checkbox columns that aren't really TRUE/FALSE

The refund column is a classic. You insert a checkbox, which stores a boolean TRUE/FALSE. Then your automation tries to write the word Yes or Refunded — and validation rejects it, because a checkbox cell only accepts its two configured values. The row looks like the refund detection failed when it actually fired correctly.

Pick one model and commit. If you want a human-readable refund state, use a plain text column or a dropdown. If you want a true checkbox, make your automation write the boolean. Mixing the two is the fastest way to a column you can't trust. We make the case for keeping it simple in the argument for binary Yes/No refund columns.

Trap 4: Date validation vs. raw API timestamps

EDD and AE order-date columns are another quiet failure point. AliExpress hands you timestamps as strings — often ISO-style like 2026-05-21T08:14:00 or epoch milliseconds. If your column has “is a valid date” validation set to a strict locale format, the raw string gets rejected and the date never lands.

1. Leave date columns on Automatic or Plain text while automation writes to them.
2. Apply display formatting (not validation) after the value is in the cell.
3. Keep your locale consistent — a UK DD/MM sheet will silently reject a MM/DD string.

Trap 5: Validation ranges that don't cover new rows

You set up validation on A2:A500. Six months later you're at row 900. New orders land in rows the validation never covered, so they behave differently from the rows that do — and when you later extend the range, Sheets re-checks every existing cell and may flag the ones your automation wrote in a format the rule no longer likes. Suddenly half your sheet is wearing little orange warning triangles.

Apply validation to the whole column (A2:A) from day one, or skip cell-level validation on machine-written columns entirely and reserve it for the columns humans actually type into. A spreadsheet that's designed for automation from the start saves you this exact headache — we cover that mindset in a column layout that scales from 100 to 10,000 orders.

The bigger lesson

Data validation is a tool for catching human mistakes. The moment a script or API is doing the writing, every rule becomes a potential silent veto. The safest pattern is to validate the handful of columns people touch, leave the machine-written columns on plain text, and use a fetcher that emits a known, stable set of values.

That last part is exactly why Fetch Order Tracking exists. It syncs eBay and AliExpress straight into the Google Sheet you already own, writing tracking IDs, carriers, statuses, EDDs and refund states in consistent, validation-friendly formats — so “the sheet didn't update” stops being a sentence you ever say again. Build your dropdowns around its outputs once, and your columns just work.

Frequently asked questions

Why does my Google Sheet automation appear to skip rows with no error?

The most common cause is data validation set to Reject input on a column your script writes to. When the value your tool sends does not exactly match an allowed dropdown entry, Sheets silently refuses the write and keeps the old cell value, so nothing visibly fails. Switch the rule to Show a warning, or make sure every value your automation can emit is listed verbatim in the dropdown.

Should I validate my tracking-ID column as a number?

No. Tracking IDs often mix letters and digits, and even all-digit IDs lose leading zeros when a cell is treated as a number, which breaks your eBay upload. Format the tracking and order-ID columns as Plain text before anything writes to them, and never apply is a valid number validation to those columns.

How do I stop validation from breaking when my sheet grows past the validated range?

Apply validation to the entire column using a range like A2:A from the start, rather than a fixed range such as A2:A500 that new orders will eventually fall outside of. Better still, skip cell-level validation on machine-written columns entirely and keep it only on the columns humans type into. Fetch Order Tracking writes consistent, validation-friendly values, so you can build your dropdowns around its outputs once.

Related guides

• The case for binary Yes/No refund columns
• A spreadsheet column layout that scales from 100 to 10,000 orders
• Using Google Sheets as your dropshipping order database

---