Sheet phones arrive as (555) 123-4567, 555-123-4567, +1 555 123 4567, or 0071…. APIs and SMS gateways want E.164. normalizePhone(raw, defaultCountryCode) strips formatting and returns +15551234567-style strings, or null when there's nothing usable.
When to use
- Cleaning a Phone column before Twilio, WhatsApp, or a CRM sync
- Deduping contacts where the same number appears in three formats
- Validating that a row has a dialable number before you enqueue it
How to use this snippet
/**
* Normalize a phone string to E.164 (+country…digits) or null.
*
* @param {*} raw
* @param {string=} defaultCountryCode digits only, default '1' (US/CA)
* @return {string|null}
*/
function normalizePhone(raw, defaultCountryCode) {
if (raw == null) return null;
var s = String(raw).trim();
if (!s) return null;
var hasPlus = s.charAt(0) === '+';
var digits = s.replace(/\D/g, '');
if (!digits) return null;
if (hasPlus) return '+' + digits;
var cc = defaultCountryCode == null || defaultCountryCode === ''
? '1'
: String(defaultCountryCode).replace(/\D/g, '');
if (!cc) cc = '1';
// Already includes the country code without a plus.
if (digits.indexOf(cc) === 0 && digits.length > cc.length + 1) return '+' + digits;
// US/CA local 10-digit.
if (cc === '1' && digits.length === 10) return '+1' + digits;
// National numbers often keep a leading 0 trunk prefix — drop it before adding cc.
if (digits.charAt(0) === '0') digits = digits.substring(1);
return '+' + cc + digits;
}
Example
function cleanPhoneColumn() {
var sh = SpreadsheetApp.getActive().getSheetByName('Contacts');
var data = sh.getDataRange().getValues();
var phoneCol = data[0].indexOf('Phone');
var e164Col = data[0].indexOf('Phone E164') + 1;
for (var r = 1; r < data.length; r++) {
sh.getRange(r + 1, e164Col).setValue(normalizePhone(data[r][phoneCol]) || '');
}
}
// (555) 123-4567 → +15551234567
// +44 20 7946 0958 → +442079460958
Tips:
- A leading
+is trusted: digits after it are kept as-is (already international). - Without a
+, US/CA 10-digit numbers become+1…. Other countries: passdefaultCountryCode('44','61', …). A leading national0is stripped before the country code is applied. - Returns
nullfor blank/punctuation-only input — write a blank or flag the row, don't coerce to+1. - This does not check that the number is allocated or reachable. It's formatting, not a lookup.
Tip: NitroGAS Co-Pilot can add a Status column for rows where normalizePhone returns null. Paste your header row.
Happy Coding!
