Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have spreadsheet with two sheets inside. The name of the first sheet is "fr" and the name of the second sheet "cql". Now the first sheet contains data as the picture given below.

Sheet 1

And the second script contains the data as follows,

Sheet 2

Now I want do something here with Google 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 gest the value Ghees Alias T from the first sheet and search the entire second sheet to get a cell with a value contain that string ("Ghees"). If script got 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;
     }
  }
}
share|improve this question

migrated from superuser.com Mar 11 at 14:44

This question came from our site for computer enthusiasts and power users.

    
What exacty is it that you're asking? –  Punchlinern Mar 11 at 22:35
    
Please see the edit. –  Uvais Ibrahim Mar 14 at 5:35
    
I'm trying to do something similar. I haven't been able to find anything helpful. I'm about to decide I have to do some sort of counting in my loop through the sheet and figure out the coordinates that way. Seems sort of hacky to me but maybe it will work. –  user67982 May 6 at 19:20

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.