How to automate spreadsheet data cleaning without destroying evidence

To automate spreadsheet data cleaning without destroying evidence, write every cleaned value to a new column, keep a timestamped copy of the input, apply only rules you can explain, and send a report of what changed to the person who owns the data. Every cleaning rule is a claim about what the data means, so each rule needs a record of the original value.

The design question is therefore not how to normalise a phone number. It is how to normalise it while keeping the evidence of what was there before, and how to notice when a rule does something you did not intend.

What we'll cover

• Cleaning is interpretation
• Keep the original, always
• The rules worth encoding first
• Building it with CodeWords
• Deduplication, which deserves caution
• Reporting what changed
• When the fix belongs upstream
• Cleaning data about people
• Making it survive
• Limits worth knowing about
• What to build first
• Frequently asked questions

Key takeaways

• Trimming whitespace and normalising case are the only cleaning rules safe to apply without review.
• Writing cleaned values to new columns keeps the original, so a wrong rule can always be undone.
• Duplicates should be reported before they are merged, because a merge cannot be unwound.
• A weekly report of what had to be cleaned is the strongest argument for fixing the problem at its source.
• Records about people carry extra obligations, so keep the original and the audit trail.

Cleaning is interpretation

Cleaning is interpretation because almost every rule assumes something about the data, and only trimming whitespace is free of assumptions. A rule applied automatically applies that assumption to rows nobody looked at.

• Normalising a date assumes a format. The classic failure reads a day as a month, which produces a plausible wrong date instead of an error.
• Standardising a company name assumes two spellings are one company. Sometimes they are two companies.
• Filling a blank with a default asserts that absent means that default. A blank usually means "unknown", not zero.
• Correcting a spelling assumes you know what the author intended.

None of these rules is wrong to apply. Each one is a decision that should be explicit, reversible and reported, not a step buried in a cleaning script nobody reads.

Keep the original, always

Keep the original by writing cleaned values to new columns, or by saving a timestamped copy of the input before anything runs. This is the one practice that makes every other rule recoverable.

You will get a rule wrong. Without the original, the only recovery is re-exporting from the source, which may no longer hold the old value. Keeping the original also makes the rules auditable: anyone who questions a figure can see what the value was and what it became.

Drop the originals later only as a deliberate decision, once the rules have been stable for a while. A new column costs little, and a lost original costs a bad afternoon.

The rules worth encoding first

The rules worth encoding first are the safest and most valuable ones, in this order:

1. Trim whitespace and normalise case for anything you match or group on. This is safe and fixes a large share of duplicate problems.
2. Normalise phone numbers to one international format. Inconsistent formats are where most contact deduplication fails.
3. Parse dates with an explicit expected format, and reject a value that does not match instead of guessing.
4. Standardise a fixed vocabulary such as country, status or category against a list. Flag anything not on the list instead of mapping it to the nearest match.
5. Strip formatting from numbers, such as currency symbols and thousands separators, and record the currency in its own column.
6. Flag instead of fix anything you are not certain about. That covers most cases.

Building it with CodeWords

To build a cleaning automation in CodeWords, describe the sheet, the columns and each rule to Cody, the automation builder, then test it on a copy. CodeWords connects to 3,000+ integrations (CodeWords integrations page, 2026), so the cleaning can run where the data arrives instead of depending on someone remembering to run it.

1. Open CodeWords and start a new automation.
2. Describe in plain language which sheet, which columns, and what each rule should do.
3. Authorize the connection to the spreadsheet and to wherever the report should go.
4. Describe the exceptions: a value that does not parse, a duplicate, or a value outside a plausible range.
5. Run it against a copy and compare before and after before pointing it at anything live.

You describe the outcome, and Cody builds it, connects it and deploys it. The Free plan covers light use. Pro costs $39 per month and Business costs $100 per month as usage grows (CodeWords pricing page, 2026). Details are on the pricing page.

Deduplication, which deserves caution

Deduplication deserves caution because it is the most requested cleaning operation and the most destructive. Report duplicates first, and merge only after a person has reviewed them. A list that someone checks in ten minutes is safer than an automatic merge you cannot unwind.

• Normalise before comparing. Otherwise two strings that differ by a full stop look like two companies.
• Match on more than one field. Email alone misses one person with two addresses, and name alone matches two different people.
• Decide which row wins when you do merge: most recent, most complete, or from the most trusted source. Record the decision.
• Keep what the losing row had that the winning row lacks.
• Treat near-duplicates as possibly real. Two branches of one company, two people sharing a family email and two similar product names all need a person to decide.

Reporting what changed

A cleaning report should show the count of changes per rule, the unusual spikes, the rejected rows with reasons, and a few examples. It should go to the person who owns the data, because the owner can tell a correct transformation from a plausible one.

• Count changes per rule. A rule that fired on four rows and one that fired on four thousand need different levels of confidence.
• Report spikes. A rule that suddenly fires far more than usual means the input changed, and that is what you want to know.
• List rejected rows with the reason. This list is the real data quality report and is more useful than the cleaned output.
• Show examples. Five sample transformations per rule let someone check the rule in seconds.

When the fix belongs upstream

The fix belongs upstream when the same problem arrives every week. A form validation, a dropdown instead of free text, or a required field removes the problem at the source. Cleaning treats the symptom and hides the cause, which is why the problem persists.

A weekly report of what had to be cleaned is the argument for the upstream fix. A spreadsheet that needs cleaning before it is usable may also be the wrong tool, since databases and structured form tools enforce constraints that block the bad states.

Some cleaning is permanent and fine: data from a third party you do not control, historical records and one-off migrations. State which situation you are in. Building an elaborate pipeline for a problem that a required field would remove is a common and avoidable cost.

Cleaning data about people

Cleaning data about people adds obligations that product or order data does not carry. Merging two person records asserts that two people are one, and a wrong merge combines two individuals' data.

• Do not overwrite what people entered about themselves. Their spelling of their own name is correct by definition, and names do not follow the rules a normalisation rule assumes.
• Keep the audit. Laws such as the General Data Protection Regulation (GDPR, Article 15) let people ask what you hold about them. That request is hard to answer if the original was overwritten.
• Make deletion reach every copy. A pipeline that writes to three sheets creates three places a deletion request must reach.
• Ask early. Whether your cleaning counts as processing that needs a legal basis is a question for your legal adviser, and it is cheaper to ask before the pipeline exists.

Making it survive

A cleaning automation survives change when it is idempotent, tolerates new columns, keeps row order and fails on unexpected input.

• Make it idempotent. Running the cleaning twice must give the same result, so every rule must be safe to reapply to cleaned values.
• Handle the new column. A column added to the sheet must not break the automation or be skipped silently.
• Do not reorder or sort. Changing row order breaks anything that references rows by position.
• Fail on the unexpected. A missing column should stop the run, not produce a cleaned sheet that lacks a field.
• Report the outcome: rows processed, changes per rule, rejections and anything skipped.

Limits worth knowing about

The main limits are cell caps, aggressive type coercion, lost precision on long identifiers, concurrent edits and write limits.

• Cell limit. Spreadsheets cap the number of cells, and keeping original columns beside cleaned ones uses the cap faster.
• Type coercion. A spreadsheet reinterprets anything that looks like a date or a number, which causes much of the corruption you are cleaning.
• Long identifiers lose precision when stored as numbers, so store them in text columns before any cleaning.
• Concurrent edits. Someone editing the sheet while the automation runs produces results neither of you expects.
• API write limits. A large run needs batching instead of cell-by-cell updates.

What to build first

Build first a set of rules that trims and normalises the columns you match or group on (whitespace, case, phone format), writes to new columns, and reports what changed and how often. These are the safest rules, they fix a large share of downstream matching problems, and the report shows what to encode next.

Two habits make the difference. Keep the original values while the rules are new, because re-exporting from the source is not always possible. And flag instead of fix anything you are unsure about: a flagged row gets looked at, and a confidently wrong correction does not.

Frequently asked questions

What cleaning rules are safe to apply automatically?

Trimming whitespace and normalising case are the safe rules. Almost everything else encodes an assumption, such as a date format, two spellings meaning one company, or blank meaning zero. Flag or report those cases instead of applying them silently.

Why do dates get corrupted?

Dates get corrupted because the cleaning rule assumed a format. Day and month swap silently for any date whose day is 12 or lower, which produces a plausible wrong value instead of an error. Parse with an explicit expected format and reject values that do not match.

Should duplicates be merged automatically?

No, report duplicates first. Merging is destructive, and near-duplicates are sometimes different records, such as two branches or two people sharing an address. When you do merge, decide explicitly which row wins and keep what the losing row held.

Do I need to keep the original values?

Yes, while the rules are new. You will get one rule wrong, and re-exporting from the source is not always possible. Drop the originals later as a deliberate decision once the rules have been stable for a while.

What should the report contain?

The report should contain the changes per rule with example transformations, plus the rejected rows with reasons. The rejection list is the real data quality report and is more useful than the cleaned output itself.

When should I fix the source instead?

Fix the source when the same problem arrives every week. Cleaning hides the upstream issue, which is why it persists. A form validation or a dropdown usually removes the problem that an elaborate pipeline only treats.

Why did the automation break after somebody edited the sheet?

The likely cause is a renamed, added or reordered column. Make the automation fail on unexpected input, since a run that stops and explains why is better than a cleaned sheet that is missing a field.

Is cleaning personal data different?

Yes. Merging two people's records is more consequential than merging product rows, names do not follow normalisation rules, and people can ask what you hold and where it came from. Answering that request needs the original value.

What does a cleaning pipeline do to deletion requests?

A cleaning pipeline multiplies deletion requests. Every sheet the pipeline writes to is another place a deletion must reach. Plan for this before the pipeline has three outputs instead of after.

Related reading

• Connecting MongoDB to Google Sheets
• Automated reporting for agencies
• ETL pipelines explained