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.

Answer 1

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";
}

Answer 2

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
      }
    }    
  }  
}