Find Cell Matching Value And Return Rownumber

Question

The employee sheet contains the name of the employee in cell C2. The name of the employee should also be on the data sheet in the range B3:B153.

How can I get the rownumber of the cell on the data sheet that matches the employee name?

I tried the following script but it doesn't seem to work.

  var Sheet = SpreadsheetApp.getActive();   var Employeesheet = Sheet.getSheetByName('Employee')   var DataSheet = Sheet.getSheetByName('Data');   var Column = Sheet.getRange(3,2,151,1);   var Values = column.getValues();    var Row = 0;    while ( Values[Row] && Values[Row][0] !=(EmployeeSheet.getRange(2,3,1,1).getValue()) ) {     Row++;   }    if ( Values[Row][0] === (EmployeeSheet.getRange(2,3,1,1).getValue()) )      return Row+1;   else      return -1;    } 

Answer 1

Here the code

function rowOfEmployee(){   var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();   var data = sheet.getDataRange().getValues();   var employeeName = sheet.getRange("C2").getValue();   for(var i = 0; i<data.length;i++){     if(data[i][1] == employeeName){ //[1] because column B       Logger.log((i+1))       return i+1;     }   } } 

When you want to perform this kind of lookup it is better to retrieve data with sheet.getDataRange().getValues() because in this case you will get data as a table of values this is faster. When you use the standard EmployeeSheet.getRange(2,3,1,1).getValue() in fact you retrieve an object which need more time to be processed and each time you query the spreadsheet.

In my exemple I made only one query to retrieve all data instead n query to retrieve one data each time.

Stéphane

Answer 2

I faced this problem today and I found a native method to do the job.

Here is the snippet to test if it works for you:

  var spreadsheet = SpreadsheetApp.getActive();   var tosearch = "your text to search";   var tf = spreadsheet.createTextFinder(tosearch);   var all = tf.findAll();      for (var i = 0; i < all.length; i++) {     Logger.log('The sheet %s, cell %s, has the value %s.', all[i].getSheet().getName(), all[i].getA1Notation(), all[i].getValue());   }