Google Apps Script - Find Row number based on a cell value

Question

I'm looking for some assistance on find the row number for a cell that contains a specific value.

The spreadsheet has lots of data across multiple rows & columns. I'm looping over the .getDataRange and can located the cell containing the value I'm looking for. Once found I'd like to know the row that his cell is in, so that I can further grab additional cell values since I know exactly how many rows down and/or columns over the additional information is from the found cell.

Here is a bit of the code for finding the cell containing a specific string.

function findCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  for (var i = 0; i < values.length; i++) {
    var row = "";
    for (var j = 0; j < values[i].length; j++) {     
      if (values[i][j] == "User") {
        row = values[i][j+1];
        Logger.log(row);
      }
    }    
  }  
}

When outputting Logger.log(row) it gives me the values I'm looking for. I want to determine what row each value is in, so I can then go down X number of rows and over X columns to get the contents of other cells.

Sheikh

The answer is i + 1 – Nickolasnickolaus

Answer 1

I don't know much about google apps but it looks like [i] is your row number in this circumstance.

So if you did something like:

function findCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();

  for (var i = 0; i < values.length; i++) {
    var row = "";
    for (var j = 0; j < values[i].length; j++) {     
      if (values[i][j] == "User") {
        row = values[i][j+1];
        Logger.log(row);
        Logger.log(i); // This is your row number
      }
    }    
  }  
}

Krutz

Yes, that does give me the full row of data. What it doesn't give me is the row number, as it just returns the row as any array. [ABC,DEF,GHI,etc....] I need to know that this row is number 13 for example. – Sheikh

for info : arrays count from 0 and rows start at 1 so the value returned by this function is not the row number but its value -1...(see the other answer, he did it right ;-) – Carlynne

This does seem very time consuming to go through each column. Is there any more efficient way? – Klump

There is now (almost 10 years later) a better way - see the answer below from AWoods (https://mcmap.net/q/656581/-google-apps-script-find-row-number-based-on-a-cell-value) – Burrussburry

Answer 2

This method finds the row number in a particular column for a given value:

function rowOf(containingValue, columnToLookInIndex, sheetIndex) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets();
  var dataRange = sheet[sheetIndex].getDataRange();
  var values = dataRange.getValues();

  var outRow;

  for (var i = 0; i < values.length; i++)
  {
    if (values[i][columnToLookInIndex] == containingValue)
    {
      outRow = i+1;
      break;
    }
  }

  return outRow;
}

But if you do it like that, it won't refresh unless you change any of the parameters, that's because a custom function should be deterministic, that is, for the same parameters, it should always give the same result.

So instead, it's better to do it this way:

function rowOf(containingValue, range) { 
  var outRow = null;

  if(range.constructor == Array)
  {
    for (var i = 0; i < range.length; i++)
    {
      if(range[i].constructor == Array && range[i].length > 0)
      {
        if (range[i][0] == containingValue)
        {
          outRow = i+1;
          break;
        }
      }
    }
  }

  return outRow;
}

In this case, you need to pass the full column range your looking for like so:

rowOf("MyTextToLookFor", 'MySheetToLookIn'!A1:A)

Where you would replace A by the colum of your choice, and MySheetToLookIn by your sheet's name and MyTextToLookFor by the text you are looking for.

This will allow it to refresh on adding rows and removing rows.

Tricky

I try to use your line rowOf("MyTextToLookFor", 'MySheetToLookIn'!A1:A) for my purpose like this: var boldRow = rowOf("∑", 'MK_Help'!A7:A); and I get error: Missing ) after argument list! ;( Am I missing something? – Anethole

Answer 3

This can be done with the Text Finder:

function findRow(searchValue){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var thisSheet = ss.getActiveSheet();
   var tf = thisSheet.createTextFinder(searchValue)
   var thisRow = tf.findNext().getRow()
   return thisRow
}

Discovered through this post: Google App Scripts find text in spreadsheet and return location index

Implementing: https://developers.google.com/apps-script/reference/spreadsheet/text-finder

Lodie

Answer 4

Since 2018 this can be done without a loop using flat().

function findRow(searchVal) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var columnCount = sheet.getDataRange().getLastColumn();

  var i = data.flat().indexOf(searchVal); 
  var columnIndex = i % columnCount
  var rowIndex = ((i - columnIndex) / columnCount);

  Logger.log({columnIndex, rowIndex }); // zero based row and column indexes of searchVal

  return i >= 0 ? rowIndex + 1 : "searchVal not found";
}

Ilbert

Answer 5

ES6 gave us a one liner for this (but expanded for full detail).

function findRow() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss. getActiveSheet(); // OR GET DESIRED SHEET WITH .getSheetByName()
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const columnIndex = 3 // INDEX OF COLUMN FOR COMPARISON CELL
  const matchText = "User"

  const index = values.findIndex(row => row[columnIndex] === matchText)
  const rowNumber = index + 1
  return rowNumber
}

Teleplay

Thanks! I added a couple arguments to the function so that I can pass in searchText and column and have the function be a little more dynamic. – Family

Answer 6

if you want the row number look at this code,that will help you

var ss = SpreadsheetApp.getActiveSpreadsheet();
var form = ss.getSheetByName("form");//the sheet where the search value in
var data = ss.getSheetByName("data");//the sheet where the data in
var searchvalue = form.getRange("L3").getValue();//the value that you want to find its row noumber in the data sheet
var vCol = data.getRange(1,1,data.getLastRow(),1).getValues();// vCol is all the values in the column you search in it, make sure to chose the right column number when you define the vCol
var rowNumber = sCol.flat().indexOf(searchvalue) + 1;// and that's it, rowNumber = the row number of the value you are looking for.

Victuals

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Manchukuo

Answer 7

To find a row in a Google Sheets sheet based on the value of a specific column (in this case, the invoice_id column) and edit another column (in this case, the send column) in that row, you can use the following script:

// Replace "Sheet1" with the name of your sheet
var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");

// Replace "A" with the column letter for the invoice_id column
// and "B" with the column letter for the send column
var invoiceIdColumn = "A";
var sendColumn = "B";

// Replace "12345" with the invoice_id you want to search for
var invoiceIdToSearch = "12345";

// Find the row number of the row with the matching invoice_id
var data = sheet.getDataRange().getValues();
var row = data.findIndex(row => row[invoiceIdColumn - 1] == invoiceIdToSearch) + 1;

// If a row with the matching invoice_id was found
if (row) {
  // Set the value of the send column in that row to "true"
  sheet.getRange(row, sendColumn).setValue(true);
}

This script will search through the entire sheet for a row with an invoice_id value that matches the invoiceIdToSearch variable, and then set the value of the send column in that row to true.

Note that this script assumes that the invoice_id column is the first column in the sheet (column A) and the send column is the second column (column B). If your columns are in different positions, you will need to adjust the invoiceIdColumn and sendColumn variables accordingly.

Israel