Mask sensitive columns for shareable copies

Northwind regularly shares performance data with outside vendors — booking volumes, conversion rates, the numbers a partner needs to do their job. The trouble is the source sheet sits next to columns of personal data: client names, email addresses, phone numbers. You cannot just hand over the file, and copying the useful columns into a fresh sheet by hand is fiddly and easy to get wrong.

A custom function gives you a clean middle ground. =MASK() takes a value and returns a redacted version — enough of it left visible to be recognisable, the rest replaced with asterisks. Build a “shareable” view of the sheet with masked formulas, copy it to values, and send that. The personal data never leaves your copy.

What you’ll need

• A Google Sheet with the columns you want to redact — names, emails, phone numbers.
• A header row, so masked formulas start on row 2.
• Nothing else. The function is pure JavaScript with no external calls, so there is no API key and no setup beyond pasting it into the Apps Script editor.

The script

// The masking modes the function understands. Passing anything else
// falls through to a fully redacted '***'.
const MASK_MODES = {
  EMAIL: 'email',
  PHONE: 'phone',
  NAME: 'name',
};

/**
 * Redacts a value for sharing. Keeps just enough visible to stay
 * recognisable, and replaces the rest with asterisks.
 *
 * @param {string} value The value to mask.
 * @param {string} [mode] One of 'email', 'phone', or 'name'.
 * @return {string} The masked value, or '' for a blank cell.
 * @customfunction
 */
function MASK(value, mode = MASK_MODES.EMAIL) {
  // 1. Blank in, blank out — never mask an empty cell into '***'.
  if (value === '' || value === null || value === undefined) return '';
  const s = String(value);

  // 2. Email: keep the first letter and the whole @domain, hide the rest.
  //    [email protected] -> a***@northwind.studio
  if (mode === MASK_MODES.EMAIL) {
    return s.replace(/^(.).+(@.+)$/, '$1***$2');
  }

  // 3. Phone: star every digit except the last four, so a partner can
  //    still match a number against their own records.
  if (mode === MASK_MODES.PHONE) {
    return s.replace(/.(?=.{4})/g, '*');
  }

  // 4. Name: reduce each word to its initial, e.g. "Anita Miller" -> "A. M.".
  if (mode === MASK_MODES.NAME) {
    return s
      .split(/\s+/)
      .filter(Boolean)
      .map((part) => part[0].toUpperCase() + '.')
      .join(' ');
  }

  // 5. Unknown mode: redact the value completely rather than leak it.
  return '***';
}

How it works

1. MASK receives the value and a mode string. Because it is tagged @customfunction, Sheets exposes it as a formula.
2. An empty cell returns an empty string immediately. Without that guard a blank row would be masked into a misleading ***.
3. In email mode, a regular expression keeps the first character of the local part and the entire @domain, replacing everything in between with ***. The domain is usually safe to show and helps a vendor sanity-check the data.
4. In phone mode, the lookahead .(?=.{4}) matches any character that still has at least four characters after it — so every digit except the last four is starred.
5. In name mode, the value is split on whitespace and each word collapses to a capitalised initial followed by a full stop.
6. Any other mode value falls through to a complete *** redaction. The default is to hide, never to leak.

Example run

Say a sheet has client contact details in columns B, C, and D. You add three columns alongside with masking formulas:

Email (B)	=MASK(B2,“email”)	Phone (C)	=MASK(C2,“phone”)	Name (D)	=MASK(D2,“name”)
[email protected]	a***@northwind.studio	020 7946 1234	**********1234	Anita Miller	A. M.
[email protected]	t***@example.com	07700 900456	********0456	Tom Lyle	T. L.

Copy the masked columns, paste them as values into a fresh sheet, and that file is safe to share. The original personal data stays only in your copy.

Use it

Type the formula into the first data row, passing the column to redact and the mode:

=MASK(B2, "email")    →  a***@northwind.studio
=MASK(C2, "phone")    →  **********1234
=MASK(D2, "name")     →  A. M.

Fill each formula down its column. Before sharing, select the masked columns, copy them, and use Paste special → Values only in the destination file so the recipient gets static text — not formulas that reference your private data.

Watch out for

• Masked formulas still reference the original cells. If you share the live file rather than a values-only copy, anyone with access can read the source columns the formulas point at. Always paste as values into a separate file.
• MASK is presentation, not encryption. The output is irreversible, but it is not a security boundary — treat it as redaction for a screenshot, not a way to protect data inside a file you do not control.
• The email regex assumes a standard local@domain shape. An address with no @, or an unusual format, will not match and is returned unchanged — which could leak it. Check the column type before you rely on the result.
• phone mode stars characters, not just digits. A number written with spaces or brackets keeps that punctuation starred too, so **********1234 may not line up digit-for-digit with the original. That is fine for redaction but do not parse the masked value back into a number.