CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
DaMaxContent
DaMaxContent

Reputation: 254

Get cell by value

How do I get the range of a cell using its value and column?

Say I have this column:

[   ][   A   ]
[ 1 ][ hello ]
[ 2 ][ world ]
[ 3 ][ waaah ]
[ 4 ][ waaah ]
[ 5 ][ waaah ]
Sheet: WALUIGI_SAYS_"HI"

I want to find the first instance of waaah. I know it is in column A, but I do not know the row.

How do I find it with apps scripts?

Upvotes: 0

Views: 263

Answers (1)

bcperth
bcperth

Reputation: 2291

There are 3 ways at least to do what you ask.

  1. Manually put a formula like =MATCH("waaah",A1:A5,0) in a cell an read the value of that cell it in the script.

  2. Put the formula in a cell from a script and read as above.

  3. Make your own script to loop through the range, checking for a value match at each location. Increment a counter on each loop and exit the loop when a match is found. The counter will be the index.

EDIT: Added some sample code for clarity with each method demonstrated.

/**
 * Finds first matching row in a column
 * @customfunction
 */
function findFirstMatchingRow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  //Method 1 - Formula manually placed in B1
  var row = sheet.getRange("B1").getValue();
  if (row=="#N/A"){
      Logger.log("NoMatch");  
  }else
  {
       Logger.log(row);  // shows 3
  }


  //Method 2 - Formula placed in B2 via script.
  row = sheet.getRange("B2").setFormula("=MATCH(\"waaah\",A1:A5,0)").getValue();
   if (row=="#N/A"){
      Logger.log("NoMatch");  
  }else
  {
       Logger.log(row);  // shows 3
  }

  //Method 3 - Make your own search loop
  // first load the values into an array (in one trip to the server)
  var valuesArray = sheet.getRange("A1:A5").getValues();
  var lengthOfArray = valuesArray.length;
  var matchFound = false;
  var n;

  for (n=0; n<lengthOfArray; n++){   // loop through the values looking for a match
      if (valuesArray[n] == "waaah")
      {
        matchFound=true;
        n++;                        // add one because arrays start at 0 not
        break;
      }
  }
  if (matchFound){
    Logger.log(n);    // shows 3
  }
  else {
    Logger.log("No match");
  }

}

Produces this log output:

[18-10-22 08:43:49:917 HKT] 3.0
[18-10-22 08:43:50:176 HKT] 3.0
[18-10-22 08:43:50:300 HKT] 3.0

Upvotes: 2

Related Questions