How to Hash Phone Numbers and Emails in Google Sheets

Everyone gets this backwards. Producing a SHA-256 digest takes four lines of Apps Script. Getting a match out of an ad platform takes normalisation, and normalisation is where lists die.

Google says it plainly in its own API documentation: "If the contact information is not correctly formatted before hashing, the API still accepts the hashed information, but it can't be matched with a customer."

The upload succeeds. The match rate is what fails. Nothing tells you.

This guide covers both halves - how to actually produce a hash in Google Sheets, and the per-platform normalisation rules that decide whether it matches anything. There are four ways to do the first half. The shortest is an add-on that normalises and hashes in one function - Route 1 below - and the rest of this page is the detail behind why normalisation is the part worth getting right.

First: Sheets has no hash function

Google's published function list runs to seventeen categories - Date, Engineering, Financial, Text, Math, and so on. There is no SHA256, no MD5, no HASH, no DIGEST. The Engineering category holds base conversions and complex numbers and nothing cryptographic.

So every route is a workaround. There are four, and they are not equally good:

The rest of this section covers each in turn. The normalisation rules further down apply whichever one you pick - they are the part that decides your match rate, and no route exempts you from them.

Route 1: an add-on that puts the functions in the sheet

This is the one to reach for, and the reason is the argument this whole page is making. Producing a digest is trivial. Normalising the input correctly is what decides whether the upload matches anybody - and that is the step an add-on can do for you, per input type, the same way on every row.

Hash Data splits the job accordingly:

Four things this buys you over a hand-rolled script:

• The normalisation is in the function, not in a helper column you have to rebuild in every new sheet.
• No 30-second custom-function ceiling to design around - see Route 2 for why that matters on a real list.
• Your list stays in the spreadsheet. Nothing is pasted into a third-party website.
• SHA3-256 is available, which Apps Script's own computeDigest() does not offer.

One caveat, stated plainly, because it decides which function you use. =HASHPHONE() normalises to E.164 - digits with a leading +. That matches what Google Ads documents. Meta's documented example goes the other way: digits only, no +. So for a Meta upload, normalise the number yourself with =REGEXREPLACE(A2, "[^0-9]", "") and hash that string with =HASH(). One function does not serve both platforms, because the platforms do not agree - which is the same point the comparison table below makes.

The free tier covers 100 hash calls a day; the paid tier removes the cap.

→ Install Hash Data from the Google Workspace Marketplace

Route 2: write the custom function yourself

If you would rather own the code, this is the whole of it. Open Extensions → Apps Script, paste this, save, and use =SHA256(A2) in the sheet.

/**
 * Returns the lowercase hex SHA-256 digest of the input.
 * Accepts a single cell or a range.
 * @customfunction
 */
function SHA256(input) {
  if (Array.isArray(input)) {
    return input.map(function (row) {
      return row.map(function (cell) { return SHA256(cell); });
    });
  }

  if (input === '' || input === null || input === undefined) return '';

  var bytes = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    String(input),
    Utilities.Charset.UTF_8
  );

  return bytes
    .map(function (b) { return ((b < 0 ? b + 256 : b)).toString(16).padStart(2, '0'); })
    .join('');
}

Two things trip people up here, and both are worth understanding even if you never write the script yourself.

computeDigest() returns a signed byte array, not a hex string. Apps Script hands back Java-style signed bytes, so anything above 0x7F comes through negative. That is what b < 0 ? b + 256 : b is fixing. Skip it and you get a digest full of - characters that no platform will accept. Skip the padStart(2, '0') and you silently drop leading zeros from individual bytes, producing a digest shorter than 64 characters that is wrong in a way that is very hard to see.

A custom function must return within 30 seconds, or the cell shows #ERROR!. A per-cell version - one formula in every row - breaks on a real customer list. That is why the function above tests for an array first: written that way, you can put =SHA256(A2:A5000) in a single cell and let it return the whole column as one array.

Route 3: a web hasher

This is the route to think hardest about, because it is the easiest one to reach for and the only one with a disclosure problem.

Pasting a customer list into a website is a transfer of personal data to whoever runs that website. You generally cannot tell from the page whether the hashing happens in your browser or on their server, and the answer matters enormously. For checking a single test string, fine - there is one further down this page you should use. For a list of real customers, no.

Route 4: don't hash at all

The forgotten option. Google Ads accepts plaintext customer data and hashes it before upload, and TikTok documents that it "accepts both hashed and original values."

So if your reason for hashing is a policy or contractual one, hashing is the answer. If you assumed it was mandatory, it often is not - and pre-hashing means you own the normalisation, and every mistake in it, instead of the platform. That is worth knowing before you build a pipeline around it.

Then: normalise, and normalise per platform

This is the part most guides skip, and it is the reason match rates collapse.

Google Ads

Customer Match documents, for hashed columns: lowercase everything, strip whitespace, "Format phone numbers using the E.164 format" and - explicitly - "Include the country code and '+' sign."

Google documents a Gmail-only rule in two places, and they are not identical - which is worth knowing before you copy one into a pipeline.

The Customer Match data-file page says only: "Remove all periods (.) that precede the domain name in gmail.com and googlemail.com email addresses." Periods, nothing else.

The Customer Match API page goes further, but under a heading scoped to enhanced conversions: strip periods from the username and the plus sign with everything after it. Its worked example is Jane.Doe+Shopping@googlemail.com → jane.doe+shopping@googlemail.com (lowercase) → janedoe@googlemail.com.

So the plus-suffix half is documented for enhanced conversions, not for a customer-list upload. If you are building an audience list, the periods rule is the one Google actually documents for that job. What both agree on: this applies to gmail.com and googlemail.com only. Applying it to every domain is its own bug - plenty of mail systems treat jane.doe@ and janedoe@ as different people.

Meta

Meta documents something different. For email: "Trim any leading and trailing spaces. Convert all characters to lowercase." No Gmail dot rule at all.

For phone: "Remove symbols, letters, and any leading zeros," plus "Always include the country code as part of your customers' phone numbers." Meta's own worked example is (650)555-1212 → 16505551212 - country code, digits only, no plus sign.

So the same phone number produces two different hashes

One hashed column cannot serve both platforms. If you have been reusing one, that is your match rate.

TikTok is the outlier

TikTok documents far less: "MD5 and SHA256 encryption is supported. The content of the file before encryption needs to be in all uppercase or lowercase." No E.164 requirement is published, and it accepts both hashed and original values. Do not assume Google's rules apply there.

The formulas, in the order you apply them

Normalise in a helper column, hash the helper column, then keep only the digest.

That last row carries a caveat worth stating out loud: prefixing + to a national number does not create a valid E.164 number. 07700 900461 becomes +7700900461, which is wrong and will not match. The country code has to actually be there.

The failure modes, in the order they happen

1. Your spreadsheet destroys the data before you hash it. A 16-digit number becomes scientific notation, 07700 900461 loses its leading zero, +44… gets read as a formula. Format the column as plain text before anything lands in it - fixing it afterwards is data loss, not formatting.
2. Casing and whitespace. John@ and john@ are different digests. So are john@x.com and john@x.com with a trailing space from a CSV export. And TRIM() will not remove a non-breaking space, which is exactly what a copy-paste from a web page leaves behind.
3. Hashing the display value. (650) 555-1212 is not 16505551212. If the cell is formatted as a phone number, what you see and what you hash are different strings.
4. Double-hashing. Running the formula over a column that already holds hashes. Easy check: a SHA-256 hex digest is always exactly 64 characters of 0-9a-f, so =LEN(B2)=64 catches it - and catches truncation too.
5. Wrong encoding. A base64 digest is a valid hash of the right input and still will not match. Several popular copy-paste snippets default to MD5 and base64.

Verify before you upload

Meta publishes the expected digest for john_smith@gmail.com:

62a14e44f765419d10fea99367361a727c12365e2520f32218d505ed9aa0f62f

Hash that exact string with your formula. If you do not get that exact value back, fix the formula - not the list. It takes thirty seconds and it is the only test that distinguishes "my hashing is broken" from "my list is bad", which are otherwise indistinguishable from the outside.

One honest word about privacy

Hashing is pseudonymisation, not anonymisation. The EDPB's January 2025 guidelines state that "pseudonymised data, which could be attributed to a natural person by the use of additional information, is to be considered information on an identifiable natural person, and is therefore personal."

An unsalted phone hash is brute-forceable - the number space is small enough to exhaust. And you cannot salt it, because salting is exactly what stops the platform matching. Hashed customer data is still customer data. Treat it that way.

When you are done: copy the hashed column, paste special as values only, and delete the raw column.

The whole thing, in order

Whichever route you took, the sequence is the same:

1. Format the column as plain text before the data lands in it.
2. Normalise in a helper column, using the rules for the platform you are uploading to - not the other one.
3. Hash the helper column, hex SHA-256 unless the platform says otherwise.
4. Test one known string against Meta's published digest before you trust the column.
5. Check =LEN(B2)=64 across the range to catch truncation and double-hashing.
6. Paste special as values only, delete the raw column, and upload.

Steps 2 and 3 are the ones Hash Data collapses into a single function - =HASHEMAIL() for email, =HASHPHONE() for E.164 phone numbers, =HASH() for anything you have normalised yourself. It supports SHA256, MD5, SHA1, SHA512 and SHA3-256, and everything runs inside your spreadsheet. The free tier covers 100 hash calls a day; the paid tier removes the cap.

→ Install Hash Data from the Google Workspace Marketplace

Sources

• Google Sheets function list
• Apps Script - Utilities.computeDigest and DigestAlgorithm
• Apps Script - Custom functions in Google Sheets
• Google Ads Help - Format your customer data file
• Google Ads API - Get started with Customer Match
• Meta - Conversions API customer information parameters
• Meta - Custom Audiences guide
• TikTok - Guidelines for customer files
• EDPB Guidelines 01/2025 on Pseudonymisation

Platform specifications verified 20 August 2026. Ad platforms revise these pages; check the source before a large upload.

Related reading

• Hash Data documentation - =HASHPHONE()
• Best practices for hashing in Google Sheets
• How to Audit Who Has Access to Your Google Drive