Create your first project
Upload your first Excel to the platform, open the in-browser editor and save a version.
auto_fix_high
For data prep and cleaning
Data cleaning and transformation with JS
5 real-world patterns to clean columns: emails, dates, names, dedup, regex. All with sandboxed JS scripts that auto-save.
schedule 7 minutes read
-
Step 1
The data.map() pattern
Inside the JS sandbox, the
datavariable is a 2D array:data[row][column]. To transform a column 1-to-1, the standard pattern isdata.map(r => transform(r[0])). The result is a 1D array written vertically starting atoutputCell.💡 Tip: If you want to return more than one column per row, return an array: <code>data.map(r => [r[0].trim(), r[1] * 2])</code>. -
Step 2
Cleaning emails
Typical case: column A has emails with whitespace, inconsistent casing, some empty rows. Ask the AI: "clean the emails in column A into B2: trim, lowercase, drop ones without @". Generates something like
data.map(r => { const e = (r[0]||"").toString().trim().toLowerCase(); return e.includes("@") ? e : null; }).💡 Tip: If the answer is <code>null</code>, that cell is left empty in the output — useful for filtering. -
Step 3
Extract domain from an email
"From the emails in A2:A100, get the domain (after the @) into B2." The assistant generates
data.map(r => { const m = (r[0]||"").match(/@([^@\s]+)$/); return m ? m[1].toLowerCase() : null; }). Regex + capture group pattern. -
Step 4
Parse mixed date formats
If the column has dates like "01/03/2026", "2026-03-01", "1 mar 2026" mixed together, ask: "Parse column A as a date; if not parseable, leave empty." The assistant tries multiple formats:
data.map(r => { const s = String(r[0]||"").trim(); const d = new Date(s); return isNaN(d) ? null : d; }).💡 Tip: For European DD/MM/YYYY where Date() fails, ask it to use regex: <em>"...and recognize the DD/MM/YYYY format"</em>. -
Step 5
Dedup with custom rules
"In A2:A100 there are names with inconsistent casing (Juan / JUAN / juan). Give me only uniques in B2, in title-case." The assistant generates something like
(() => { const seen = new Set(); const out = []; for (const r of data) { const k = String(r[0]||"").trim().toLowerCase(); if (!k || seen.has(k)) continue; seen.add(k); out.push(k.charAt(0).toUpperCase() + k.slice(1)); } return out; })(). IIFE because the body needs statements. -
Step 6
Run and verify
After every compute_with_js, look at the result in the spreadsheet. If something looks off: "the script left null in B23 and B47, why?". The AI can show the original row and diagnose (probably empty or malformed input).
💡 Tip: The assistant verifies the tool snapshot before replying, so if it said "done" it's because it saw the result. -
Step 7
Auto-save in Functions
When the assistant uses
compute_with_jsfor a transformation that looks reusable, it saves it withsaveAs. Click Functions in the editor header and you'll see your scripts:clean_emails,extract_domain, etc. Click ▶ to re-run (creates a new version), ✏ to edit the JS code, 🗑 to delete.💡 Tip: Pro tip: in the panel you can tweak the script's <em>defaults</em> (input range, output cell) without changing the code. Useful when you move a sheet around.
Ready to apply it?
Open ExcelEmpowers and follow these steps on your own Excel.
Create free account ← Browse more tutorials