Cannot find method getRange(number,(class),number)

Question

I have read all answers about this error, but none of them work for me. So maybe someone has another idea.

I'm trying to pass parameter to function getRange but when I'm trying to invoke this function it shows me error

Cannot find method getRange(number,(class),number)

Here is my code:

function conditionalCheck(color,rangeCondition, rangeSum, criteria){

 var condition = SpreadsheetApp.getActiveSheet().getRange(2,rangeCondition,15).getValues();
 var val = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(2,rangeSum,15).getValues();
 var bg = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(2,rangeSum,15).getBackgrounds();
 var sum = 0;

  for(var i=0;i<val.length;i++){
    if(condition[i][0] == criteria && bg[i][0] != color){
    sum += val[i][0];
    }
  }
  return sum;
}

And I pass a custom function like:

conditionalCheck("#ffff00",1,3,A3)

This is how the sheet looks like:

I understand that JS trying to guess the type of parameters, that is why it thinks that ex. "rangeCondition" is a class, but I don't know how to give him a Number type.

Funny thing is, that this function works when I open the spreadsheet, but when I'm trying to invoke this function while I'm working it shows me this error. So to update sheet I have to reopen the whole spreadsheet.

Try this: `var val = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(2,Number(rangeSum),15).getValues();` and do the same with the next line. When you invoke it while your working on it, do you provide inputs? — Cooper, Aug 28 '17 at 14:31
How exactly are trying to invoke the function? Do you attempt to do it directly from an individual cell? — Anton Dementiev, Aug 28 '17 at 15:49
@Cooper it doesn't work. It displays that "Cannot convert NaN to (class). — Marek Kolo, Aug 28 '17 at 16:41
@AntonDementiev if I wanted to invoke the function from a custom menu or in the Script Editor. If I use this function in a new cell it works, but I need to update this while I'm working on that file. That's why I made a custom menu to could invoke this function when I need this — Marek Kolo, Aug 28 '17 at 16:41
Well at least we know that's not a number? I think it's time to get to know your debugger. — Cooper, Aug 28 '17 at 16:42
Just tried that in Chrome JS Console. http://prntscr.com/ge3ln5 It's clear that you are not passing the parameter to the function — Anton Dementiev, Aug 28 '17 at 16:49
Yes, that is right. I also get the "undefined" as result. But I'm wondering how it is possible, that this function works when it's invoked in the cell (as we can see a result on the Print Screen). But when I want to call this function from Custom Menu or Script Editor it shows error? — Marek Kolo, Aug 28 '17 at 17:13
When you invoke the function from the cell, you pass the parameters between the brackets. When you invoke it from the custom menu or script editor, nothing is passed, so parameter is 'undefined' — Anton Dementiev, Aug 29 '17 at 00:41
@AntonDementiev it makes much sense, and I think you are right. So, can I do anything with that? Because while working on that sheet, the result of this function doesn't update automatically and I have to reopen spreadsheet to update. Thank you for your help — Marek Kolo, Aug 29 '17 at 07:12

Anton Dementiev · Answer 1 · 2017-08-29T14:11:33.093

I was able to reproduce the error by executing the function directly from the editor.

This behavior makes sense, considering custom function needs to receive a valid parameter. At runtime, your 'rangeSum' parameter is set to 'undefined', which invalidates the getRange() method as it requires a number.

It's actually quite strange that you got your 'for' loop working. In most cases, using the the '+' operator on array values obtained from the sheet will concatenate them into a string, so you'll get '5413' instead of 13 (5 + 4 + 1 + 3)

function calculateSum(column) {

column = column||1;  //set the default column value to 1

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(1, column, sheet.getLastRow());
var values = range.getValues();

var sum = 0;

  for (var i=0; i < values.length; i++) {

    sum += parseInt(values[i]);


  }

  return sum;

}

Finally, this approach is not very efficient in terms of performance. Once you get the instance of the object, you should use that instance instead of attacking the server with multiple calls like you do in the first 3 lines of your code. For example

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = sheet.getRange(1, 1, sheet.getLastRow(), 1);

If you plan on using this custom function in multiple places within the sheet, consider replacing it with the script that processes the entire range - this way you will only perform a single read and write operation to update all values. Otherwise, you may end up filling up your service call quotas very quickly

UPDATE

Instead of creating custom function, create the function that takes the entire range and processes all data in one call. You can test the 'calculateSum()' function above with predefined column number, e.g.

var column = 1 // the column where you get the values from

Upon getting the 'sum' value, write it into the target range

var targetRange = sheet.getRange("B1"); // the cell that you write the sum to

targetRange.setValue(sum);

Finally, you can make that function execute after you open or edit the sheet by appending the following code

function onOpen() {

    var ui = SpreadsheetApp.getUi();
var menu = ui.createMenu('Menu')
.addItem('Calculate sum', 'calculateSum')
.addToUi();

   calculateSum();
}


function onEdit() {
  calculateSum();

}

Calling it from the custom menu would be simple - you just need to create the handler and pass function name as parameter.

Thank you. I optimized my code. "For" loop can add, not concatenate, if you treat the result as a two-dimensional array, like in my code. At the beginning, I had the same problem. — Marek Kolo, Aug 28 '17 at 17:04

score 1 · Answer 2 · answered Sep 16 '17 at 10:36

I found a solution for a problem with reloading custom function after changing data.

The solution is described here: Refresh data retrieved by a custom function in google spreadsheet

It showed that we cannot refresh custom function if we don't change inputted parameters because it is cached. So if we change inputted data, a function will reload. We can force a function if we put there timestamp

Cannot find method getRange(number,(class),number)

2 Answers2