Reading and writing sheets Updated November 2026

Find a row by value

Two reliable ways: sheet.createTextFinder("ACME").matchEntireCell(true).findNext() returns the first matching Range (or null) and .getRow() gives its row number; or read the column with getValues and use findIndex, adding the start row offset. The array way is best when you then need the whole row's data.

Syntax

sheet.createTextFinder(text).matchEntireCell(true).findNext() | values.findIndex(row => row[0] === target)

Part	What it is
`createTextFinder(text)`	Searches the sheet or range for text; chain matchCase, matchEntireCell, useRegularExpression
`findNext()`	First match as a Range, or null
`findAll()`	Array of all matching Ranges

Examples

const sheet = SpreadsheetApp.getActiveSheet();
const cell = sheet.getRange("A:A").createTextFinder("ACME").matchEntireCell(true).findNext();
Logger.log(cell ? cell.getRow() : "not found");

Result: The row number where column A equals ACME, for example 14

const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4).getValues();
const idx = data.findIndex(row => row[0] === "ACME");
if (idx > -1) Logger.log("Row " + (idx + 2) + ": " + data[idx]);

Result: Row 14: ACME,Berlin,2200,Open

const matches = sheet.createTextFinder("open").matchCase(false).findAll();
Logger.log(matches.map(r => r.getA1Notation()));

Result: [D5, D14, D22]

Common mistakes

findNext returns a cell that merely contains the text (ACME Ltd matches ACME): add matchEntireCell(true).
Comparing a number cell to a string never matches: cells hold numbers, so compare row[0] === 1001 or String(row[0]) === "1001".
The found row number is off by one: findIndex gives an array index; add the first data row (usually 2).
A search on a huge sheet is slow with getValues: for one lookup, createTextFinder is faster; for many lookups, build a Map from the column once.

Practice Find a row by value

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

Pivot rows into a lookupTurn flat rows into a two-level lookup: `result[row[rowKey]][row[colKey]]` is the sum of `row[valueKey]` over every row with that pair. Pairs that never appear are absent. A sum of 0 is still kept.
Left join two listsFor each row of `left`, in order, return a new object combining it with the first row of `right` that has the same `row[key]` (fields from `right` win when both have them). A left row without a match is returned as a copy. Keys match only when they are the same value, so `1` does not match `"1"`. Do not change either list.
Read a nested value by pathFollow a dot path like `"user.address.city"` or `"items.0.id"` (numbers index arrays) into `obj` and return the value there. If a step along the way is missing, `null` or `undefined`, or the final value is `undefined`, return `fallback`. Real values such as `0`, `false`, `""` and a final `null` are returned as they are. An empty path returns `obj`.

Questions

How do I look up many values quickly?

Read the key column once, build a Map of key to row index, then look up each value in O(1): const map = new Map(data.map((r, i) => [r[0], i + 2])).

Can I search with a regular expression?

Yes, chain useRegularExpression(true) on the TextFinder, for example createTextFinder("^INV-\\d+$").useRegularExpression(true).

Loop through rowsLoop through rows in Google Sheets with Apps Script: read the data once with getValues, loop the 2D array with for, for...of or forEach, then write back.
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.
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.
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.