📎 This article includes 1 downloadable practice file ↓
In this article
Most automation starts with cleaning: spaces, inconsistent case, “Rs 1,200” stored as text, blank rows. Doing it in the array is fast and repeatable.
Clean in one pass
const rows = values.slice(1)
.filter(r => r.some(v => String(v).trim() !== "")) // drop blank rows
.map(r => r.map(v => String(v).trim().replace(/\s+/g, " "))); // trim + squeeze spacesfilter keeps rows that pass a test; map transforms each value. Text-to-number: strip non-digits and Number().
Formatting
range.setNumberFormatLocal("#,##0");
sheet.freezePanes.freezeRows(1);
const cf = range.addConditionalFormat(ExcelScript.ConditionalFormatType.cellValue);💡 Write the cleaned data to a new sheet first while testing (workbook.addWorksheet(“Clean”)). Once you trust the script, overwrite in place.
Where people go wrong
| Problem | Fix |
|---|---|
| Leading zeros lost | Don’t convert code columns to numbers |
| Formulas replaced by values | Use getFormulas() for columns with formulas |
Practice
Paste a messy list (Name, Customer, Amount as text) and run the script on a copy of the sheet.
📎 Practice files for this article
- 📝Lesson 4 Office ScriptPaste into Excel on the web > Automate > New Script.⬇ TS · 934 B
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong