A lookup that should match "Ada Lovelace" fails because the cell is Ada\u00A0Lovelace with a non-breaking space, or has a zero-width character you cannot see. cleanCellText(value) strips those, trims the ends, and collapses repeated spaces. cleanColumn(sheet, header) does the same for every data cell in a named column, in one setValues write.
When to use
- Names or keys that look identical but fail
===orMATCH - Data pasted from the web, Docs, or PDFs
- A cleanup pass before lookups, validations, or joins
How to use this snippet
/**
* Strip non-breaking and zero-width characters, trim ends, collapse spaces.
* Non-strings (numbers, Dates) are returned unchanged; null/undefined become ''.
*
* @param {*} value
* @return {*}
*/
/**
* Clean every data cell in a column named `header`, in one batch write.
* Leaves the header row alone. Returns the number of cells that changed.
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
* @param {string} header
* @return {number}
*/
function cleanCellText(value) {
if (value == null) return '';
if (typeof value !== 'string') return value;
return value
.replace(/[\u200B-\u200D\uFEFF\u2060]/g, '') // zero-width, BOM, word joiner
.replace(/[\u00A0\u202F\u2007]/g, ' ') // NBSP family → regular space
.replace(/[ \t]+/g, ' ')
.replace(/^ +| +$/g, '');
}
function cleanColumn(sheet, header) {
var data = sheet.getDataRange().getValues();
if (!data.length) throw new Error('cleanColumn: sheet is empty');
var col = data[0].indexOf(header);
if (col === -1) throw new Error('cleanColumn: no column "' + header + '"');
if (data.length < 2) return 0;
var range = sheet.getRange(2, col + 1, data.length - 1, 1);
var vals = range.getValues();
var changed = 0;
var out = vals.map(function (r) {
var cleaned = cleanCellText(r[0]);
if (cleaned !== r[0]) changed++;
return [cleaned];
});
range.setValues(out);
return changed;
}
Example
function cleanNameColumn() {
var sheet = SpreadsheetApp.getActive().getSheetByName('CRM');
var changed = cleanColumn(sheet, 'Name');
Logger.log('Cleaned ' + changed + ' Name cells');
}
Notes
- Non-breaking spaces (U+00A0 and a few siblings) become regular spaces, then repeats are collapsed. Zero-width characters are removed entirely.
- Dates and numbers are left alone so a date column you pass by mistake is not stringified.
setValueskeeps existing formatting. This only changes the text.
Happy Coding!
