Reading and writing sheets Updated November 2026

getRange and getValues

Q: What is the difference between getValue and getValues?

getValue returns the top-left cell of the range as a single value. getValues returns every cell in the range as a 2D array, even if the range is one cell ([[value]]).

Q: How do I get a whole column?

Use sheet.getRange("A2:A" + sheet.getLastRow()).getValues() or getRange(2, 1, sheet.getLastRow() - 1, 1). Reading A:A reads to the sheet's maximum rows, mostly blanks.

Q: How do I read a sheet as objects?

Read the block, take values[0] as headers and map each row to an object: rows.map(r => Object.fromEntries(headers.map((h, i) => [h, r[i]]))).

sheet.getRange("A1:C10").getValues() returns a 2D array (an array of rows, each row an array of cell values); sheet.getRange("A1").getValue() returns a single value. getRange also accepts numbers: getRange(row, column, numRows, numColumns). Reading a whole block once is far faster than reading cells one by one.

Syntax

sheet.getRange(a1Notation) | sheet.getRange(row, column, numRows, numColumns) then .getValue() or .getValues()

Part	What it is
`row, column`	1-based position of the top-left cell
`numRows, numColumns`	Size of the block; default 1 each
`getValues()`	2D array; blanks are empty strings, dates are Date objects
`getDisplayValues()`	Same shape but strings exactly as shown in the sheet

Examples

const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getRange("A1:B3").getValues();
Logger.log(data);

Result: [[Name, Qty], [Pens, 12.0], [Paper, 5.0]]

data[1][0] is Pens, data[1][1] is 12.

const price = sheet.getRange(2, 3).getValue();
Logger.log(price * 2);

Result: Twice the number in C2, for example 19.9

const all = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4).getValues();
Logger.log(all.length);

Result: Number of data rows below the header, for example 120

This is the standard way to read everything except row 1.

const shown = sheet.getRange("A2").getDisplayValue();
Logger.log(shown);

Result: 11/03/2026 (the formatted text, not a Date object)

Common mistakes

The range you requested is out of bounds: getLastRow() - 1 is 0 on a sheet with only a header, so guard with if (lastRow < 2) return.
Logged value looks like Tue Nov 03 2026 instead of a date: date cells come back as Date objects; use getDisplayValues or Utilities.formatDate to show text.
You read data[0] expecting a cell but get an array: getValues is always 2D, so a single column still needs data[i][0].
A loop calls getRange(i, 1).getValue() 5,000 times and takes minutes: read the block once with getValues and loop the array.

Practice getRange and getValues

These drills check your answer on live data, free, no account needed.

Total of a listReturn the total of an array of numbers. An empty array totals `0`. Numbers can be negative or have decimals.
Items in stockEach item looks like `{ name, qty }`. Return the items that are in stock, meaning `qty` is greater than 0, in their original order. A negative `qty` is a back order, not stock.
CSV text to objectsParse simple CSV text (no quoted fields) into an array of objects. The first non-blank line holds the headers. Lines end with `\n` or `\r\n`, and blank lines are skipped. Headers and values are trimmed of surrounding spaces and stay strings. A row with fewer values than headers gets `""` for the missing ones.

Questions

What is the difference between getValue and getValues?