CODEWITHSUNDEEP
google-apps-scriptscriptinggoogle-sheets
DanielAttard
DanielAttard

Reputation: 3595

Google Apps Script function return value does not display on spreadsheet

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.

Upvotes: 1

Views: 2253

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

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.

Upvotes: 1

Related Questions