Key Takeaways
- Pull the table once, clean it in the staging sheet, then write back only the cells that changed.
- Preview reports the new, changed and unchanged counts before a single record is touched.
- Rows missing from the staging sheet are never deleted in the base.
- A pull stops at 5,000 rows and a run applies 500 changes, so a large table is worked in passes.
Sync tools that append rows will stack the same records on top of each other on every run, which is how an Airtable base fills with copies. The method below runs the other way round. Open Extensions > Flookup Data Wrangler > Data Connectors, pull the table into a staging sheet inside your spreadsheet, clean it there and send only the differences back after a preview.
Where duplicate rows come from
Append based syncs write new rows for every record they read, so each run copies the whole table again. Re-importing a CSV export has the same effect, and form submissions or CRM syncs bring the same person in twice with different spacing, casing or suffixes. The counts in the base then disagree with the reports built on top of them.
The tidy up is the same discipline used in user list consolidation: keep one master row per entity and record what changed beside it. What differs here is where the tidy up happens. The staging sheet is a working copy, so the cleaning runs on a copy and the base only receives the cells that were edited.
Pull the table into a staging sheet
Create a personal access token in Airtable with
data.records:read
and
schema.bases:read, add
data.records:write
when a write back is planned, and grant the token access to the base. Airtable shows the token once. Paste it into the Airtable card in the sidebar, click
Load bases, then choose the table. A view name is optional and worth using: it narrows the pull to the rows the sync should cover.
Click Pull to staging. The connector clears the staging sheet, writes the field names as a bold header row and then the records, fetched 100 at a time with a short pause to stay inside the Airtable limit of 5 requests per second. Airtable omits empty fields from its responses, so those cells arrive empty rather than as placeholders. A pull stops at 5,000 rows and says so, which is why a narrower view is the better lever than pulling everything.
A hidden _flookup_record_id column keeps the source id for every row. Do not edit or delete it: without that column the preview cannot match a staged row to a record, and every row reads as new. The control list, including the scopes and the troubleshooting table, is in the Airtable connector documentation.
Clean and deduplicate in Sheets
Standardise before matching so variants group together: trim the whitespace, fix the casing, then run Standard Data Cleaning over the columns that carry names, emails or phone numbers. Matching then compares exact, normalised, phonetic and fuzzy measures across one column or a whole table. Start at 0.85 for names, as set out in fuzzy matching in Google Sheets, and raise the threshold toward 0.90 when false positives appear.
When the groups look right, run Remove Duplicates on a copy of the staging sheet. It writes one master row per entity to the output range you chose and leaves the staging sheet as it was, so the reviewed list and the source sit side by side. Those master rows are what the write back sends. The full sequence, including the review band, is in how to remove duplicate records in Airtable.
Preview before anything is written
The first press is always Preview changes. It re-reads the live base, compares every staged row against it cell by cell and returns three numbers: new, changed and unchanged, with a sample of the first rows beneath them. Nothing is written at this stage, and nothing is queued for later.
The comparison is per field rather than per row, so a cell that still matches the source is not collected into the plan. That is what keeps a sync quiet: run the preview twice in a row and the second run reports zero changes, because the first run already brought the base in line with the sheet.
Write back only changed fields
Apply changes sends the plan to Airtable. Changed records go out as patches in batches of ten records per call with typecast enabled, and only the fields that differ are included, so untouched columns are never overwritten. Staged rows with no record id are created as new records, also in batches of ten. Updates are applied first and creations fill whatever budget is left, up to 500 changes in a single run.
The result reports the inserted, updated and skipped counts, and says when the cap stopped the remainder. Preview and apply again to continue where the run stopped. Records that exist in Airtable but are absent from the staging sheet are not referenced by the plan at all, so nothing in the base is deleted. A rate limit response stops the run with a message rather than being retried in a loop: wait a minute and apply again.
What deleting rows does in each direction
Deleting a row from the staging sheet is safe. The plan is built only from the rows that are present, so the matching record in Airtable keeps its values exactly as they are. That is the ordinary way to leave a record alone.
Deleting the record in Airtable while its row stays in the staging sheet is the case to watch. The preview re-reads the base, no longer finds that id in the source and plans the row as a new record, so Apply would recreate it with the same values. The same happens when a row was pasted in from elsewhere and carries no id. The rule is simple: re-pull after records are removed or added in Airtable, before previewing a write.
Limits worth knowing
| Limit | Value and effect |
|---|---|
| Rows per pull | 5,000, reported when the cap stops the pull. Narrow the view and pull again. |
| Changes per run | 500, updates before creations. Preview and apply again for the remainder. |
| Page size and rate | 100 records per page at 5 requests per second per base, with a pause on 429 responses. |
| Linked records and arrays | Flattened to comma separated text for reading. Treat those columns as read only in staging. |
That last row matters. A changed linked record cell is sent back as text with typecast, which can create or link records rather than update the existing ones. Edit linked record and attachment fields in Airtable, and keep the cleaning on the ordinary columns.
Credentials are stored in the Apps Script user properties of the Google account that set them up, are never returned to the sidebar and are removed by Clear on the card. The connector is included with the 1-Year Pass and the Team Pass; the 1-Month Pass and the free tier cover Standard Data Cleaning, which is enough to prove the workflow on a sample before the base is touched.
Frequently Asked Questions
Does this sync create duplicate rows?
No. A pull replaces the staging sheet, and write back sends only the fields that differ from the live base. Rows that already match are skipped, so a second run over the same data writes nothing.
What does Preview changes report?
It re-reads the base, compares every staged row with the live record and returns the new, changed and unchanged counts plus a sample of the first rows. Nothing is written until Apply changes is clicked.
Which Airtable token scopes are needed?
Reading needs data.records:read and listing bases needs schema.bases:read. Writing needs data.records:write on the same token, granted access to the base.
How many rows can one pull and one run handle?
A pull stops at 5,000 rows and reports the cap. A single write applies at most 500 changes, updates first, then tells you when the cap stopped the rest. Preview and apply again for the remainder.
What happens to records deleted in Airtable?
A staged row whose record no longer exists in the base is planned as a new record, so Apply would recreate it with the same values. Re-pull after records are removed in Airtable, before previewing a write.