Find cell matching and return number

Question

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

How can I get the cell number of a cell in a data sheet that matches the employee name?

I tried the following script, but it does not 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 is 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; } } } 

If you want to perform such a search, it is better to get the data using sheet.getDataRange (). getValues ​​(), because in this case you will get the data as a table of values, it is faster. When you use the standard EmployeeSheet.getRange (2,3,1,1) .getValue (), you are actually retrieving an object that takes more time to process, and every time you request a spreadsheet.

In my example, I made only one request to retrieve all the data instead of n requests, so that I get the data every time.

Stéphane

Answer 2

I prefer to load all values ​​into an array once when opening a sheet, and then use Array.indexOf () to find the index of the matching string. I think this solution will have less lead 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 } } 

Answer 3

Julian, I really like what you do here. But, being NOOB in application scripts, I struggle to change what you do to fit my needs, but I understand what you do is the way I want to go.

For an active spreadsheet, I need to find the column index (in the first 3 rows) that contains the value "Unit #".

Ultimately, I try to provide a menu item "Sort by unit no." That will run the script, and sort the range of cells from A1: (last range of values) by the found column number containing "Block no.":

Here is what I have so far:

 function sortByUnit2() { var sheet = SpreadsheetApp.getActiveSheet(); updateValues(sheet); var columnName = "Unit #"; var columnIndex = findValue(columnName) var range = sheet.getDataRange(); var cl = Sheet.getRange(fieldName).getColumn(); // Sorts by the values in column c1 range.sort([{column: columnIndex, ascending: true}]); } function updateValues(sheet){ dataRangeSearch = sheet.getRange(1,2,activeSheet.getLastRow()); dataSearch = dataRangeSearch.getValues().reduce(function (a, b) { return a.concat(b); });; } 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 }