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

Question

Just want to create simple filler to use it in other functions:

function fillLine(row, column, length, bgcolor)
{
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1+row, 1+column, 1, length).setBackground(bgcolor)
}

But getting: "Cannot find method getRange(number,number,number,(class))".

Is it possible to create one?

added 23.10.2014 23:32 GMT+2

I was trying to run function itself. Option "fillLine" in run menu was checked. When I change it to my main function "onOpen" everything works. For else functions I can run them even without arguments, but not for this one. Maybe someone can explain?

Answer 1

You're running this in the debugger with NO parameters. If you want to do that, you need to ensure you have appropriate default values defined before using the parameters, otherwise the interpreter will start making guesses.

This would fix things for you, by providing defaults if parameters are empty:

function fillLine(row, column, length, bgcolor)
{
  row = row || 0;
  column = column || 0;
  length = length || 1;
  bgcolor = bgcolor || "red";
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1+row, 1+column, 1, length).setBackground(bgcolor)
}

Alternatively, you could use a test function like this, and call it from the debugger:

function test_fillLine() {
  fillLine(0, 0, 1, 'red');
}

There are other approaches, some described in Is there a better way to do optional function parameters in Javascript?

---

So what's going on?

Javascript parameters are not explicitly typed. Therefore, the interpreter must examine passed objects at run time to determine their type.

In this example, with no parameters passed, all are set to the undefined value, with the 'Undefined' type.

When a function is invoked, the interpreter needs to determine which of sometimes several signatures applies. (Sheet.getRange has 4 variants.) To do that, it must resolve the number and types of the parameters provided.

So, here we go...

• 1+row, and 1+column are identified as numbers, because the literal "1" is a number.
• 1 is clearly a number
• length is an Undefined Object, and without any other objects to provide hints, the interpreter thinks it is a plain class.

Therefore, the interpreter decides it needs to find a getRange method of the sheet object with a signature number, number, number, class. It doesn't find one, and throws the exception you see.

Answer 2

I succeeded by using offset.

var sheet = SpreadsheetApp.getActiveSheet()
//var range = sheet.getRange(row, column, height, width)
var range = sheet.getRange('A1').offset(row - 1, column - 1, height, width)

So in your case, the following code may run.

function fillLine(row, column, length, bgcolor) {
  var sheet = SpreadsheetApp.getActiveSheet()
  //var range = sheet.getRange(row, column, 1, length)
  var range = sheet.getRange('A1').offset(row - 1, column - 1, 1, length)
  range.setBackground(bgcolor)
}

In my case, I need to add permission to use setBackground. A popup menu to add the permission appeared in a script editor when I run a test script as @Mogsdad said.

function test_fillLine() {
  fillLine(1, 1, 2, 'red');
}

https://developers.google.com/apps-script/guides/services/authorization

Answer 3

You have to go through the activeSpreadsheet, where the activeSheet is "embedded" in, i. e.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

intead of

var sheet = SpreadsheetApp.getActiveSheet();