Building a Real-Time Data Cleaning Pipeline with n8n, Google Sheets, and JavaScript

The Problem: Every Team Has a "Messy Spreadsheet"

If you've ever inherited a spreadsheet that's been manually updated by multiple people over months, you know the pain. Here's what I was staring at recently:

• 📞 Phone numbers in 5+ different formats — 1711019283, [phone], [phone]
• 📧 Emails with typos in the domain — gmail.co, gmial.com instead of gmail.com
• 🔤 Status fields with inconsistent casing — active, ACTIVE, Active, pending, INACTIVE
• 📅 Join dates in at least 4 different formats — YYYY-MM-DD, DD/MM/YYYY, MM/DD/YYYY, all mixed in the same column

None of this is unusual — it's what happens when data entry isn't enforced at the source. But it wrecks every filter, every report, and every automation built on top of it.

Rather than clean it manually (again), I decided to automate the entire process.

The Approach

I built a real-time transformation pipeline using three pieces:

Layer	Tool	Role
Trigger	n8n (Google Sheets Trigger)	Detects new/edited rows
Transform	JavaScript (Code node)	Cleans & standardizes data
Write-back	n8n (Append/Update node)	Writes cleaned data to sheet

Step 1 — Trigger on Change

The workflow starts with a Google Sheets Trigger node set to anyUpdate, so it fires the moment a row is added or edited — no polling delay, no manual "run" button.

Step 2 — Transform with JavaScript

This is where the actual cleanup logic lives. A few examples of the kind of normalization rules involved:

// Normalize phone numbers to a single consistent format
function normalizePhone(raw) {
  const digits = raw.replace(/[^\d]/g, '');
  const local = digits.startsWith('880') ? digits.slice(3) : digits;
  return `+880${local}`;
}

// Fix common email domain typos
function fixEmailDomain(email) {
  return email
    .replace(/gmial\.com$/i, 'gmail.com')
    .replace(/gmail\.co$/i, 'gmail.com');
}

// Standardize status casing
function normalizeStatus(status) {
  return status.trim().toLowerCase();
}

Each field gets passed through its own validation/normalization function, and the row is reassembled with a Cleaned_Status flag so downstream steps (and humans) can tell what's already been processed.

Step 3 — Write Back Automatically

The cleaned row is pushed back into the sheet using an Append or Update Row node, matching on a unique ID so existing rows get updated in place instead of duplicated.

Google Sheets Trigger  →  JavaScript Transformation  →  Append/Update in Sheet
     (anyUpdate)              (clean & validate)          (appendOrUpdate)

The Results

• ✅ Zero manual cleanup after setup — the pipeline runs on its own
• ✅ Real-time processing — rows are cleaned within seconds of being entered
• ✅ Consistent formatting across phone numbers, emails, statuses, and dates
• ✅ Hours saved weekly that used to go into manually fixing spreadsheet entries

Why This Matters

It's easy to underestimate how much time "small" data inconsistencies cost. A malformed phone number or an inconsistent status value doesn't seem like a big deal — until it silently breaks a filter, skews a report, or fails a downstream integration.

Low-code tools like n8n make it realistic to solve this kind of problem without standing up a full backend service. The trigger → transform → write-back pattern shown here can be adapted to almost any messy-data scenario: CRM exports, form submissions, survey responses, you name it.

Takeaways for Your Own Projects

If you're dealing with something similar, a few principles that helped:

1. Normalize at the field level — write one small function per field type (phone, email, date, etc.) rather than one giant cleaning function.
2. Flag what's been processed — a Cleaned_Status column avoids re-processing rows and makes debugging easier.
3. Trigger on change, not on schedule — real-time beats batch when the goal is "always clean," not "clean once a day."

---

Have you built something similar, or dealt with messy data at scale? Would love to hear how you approached it in the comments 👇

---

Connect with me:

• 💼 LinkedIn: www.linkedin.com/in/bastullah-batas-54ab23335
• ✖️ X (Twitter): @batas999