Google-sheets – Fetch cell row and column indices in Google Sheets

google sheetsgoogle-apps-script

I have a spreadsheet with two sheets inside. The name of the first sheet is fr
and the name of the second sheet cql. The first sheet contains data as below:

Sheet 1

and the second as follows:

Sheet 2

I want do something here with Google Apps Script. My script should get the value from each row, from the first column of my first sheet (fr), and search for a cell in the second sheet (cql), which contain that value. That is, the script first gets the value Ghees Alias T from the first sheet and searches the entire second sheet to get a cell with a value containing that string ("Ghees"). If script finds a cell, then it should return the row index and column index of that cell.

Edit: As of now, I have developed my script up to this, in which it will select the cell with the item given. Now I want to get the row and column index of that cell. And please give any advice also to make the search algorithm much more efficient.

function myFunction(e)
{
  var ss =SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 =ss.getSheetByName("fr");
  var datarange = sheet1.getDataRange();
  var values = datarange.getValues();
  for( var i=1; i<values.length; i++){
   Browser.msgBox(values[i][0]);
   arr = findRow(values[i][0]);
  }
} 

function findRow(item) { 
   var ss =SpreadsheetApp.getActiveSpreadsheet();
   var sheet2 =ss.getSheetByName("cql");
   var values = sheet2.getDataRange().getValues();
   for(i =0; i < values.length; ++i) {
      for(j=0;j<values.length; ++j){
          Browser.msgBox( "--->" + values[i][j]);
          if(values[i][j].toString().match(item)==item){
             Browser.msgBox("Got an item");
             var bool = 1;
             break;
          }
      }
     if(bool==1) {
       break;
     }
  }
}

Best Answer

With the following little piece of code you can accomplish that.

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.

Screenshot

find
enter image description here

search enter image description here

result
enter image description here

Example

I've created an example file for you: get cell row and column index

Related Topic