1

I'm looking for a way to search for a string in a range and get the position of the cell once found.
Here's what I use to find the row number 

var ss = SpreadsheetApp.getActiveSheet();
var values = ss.getRange("B:B").getValues();
var i=rownum=0;
for(i=0;i<values.length;i++) {
  if(values[i]=='string') rownum=i+1;

I then use the following code to find the column number

var colvalue;
for (j=1;j<ss.getLastColumn();j++) {
if (ss.getRange(3,j).getValue() == "string" {
colvlaue = j;
  }
}

Is there a way to search both entire rows and columns within a range that contains a specific string and return its cell position once it finds it?

Alan Wells
  • 30,746
  • 15
  • 104
  • 152
Sean W
  • 377
  • 1
  • 5
  • 18
  • Sean: do you want to look up that string in one column (as your code seems to suggest) or in multiple columns ? – JPV Aug 29 '15 at 16:48
  • I'm looking to have it search multiple rows and columns for a cell that contains the string. So essentially going through every cell in a range. – Sean W Aug 29 '15 at 17:43

1 Answers1

4

You need to use a nested for loop. This code will do it:

function findValueInRange (po) {
  /*
    po.whatToFind - the string value to find

  */

  var colvalue,i,j,L,L2,ss,theLastColumn,theLastRow,thisRow,values;

  ss = SpreadsheetApp.getActiveSheet();
  theLastColumn = ss.getLastColumn();
  theLastRow = ss.getMaxRows();
  
  //Returns a two dimensional array of both rows and columns
  values = ss.getRange(1,1,theLastRow,theLastColumn).getValues();
       
  
  whatToFind = po.whatToFind;
  
  L = values.length;

  for(i=0;i<L;i++) {
    thisRow = values[i];
    L2 = thisRow.length;

    for (j=0;j<L2;j++) {
      colvalue = thisRow[j];

      if (colvalue === whatToFind) {
        Logger.log("The string is in row: " + (i + 1) + " and column: " + (j+1));
      };
    };
  };
};
Alan Wells
  • 30,746
  • 15
  • 104
  • 152
  • Thank you, the code above got the result I sought for. I don't know if it's appropriate to ask here, but is there a way to search for partial string instead of exact match? – Sean W Aug 29 '15 at 20:05
  • 1
    Yes, you can use RegEx. [Documentation](http://www.w3schools.com/jsref/jsref_obj_regexp.asp) Also see [stackoverflow partial match javascript](http://stackoverflow.com/questions/9060979/javascript-regex-partial-match?rq=1) – Alan Wells Aug 29 '15 at 20:29
  • I read through the documentation on RegEx, but I'm still not able to figure out how to implement it into the code to make it a partial string search. – Sean W Aug 30 '15 at 02:34
  • @SeanW: you can also use indexOf in the if-statement: if (colvalue.indexOf(whatToFind) > -1) { – JPV Aug 30 '15 at 06:27
  • Thank you, JPV! I had trouble using indexOf but found out I need to convert colvalue to string, now it works great. – Sean W Aug 30 '15 at 17:48