Google WorkspaceSecurity & Privacy

Hash Email Addresses Correctly in Google Sheets

8apps Team·

Email hashing fails on casing, invisible spaces and Gmail's dot rules – not on the hash. The normalisation order that makes digests actually match, platform by platform.

The hash is fine. The input is the problem.

John@Example.com, john@example.com and john@example.com (trailing space) are one mailbox and three completely different SHA-256 digests. Every failed email match traces back to a moment like that – so this page is mostly about the three seconds before the hash.

The universal pass: trim, then lowercase

Both major platforms agree on the baseline. Meta: "Trim any leading and trailing spaces. Convert all characters to lowercase." Google Ads: lowercase, whitespace stripped. In Sheets:

=LOWER(TRIM(A2))

Two traps survive that formula:

  • The non-breaking space. TRIM() does not remove CHAR(160), the invisible character CSV exports and copy-pastes plant. If a visually clean cell produces a wrong digest: =SUBSTITUTE(A2, CHAR(160), "") first.
  • Hidden characters from rich sources. Addresses pasted from email clients occasionally carry zero-width characters. A length check against what you expect (=LEN(A2)) is the cheap detector.

The platform fork: Gmail's dots and plus signs

Here the platforms genuinely differ, and copying one platform's rule to the other corrupts the data:

  • Google Ads documents extra cleanup for gmail.com and googlemail.com addresses only: remove the dots in the username, and remove everything from a + onwards. Jane.Doe+Shopping@googlemail.com → janedoe@googlemail.com. Applying this to other domains is wrong – dots are significant in most mail systems, and Google's rule names those two domains alone.
  • Meta documents no dot or plus rule at all. Trim and lowercase is the entire documented instruction.

So a Gmail-heavy list needs two email columns – one Google-normalised, one Meta-normalised – exactly as phone numbers do (that story is in our phone-hashing guide).

Then hash – and prove it once

SHA-256, lowercase hex output – Meta's docs specify lowercase hex explicitly. Sheets has no built-in hash function (nothing cryptographic anywhere in Google's function list), so the digest comes from Apps Script or an add-on. Whichever you use, prove the pipeline once: Meta publishes the expected digest for john_smith@gmail.com –

62a14e44f765419d10fea99367361a727c12365e2520f32218d505ed9aa0f62f

– so hash that exact string. The right answer validates your whole chain; anything else means fix the formula before touching the list.

The checklist

  1. SUBSTITUTE(…, CHAR(160), "") if the source was CSV or paste
  2. LOWER(TRIM(…)) – always
  3. Gmail dot/plus cleanup – Google Ads column only, those two domains only
  4. SHA-256, lowercase hex
  5. Test vector check – once per pipeline, not per list

Where Hash Data fits

Hash Data puts the hashing functions in the sheet next to these formulas, so normalise-then-hash is a fill-down rather than a script to maintain – several algorithms, standard hex output, in the spreadsheet the list already lives in.

→ Hash Data on the Google Workspace Marketplace

Sources

Tags

google sheetsemailhashingsha256normalisationcustomer matchhash data8apps
← Back to Blog