Syncing data between Google Sheets and your CRM

Monday morning at a twenty-five-person company. The marketing lead is building the weekly report, so she exports the lead list from the CRM and pastes it into a Google Sheets file, because the pivot the managing director is used to has lived there for years and nobody wants to rebuild it. In the same file, someone from sales corrects two phone numbers that look wrong to him. On Wednesday the scheduled import writes that sheet back into the CRM and lands on top of three fields a rep updated on Tuesday. Nobody notices. Ten days later a customer says you called me on my old number. The real loss is not that phone number; it is the ten days during which nobody could say which record was correct.

The link between Google Sheets and a CRM is the integration small companies build most often and think about least. This article covers, in order: why the spreadsheet is still there and what role it should be allowed to play, the three sync patterns and how each one fails, the matter of the record key, field mapping and silent type corruption, the conflict rule in two-way sync, how to choose a sync frequency, the tab architecture of a synced file, access and privacy, early warning when the connection dies, and the cases where you should migrate instead of syncing.

CRMRaw tabView tabError tabReport

Why the spreadsheet is still there

The usual advice is to drop spreadsheets and move everything into the CRM. Even where it is right, that advice is incomplete, because it never explains why the spreadsheet survives. Google Sheets does three things better than most CRMs: it lets you add a column in five minutes, it lets you rearrange data around a temporary model, and it lets you share with someone who has no seat in your system. The accountant, the outside agency, a partner running a one-off calculation — all of them are inside the file in seconds.

So the useful question is not how to shut the spreadsheet down, but what the spreadsheet is allowed to be. It can be an endpoint: it takes data from the CRM, displays it, calculates on it. It can be an intake: it receives an external list, cleans it, hands it over. The one thing it must not be is the place where the truth of a record lives. How much of the reporting need can be met inside the system is covered in our piece on the custom report builder; once that need is closed, a large share of spreadsheet traffic disappears on its own.

Three sync patterns and how each one breaks

Before you build a sync, name the pattern you are building — what changes between patterns is not the difficulty of setup but the shape of the failure. In a one-way export, data flows from CRM to sheet and the sheet behaves like a dashboard. In a one-way import, the sheet is an intake channel: a trade-fair list, a supplier file, registrations from a campaign. In a two-way sync both sides write, and that is the pattern most requested and least understood.

Pattern	When it is right	Typical failure
CRM to sheet	Reporting, pivots, sharing outward	Someone edits the sheet; the next refresh erases it
Sheet to CRM	List intake, event signups, bulk updates	No key, so every run creates fresh duplicates
Two-way	Two teams genuinely manage the same record	Last write wins, silent data loss
One-time transfer	The process is moving into the CRM for good	The sheet is never deleted and lives on in parallel
Read-only formula link	Small reference lists	Slow refresh and cells that quietly return empty

The point to take from that table is this: do not run two patterns in one file. If a tab is fed by the CRM and also writes back to it, that file is neither a report nor an intake — it is a middle layer whose rules nobody knows. The underlying mechanics of moving records in and out are covered in data import and export.

A sync without a record key manufactures duplicates

If nothing in a row stably says which CRM record it corresponds to, the sync guesses every time it runs. Matching on company name is the worst option: "Northwind Trading Ltd." and "Northwind Trading" are the same company, and no matching engine can know that with confidence. Email is slightly better, but it breaks the moment one person uses two addresses or one shared address sits on two contacts.

The correct method is boring and reliable: carry the CRM record identifier as a column in the sheet. That column fills on the first export, is never edited by hand afterward, and stays protected and preferably hidden. New rows have an empty identifier; the sync treats an empty key as a new record and writes the resulting identifier back into the sheet. That single habit removes most duplicates before they exist. The wider matching and deduplication logic sits in data quality and validation.

The second issue, as important as the key, is normalization — and Google Sheets has traps of its own here. Automatic formatting turns a phone number with a leading zero into a number and swallows the zero, pushes a long tax number into scientific notation, and converts text like "3-4" into a date. None of that raises an error; it just corrupts the data. Case conversion is locale-dependent too, so in languages with dotted and dotless letters the uppercase of a city name may not round-trip, and two rows that should match do not. The fix is simple: force matching columns to plain text, switch off automatic type detection on import, and compare on a trimmed, normalized helper column rather than the raw one.

Field mapping and silent type corruption

The real work of connecting two systems is not matching column names but matching what values mean. A field that is a picklist in the CRM is free text in the sheet; "Pending," "pending" and "On hold" are three different values in a spreadsheet and two of them are invalid in the CRM. Decide in advance what the sync does in that case: reject the row, snap to the nearest valid value, or leave the field empty and keep processing.

On numbers and dates the damage is sneakier. If the file's locale uses a comma as the decimal separator, an exported amount can arrive a thousand times larger in the other system. When date format flips between day/month and month/day, the first twelve days of every month silently swap places and you discover it on the thirteenth. Multi-select fields flattened into one comma-joined cell split in the wrong place on the way back whenever a value itself contains a comma. How to document a mapping and which rules to write down before the first run are collected in field mapping.

Who wins a conflict in two-way sync?

Most teams asking for two-way sync do not actually want two-way data. They want two teams to manage two different sets of fields. That distinction rescues the whole design. Resolve conflicts at field level rather than record level: phone and address belong to the CRM, campaign tag and segment belong to the sheet. When every field has exactly one owner, you never need a last-write-wins rule, because two sides never write the same field.

Any spreadsheet where humans and robots write to the same tab will eventually erase somebody's work with nobody noticing.

If field ownership is not achievable, at least keep a timestamp column and a changed-by column, and resolve conflicts through those two. And rather than overwriting on every run, show the difference first: having the sync dump the fields it is about to change into a tab before writing them is the most useful investment you can make during setup. The general logic of bidirectional sync and its conflict rules is covered in two-way data synchronization.

How often should it sync?

Instinct says more often is better. With spreadsheets that instinct is wrong. Google Sheets has no "row committed" event; cells save one by one as a person types. A sync running every five minutes reads a row somebody is halfway through and creates a CRM record with a name and no phone number. The next run updates it, but in between it was assigned to someone and a notification went out.

The answer is an explicit commit signal. Put a status column in the sheet and let the sync read only rows marked ready. Frequency then largely stops mattering: even an hourly job never touches a half-typed row without human confirmation. When you do pick a frequency, watch the quota limits on both sides; past a certain number of calls each will throttle requests, and a sync that quietly enters a queue at midnight can still be unfinished by morning. A comparison of the platforms that schedule and trigger these jobs is in Zapier versus Make.

Turning the sheet into an interface contract

The moment you open a spreadsheet to a machine, that file stops being a document and becomes an interface. Interfaces have contracts, and when the contract is unwritten, one blank row somebody inserted stops the whole flow. The seven rules below keep the large majority of synced sheets standing.

• One header row: Headers belong on row one, on a single line, with no merged cells; a two-line header is an unreadable table for most connectors.
• Column names are frozen: Even if the visible label has to change, the mapping should bind to a stable technical name, not to the header text; renaming "Phone" to "Mobile" is a silent outage.
• No blank rows: A blank row left as a visual separator is, for most readers, the end of the data, and everything below it becomes invisible.
• Data validation on picklist columns: A status column that accepts free text will reach seven different spellings within three months.
• Protect the identifier column: Lock the column carrying the record key against editing, and make sure it travels with the row whenever anyone sorts.
• No formulas, at least on the raw tab: A formula placed in a cell the sync writes to is destroyed on the first write; calculations belong on a separate tab.
• Stable column order: Add new columns at the end; a column inserted in the middle shifts every position-based mapping by one cell.

Raw tab, view tab, error tab

The way to apply all of those at once is to split the file into three tabs. Only the sync writes to the raw tab, and people do not touch it. The view tab pulls raw data through formulas, formats it and feeds the pivot; people work here, and nothing they type goes back. The error tab receives rows that could not be processed, along with the reason: unmatched identifier, invalid picklist value, empty required field. Those three tabs make the question of who broke the spreadsheet disappear, because humans and machines no longer write in the same place.

A shared spreadsheet is also a permanent export

Remove a user's access in a CRM and their access ends. Remove access to a spreadsheet and the copy that person already took stays exactly where it is. A file syncing your customer list, left open to anyone with the link or downloaded once, becomes an export you cannot recall. That is an administrative risk rather than a technical one, and no technical control closes it completely.

The practical approach has three steps. Share synced files with named people, turn off link access, and restrict downloading and copying. Carry only the columns you actually need; identification numbers, full addresses and correspondence history are unnecessary for most reports. Keep the permissions of the account that runs the connection narrow as well: the sync can see everything that account can see. How the account, calendar and file connections are set up on the Google side is covered in CRM and Google integration.

Hear about a broken connection before your customers do

Most syncs die quietly rather than loudly. An authorization expires, someone moves the file to another folder, a column gets renamed; the job keeps running and processes nothing. The most practical way to notice is a visible health indicator inside the sheet: a single cell holding the time of the last successful sync. Anyone who opens the file sees at a glance that the date is not yesterday but last Wednesday.

The second check is row count. Record how many rows each run processed, and set a simple threshold that alerts when the number drops to zero or unexpectedly doubles. The third is the error tab filling up: if it is a queue that fills silently, nobody looks, so a notification should go out when it grows. General methods for watching connection health are in integration monitoring, and the options for building connections without writing code are in CRM integrations.

When you should migrate instead of sync

Here is where the usual advice needs a limit. Syncing is the right answer when both systems will genuinely keep existing. If the spreadsheet is the real center of a process — order tracking happens there, who did what is written there, decisions get made there — then building a sync makes that disorder permanent and adds a maintenance burden on top of it.

There is a simple test. If you made the sheet read-only for one week, would work stop? If it would, that file is a system and it needs migrating, not syncing. If it would not, the sheet really is an endpoint and a sync is the right tool. When you decide to move the process across, the step-by-step path is laid out in moving from spreadsheets to a CRM.

Common mistakes and a two-week starting plan

Four mistakes account for most of the damage. First, building an import with no record identifier, which produces four copies of the same customer. Second, letting people and the sync write to the same tab. Third, ignoring failed rows: if unprocessable rows land nowhere, you are silently losing data on every run. Fourth, mistaking the sheet for a backup — a synced file is not a backup, because a bad deletion reaches it just as fast as everything else does.

Two weeks is enough to start. In week one, run one direction only: CRM to sheet, identifier column included, once a day. Make sure nobody edits the sheet during that week, and split the raw tab from the view tab. In week two, open the reverse direction, but only for rows marked ready and only for two or three fields. Build the error tab, add the last-sync cell, and at the end of the fortnight look at which rows failed and why; that list will have written your permanent rules for you.

You do not have to abandon the spreadsheet — you only have to put it in the right place. Keeping records, field definitions, import rules and reports in one system reduces spreadsheet traffic on its own. Rocketly runs data import, custom fields, workflow rules and reporting in the same place — open a free account and build your own sync flow.