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;

  }

Ines

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

Exsert

I'm getting an error: Errortype: Can't read attribute '1' from undefined. Do you know how I can solve it? – Ines

Script must be launch from à script within à spreadsheet ans not standalone script. If standalone script change var sheet =SpreadsheetApp.openById("IDofSpreadsheet"); – Exsert

sorry var sheet =SpreadsheetApp.openById("IDofSpreadsheet").getSheetByName("NameOfTheSheet"); – Exsert

Thank you for helping me out! – Ines

I have a similar requirement, however, data.length can go in thousands in my case, and I need to perform the search for many strings in the same range in my script. Is there any way to make it quicker and possibly run with lesser processing load? – Artair

This answer makes sense. But I'm in disbelief that this is the best way to do it, and GAS haven't added this into their API. I have a spreadsheet of 4000 rows, and 125 data items that need updating. So just for this "search" alone, I have to traverse through the rows 4000 * 125 times? Wow. – Nobody

Yes but this code is pretty fast and for your case it must be fast anyway. – Exsert

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());
  }

Unsubstantial

I have tried this function to find all the 0's in the sheet, not only does it find all the 0's but also the numbers that contain 0's (100,2000,10). Can we just use it to find only zeros? Moreover, it runs for ages and does not end until it crashes. Any suggestion? – Alboin

Hi @Roomi. Looking at the class documentation (developers.google.com/apps-script/reference/spreadsheet/…), I saw that you need to use the matchEntireCell method as TRUE to accomplish what you need. I hope it helps. – Unsubstantial

Yeah, this solved the issue. Thank you for the suggestion. – Alboin

Answer 3

Since Google App Script is a JavaScript platform, I can think of two more methods to get the row index. It may be a bit faster because these are built-in Array methods.

Method 1: using Array.prototype.forEach()

function getRowIndexUsingforEach(){
  let term = '[email protected]';
  var data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, 2000).getValues();
  data.forEach((val, index) => {
     if(val == term){
         Browser.msgBox(index+1); //because Logger.log() takes too long
     }               
  })
}

Method 2: using Array.prototype.findIndex()

function getRowUsingfindIndex(){
  let term = '[email protected]';
  let data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1, 1, 2000).getValues();
  let row = data.findIndex(users => {return users[0] == term});  
  Browser.msgBox(row+1); //because Logger.log() takes too long
}

To see which method will be faster, I ran these two methods and the accepted anwer's method over a column of 22000 rows of data several times. The averages are listed below:

• Using for loop (from Accepted Answer): 2.66ms
• Using Array.prototype.forEach: 11.75ms
• Using Array.prototype.findIndex: 2.00ms

I guess Array.prototype.forEach is not meant to find the index of a match; it is Array.prototype.findIndex that is designed for this purpose

Anagoge

Answer 4

I prefer loading all values into an array once when the sheet is opened, and then using Array.indexOf() to look up the index of the matched string. I think this solution will have less execution time than other solutions.

function updateValues(){
  dataRangeSearch = activeSheet.getRange(1,1,activeSheet.getLastRow());
  dataSearch = dataRangeSearch.getValues().reduce(function (a, b) {
    return a.concat(b);
  });;
}
updateValues();

function findValue(fieldName){
  var row = dataSearch.indexOf(fieldName);
  if (row == -1){ // Variable could not be found
    SpreadsheetApp.getUi().alert('I couldn\'t find field name "'+fieldName+'"');
    return "";
  } else {
    return activeSheet.getRange(row+1,2).getValue(); //Return the value of the field to the right of the matched string
  }
}

Agnomen

I wanted to test this out as I was hoping to get the fastest way possible to get to the row with the element being searched but it turned out that using indexOf is NOT the fastest way and the accepted solution was the fastest way out of the two approaches. I think the problem here is the fact that you need to convert the data column into a unidimensional array by either mapping it or reducing it, as you did here, before you can apply indexOf which I believe defeats the purpose of using indexOf to find the row number where your searched value is at. – Succubus

doesn't indexOf only get the first instance? – Kikuyu