Renaming a product, correcting a misspelled client name, or updating a category label often means the same text is scattered across many tabs in one spreadsheet, and fixing each tab by hand with Ctrl+H one at a time is slow and easy to get wrong.
This tutorial loops over every sheet returned by getSheets() and runs Apps Script's built-in TextFinder against each one, replacing every match in a single pass rather than opening each tab and running Find and Replace manually.
createTextFinder returns a TextFinder object configured for one search term, and calling replaceAllWith on it performs every replacement in that sheet at once and returns a count of how many cells changed, which this script totals across the whole spreadsheet.
A Change Log sheet records which tabs had replacements and how many, giving a lightweight audit trail for a bulk edit that would otherwise leave no trace of what changed or where.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Setting | Value | Purpose |
|---|---|---|
| Find text | Q1-2025 | TextFinder find |
| Replace text | Q2-2025 | replaceWith |
| Scope | All sheets in spreadsheet | getSheets() loop |
What it does
replaceTextAcrossAllSheets iterates every sheet in the active spreadsheet, skips its own Change Log tab, runs a case-insensitive text replacement on each remaining sheet, and logs a summary row for any sheet where matches were found.
Prerequisites
matchCase(false) makes the search ignore letter casing, and matchEntireCell(false) allows the finder to match text that is only part of a cell's contents, which together make the search broader than an exact, case-sensitive match would be.
Walkthrough
No special authorization is required beyond the script's normal ability to read and edit the spreadsheet; this works on any spreadsheet the script is bound to or has been granted access to.
Edge cases
The function explicitly skips the Change Log sheet by name so a replacement run does not accidentally rewrite its own audit history, which would happen if that sheet were included in the same loop as the data sheets.
Testing
Because replaceAllWith operates on a live sheet rather than an in-memory copy, there is no automatic undo if the wrong search term is used, which is why this tutorial recommends testing on a duplicate spreadsheet before running it against the real one.
Hardening
Make a copy of the spreadsheet, run replaceTextAcrossAllSheets there first, and check the Change Log's counts against your own expectation of how many cells should have matched before running it against the live file.
Variations
For spreadsheets with formulas, matchEntireCell(false) can accidentally alter text inside a formula string if the search term happens to appear there, so a production version handling sheets with formulas should review whether searchType needs to be restricted to plain values.
Full code: replaceTextAcrossAllSheets()
Duplicate the spreadsheet, run replaceTextAcrossAllSheets there first, and review the Change Log before running the same replacement against the live file.
function replaceTextAcrossAllSheets(searchText, replacementText) {
var spreadsheet = SpreadsheetApp.getActive();
var sheets = spreadsheet.getSheets();
var totalReplacements = 0;
var summaryRows = [];
sheets.forEach(function (sheet) {
if (sheet.getName() === 'Change Log') return;
var finder = sheet.createTextFinder(searchText).matchCase(false).matchEntireCell(false);
var count = finder.replaceAllWith(replacementText);
totalReplacements += count;
if (count > 0) {
summaryRows.push([sheet.getName(), count, new Date()]);
}
});
var logSheet = spreadsheet.getSheetByName('Change Log') || spreadsheet.insertSheet('Change Log');
if (summaryRows.length > 0) {
logSheet.getRange(logSheet.getLastRow() + 1, 1, summaryRows.length, 3).setValues(summaryRows);
}
return totalReplacements;
}- Line 3: Loops over every sheet in the spreadsheet.
- Line 8: Skips the Change Log sheet so it never rewrites its own history.
- Line 9: Builds a case-insensitive, partial-match text finder per sheet.
- Line 10: replaceAllWith performs the replacement and returns a count.
- Line 17: Gets or creates the Change Log sheet for the audit trail.
- Line 19: Writes every summary row in a single batched call.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: replace text across all sheets
- 1Spreadsheet duplicated for a dry run before editing the live file
- 2Change Log sheet excluded from the replacement loop
- 3matchCase and matchEntireCell settings reviewed against the intended search
- 4Total replacement count checked against manual expectations
- 5Formula cells checked for accidental text changes if searchType wasn't restricted
- 6Search and replacement text double-checked for typos before running
- 7Change Log reviewed after the run to confirm which sheets were affected
Frequently asked questions
The built-in UI option works fine for a one-off manual edit, but createTextFinder lets the same replacement run as part of an automated or scheduled process, and it produces a count and log this tutorial uses for an audit trail.
It returns the number of cells that were changed by that specific replacement call, which this script sums across every sheet to report a total.
Change matchCase(false) to matchCase(true), which restricts matches to text with exactly the same letter casing as the search term.
Yes, if matchEntireCell is false and searchType is left at its default, TextFinder can match and replace text inside a formula string, so sheets containing formulas should be reviewed carefully before running a broad replacement.
Call createTextFinder on a Range object instead of a Sheet object, which scopes both the search and any replacement to just that range.
Yes, call findAll() on the TextFinder instead of replaceAllWith to get an array of matching Range objects, which lets you inspect or log what would be changed before committing to the replacement.