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
– Zig Mandel, Commented Apr 5, 2015 at 15:11

ScampMichael · Accepted Answer · 2015-04-06 16:28:15Z

2

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

edited Apr 6, 2015 at 16:28

answered Apr 5, 2015 at 17:26

ScampMichael

3,7382 gold badges19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HuN Over a year ago

Thanks, that solves my problem. I think you and Sandy are right about the real issue being the way GSheets handles return values.

Alan Wells · Accepted Answer · 2015-04-05 14:31:22Z

1

You are referring to two custom functions in the same cell, printObject(obj) and removeDuplicateSum(label, data). I'm not sure if the Sheet will allow that. I'd change things around, and only use one custom function name in the cell, and have the function call the other function in the .gs file.

I don't know of any reference that compares JavaScript functionality in .gs, server side code, vs. JavaScript used elsewhere. The only thing I could find is something in Wikipedia, which may be outdated:

Wikipedia - Apps Script

I looked through all the Google Apps Script documentation, and didn't see anything about what JavaScript version Apps Script uses, or what functionality Google may have excluded.

Obviously, you can't access the browser's DOM from server side code, but other than that, I haven't ever noticed a difference.

I don't think this is an issue of how JavaScript in a .gs file is behaving. I think it's how the spreadsheet is handling the return value.

edited Apr 5, 2015 at 14:31

answered Apr 5, 2015 at 14:06

Alan Wells

31.4k16 gold badges113 silver badges164 bronze badges

1 Comment

ScampMichael Over a year ago

Hey Sandy, I wasn't sure myself so I tested and found you could use nested custom formulas. Opens up some things to consider.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related