99% Fewer Data Errors

The correction loop was a systems problem, not a human problem.

Picture one concrete row. Someone pastes an Instagram reel link for “Brand X / Creator Y / June” — say a clean link like instagram.com/reel/AbC123xyz. That row should mean one real post, with a stable machine name for that post (a content ID), ready for reporting. If the link is truncated, points at a profile instead of a reel, or is the same reel already on row 14, reporting is wrong even when the sheet “looks full.”

The old workflow waited for someone downstream to discover the mistake. A reporting team might catch it while building a dashboard. Data engineering might catch it when a join failed. An account team might catch it when numbers didn't match. In the worst case, the client found it first. Then the issue walked backward from team to team until someone found the original row, figured out what the value should have been, corrected every affected copy, and asked the downstream teams to run their work again.

That loop made careful humans look careless. More training or another reminder couldn't solve the main problem, because the interface still accepted the same bad input without protest. The humans doing the entry needed a better environment: one that made the rules visible, checked the value right away, and explained what to change.

Immediate feedback replaces blame with confidence.

Sentry moved the answer beside the action. Back to that reel link: a valid URL gets a clear accepted state and a stable content ID (the short machine name for that post). A truncated or wrong-shaped link gets the correct format written next to the cell. The same reel pasted twice points back to the first row. A missing required field gets a note on the exact cell. Humans no longer had to wait for a reporting team to tell them whether an entry was usable.

What they saw was simple: green when the link worked, red when the format was wrong, orange when the post was already on the sheet, and a field note when a business field failed. That short, private loop — enter, see, correct, continue — taught the rule through the work itself. Confidence came from knowing the system would speak up, not from hoping every value had been remembered perfectly.

Move the check to the earliest responsible boundary.

“Validate early” does not mean place every rule in the browser or block every imperfect row. It means enforce a rule at the first point where the system has enough information to judge it. A URL format can be checked as soon as it is pasted. A duplicate needs a view of the wider sheet. Bulk approval across many campaigns and files is a heavier job and belongs later. Those are different boundaries and should not be forced into one trigger.

Sentry owned the first two boundaries: immediate input guidance and a wider scan of the dataset. A separate later system handled transport, reconciliation, and approval across many links. Keeping those jobs separate mattered. It let the sheet checker stay responsive while the heavier workflow ran on purpose, more slowly.

A social link is a typed record, not just a string.

Later systems do not want the full URL as their only key. They want a reliable name for this post — the same idea as a product SKU or an invoice number. A spreadsheet sees a URL as text. Reporting needs more: which platform (Instagram, TikTok, YouTube, and so on), what kind of content (a short video, a feed post), a canonical content ID, and whether the format is acceptable.

Two links can look different while still pointing to the same post. One link can look fine and still point to a profile page, a share redirect, or a page the downstream collector cannot read. For the Brand X reel, the useful answer is not the whole pasted string. It is something like “Instagram reel, ID AbC123xyz,” so joins and counts refer to one object.

Sentry turns the raw string into a small typed record. It first matches a known URL shape. It then runs a regular expression — a compact pattern that pulls out the part that identifies the content. If both steps pass, the system stores the content ID. If the format is close but wrong, it returns a specific example instead of a generic failure.

The current configuration defines 17 accepted URL shapes across eight platform families. It also supports intentional non-link cases — for example a live event with no public URL yet, entered as a status like “LIVESTREAM,” or an explicit “NO LINK.” Those states get synthetic IDs (generated keys used when a real platform ID does not exist). That keeps an intentional exception distinct from a blank or broken value, so reporting does not treat “we meant no link” the same as “someone forgot.”

Identity is the bridge between validation and reporting.

The extracted ID is more than a pass mark. It gives later systems a compact key for duplicate checks, joins, reconciliation, and audits. That is why a validator should return structured output, not only red or green formatting. The formatting helps the person editing the sheet. The ID helps every system after the editor.

The key must also keep its meaning. In a general multi-platform design, namespace the value with its platform and content type, such as youtube:video:abc123. Sentry’s duplicate index uses the extracted ID itself. That worked for the operating data, but a composite key would better protect against the rare case where two platforms issue the same raw ID.

Give every row one clear state.

Validation becomes hard to reason about when several functions can partly approve the same row. A better design treats evaluation as a state transition. Start with the raw value, analyze it once, assign one URL state, then apply the rules that are relevant to that state.

Separate format, identity, and business rules.

Format asks whether a value has the right shape. Identity asks which real object the value names. Business rules ask whether the rest of the row is complete for this workflow. These checks fail for different reasons and need different messages. A valid Instagram reel can still be a duplicate. A unique reel can still be missing its live date. Not every row needs the same extra fields.

Conditional rules prevent false alarms.

Some posts are paid a fixed fee — one agreed price for that piece of content. Those rows need a dollar amount filled in. Other posts are tracked differently (for example performance-based or affiliate-style arrangements) and should not be forced through the same price field. Sentry’s rules can include a condition that asks whether a rule applies before it runs. A price check can run only for fixed-fee rows. A field rule can depend on the URL already being accepted. That avoids turning optional fields into a wall of red errors.

Conditional validation is a small rules engine. Its value is not the number of operators it supports. Its value is that policy becomes visible and testable. The system resolves rules through stable field IDs, then maps those IDs to the sheet’s actual column headers. A display label can change without silently changing what the rule means — for example the sheet can show “Price per post” while the rule still targets the same logical field.

Use a fast loop and a complete loop.

Humans need feedback while they still remember what they entered. They also need confidence that the whole dataset is coherent. One validation path cannot do both jobs well in a large shared sheet. Sentry therefore uses two loops.

The edit trigger exits early unless Sentry is enabled, the sheet is configured, the edit touches the monitored URL column, and the row is below the header. A single-cell edit reads one row and applies one result. A pasted block is handled differently: Sentry clears its owned IDs and notes for those rows, then asks the user to run a scan. That is a deliberate safety boundary. Treating a 5,000-row paste like one keystroke would freeze the sheet or exceed Google Apps Script limits.

Targeted does not mean incomplete.

A targeted scan processes rows without an existing Sentry ID. That saves work during normal use. When logs or summaries are enabled, the system can still build a full reporting snapshot after the targeted write. This distinction is subtle: the mutation can be narrow while the report remains complete.

Full scans remain necessary after rule changes, structural changes, or uncertainty about past results. They re-evaluate every row and replace Sentry-owned colors, comments, IDs, and notes in batches. A validator needs both paths because speed and completeness are different requirements.

Build an index before looking for duplicates.

The simplest duplicate check compares every row with every other row. With n rows, that can perform roughly n squared comparisons. This is called O(n²), or quadratic time. Doubling the data can make the comparison work about four times larger.

Sentry makes one pass through the analyzed rows and stores each extracted content ID in a JavaScript Map. A map is a lookup table. Each key points to the row positions where that ID appeared. A second pass marks every occurrence after the first and records the first cell’s address — so the second Brand X reel points at the first. The total work grows roughly in line with the number of rows, which is O(n).

Big O describes growth, not elapsed seconds.

O(n) does not promise that a scan will finish in a fixed time. It says how the amount of work grows as the dataset grows. Network calls, spreadsheet reads, formatting writes, regular expressions, and platform limits can still dominate the clock. The useful result is structural: duplicate checking no longer adds a nested search for every row.

A production duplicate rule also needs a definition of “same.” Raw URL equality is often too weak, because tracking parameters and alternate URL forms can point to one post. Raw content ID alone can be too broad across platforms. A strong key usually combines platform, content type, and canonical ID. The index is only as correct as the key it stores.

In spreadsheets, round trips are usually more expensive than loops.

Google Apps Script runs on a server and talks to Google Sheets through service calls. Reading one cell at a time or setting one format at a time creates many remote round trips. Even simple JavaScript can feel slow when every iteration waits for the spreadsheet service.

Sentry’s full-scan path resolves the sheet layout once, loads the needed columns into memory, evaluates rows without changing the sheet, then writes arrays of IDs, colors, and comments back in groups. This is the spreadsheet version of vectorized work: move a block of data across the boundary, do the local computation, and cross the boundary again only when the result is ready.

In the year-end record, I reported that repeated redesigns reduced processing time from about six minutes to 15–30 seconds. That history is consistent with the current source: narrow edit handling, in-memory evaluation, map-based duplicate detection, and batched output. It is not a preserved benchmark with the same dataset and environment, so it should be read as a documented project result, not as a controlled performance test.

A failed rule should tell someone how to repair the row.

Red cells without explanations create a new support queue. Good validation answers four questions: what failed, why it failed, where to fix it, and whether the repair passed. Sentry uses color for quick scanning, comments for accepted formats, notes on missing fields, and issue logs with cell addresses that can take a reviewer back to the source.

One evaluation record should feed every output.

The current runtime produces one agreed scan record for each loggable row. It holds the row number, URL, content ID, completeness state, explanation, URL state, applicable rules, failed rules, and tracked business fields. Logs and campaign summaries consume this record instead of rebuilding their own version of the validation logic.

This is a small form of event design. The evaluator decides what happened once. The user interface, issue log, ledger, and summary then present that decision for different readers. If each output recalculated validity, they could disagree. One record turns one validation decision into several consistent views.

An audit trail needs history, not just the current color.

Formatting shows the present state. The ledger records scan time, sheet, mode, rows scanned, compliant rows, issue rows, and compliance rate. That history can reveal drift after a rule change or a new source workflow. It does not by itself prove that every accepted value is true; it proves which checks ran and what they returned.

Treat the spreadsheet like an interface contract.

Shared sheets change. Someone renames a header, inserts a column, pastes over a formula, or moves the start row. A script that depends on column C will keep running and quietly read the wrong field. That failure is more dangerous than a visible crash.

Sentry defines installations through configuration: sheet name, header row, start row, URL field, tracked fields, rule IDs, and output columns. At runtime it resolves those names to column positions. Setup verification checks the configuration and required headers. A structural-change trigger warns when monitored rows or columns are inserted or removed.

Stable IDs separate meaning from display.

A rule can target a logical field such as “price for this post” while the sheet displays whatever label the team prefers. Logs can refer to a stable rule ID instead of copying the label. This is basic normalization: store the stable meaning once, then let different surfaces show it in their own words.

The tradeoff is configuration discipline. A flexible engine can become harder to understand than a small script if every installation invents new names and exceptions. Keep field IDs boring, define each rule once, fail setup when required inputs are missing, and archive old configurations rather than layering patches forever.

Test rules at their boundaries, not only on happy rows.

A validator can pass thousands of ordinary examples and still fail on the one case that matters: a shortened TikTok link, two URLs in one cell, a blank conditional field, a pasted block, or a renamed header. Test design should follow the states and boundaries in the system.

The 2026 parallel rebuild includes 14 smoke tests. They cover representative ID extraction, accepted and ignored inputs, synthetic states, column labels, date-period parsing, access roles, menu wiring, log columns, field deduplication, configuration validity, rule resolution, and one conditional-rule skip. These tests prove important pure behavior. They are not a full end-to-end suite for Google Sheets triggers, duplicate scans, writes, or permissions.

A practical playbook for point-of-entry data quality

1. Trace one bad row.Follow it from entry to report. Count every handoff, delay, and correction it creates.
2. Define the record.Name the type, stable identity, required fields, intentional exceptions, and duplicate key.
3. Separate rule layers.Keep format, identity, uniqueness, and completeness distinct so failures stay clear.
4. Choose the earliest sufficient boundary.Run local checks on edit and wider checks when the dataset is available.
5. Return repair guidance.Point to the exact cell, explain the accepted form, and preserve the first occurrence of a duplicate.
6. Index before comparing.Build a map of canonical keys instead of scanning the full dataset for every row.
7. Batch remote work.Read the needed data once, evaluate in memory, and write results in groups.
8. Record the decision.Let one agreed result feed the row UI, issue log, ledger, and summary.
9. Test failure boundaries.Cover bulk edits, schema drift, optional rules, unsupported formats, and cross-platform identity.
10. Measure separate outcomes separately.Track error rates, scan time, and correction time without mixing in results from later approval systems.