Google Sheets Script: TypeError: getValues is not a function

Question

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();
}

If the error occurs at var data = range.getValues(), from Logger returned: c_VLookup range: A160:E162, it seems that the range is the string value. So in this case, how about modifying the function of c_GetRangeByName(NamedRange)? Because in this case, I think that it is required to know the information of Spreadsheet and sheet for the range. For example, if var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(NamedRange) cannot be used instead of var range = c_GetRangeByName(NamedRange), can you provide the script of c_GetRangeByName(NamedRange)? — Tanaike
– Tanaike, Commented Nov 21, 2020 at 11:08
Hi Tanaike, I've added the function c_GetRangeByName above. I think you're right, I can't use this to get a range object? — maxhugen
– maxhugen, Commented Nov 21, 2020 at 11:19

Tanaike · Accepted Answer · 2020-11-21 11:45:28Z

2

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:

edited Nov 21, 2020 at 11:45

answered Nov 21, 2020 at 11:34

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

maxhugen Over a year ago

Re your last comment, col A is the "lookup" column, like in sheets VLOOKUP(). Then the "index is the column from which to get the result, numbered 1,2,3 etc. I tried to make it exactly like the sheets function. So it should look for "25" in col A, and return the value in col B.

Tanaike Over a year ago

@maxhugen Thank you for replying. I have to apologize for my poor English skill. Unfortunately, from your replying, I cannot understand it. I think that in this case, in order to know your actual goal, it is required to confirm your sample input and output you expect. So can you post it as new question by including more information? By this, it will help users including me think of the solution. If you can cooperate to resolve your issue, I'm glad.

Collectives™ on Stack Overflow

Google Sheets Script: TypeError: getValues is not a function

1 Answer 1

Modified script:

Note:

References:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Note:

References:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related