Fix TypeError: Cannot read properties of undefined (reading 'getValue') in Google Apps Script

The Error

TypeError: Cannot read properties of undefined (reading 'getValue')

You called .getValue() — or .getValues(), .getRange(), something similar — on a variable that was undefined at runtime. The three most common triggers: looping over rows with the wrong index, referencing a sheet that doesn't exist, or assuming getActiveSheet() always returns something valid.

Root Cause

Apps Script runs on V8 JavaScript. Call a method on undefined and you get this error — no exceptions. Here's where it usually comes from:

• Array index out of bounds: You loop with rows[i] but the array has fewer elements than you expected — rows[10] on a 10-row array is undefined.
• Wrong sheet name: spreadsheet.getSheetByName('Data') returns null when the sheet doesn't exist. Calling .getRange() on that null blows up the same way.
• Empty range mishandling: getValues() on an empty range gives you a 2D array of empty strings, not undefined. But misread the dimensions and you can still land on undefined cells.
• Off-by-one in row indexing: Sheet methods like getRange(row, col) are 1-based. Arrays from getValues() are 0-based. Mix them up and you're reading one row past the end.

Fix 1: Guard the sheet reference before touching it

Fetching a sheet by name? Don't assume it exists. Rename a tab in the spreadsheet and your script silently breaks. A two-line guard prevents that:

function readData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data');

  if (!sheet) {
    Logger.log('Sheet "Data" not found');
    return;
  }

  const value = sheet.getRange(1, 1).getValue();
  Logger.log(value);
}

Note: getSheetByName() returns null, not undefined. Doesn't matter — null.getValue() throws the same error. The if (!sheet) check catches both.

Fix 2: Fix off-by-one when iterating with getValues()

This is the classic trap. getValues() hands you a 0-indexed array, but the loop below starts at i = 1 — so on the last iteration, data[lastRow] is past the end of the array:

// BROKEN: starts loop at 1, but array index goes 0 to lastRow-1
function brokenLoop() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const data = sheet.getRange(1, 1, lastRow, 3).getValues();

  for (let i = 1; i  {
    row.forEach((cell, colIndex) => {
      Logger.log(`[${rowIndex}][${colIndex}]: ${cell}`);
    });
  });
}

Fix 4: Check intermediate results in chained calls

findNext() returns null when it finds nothing. Chain straight into .getValue() and you crash every time the keyword is absent:

// BROKEN: assumes the keyword always exists in the sheet
function findAndRead() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const found = sheet.createTextFinder('keyword').findNext();
  const value = found.getValue(); // null.getValue() → TypeError
}

// FIXED
function findAndReadSafe() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const found = sheet.createTextFinder('keyword').findNext();

  if (!found) {
    Logger.log('Keyword not found in sheet');
    return;
  }

  const value = found.getValue();
  Logger.log(value);
}

Fix 5: Accessing named ranges that don't exist

getRangeByName() quietly returns null for any range name that hasn't been defined in the spreadsheet. Always verify before reading:

// BROKEN
function readNamedRange() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const range = ss.getRangeByName('MyConfig'); // null if not defined
  const value = range.getValue();              // TypeError
}

// FIXED
function readNamedRangeSafe() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const range = ss.getRangeByName('MyConfig');

  if (!range) {
    Logger.log('Named range "MyConfig" does not exist');
    return;
  }

  const value = range.getValue();
  Logger.log(value);
}

Debugging tip: log the variable before it crashes

Stack traces in Apps Script can be vague. When you're not sure which variable is undefined, drop a log line right before the failing call:

Logger.log('sheet: ' + sheet);
Logger.log('range: ' + range);
Logger.log('data type: ' + typeof data);

Open View → Logs (or press Ctrl+Enter in the script editor) and you'll see exactly which variable printed null or undefined before the crash. Faster than reading the stack trace.

Verification

Once you've applied a fix, confirm it actually holds:

• Run the function manually via Run → Run function in the Apps Script editor.
• Open View → Executions — a clean run shows no red entries.
• Test edge cases deliberately: an empty sheet, a tab with a renamed sheet, a range with no data. These are the exact conditions that exposed the bug.
• Using a time-based trigger? Check View → Executions after the next scheduled run to confirm no silent failures.

Prevention

• Treat getSheetByName(), getRangeByName(), and findNext() as nullable. Always add if (!result) return; before using their output.
• Drive loops with data.length, not the raw row count from the sheet. The array already knows how many rows it has.
• Keep the indexing mental model straight: sheet rows and columns start at 1, array indexes start at 0. Never use one where you need the other.
• Put if (lastRow < 1) return; at the top of any function that reads sheet data. It costs one line and prevents a whole class of errors.