Rows, columns, and 2D arrays in Sheets scripts

1 of 8

A range is a rectangle of cells

A range is any rectangle of cells on one tab. A single cell is a range. A whole column is a range. The block from A2 down to D50 is a range.

Apps Script never reaches into a cell out of thin air. It asks the spreadsheet for a tab, asks the tab for a range, and then asks the range for its values.

A range always belongs to one tab. In code a tab is called a Sheet.
A range is described by four things: its first row, its first column, how many rows, and how many columns.
Nothing is read or written until you call a method on the range, such as getValues() or setValues().

2 of 8

getValues() gives you an array of rows

getValues() returns a 2D array. A 2D array is an array whose items are themselves arrays. The outer array holds the rows of the range, top to bottom. Each inner array holds the cells of one row, left to right.

So values[0] is the first row you read, and values[0][1] is the second cell in that first row. The shape is always values[rowIndex][columnIndex]: the outer index picks the row, the inner index picks the column.

The values are plain JavaScript values, not cells. Changing one of them changes nothing in the spreadsheet until you write the array back.

values.length is the number of rows you read.
values[0].length is the number of columns you read.
An empty cell comes back as an empty string, so every row has the same number of items.
A number cell arrives as a number, a date cell as a Date object, and a checkbox as true or false.

3 of 8

Sheets counts from 1, JavaScript counts from 0

In the spreadsheet grid, rows and columns start at 1. Row 1 is usually the header row, and column 1 is column A. In JavaScript, positions in an array start at 0.

That gap causes most off by one mistakes. If your range starts at row 2, then values[0] is sheet row 2, values[1] is sheet row 3, and so on. The array does not know where it came from.

To avoid it, keep the first data row in a variable, then add the array index to it whenever you need the real row number.

Sheet row number = the first row of the range + the array index.
Column A is column 1 in getRange(), and index 0 in the array.
Writing a result back to a row means getRange(firstDataRow + index, someColumn), not getRange(index, someColumn).

4 of 8

Picking the range you want

getRange(row, column, numRows, numColumns) takes four numbers: the first row, the first column, how many rows, and how many columns. getRange(2, 1, 10, 4) is ten rows of four columns starting at cell A2.

You can also use A1 notation, the same text that appears in the name box in Sheets: getRange("A2:D11"). Both describe the same rectangle. The four numbers are easier when the size depends on how much data there is, because you can work them out while the script runs.

getDataRange() returns every cell that has anything in it, header row included. It is the quickest way to read a whole small tab.

getLastRow() is the last row that holds data. On a tab with a header and 20 entries it returns 21.
getMaxRows() is how many rows the tab has in total, including the empty ones at the bottom. It is often 1000 on a new tab.
Size a read with getLastRow(). Using getMaxRows() by mistake reads hundreds of empty rows and hides real data behind them.
The number of data rows below a header is getLastRow() - 1. Check that it is at least 1 before you read.

5 of 8

Read once, write once

Every call to a Sheets method is a round trip from the script to Google servers and back. One round trip is quick. A thousand of them inside a loop is slow, and it is the usual reason a script runs for minutes or stops with "Exceeded maximum execution time".

So read the whole block one time with getValues(), do the work on the plain array in memory, and write the result one time with setValues(). The array is free to work with. The service calls are what cost you.

A loop that calls getValue() on one cell per row does the same job with one round trip per row. On 500 rows that is 500 trips instead of 1.

Slow: call getValue() or setValue() inside a loop.
Fast: one getValues() before the loop, one setValues() after it.
When you only need to stamp a single cell after an edit, one setValue() is fine. The cost only adds up in loops.

6 of 8

Read a tab and log what is in it

This function reads columns A and B of a tab named Customers, starting at row 2 so the header row is left out. It logs one line per row, with the real sheet row number.

const [name, emailAddress] = row; is called destructuring. It takes the first two items of the row array and gives them names, so the rest of the code reads like the spreadsheet instead of like row[0] and row[1].

Log the name and email address in every row

function logCustomerNamesAndEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
  if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');

  const firstDataRow = 2;
  const lastRow = sheet.getLastRow();
  if (lastRow < firstDataRow) {
    Logger.log('The Customers tab has no data rows yet.');
    return;
  }

  const values = sheet.getRange(firstDataRow, 1, lastRow - firstDataRow + 1, 2).getValues();
  values.forEach((row, index) => {
    const [name, emailAddress] = row;
    const sheetRow = firstDataRow + index;
    Logger.log('Row ' + sheetRow + ': ' + name + ' <' + emailAddress + '>');
  });
}

7 of 8

Write with setValues(), and match the shape exactly

setValues() writes a rectangle, and the array you hand it must be exactly the size of the range. Ten rows of two columns means ten inner arrays, each holding two items. If the counts do not match, the run stops with "The number of rows in the data does not match the number of rows in the range".

Build the array first, then size the range from the array, using rows.length for the row count. Then the two cannot drift apart.

Writing a single column still needs a 2D array. Each row is an array holding one value, like ["Hello Sam"].

Build a 2D array and write it in one call

function writeGreetingsColumn() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
  if (!sheet) throw new Error('Add a Sheet tab named Customers before running this function.');

  const firstDataRow = 2;
  const lastRow = sheet.getLastRow();
  if (lastRow < firstDataRow) return;

  const names = sheet.getRange(firstDataRow, 1, lastRow - firstDataRow + 1, 1).getValues();
  const greetings = names.map((row) => {
    const [name] = row;
    return [name ? 'Hello ' + name : 'Hello there'];
  });

  sheet.getRange(firstDataRow, 3, greetings.length, 1).setValues(greetings);
  Logger.log('Wrote ' + greetings.length + ' greetings into column C.');
}

8 of 8

getValues() and getDisplayValues()

getValues() returns what is stored in each cell. A number stays a number, a date arrives as a Date object, and a formula gives you its result.

getDisplayValues() returns the text the person sees on screen, always as a string. A cell showing $1,200.00 comes back as the string "$1,200.00", and a date shown as 3/14/2026 comes back as that text.

Use getValues() when you need to add numbers up or compare dates. Use getDisplayValues() when you want the formatting somebody set up in the sheet, for example when you drop the values straight into an email.

Remember

The short version

A range is a rectangle of cells, and getValues() returns it as an array of rows.
values[rowIndex][columnIndex]: the outer index picks the row, the inner index picks the column.
The real sheet row number is the first row of the range plus the array index, because Sheets counts from 1 and arrays count from 0.
Read the whole block once and write it back once instead of touching one cell at a time.
setValues() needs an array whose row and column counts match the range exactly.

SpreadsheetApp.SpreadsheetSpreadsheetApp.SheetSpreadsheetApp.Range

Keep going

Put it to work

All basics