CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
maxhugen
maxhugen

Reputation: 1944

Google Sheets Script: TypeError: getValues is not a function

I've created a vlookup function to retrieve values in a Named Range, as it is part of an equation that's rather complicated to use in the sheet. It's structured just the same as the native VLOOKUP, but I only use it for Named Ranges.

Getting "TypeError: range.getValues is not a function" when I run a test, haven't been able to determine why. Any advice would be appreciated. MTIA

function c_test(){
  var result = c_VLookup(25, "Foil_Cant_Values", 2);  
  Logger.log("c_VLookup returned: " + result);
}

function c_VLookup(searchKey, NamedRange, index) {
// Author: Max Hugen
// Date: 20102-11-21
// Purpose: Script version of sheets VLOOKUP(search_key, range, index)

  var range = c_GetRangeByName(NamedRange);
    if (!range) { 
      Logger.log("c_VLookup didn't return a range.");
      return;
    }
  Logger.log("c_VLookup range: " + range);
  //Logger returned: c_VLookup range: A160:E162

  //TypeError: range.getValues is not a function (line 26, file "Utils")
  var data = range.getValues();

  var resultRow = data.filter( function(row) { row[1] = searchKey; });
  var result = resultRow[index];
  return result;
}

function c_GetRangeByName(NamedRange) {
SpreadsheetApp.getActiveSpreadsheet().getRangeByName(NamedRange).getA1Notation();
}

Upvotes: 0

Views: 2689

Answers (1)

Tanaike
Tanaike

Reputation: 201513

In your script of c_GetRangeByName, getA1Notation() is used and no values are returned. I think that this is the reason of your issue. So please modify as follows.

Modified script:

function c_GetRangeByName(NamedRange) {
  return SpreadsheetApp.getActiveSpreadsheet().getRangeByName(NamedRange);
}

And also, your filter is required to be modified as follows.

var resultRow = data.filter( function(row) { return row[1] == searchKey; });

Note:

  • If you want to check the A1Notation at Logger.log("c_VLookup range: " + range);, please modify it to Logger.log("c_VLookup range: " + range.getA1Notation());
  • As the additional information, in your script, when the cells "B160:B162" have the value of 25, the values of row 162 is returned. Although I'm not sure about your actual situation, please be careful this.

References:

Upvotes: 2

Related Questions