updateFormulas

function updateFormulas(
  target: GoogleAppsScript.Spreadsheet.Sheet | GoogleAppsScript.Spreadsheet.Range,
  rewrite: FormulaTransformer | Record<string, string>
): number;

Pass a map to replace sheet names wholesale — the usual need after a rename or a copy — or a function to decide each formula for itself. The function receives the formula and returns the one to put back.

Only cells that hold a formula are visited, and only the ones that actually changed are written, so a no-op costs one read and nothing else.

A sheet means its whole data range; a range means only the cells inside it, so a rewrite can be confined to one block. The row and column handed to the transformer are positions on the sheet either way, which is what makes a formula's own address usable in the replacement.

Parameters

Parameter	Type	Description
`target`	`GoogleAppsScript.Spreadsheet.Sheet \\| GoogleAppsScript.Spreadsheet.Range`	The sheet whose formulas are rewritten, or the range to rewrite within: only its cells are read and written.
`rewrite`	`FormulaTransformer \\| Record<string, string>`	A map of old sheet name to new one, or a function taking a formula and returning its replacement.

Returns

number — how many formulas were rewritten.

Throws

Exception	Condition
`InvalidSheetException`	the first argument is neither a sheet nor a range.

Examples

Rewriting formulas

const sheet = SpreadsheetApp.getActiveSheet();

// After renaming a sheet, point the formulas at the new name.
updateFormulas(sheet, { Sheet1: "Data" });

updateFormulas(sheet, (formula) => formula.replace(/OLD_/g, "NEW_"));

Within one block

const sheet = SpreadsheetApp.getActiveSheet();

// Only the formulas in D2:D100 are rewritten.
updateFormulas(sheet.getRange("D2:D100"), { "=SUM(A2:A)": "=SUM(A2:A1000)" });

Source

src/appsscript/sheet/updateFormulas.ts

23 September 2026

This documentation was generated with AI (Claude) from the library's source code.

apps-script-utils 2.1.1 Help