Why does getRange work fine when I test but throws Range not found in a trigger?

Time-driven and form-submit triggers run without an active user session. getActiveSheet() can return a different sheet than during manual testing, or null if the spreadsheet has changed structure. Use getSheetByName('YourSheetName') to target a specific sheet by name instead of relying on whichever sheet happens to be active.

getRangeByName returns null but the named range shows up in the spreadsheet UI. What's wrong?

Named ranges are scoped. A range created under a specific sheet is addressable as 'SheetName!RangeName'. If your script calls ss.getRangeByName('RangeName') and the name is sheet-scoped, you may need to call it as ss.getRangeByName('SheetName!RangeName'), or delete and re-create the named range as spreadsheet-scoped from the Data > Named ranges panel.

I get Range not found only on the first row of my loop. Why only the first row?

Row index 0. If you initialize your loop variable at 0 and pass it directly to getRange(i, col), the first iteration calls getRange(0, col), which is invalid because Apps Script row indices start at 1. Initialize at 1, or add 1 when passing the index.

The error says 'Range not found' but my A1 notation looks correct. How do I debug it?

Log the exact string you're passing to getRange immediately before the call: Logger.log('range arg: ' + rangeArg). Copy that logged value and paste it into the Name Box in the sheet UI. If the sheet accepts it, the problem is the object type (Spreadsheet vs Sheet). If the sheet rejects it, the string has a whitespace character, a hidden Unicode character, or a typo that wasn't visible in the code editor.

// Sheets · Apps Script

Fix "Range not found" in Google Sheets scripts.

Why Apps Script throws "Range not found" on valid-looking A1 notation, and how to fix it — covering the Spreadsheet vs Sheet object gotcha and dangling named-range references.

“I have a script that calls getRange with a perfectly valid cell address and it throws "Range not found" at runtime, and I don't know why.”

The script

copy · paste · trigger

fix-range.gs

Apps Script

// Diagnose and fix Range not found in Apps Script
function safeGetRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data');

  // WRONG: getRange on the Spreadsheet object requires sheet prefix
  // var bad = ss.getRange('A1'); // throws Range not found

  // RIGHT: call getRange on the Sheet object
  var cell = sheet.getRange('A1');
  Logger.log(cell.getValue());

  // Named range check: skip if the name no longer exists
  var named = ss.getRangeByName('SalesTotal');
  if (named === null) {
    Logger.log('Named range SalesTotal has been deleted');
    return;
  }
  Logger.log(named.getValue());
}

Generate yours

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

Walkthrough

The Spreadsheet vs Sheet object split

The most common cause is calling getRange on the wrong object. The Apps Script API has two classes that both expose getRange, and they resolve A1 notation differently. SpreadsheetApp.getActiveSpreadsheet() returns a Spreadsheet object. When you call getRange('A1') on that object, it expects the notation to include a sheet name prefix, like 'Data!A1'. Without it, the runtime has no sheet context to resolve against, so it throws Range not found.

The fix is almost always one line: get the Sheet object first. Call ss.getSheetByName('Data') or ss.getActiveSheet(), then call getRange('A1') on that. The Sheet object already carries its own sheet context, so bare A1 notation works.

The first time I hit this, I spent twenty minutes staring at a valid-looking address before noticing the object type in the variable name. Worth naming your variables ss for spreadsheet and sheet for the Sheet object from the start; the distinction stays visible in every line that follows.

Deleted named ranges leave silent danglers

The second cause is harder to spot: a script references a named range by name, someone deletes it in the spreadsheet UI (Data > Named ranges), and the script keeps running until the next execution, when getRangeByName returns null and any immediate method call on it throws.

getRangeByName never throws on a missing name. It returns null quietly. The throw comes one line later when you call .getValue() or .getValues() on null. The stack trace points at that line, not at the getRangeByName call, which misleads the diagnosis.

The fix is a null guard before every getRangeByName call, as shown in the snippet above. If the guard fires in production, log the name and bail gracefully. Then go to Data > Named ranges in the sheet and either re-create the name or update the script to use the current name.

Row and column index errors that look like range errors

getRange also accepts row and column indices as integers: sheet.getRange(row, col). If either index is zero or negative, the call throws with the same Range not found message even though the code looks nothing like a range-not-found bug. Apps Script uses one-based indexing, so the first cell is getRange(1, 1), not getRange(0, 0).

A loop that iterates from zero, or a calculation that underflows to zero on an edge case, produces this error intermittently, which makes it harder to catch in testing. Log the row and col values immediately before the call when you're chasing an intermittent Range not found.

If the error only appears on certain sheets, check the sheet's actual row and column count with sheet.getLastRow() and sheet.getLastColumn() and confirm your indices stay inside those bounds. getRange does not clamp silently; it throws.

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

Why does getRange work fine when I test but throws Range not found in a trigger?: Time-driven and form-submit triggers run without an active user session. getActiveSheet() can return a different sheet than during manual testing, or null if the spreadsheet has changed structure. Use getSheetByName('YourSheetName') to target a specific sheet by name instead of relying on whichever sheet happens to be active.
getRangeByName returns null but the named range shows up in the spreadsheet UI. What's wrong?: Named ranges are scoped. A range created under a specific sheet is addressable as 'SheetName!RangeName'. If your script calls ss.getRangeByName('RangeName') and the name is sheet-scoped, you may need to call it as ss.getRangeByName('SheetName!RangeName'), or delete and re-create the named range as spreadsheet-scoped from the Data > Named ranges panel.
I get Range not found only on the first row of my loop. Why only the first row?: Row index 0. If you initialize your loop variable at 0 and pass it directly to getRange(i, col), the first iteration calls getRange(0, col), which is invalid because Apps Script row indices start at 1. Initialize at 1, or add 1 when passing the index.
The error says 'Range not found' but my A1 notation looks correct. How do I debug it?: Log the exact string you're passing to getRange immediately before the call: Logger.log('range arg: ' + rangeArg). Copy that logged value and paste it into the Name Box in the sheet UI. If the sheet accepts it, the problem is the object type (Spreadsheet vs Sheet). If the sheet rejects it, the string has a whitespace character, a hidden Unicode character, or a typo that wasn't visible in the code editor.

// one good script a week