Google Apps Script (in Google Sheets) behaves differently from JavaScript

Question

My JavaScript code returns different outputs when run locally and when run in Google Sheets. The code below takes 2 arrays (one contains labels, one contains data) and sum the data according to the label:

function removeDuplicateSum(label, data) {
    var result = {};
    for (var i = 0; i < label.length; i++) {
        if (label[i] in result) result[label[i]] += data[i];
        else result[label[i]] = data[i];
    };
    return result;
}

And here's a sample output:

function printObject(obj) {
// helper function to print the object
    return JSON.stringify(obj);
}

c = [1, 2, 3, 4, 5]
b = ['a', 'a', 'b', 'b', 'c']

console.log(printObject(removeDuplicateSum(b, c))); 
>>> {"a":3,"b":7,"c":5} // Correct output

But when I use it as a custom function in Google Sheets, it returns an empty object:

Output is simply an empty string ""

And the output in that cell is simply an empty string (""). I expect it to return this string "{"a":3,"b":7,"c":5}".

I also have another function which returns an int locally but returns a string in Google Sheets - another example of how JavaScript works differently in Google Sheets.

Why does my JavaScript code work so differently in Google Apps Script (and specifically in Google Sheets)? And where can I find a reference on the differences between Google Apps Script and the JavaScript used in a web environment?

Thanks a lot.

Debug it and look at the input data types. Apps script uses regular unmodified javascript — Zig Mandel

ScampMichael ScampMichael · Accepted Answer · 2015-04-05T17:26:36

My guess is that you are trying to return an {object} to be used a parameter in the outer function and sheets do not accept(know what to do with) {objects} as parameters.

In addition, arrays for label and sum are 2d.

The following gives the expected results:

function removeDuplicateSum(label, data) {
  var result = {};
    for (var i = 0; i < label.length; i++) {
        if (label[i][0] in result) result[label[i][0]] += data[i][0]
        else (result[label[i][0]] = data[i][0]);
    };
    return JSON.stringify(result);
}

Edit: To test the use of nested custom functions I passed text, created by the inner function, as the parameter to the outer function and added more text from the outer function and worked as expected therefore I still believe passing an {object} as a parameter to a custom function is the problem:

The custom formula:

=outerFunction(innerFunction($A$2:$A$6,$B$2:$B$6))

functions:

function innerFunction(label, data) {
  var result = {};
    for (var i = 0; i < label.length; i++) {
        if (label[i][0] in result) result[label[i][0]] += data[i][0]
        else (result[label[i][0]] = data[i][0]);
    };
    return JSON.stringify(result); // {"a":3,"b":7,"c":5} as text
}

function outerFunction(parameter) {
    return parameter + " is parameter";
}

yield:

{"a":3,"b":7,"c":5} is parameter

Google Apps Script (in Google Sheets) behaves differently from JavaScript

2 Answers