// Sheets · Apps Script

Find and replace across a sheet in Google Sheets.

Use Apps Script's TextFinder to find and replace text across an entire Google Sheet in one call, without overwriting formulas or losing data.

“I need to programmatically find and replace a string across my whole Google Sheet without manually opening the Find & Replace dialog every time.”

The script

copy · paste · trigger

findReplace.gs

Apps Script

// Find and replace across the active sheet using TextFinder
// Safe for sheets that contain formulas
function findAndReplaceInSheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var finder = sheet.createTextFinder('old value');
  finder.matchCase(false);
  finder.matchEntireCell(false);

  var count = finder.replaceAllWith('new value');

  Logger.log('Replacements made: ' + count);
}

// To target the entire workbook instead of one sheet:
// var finder = SpreadsheetApp.getActiveSpreadsheet().createTextFinder('old value');

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why TextFinder and not getValues/setValues

The instinct most people reach for first is getValues() on the full range, map() over the 2D array to swap strings, then setValues() to write it back. That works until it does not. Any cell containing a formula gets flattened to its current display value on the round-trip. A cell with =SUM(A1:A10) becomes 42. Permanently. TextFinder operates on the cell content layer directly, the same layer the native Ctrl+H dialog touches, so formulas survive untouched.

createTextFinder() returns a TextFinder object bound to whatever you called it on: a single range, a sheet, or the whole spreadsheet. replaceAllWith() fires the substitution in one API call and returns the number of cells changed, which is useful for logging or conditional logic. There is no loop to write, no quota to burn on individual setValue() calls per cell.

Controlling what counts as a match

Three options matter most in practice. matchCase(true) makes the search case-sensitive; the default is false. matchEntireCell(true) requires the cell content to equal the search string exactly, which is what you want when replacing a status code like 'PENDING' and you do not want to touch 'PENDING_REVIEW'. useRegularExpression(true) opens up pattern matching, though regex mode and replaceAllWith() together follow JavaScript regex replacement syntax for the replacement string, including capture-group references like $1.

I keep matchEntireCell(false) as the default in any shared utility because the callers almost always want substring replacement. The one time I silently left it true on a batch-rename script, every partial match was skipped and nobody noticed until a downstream report showed stale values three days later. Worth an explicit comment in your code stating which mode you chose.

Scoping to a named range or a specific sheet tab

If the replacement should only touch one tab, call createTextFinder() on that sheet object as shown in the snippet. To limit to a specific block of cells, call getRange('B2:D50') first and call createTextFinder() on that range object instead. The API signature is the same regardless of what you call it on.

To iterate over every sheet in the workbook and log which tabs had matches, getSheets() returns an array you can loop over, calling createTextFinder() on each sheet and accumulating the counts. That is cheaper than calling it on the spreadsheet root when you need per-tab reporting, because the root-level call gives you only one aggregate count back.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Does replaceAllWith work on cells that contain formulas?: Yes. TextFinder operates on the underlying cell content, so a formula string like =VLOOKUP(...) can itself be matched and rewritten if your search term appears in the formula text. The formula is not evaluated and then replaced; it is treated as a string. That also means if you are only trying to replace displayed values in formula cells, TextFinder will not do it — the display value is not the cell content.
How do I make the replace case-sensitive?: Call finder.matchCase(true) before replaceAllWith(). The default is case-insensitive. There is no way to set this after replaceAllWith() has already run; the options must be set on the finder object before you trigger the replacement.
Can I use a regex pattern as the search string?: Yes. Call finder.useRegularExpression(true) and pass a regex pattern string to createTextFinder(). The replacement string in replaceAllWith() then accepts $1, $2, etc. for capture groups, following JavaScript regex replacement conventions. One gotcha: the pattern is matched against the full cell content string, not just a substring, so you may need .* anchors if you expect partial-cell matches.
What does replaceAllWith return if nothing matched?: It returns 0. It does not throw an error on zero matches. If your script needs to distinguish 'nothing found' from 'something replaced', check the return value explicitly.