Simple Google Script not working

I have decided to use Google Scripts when I can when using spreadsheets, just to start increasing knowledge. BUT I am untrained and got stuck quite quickly.

I saw some code on you开发者_如何学编程tube for a different script but I thought I extracted what I needed and could get this to work...

function y_add_up_function() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange["I2:J15"];
  var data = range.getValues();
  var sum = 0;

  for (var i = 0; i < 14; ++i)
  {
    var row = data[i];
    if(row[1] == "y")
    {
      sum = sum + row[0];
    }
  }
  return sum;
}

This is my spreadsheet. I am trying to just simply get the SS to add up where I have ticked y next to a price, but I am getting an error that reads:

TypeError: Cannot call method "getValues" of undefined. (Line 5)

Please teach me what I am doing wrong ^_^!!!

Cheers!

The error means that you are trying to call a method on an undefined object. If you look at your code, you have range.getValues(). This means that range is undefined. Therefore you need to look at the assignment of range: var range = sheet.getRange["I2:J15"]. And here is the problem, getRange is a method and need to be called with parenthesis, that is getRange("I2:J15").

Here is the working code:

function y_add_up_function() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange("I2:J15"); // <-- fix here
  var data = range.getValues();
  var sum = 0;



  for (var i = 0; i < 14; ++i)
  {
    var row = data[i];
    if(row[1] == "y")
    {
      sum = sum + parseFloat(row[0].substring(2)); // <-- fix to get floats from "£ <number>"
    }
  }
  return sum;
}

Also note that since you have pound signs in your cost column, you need to strip the signs to get a number. Here I have used .substring(2), but this is not very fail safe. If you e.g. forget to put "£ " in a cell the number will be read incorrectly. I recommend putting the pound sign in the column header or use a more sophisticated method to remove it when parsing the numbers.

You can for example use row[0].replace(/^[0-9.]/g, ''). This will replace all characters that are not digits nor decimal points. This has no error-checking, but I think it is enough in a personal spreadsheet.