Why Your Spreadsheet Sync Keeps Creating Duplicates

Same Customer, Eleven Times

You open the sheet and there they are. The same person, eleven rows, slightly different each time. One has a phone number with a space in it. One has their name in caps. Two are identical as far as you can tell. Somebody now has to work out which one is real, and the automation that emails these people has already emailed them eleven times.

We get called about this a lot, and the first thing worth saying is that it's almost never the connector's fault. n8n, Make and Zapier all do exactly what they were told. What they were told was to match records on a field that isn't reliable.

The Actual Cause

Every sync between two systems has to answer one question on every run: is this record already over there?

To answer it, the workflow searches the destination for something. Whatever you told it to search on is your key. If that key is not unique, not stable, and not always present, the search fails, the workflow concludes the record is new, and it creates a second one. Repeat daily and you get eleven.

Three properties matter, and most people only check the first.

Unique. No two real records share it. Stable. It never changes for a given record. Always present. It's never blank.

Fail any one and you will get duplicates eventually.

Why the Obvious Keys Fail

Email address. Usually unique, mostly present, not stable. People change jobs and change addresses. Two family members share one. Someone types it with a trailing space, or capitalises the first letter, and now it doesn't match. Case and whitespace are the single most common cause we find.

Phone number. Rarely stable in format even when the underlying number is the same. The same mobile can arrive as 07700 900123, +447700900123, 447700900123 and 7700900123 from four different sources. String matching treats those as four people.

Full name. Not unique in any dataset above about fifty rows. There are two Sarah Joneses. There will be more.

Row number. Not stable at all. Sort the sheet and every row number changes. We still find syncs keyed on row position, and they scramble the moment someone clicks a column header.

Company name. Unique-ish, wildly unstable in formatting. "Acme Ltd", "Acme Limited", "ACME", "Acme Ltd." are one customer and four keys.

What to Use Instead

In order of preference.

A system-generated ID. Airtable record IDs, HubSpot object IDs, Stripe customer IDs, Shopify order IDs. Immutable, guaranteed unique, never blank. If both systems can hold the other's ID, store it and match on that. This is the correct answer and it's worth the extra field.

Your own generated ID. If no system provides one, generate a UUID when the record is first created and write it to both sides. Slightly more work up front, no ambiguity afterwards.

A normalised natural key. When you genuinely can't store an ID, normalise before matching. Lowercase the email and trim whitespace on both sides of the comparison. Strip everything that isn't a digit from phone numbers, then compare the last nine or ten digits so country code formatting stops mattering. Do the normalisation inside the workflow, on both the incoming value and the search value, every run.

The mistake is normalising the incoming record but searching the destination for the raw value. That fails silently and looks like the connector is broken.

The Other Cause: No Search Step At All

Worth checking before you redesign your keys. A surprising number of syncs we inspect have no lookup in them whatsoever. The workflow triggers, and it appends. That's it.

It works perfectly during testing, because in testing every record genuinely is new. It generates a duplicate for every single update once real data starts flowing. If your sheet gains a row every time someone edits a record rather than only when one is created, this is what's happening, and the fix is inserting a search-and-branch before the write.

The pattern is always the same: search destination, branch on found or not found, update on one path, create on the other. We describe it step by step for the Airtable case in connecting Airtable to Google Sheets and Gmail.

Cleaning Up What's Already There

Fix the sync first. Deduplicating while the cause is still running is wasted effort.

Then work through the existing mess in this order. Export a copy before touching anything, because dedupe scripts are unforgiving. Normalise the key column across the whole dataset so genuine matches actually match. Sort by that column and review the groups by eye if the volume allows, because automated merge rules make confident wrong decisions about which record to keep. Where you do merge automatically, keep the oldest record and its creation date, and carry across any field the newer duplicates have filled that the original left blank.

Do not delete duplicates that other systems reference by ID. Mark them inactive instead. A deleted row that something downstream still points at causes a worse problem than the duplicate did.

Preventing the Next One

Two habits. Decide the key field before you build anything, and write it down somewhere the next person will find. And add a duplicate check that runs on a schedule and alerts you, rather than assuming the fix held. A weekly workflow that counts records sharing a key and posts to Slack when the count is above one takes twenty minutes to build and tells you within a week instead of within a year.

That second one falls under the same discipline as detecting silent automation failures, and it's the difference between an integration you trust and one you check by hand.

If the underlying problem is that a spreadsheet is being asked to behave like a database, that's worth facing directly. When Google Sheets automation stops scaling and Airtable vs Google Sheets for automation both cover that call.

Book a free 60-minute call and we'll look at where your duplicates are coming from, tell you which key your sync should actually be using, and sort the existing mess if you want it sorted.