Google Scripts Search & Return Cell Location

Question

I am having some trouble with my first Google Script and would appreciate some help.

Objective: To search multiple sheets for a value and then return the location of the cell if a match was found.

I have tried using this script:

/**
* Finds a value within a given range. 
* @param value The value to find.
* @param range The range to search in.
* @return A range pointing to the first cell containing the value, 
*     or null if not found.
*/
function find(value, range) {
var data = range.getValues();
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j++) {
if (data[i][j] == value) {
return range.getCell(i + 1, j + 1);
}
}
}
return null;
}

However it returns the error "TypeError: Cannot find function getValues in object "

My value will be defined as a text string e.g "ABCD"

My range will be ranges of multiple sheets e.g sheet1!A1:C4,sheet2!A1:C4

The function should return the sheet, column and row of the lookup value

Currently i'm using an iferror index match loop formula to search sheets one column at a time (I'm using indirect as my range is variable) but would like a script to make this easier and faster.

=IFERROR(index(INDIRECT(Sheet1!A:A),
iferror(MATCH(A2,INDIRECT(Sheet1!B:B),0),
iferror(MATCH(A2,INDIRECT(Sheet1!C:C),0),
...ETC

Thank you in advance for any help.

Akshin Jalilov Akshin Jalilov · Accepted Answer · 2015-07-23T11:59:57

Try this:

function find(value, range) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = range.split("!")[0];
  var A1Ref = range.split("!")[1];
  var activeRange = ss.getSheetByName(sheet).getRange(A1Ref);
  var data = activeRange.getValues();
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] == value) {
        return (ss.getSheetByName(sheet).getRange(i + 1, j + 1));
      }
    }
  }
  return null;
}

This will return range. You can change that line to get the A1Notation:

return (ss.getSheetByName(sheet).getRange(i + 1, j + 1).getA1Notation());

Google Scripts Search & Return Cell Location

1 Answers