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++){
   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]);
             Browser.msgBox("Got an item");
             var bool = 1;
     if(bool==1) {
  • What exacty is it that you're asking? Mar 11, 2014 at 22:35
  • Please see the edit. Mar 14, 2014 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, 2014 at 19:20
  • Uvais, were you able to use my solution?
    – Jacob Jan
    Mar 12, 2015 at 15:21

1 Answer 1


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


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;


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.


enter image description here

search enter image description here

enter image description here


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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