Find Matching Rows in Google Spreadsheets

You have a Google Spreadsheet and you need to programmatically find if a cell with particular value exists in that sheet. Well, Google Scripts can help. You can either search cells in a single column (like A) or the script can search for all cells in the row and return the index of the matching row.

function findInColumn(column, data) {

  var sheet  = SpreadsheetApp.getActiveSpreadsheet();
  var column = sheet.getRange(column + ":" + column);  // like A:A
  
  var values = column.getValues(); 
  var row = 0;
  
  while ( values[row] && values[row][0] !== data ) {
    row++;
  }
  
  if (values[row][0] === data) 
    return row+1;
  else 
    return -1;
    
}

function findInRow(data) {
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var rows  = sheet.getDataRange.getValues(); 
  
  for (var r=0; r<rows.length; r++) { 
    if ( rows[r].join("#").indexOf(data) !== -1 ) {
      return r+1;
    }
  }
  
  return -1;
    
}