user3347814 user3347814 - 3 months ago 45
Javascript Question

Google Spreadsheet Script: "Cannot find function getRange in object Sheet" when creating a simple function

Sorry, for the stupid question, but I´ve searched the whole internet and I could not find a good Tutorial to learn how to program in Google SpreadSheet Script.

I want to make a very simple function just for practice.

function simplesum(input) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets();
var range = sheet.getRange(input);

var x = 0;

for (var i = 1; i <= range.getNumRows(); i++) {
for (var j = 1; j <= range.getNumColumns(); j++) {

var cell = range.getCell(i, j);

x += (cell.getValue());
}
}

return x;
}


I know I could use =sum() to do exactly the same thing. The idea here is to learn how to program.

When I try to use my function in a cell: (i.e: =simplesum((A1:A8)) it gives an Error saying: "TypeError: Cannot find function getRange in object Sheet. (line 4)"

What should I do?

And again, sorry for the dumb question....

Answer

In this case, you are implementing a Google Apps Script function as a custom function, invoked in a spreadsheet cell.

When you pass a range to a custom function invoked in a spreadsheet cell, you are not passing a range object or a range reference, but rather a 2-D Javascript array of values. So your custom function should just process that array.

function simplesum(input)
{
  var x = 0;
  for (var i = 0; i < input.length; i++)
  {
    for (var j = 0; j < input[0].length; j++)
    {
      x += input[i][j];
    }
  }
  return x;
}