Sheets' built-in Find and Replace can also rewrite formula text — but only when Also search within formulas is checked. Leave that box alone on a 14-tab workbook and you still want a pass you can dry-run. replaceInAllTabs(find, replace, {dryRun}) changes plain value cells only, skips any cell getFormulas() says is a formula, returns a count per tab, and can count without writing.
When to use
- A client or product rename that appears as text on many tabs
- A repeatable, auditable cleanup you can run first as a dry run
- Any replace you must not let touch formulas
How to use this snippet
/**
* Replace text in value cells on every tab. Formula cells are skipped.
* Writes are batched as contiguous runs per tab so formulas are never overwritten.
*
* @param {string} find
* @param {string} replace
* @param {{dryRun?: boolean, spreadsheet?: GoogleAppsScript.Spreadsheet.Spreadsheet}} [opts]
* @return {{counts: Object<string, number>, total: number, dryRun: boolean}}
*/
function replaceInAllTabs(find, replace, opts) {
opts = opts || {};
if (find == null || String(find) === '') throw new Error('replaceInAllTabs: find is empty');
var ss = opts.spreadsheet || SpreadsheetApp.getActive();
var dryRun = !!opts.dryRun;
var findStr = String(find);
var replStr = replace == null ? '' : String(replace);
var counts = {};
var total = 0;
function writeRuns(sheet, changes) {
var i = 0;
while (i < changes.length) {
var row = changes[i].r, start = changes[i].c, vals = [changes[i].v];
i++;
while (i < changes.length && changes[i].r === row && changes[i].c === start + vals.length) {
vals.push(changes[i].v);
i++;
}
sheet.getRange(row, start, 1, vals.length).setValues([vals]);
}
}
ss.getSheets().forEach(function (sheet) {
var lastRow = sheet.getLastRow(), lastCol = sheet.getLastColumn();
var name = sheet.getName();
if (!lastRow || !lastCol) { counts[name] = 0; return; }
var range = sheet.getRange(1, 1, lastRow, lastCol);
var values = range.getValues();
var formulas = range.getFormulas();
var changes = [];
for (var i = 0; i < values.length; i++) {
for (var j = 0; j < values[i].length; j++) {
if (formulas[i][j]) continue;
var v = values[i][j];
if (typeof v !== 'string' || v.indexOf(findStr) === -1) continue;
changes.push({ r: i + 1, c: j + 1, v: v.split(findStr).join(replStr) });
}
}
if (!dryRun && changes.length) writeRuns(sheet, changes);
counts[name] = changes.length;
total += changes.length;
});
return { counts: counts, total: total, dryRun: dryRun };
}
Example
function renameClient() {
var preview = replaceInAllTabs('OldCo', 'NewCo', { dryRun: true });
Logger.log(preview.counts);
var live = replaceInAllTabs('OldCo', 'NewCo');
Logger.log('Updated ' + live.total + ' cells');
}
Notes
- A cell counts as a formula when
getFormulas()returns a non-empty string, including=HYPERLINK(...). - Only string values are changed. Dates and numbers are left alone.
- Writes never use one
setValuesover a whole tab — that would paste values on top of formulas. Changed value cells are written in contiguous batches. - The match is case-sensitive and substring-based (
AcmematchesAcme Inc).
Happy Coding!
