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
How exactly are trying to invoke the function? Do you attempt to do it directly from an individual cell? — Anton Dementiev
@Cooper it doesn't work. It displays that "Cannot convert NaN to (class). — Marek Kolo
@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
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

Anton Dementiev Anton Dementiev · Accepted Answer · 2017-08-28T16:37:02

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.

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

2 Answers