Why does findNext() return the wrong row when my value appears as part of another cell?

TextFinder defaults to substring matching. A search for 'order-5' will match a cell containing 'order-50' because the string is present inside it. Add .matchEntireCell(true) to require that the full cell content equals your search string.

How do I find all rows matching a value, not just the first one?

Replace findNext() with findAll(), which returns an array of Range objects. Iterate with a for loop and collect .getRow() from each element. Be aware that on a large sheet with many matches, findAll() loads all results into memory at once.

Can I search across the whole sheet instead of a single column?

Yes — pass sheet.getDataRange() to createTextFinder instead of a column range. The match will be the first cell in reading order (left to right, top to bottom). For a keyed lookup where you know which column holds the identifier, restricting to that column is faster and avoids false matches in other columns.

What happens if the value exists in two rows — which row does it return?

findNext() returns the first match in top-to-bottom order within the range you provided. If you need to know whether duplicates exist at all, call findAll() and check the length of the returned array before deciding which row to act on.

// Sheets · Apps Script

Find the row number of a matching value in Google Sheets.

How to get the exact row number of a cell whose value matches a string in Google Sheets Apps Script — using createTextFinder with matchEntireCell(true) so partial matches don't poison your result.

“I need to locate the row number of a specific value in a Google Sheet column from Apps Script so I can read, overwrite, or delete that row programmatically.”

The script

copy · paste · trigger

findRowByValue.gs

Apps Script

// Return the 1-based row number of the first cell in a column
// whose value exactly matches `target`. Returns -1 if not found.
function findRowByValue(sheetName, column, target) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getRange(column + '1:' + column + sheet.getLastRow());

  var finder = range.createTextFinder(target)
    .matchEntireCell(true)
    .matchCase(false);

  var match = finder.findNext();
  if (match === null) {
    return -1;
  }
  return match.getRow();
}

// Example: find the row where column B equals "[email protected]"
function testFind() {
  var row = findRowByValue('Users', 'B', '[email protected]');
  Logger.log('Found at row: ' + row);
}

Generate yours

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

Walkthrough

Why the default substring match will burn you

The first time I hit this, I had a sheet with email addresses: one row held `[email protected]`, another held `[email protected]`. A plain `createTextFinder('[email protected]')` matched the second row first because TextFinder defaults to substring matching — `subbob` contains the literal string `[email protected]` starting at character four.

Calling `.matchEntireCell(true)` switches the finder to a full-cell equality check. The match now requires the entire cell content to equal your target string, not just contain it. That one chained method is the difference between a lookup that sometimes works and one that always works.

The same trap catches numeric-looking strings. If column A has `26` in row 3 and `126` in row 7, searching for `'26'` without `matchEntireCell` returns row 7 first on some Sheets versions because `126` contains `26`.

Building the range efficiently

Passing the whole sheet to `createTextFinder` scans every cell, which is slow on large data. The code above constructs a single-column range from row 1 to `sheet.getLastRow()`, so the search is bounded to the column that actually contains your lookup key.

If your data has a header row and you want to skip it, change `'1'` to `'2'` in the range string: `column + '2:' + column + sheet.getLastRow()`. The returned `.getRow()` still gives you the true 1-based sheet row number, so downstream calls to `sheet.getRange(row, col)` work without any offset arithmetic.

One thing worth knowing: `getLastRow()` returns the last row with *any* content in the entire sheet, not just in your target column. If another column extends further down, your search range will include empty cells in your column — harmless for correctness, but worth knowing if you're debugging range sizes.

Using the row number once you have it

The row number returned is 1-based, which matches the `getRange(row, col)` signature directly. To read the full row after finding it:

``` var row = findRowByValue('Users', 'B', '[email protected]'); if (row !== -1) { var rowData = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0]; } ```

To delete the matched row: `sheet.deleteRow(row)`. To overwrite a specific cell in it: `sheet.getRange(row, 3).setValue('updated')`. The -1 sentinel gives you a clean guard condition without relying on null checks after the fact — a small thing that prevents silent overwrites of row 0, which in Sheets terms is an error rather than a no-op.

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 findNext() return the wrong row when my value appears as part of another cell?: TextFinder defaults to substring matching. A search for 'order-5' will match a cell containing 'order-50' because the string is present inside it. Add .matchEntireCell(true) to require that the full cell content equals your search string.
How do I find all rows matching a value, not just the first one?: Replace findNext() with findAll(), which returns an array of Range objects. Iterate with a for loop and collect .getRow() from each element. Be aware that on a large sheet with many matches, findAll() loads all results into memory at once.
Can I search across the whole sheet instead of a single column?: Yes — pass sheet.getDataRange() to createTextFinder instead of a column range. The match will be the first cell in reading order (left to right, top to bottom). For a keyed lookup where you know which column holds the identifier, restricting to that column is faster and avoids false matches in other columns.
What happens if the value exists in two rows — which row does it return?: findNext() returns the first match in top-to-bottom order within the range you provided. If you need to know whether duplicates exist at all, call findAll() and check the length of the returned array before deciding which row to act on.

// one good script a week