ReferenceError on all google Sheet build in functions

Question

I am getting started with Google App Scripts and have written a little something. The problem I am having is the following: ReferenceError: "COUNT" is not defined

I am getting this too when I try to use COUNTA or any other build in function of the Google App Script.

I am uncertain weather or not my script will work but testing it would be much easier if I was able to execute it.

For reference I have the script in question here:

function fillMonthFromChildSheet()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var dates = [];
  var dateCell;
  var totalExpense = 0;
  var rangeB = "B:B";

  for (var i = 0; i < COUNT(rangeB) - COUNTBLANK(rangeB); ++i)
  {
    dateCell = "B" + i;
    dates.push(DATE(dateCell));
  }

  for (var i = 0; i < dates.length(); ++i)
  {
    if (MONTH(dates[i]) == MONTH(TODAY()))
    {
      var range = "A" + i;
      totalExpense += sheet.getRange(range).getValue(); 
    }
  }
  return totalExpense;
}

Help is much appreciated! Thank you in advance

i think you're missing condition in the middle of the for loop. i < COUNT(rangeB) - COUNTBLANK(rangeB); — boroboris, Apr 02 '17 at 11:45
I indeed was (awkward) but even added in it does not work :( — Timbo, Apr 02 '17 at 11:49
The use of spreadsheet functions in JavaScript is not supported. See [this link](http://stackoverflow.com/questions/11660574/using-built-in-spreadsheet-functions-in-a-script) — rasmeister, Apr 02 '17 at 11:50
Thank you @rasmeister. I will update the code once it works. Is there a reason not to make the sheet functions available through a prototype? — Timbo, Apr 02 '17 at 12:21
you can use standard javascript functions. spreadsheet functions are not javascript. — Zig Mandel, Apr 02 '17 at 20:59

score 1 · Accepted Answer · answered Apr 04 '17 at 11:38

From what I see from the code, you try to get all the expense of the month and sum it. Here is a sample of code you may like:

function fillMonthFromChildSheet()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var totalExpense = 0;

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

    var d = new Date(data[i][1]);
    var today = new Date();

    if(d.getMonth() == today.getMonth()){

      totalExpense += data[i][0];

    } 
  }
  return totalExpense;
}

Instead of using the value directly on the Spreadsheet, we get all the data in an array an use it to sum the expense of the month.

Like Vytautas say, the function COUNT, COUNTBLANK, DATE, MONTH are function that you use on the Spreadsheet, but not on Google Apps Script. For future edit, you can see the documentation here: https://developers.google.com/apps-script/reference/ — Pierre-Marie Richard, Apr 10 '17 at 07:55

score 0 · Answer 2 · answered Apr 03 '17 at 06:00

Your problems are COUNT(rangeB) COUNTBLANK(rangeB) DATE(dateCell) MONTH(dates[i]) MONTH(TODAY()) as you have not defined these anywhere. By the looks of things you are trying to write a Google Apps script and use a spreadsheet function, like =COUNT(A1:A2). These functions do not work in Google Apps script

In general it's just JavaScript with the addition of these classes and methods that you can find here.

ReferenceError on all google Sheet build in functions

2 Answers2