Why does getValues return undefined for my cell?

You're almost certainly missing the [0][0] index. getValues always returns a 2D array, so sheet.getRange('A1').getValues() is [["My Value"]], not "My Value". Access it as .getValues()[0][0].

Can I use getValues on non-contiguous ranges?

No. getValues operates on a single contiguous rectangular range. For non-contiguous data, either use multiple getValues calls (still fewer round-trips than per-cell getValue), or restructure the sheet so the data you need sits in one block.

Does getValues preserve cell formatting or just the value?

Just the value — what the cell would return if you read it as a number, string, boolean, or Date object. For formatted display strings (e.g., '12%' instead of 0.12), use getDisplayValues instead, same 2D array shape.

My script reads the same range twice. Should I cache it?

Yes. Store the result of getValues in a variable at the top of the function and reference it throughout. Re-calling getValues on the same range is two round-trips where one suffices. The array is plain JavaScript — reassigning it costs nothing.

// Sheets · Apps Script

getValue vs getValues in Google Sheets.

getValue reads one cell per call, costing one network round-trip each time. getValues batches a whole range into a single call but returns a 2D array — missing the [0][0] index is the most common mistake when switching over.

“I want to stop my Apps Script from being slow when reading spreadsheet data, and I'm not sure whether to use getValue or getValues.”

The script

copy · paste · trigger

batch-read.gs

Apps Script

// Read a column of values in one call instead of looping getValue
function summarizeScores() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Results');

  // One round-trip: returns a 2D array, rows x columns
  var values = sheet.getRange('B2:B26').getValues();

  var total = 0;
  for (var i = 0; i < values.length; i++) {
    total += values[i][0]; // [row][col] — column index is always 0 here
  }

  // Single-cell getValues still returns 2D — need [0][0]
  var label = sheet.getRange('A1').getValues()[0][0];

  Logger.log(label + ': ' + total);
}

Generate yours

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

Walkthrough

Why getValue in a loop kills performance

Every call to getValue — or getValues on a single cell — issues a separate network request to the Sheets API. Apps Script runs server-side on Google's infrastructure, but the Spreadsheet service is still a separate process, and each call crosses that boundary. A loop over 25 cells means 25 round-trips. At roughly 50–100 ms each under normal quota, a modest loop becomes a multi-second stall before you've done any real work.

The execution quota makes this worse. Apps Script allows 6 minutes of total runtime per execution for consumer accounts, 30 minutes for Workspace. A tight getValue loop on a few hundred rows can burn minutes of that quota on I/O alone. I've seen scripts that read a 200-row column hit the 6-minute wall every single run, simply because no one batched the read.

Batching with getValues: the 2D array you didn't expect

getValues() on any range — even a single cell — returns a two-dimensional array. The outer array is rows; the inner array is columns within that row. So sheet.getRange('B2:B26').getValues() gives you an array of 25 one-element arrays, and each score lives at values[i][0], not values[i].

That [0] column index is the standard trip-up. When you switch from getValue to getValues and forget it, you're logging '[object Object]' or 'undefined' instead of a number, and the bug is invisible until you check the actual array shape. A quick Logger.log(values) before processing is worth the extra line during development.

For a multi-column range like 'A2:C26', the shape is values[rowIndex][columnIndex] where column indices are zero-based relative to the range start — not the sheet. Column A in range A2:C26 is [i][0], column B is [i][1], column C is [i][2].

When getValue is still the right call

If you genuinely need exactly one cell and you don't read it again in the same script run, getValue is fine. It's one round-trip either way, and getValue returns the scalar directly — no indexing required. The cost of getValues for a single cell is the same network hit plus the overhead of unwrapping [0][0], which is noise but is real friction when you're reading config values at the top of a script.

The practical threshold: if you're reading two or more cells anywhere in the same execution, batch them. Even two separate getValue calls across non-adjacent cells are worth combining into a getValues call on a slightly larger range and ignoring the cells you don't need. The wasted bytes in the response are cheaper than the extra round-trip.

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 getValues return undefined for my cell?: You're almost certainly missing the [0][0] index. getValues always returns a 2D array, so sheet.getRange('A1').getValues() is [["My Value"]], not "My Value". Access it as .getValues()[0][0].
Can I use getValues on non-contiguous ranges?: No. getValues operates on a single contiguous rectangular range. For non-contiguous data, either use multiple getValues calls (still fewer round-trips than per-cell getValue), or restructure the sheet so the data you need sits in one block.
Does getValues preserve cell formatting or just the value?: Just the value — what the cell would return if you read it as a number, string, boolean, or Date object. For formatted display strings (e.g., '12%' instead of 0.12), use getDisplayValues instead, same 2D array shape.
My script reads the same range twice. Should I cache it?: Yes. Store the result of getValues in a variable at the top of the function and reference it throughout. Re-calling getValues on the same range is two round-trips where one suffices. The array is plain JavaScript — reassigning it costs nothing.

// one good script a week