Google Apps Script function return value does not display on spreadsheet

Question

I have been using the following custom Google Apps Script for years, and for some reason it has stopped working, partially. The script is supposed to retrieve an array of items, and then display them in a spreadsheet column. Here is the script:

function myAccounts() {
    var output = [], arr = JSON.parse(createArr());  
    for(var i=0, iLen=arr.length; i<iLen; i++) {
        output.push([arr[i][0]]);
    }  
output = output.sort();  
return output;
}

By using the debugger, I am able to see that the array appears to be created properly. This is the output of the function:

var arr = [

[["5383 - CATEGORY A"],["ASSESSMENT REVIEW BRD","ANNUAL FEE", "ATG ASSESS REVIEW BOARD TORONTO ", "ATG-ASSESSMENT REVIEW BRD TORONTO ",
 "MPAC-PICKERING","CITIZENSHIP & IMM ON-LINEOTTAWA","JONES"]],

[["5230 - CATEGORY B"], ["RED CROSS","G(IRLS)20 SUMMIT","MOVEMBER    *CHARITY","HOSP FOR SICK CHILDREN","URBAN SQUASH TORONTO"]],

[["5351 - CATEGORY C"], ["ELTE","PAYPAL *SMITH","PRAXIS INTERNATIONAL ART"]],

[["5350 - CATEGORY D"], ["PAYPAL *CANADAWIDEP","LA PARETE GALLERY","ACTIVE SURPLUS","PAYPAL *DHL EXPRESS","PRINT THREE #95","STAPLES.CA",
  "FRAMING FACTORY"]],

[["5387 - CATEGORY E"], ["SWEETSERVICES","EVENT RENTAL","GLOBAL FINANCIAL","IVISA SERVICES NORTH SYDNEY","QUEST HENDERSON",
 "SLS HOTEL BEVERLY HILLS","LITTLE TIKES","FAIRY FLOWER","FARICY PTY LTD","VISTAPRINT.AU","MYBAGSCOMPA","ITS MY PARTY","PAYPAL *CAMILLEPRIN"]]

]

For some reason, this output is not being seen in the cell of the spreadsheet which calls the function. It doesn't throw an error, but simply leaves the cell completely blank.

Any idea what might be going wrong? Thanks.

Robin Gertenbach · Accepted Answer · 2015-11-15 09:08:32Z

1

The output needs to be a value, an array or an array of arrays.
You have an array of array of arrays which Sheets doesn't know how to handle.
Try removing one level by having the output being

[["CATEGORY A"], ["CATEGORY B"], ["CATEGORY C"]]

This will return one row with three columns.
You can do that from what I can see in the code by writing output.push(arr[i][0]);.
I assume arr[i][0] is already a one element array. which is why this happens.

answered Nov 15, 2015 at 9:08

Robin Gertenbach

10.9k3 gold badges30 silver badges38 bronze badges

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

3 Comments

DanielAttard Over a year ago

Thanks @Robins. Your solution makes sense, but I think that I may not have asked the correct question. I may have oversimplified the structure of my actual data array. I have now edited the question to show my actual multi-dimensional array. As you can see from my data, the result that I am trying to return is the first level inside my array, CATEGORY A, CATEGORY B, ... etc. I am going to keep trying to figure it out based on your suggestion, but if you spot the problem I would appreciate if you could let me know. Thanks.

Robin Gertenbach Over a year ago

The same logic applies. Try output.push(arr[i][0]); and your output will be all the xxxx - category y in separate rows of the column the formula is in.

DanielAttard Over a year ago

Thanks again @Robin. You nailed it. Changing this code from this: output.push([arr[i][0]]); to this: output.push(arr[i][0]); managed to solve the problem. I have no idea why this code seemingly worked fine for years with that extra set of square brackets, and suddenly it stopped working. Now thanks to your suggestion I have removed the extra square brackets and it is working fine again. Cheers.

Collectives™ on Stack Overflow

Google Apps Script function return value does not display on spreadsheet

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related