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

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

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.