Reading and writing sheets Updated November 2026

Loop through rows

Read the block once with getValues, loop the array, then write back once: const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 3).getValues(); data.forEach((row, i) => { ... }). Inside the loop row[0] is column A and the sheet row number is i + 2. Never call getRange inside the loop for each cell.

Syntax

for (let i = 0; i < data.length; i++) { const row = data[i]; }

Part	What it is
`row[c]`	Value in column index c (0 = first column of the range)
`i + startRow`	Actual sheet row number when the range starts at startRow

Examples

const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
for (let i = 0; i < data.length; i++) {
  Logger.log("Row " + (i + 2) + ": " + data[i][0] + " = " + data[i][1]);
}

Result: Row 2: Pens = 12, Row 3: Paper = 5, and so on

const statuses = data.map(row => [row[1] > 0 ? "In stock" : "Out"]);
sheet.getRange(2, 3, statuses.length, 1).setValues(statuses);

Result: Column C gets In stock or Out for every row

map builds the output array, setValues writes it in one call.

for (const [name, qty] of data) {
  if (qty < 5) Logger.log(name + " is low");
}

Result: Paper is low

Destructuring names the columns.

Common mistakes

The script writes to the wrong row: array index 0 is sheet row 2 when the range starts at row 2, so add the offset.
Loop runs for 1000 rows on a sheet with 50 entries: you used getMaxRows or A:A; size the range with getLastRow.
Loop takes minutes: setValue is called per row; collect results in an array and write with one setValues.
forEach does not stop at the first match: forEach cannot break; use a for loop with break or find and findIndex.

Practice Loop through rows

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

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.
Running totalsReturn a new array where each position holds the sum of all numbers up to and including that position. Do not change `nums`.
Count by a fieldReturn an object that maps each value of `item[key]` to how many items have it. Skip items where the field is missing or `undefined`.

Questions

How do I loop through a column only?

Read it as a one-column range and use row[0]: sheet.getRange(2, 2, n, 1).getValues().forEach(r => Logger.log(r[0])).

Can I modify the sheet while looping over its rows?

Avoid deleting rows while looping forward; indexes shift. Loop backward for deletions, or collect changes and apply after the loop.

getRange and getValuesRead Google Sheets data with Apps Script: getRange by A1 notation or row and column numbers, then getValue for one cell or getValues for a 2D array of rows.
forEach vs for loopsforEach, for, for...of and for...in in Google Apps Script: differences on 2D sheet data, when you need the index or an early break, why for...in fails.
map, filter and reduceUse map, filter and reduce on Google Sheets data in Apps Script: transform rows, keep rows that match a condition, sum a column, chain them, write back.
Delete rowsDelete rows in Google Sheets with Apps Script: deleteRow and deleteRows by position, delete rows that match a condition by looping from the bottom, blanks.