Can I use getSheetByName on a different spreadsheet, not the active one?

Yes. Open it first with SpreadsheetApp.openById('your-spreadsheet-id'), store that in a variable, then call getSheetByName on that object. The same null-return behavior applies, so the guard pattern is identical.

My sheet name has no spaces and the capitalization is right, but it still returns null. What else could it be?

Check for a non-breaking space (Unicode U+00A0) or a lookalike character. Paste the tab name from Sheets into a hex editor or run Logger.log(tabName.charCodeAt(i)) for each character index to inspect the actual code points. Also verify you're calling getSheetByName on the correct spreadsheet object — if you have multiple SpreadsheetApp.openById calls in the same script, it's easy to call the method on the wrong one.

Why does the error say 'reading getRange' when getRange is not the problem?

JavaScript reports the property access that failed on the null value, not the call that produced the null. getSheetByName returned null without complaint; your code then tried to read .getRange on that null, which is where the runtime finally surfaced the error. The actual bug is always one step earlier.

Is there a way to get notified when a trigger-driven script hits this error?

Yes. In the Apps Script editor go to Triggers, edit your trigger, and set the failure notification to 'Notify me immediately'. You'll get an email with the execution log including your custom error message. For higher-volume scripts, writing errors to a dedicated 'Errors' sheet via a try/catch block gives you a persistent record without checking email.

// Sheets · Apps Script

Fix "TypeError: Cannot read properties of null" in Apps Script.

getSheetByName returns null when the tab name doesn't match exactly. Learn how to guard that lookup, surface the real name list, and stop the silent mismatch before it kills your script.

“I'm getting "TypeError: Cannot read properties of null (reading 'getRange')" in my Apps Script and I don't know why getSheetByName is failing.”

The script

copy · paste · trigger

guardedSheetLookup.gs

Apps Script

// Safe sheet lookup — fails loudly with the actual tab name list
function getRowCount() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sales Data');

  if (sheet === null) {
    var names = ss.getSheets().map(function(s) {
      return s.getName();
    });
    throw new Error('Sheet not found. Available tabs: ' + names.join(', '));
  }

  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(1, 1, lastRow, 3);
  var values = range.getValues();
  Logger.log('Rows fetched: ' + values.length);
  return values.length;
}

Generate yours

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

Walkthrough

Why getSheetByName returns null instead of throwing

getSheetByName does not throw an exception when it can't find the sheet. It returns null silently. Your script then tries to call getRange (or getLastRow, or anything else) on that null, and that's when the TypeError fires — one step removed from the actual problem, which is the name mismatch.

The match is case-sensitive and whitespace-sensitive. A tab named 'Sales Data ' (trailing space) is a different string from 'Sales Data'. This happens constantly when someone renames a tab by hand, copies a spreadsheet from a template, or pastes a tab name from a doc with a hidden non-breaking space. The error message names getRange as the offending line, so most people look at getRange — but the real fault is two lines earlier.

Surface the actual tab names at failure time

The fastest fix is to make the null branch tell you exactly what tabs do exist. ss.getSheets() returns an array of Sheet objects; map over it with getName() and join the result into your error message. The first time I hit this in a client's weekly report script, the error message immediately showed 'Sales Data ' with a visible trailing space — problem solved in ten seconds instead of ten minutes of re-reading the script.

That guard pattern is in the snippet above. The key detail: throw a real Error object, not Logger.log. If this script runs on a trigger (time-based, onChange), a thrown error shows up in the Executions log with the full message; a Logger.log call disappears silently because there's no open console to receive it.

Common name mismatches and how to pre-empt them

Trailing or leading whitespace is the most common culprit. You can defensively trim both sides before the lookup: ss.getSheetByName(tabName.trim()). That handles copy-paste contamination without requiring the caller to be careful.

Renamed tabs are the second culprit. If your script is a shared template, the user renames 'Sheet1' to something meaningful and your hardcoded string breaks. One pattern that survives renames is to target sheets by index (ss.getSheets()[0]) when you own the spreadsheet structure, or to scan by a sentinel value in a known cell rather than relying on the tab name at all. Neither is universally better — index works for fixed-structure workbooks; sentinel scanning works when users need naming freedom.

Unicode lookalikes are rare but genuinely maddening: a tab named with a curly apostrophe or an en-space looks identical in the Sheets UI but won't match a plain ASCII string literal. If the trim trick doesn't fix it, paste the tab name into a plain-text editor and look at the byte representation.

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

Can I use getSheetByName on a different spreadsheet, not the active one?: Yes. Open it first with SpreadsheetApp.openById('your-spreadsheet-id'), store that in a variable, then call getSheetByName on that object. The same null-return behavior applies, so the guard pattern is identical.
My sheet name has no spaces and the capitalization is right, but it still returns null. What else could it be?: Check for a non-breaking space (Unicode U+00A0) or a lookalike character. Paste the tab name from Sheets into a hex editor or run Logger.log(tabName.charCodeAt(i)) for each character index to inspect the actual code points. Also verify you're calling getSheetByName on the correct spreadsheet object — if you have multiple SpreadsheetApp.openById calls in the same script, it's easy to call the method on the wrong one.
Why does the error say 'reading getRange' when getRange is not the problem?: JavaScript reports the property access that failed on the null value, not the call that produced the null. getSheetByName returned null without complaint; your code then tried to read .getRange on that null, which is where the runtime finally surfaced the error. The actual bug is always one step earlier.
Is there a way to get notified when a trigger-driven script hits this error?: Yes. In the Apps Script editor go to Triggers, edit your trigger, and set the failure notification to 'Notify me immediately'. You'll get an email with the execution log including your custom error message. For higher-volume scripts, writing errors to a dedicated 'Errors' sheet via a try/catch block gives you a persistent record without checking email.

// one good script a week