Remove PII for Free Without Leaving Google Sheets

Introduction

You need to share a sample of your data with a vendor, a colleague or a support thread. The file works for the task, but it carries emails, phone numbers and IP addresses that have no business leaving your systems.

Scrubbing replaces those values with something that keeps the shape of the data without the person behind it. A spreadsheet has the functions to do it, and every change is visible before the file is shared.

Work on a copy. Scrubbing is destructive once values are pasted over the original column, and a spreadsheet's version history is the only easy way back.

What Counts as Personal Data

Personal data is any value that can point to a person, directly or in combination with another value. In a typical export the obvious ones are these:

• Names and postal addresses: direct identifiers that are easy to overlook because they look ordinary.
• Email addresses: direct identifiers that often double as a login.
• Phone numbers: identifiers that are easy to trace and hard to change.
• IP addresses: network identifiers that can point to a device or a location.
• Account and card numbers: financial identifiers that carry a legal retention duty in many countries.

Combinations matter as much as single fields. A postcode, a birth date and a job title together can identify a person even when no name appears. Review the file by column, not just by pattern.

Find the Columns That Need Treatment

Before changing anything, measure. ISEMAIL tests whether a value looks like an email address, and SUMPRODUCT counts the matches across a column:

=SUMPRODUCT(--ISEMAIL(A2:A1000))

A count near the number of rows means the column is a contact field and needs treatment. A count of zero means the column is safe from that pattern, though it may still hold a name or an address.

For patterns without a dedicated function, REGEXMATCH does the same job. This count finds values that look like an IPv4 address:

=SUMPRODUCT(--REGEXMATCH(C2:C1000, "\d{1,3}(\.\d{1,3}){3}"))

Put one count per column on a summary tab. The result is a short list of columns to treat, and a record of why each one was chosen.

Run the same counts on the output afterwards. A count that stays the same means the treatment missed the values, which is the failure mode worth catching before the file leaves.

Mask Values with REGEXREPLACE

Masking keeps enough of a value to confirm its format and removes the identifying part. For an email address, keep the domain and replace the local part:

=REGEXREPLACE(A2, "([A-Za-z0-9._%+-]+)@([A-Za-z0-9.-]+)", "***@$2")

The result turns alice@example.com into ***@example.com. The domain survives so the reader can see which organisation the record belongs to, while the mailbox name does not.

Phone numbers and IP addresses are easier to mask digit by digit:

=REGEXREPLACE(B2, "\d", "*")

Applied to an IP address, the pattern keeps the dots and replaces every digit, which preserves the column as a recognisable address without exposing the network. Applied to a phone number, the pattern masks every digit and keeps only the plus sign, because it matches digits and not punctuation.

The formula writes into the cell that holds it. Put it in a new column, check a sample by eye, then copy the result and use Paste special, then Values only, to replace the original column. Delete the helper column afterwards, because a masked value beside the original protects nothing.

Redact Across the Sheet

When the reader needs nothing from a column, remove the value rather than mask it. The quickest way is Find and replace with regular expressions, which works across the whole spreadsheet at once.

Open Edit, then Find and replace, and tick Search using regular expressions. Use this pattern to match any email address:

[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}

Replace it with a marker such as [redacted] and choose Replace all. The same approach works for a phone number pattern or an IP address pattern, one field type at a time.

For a column that should be empty, clear the contents instead. Select the column, press Delete, and confirm that no formula elsewhere depends on it.

Redaction is irreversible in the sheet. Keep the original file, or rely on version history, until the scrubbed copy has been reviewed.

Hash Identifiers with Apps Script

Hashing is the middle option. It replaces a value with a fixed-length digest, so equal inputs produce equal digests and grouping still works, while the original text cannot be read from the output.

Google Sheets has no native hash function. Apps Script provides Utilities.computeDigest, which takes a digest algorithm, a string and a character set:

function hashColumn() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const salt = PropertiesService.getScriptProperties().getProperty("HASH_SALT");
  if (!salt) {
    throw new Error("Set HASH_SALT in the script properties first.");
  }
  const values = sheet.getRange("A2:A1000").getValues();
  const digests = values.map(function (row) {
    if (row[0] === "") {
      return [""];
    }
    const bytes = Utilities.computeDigest(
      Utilities.DigestAlgorithm.SHA_256,
      salt + row[0],
      Utilities.Charset.UTF_8
    );
    const hex = bytes.map(function (byte) {
      return ((byte + 256) % 256).toString(16).padStart(2, "0");
    });
    return [hex.join("")];
  });
  sheet.getRange(2, 2, digests.length, 1).setValues(digests);
}

The function reads column A, hashes each value with a secret salt and writes a 64-character hex digest into column B. Bytes come back as signed numbers, which is why each one is shifted into the 0 to 255 range before conversion.

The salt is stored in Project Settings under Script properties, never in a cell. It is what stops someone with a list of common email addresses from hashing each one and matching your output.

Any spreadsheet that uses the same salt produces the same digests, so two scrubbed files can still be joined on the hashed email. Use a different salt for each sharing context when that link is not wanted.

Replace the column reference and the output range to match your sheet, and clear the output column before a second run.

A Worked Example

A support export holds one row per customer:

Masking the email local part, every digit in the phone number and every digit in the IP address produces this shareable copy:

Each digit is replaced one for one, so the octets keep their original length: a single-digit octet becomes one star and a two-digit octet becomes two. If the length itself must not be visible, replace each run of digits with a fixed marker by using the pattern \d+ instead of \d.

The two email values now look identical even though the mailboxes differ. That is the trade a mask makes: the column still proves that an email was present, and it no longer says which one.

For a use case that needs to count repeat customers, hash instead. The two digests stay different, so a pivot table can still count distinct customers without exposing an address.

Troubleshooting Common Issues

• A masked value still contains the name: the pattern covered the wrong part. Check the replacement with one sample before filling the column.
• The count does not drop to zero: some values are in an unexpected format, such as an email split across two columns. Review the leftovers by eye.
• The hash looks different on two machines: the salt or the character set differs. Use the same script properties and UTF-8 for both files.
• The digest starts with a minus sign: signed bytes were converted without the shift. Apply the 256 adjustment before converting each byte.
• The original column is still visible: hiding a column does not remove it. Delete the source data and download a fresh copy before sharing.

What Scrubbing Does Not Do

Scrubbing is a sharing aid, not a guarantee. Three limits are worth stating plainly.

• A pattern is not a meaning: a name in a free-text field, a date of birth or an address in an unusual format survives every regex above.
• A hash is not anonymity: an unsalted hash of an email can be reversed by hashing a list of likely addresses. A salt makes that harder, but hashed data can still be personal data under a privacy policy.
• A file has more than cells: comments, notes, hidden tabs, named ranges and version history can carry the values you removed from the visible grid.

If your data is governed by a policy, review the output against that policy before it moves. The scrubber is the step before the review, not a replacement for it.

Download a fresh copy rather than sharing the workbook. A downloaded CSV contains the current values of the active sheet and does not carry the other tabs, the comments or the history with it.

Share the Shape of the Data, Not the People in It

Sharing a file should not mean sharing the people inside it. A spreadsheet can find the columns that hold personal data, mask the values with REGEXREPLACE, overwrite them with Find and replace, or hash identifiers so grouping still works.

Choose the treatment by what the reader needs. Mask when the format must remain visible, redact when the column can go, and hash when records must still be counted or joined.

When the cleaning step extends beyond personal data, Flookup Data Wrangler standardises the rest of the records inside the same sheet.

Frequently Asked Questions