// Sheets · Apps Script

Create a named range in Google Sheets.

Name a range once with setNamedRange and every subsequent script references it by that name, not by a hardcoded A1 address. Insert rows above it — the name follows the cells automatically.

“I keep hardcoding A1 addresses in my Apps Script and they break silently when someone inserts a row.”

The script

copy · paste · trigger
createNamedRange.gs
Apps Script
// Create a named range 'BudgetData' covering B2:E50 on the active sheet.
// The name is owned by the Spreadsheet, not a single sheet, so any
// script in the project can look it up by name.
function createNamedRange() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  // Define the range by A1 notation — just this once.
  const range = sheet.getRange('B2:E50');
  ss.setNamedRange('BudgetData', range);
}

// Read from it by name anywhere else in your project.
function readNamedRange() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const range = ss.getRangeByName('BudgetData');
  const values = range.getValues();
  Logger.log('Rows in BudgetData: ' + values.length);
}
Generate yours

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

Walkthrough

Why a name beats a hardcoded address

When you call ss.setNamedRange('BudgetData', range), Sheets stores a live pointer to those cells — not a snapshot of the A1 string. Insert three rows above row 2 and BudgetData now covers B5:E53 without a single script edit. The A1 address updated under the hood. Hardcoded strings like sheet.getRange('B2:E50') do not survive that insert; they silently read the wrong rows.

I have watched this bite people who built a working weekly import script, handed it to a teammate, and came back to corrupted reports because the teammate inserted a header section two weeks later. Named ranges are the fix.

The name is scoped to the Spreadsheet object, not to any particular sheet. That means getRangeByName('BudgetData') works from any function in the project, and from any sheet within the same workbook.

Running and verifying the setup

Paste both functions into Extensions → Apps Script and run createNamedRange() once. After it completes, check Data → Named ranges in the Sheets menu — BudgetData should appear there with its current cell coordinates. That UI view is also how you confirm the name updated correctly after inserting rows.

Run readNamedRange() to confirm getRangeByName returns a live Range. The Logger output will show the row count; if it returns null instead of a Range, the name was not created or was created on a different Spreadsheet object (see the gotcha below).

Named ranges created by Apps Script are identical to ones created through the Sheets UI. You can rename or delete them from either place.

Updating and deleting named ranges from script

setNamedRange is an upsert: calling it again with the same name moves the pointer to the new range. There is no separate updateNamedRange call.

To delete a name, get a handle on it first: const named = ss.getNamedRanges().find(n => n.getName() === 'BudgetData'); then call named.remove(). getNamedRanges() returns all names on the Spreadsheet — useful for auditing what a workbook has accumulated over time.

One subtlety: if you call sheet.getRange('B2:E50') and then insert a column before B, the Range object you already hold updates too — it is live. But any A1 string you stored in a variable before the insert does not update. Always pass a live Range to setNamedRange, not a string you built earlier in the same function.

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 getRangeByName work across sheets in the same workbook?
Yes. The name is owned by the Spreadsheet, not a specific sheet tab. getRangeByName returns the Range regardless of which sheet is active, and the Range itself carries a reference to the correct sheet.
What happens to the named range if I delete the sheet it lives on?
Sheets deletes the named range too. getRangeByName will return null afterward. If you need the name to survive sheet deletion, move the range to a different sheet before deleting.
Can I name a non-contiguous range (multiple selections)?
No. The Sheets named-range feature only supports a single contiguous rectangle. For a multi-area reference you need a formula-level name defined in the Name Manager add-on, which is outside Apps Script's setNamedRange surface.
I created the name but getRangeByName returns null. Why?
Almost always this means the scripts are running against different Spreadsheet IDs — one used getActiveSpreadsheet() while the other used openById() with a different ID, or you created the name in a copy of the file and are reading from the original. Confirm both calls resolve to the same ss.getId() value.

Related scripts