Custom function: return value different than setValues()

Question

I am creating a custom function in Sheets that takes the serial number of a device and returns warranty information from the manufacturer's API. If I call the function on a single serial number, I get the expected output in the adjacent cells of the spreadsheet, but when I call it on a range containing serial numbers I get a blank cell back.

I created a second test function, using setValues() to populate the data instead, and it works perfectly:

var result = GET_WARRANTY(SpreadsheetApp.getActive().getActiveSheet().getRange("Warranty Details!H3:H9").getValues());
SpreadsheetApp.getActive().getActiveSheet().getRange("Warranty Details!I3:L9").setValues(result)

For reference, here is the contents of the result variable (newlines added for readability). Using typeof() shows that all the data is seen as strings within Apps Script.

[[[EXTENDED], [2016-06-12], [2018-06-11], [latitude-e5450-laptop]],
[[INITIAL], [2016-08-03], [2017-11-01], [chromebook-11-3120]],
[[INITIAL], [2017-03-26], [2018-03-26], [chromebook-13-3380-laptop]],
[[INITIAL], [2016-08-03], [2017-11-01], [chromebook-11-3120]],
[[INITIAL], [2016-08-03], [2017-11-01], [chromebook-11-3120]],
[[INITIAL], [2016-08-03], [2017-11-01], [chromebook-11-3120]],
[[INITIAL], [2016-08-03], [2017-11-01], [chromebook-11-3120]]]

The end of my custom function is

//mutliple inputs
results.push([[warrantyType],[warrantyStart],[warrantyEnd],[model]]);
//single input
var results = [warrantyType,warrantyStart,warrantyEnd,model];
return results;

where warrantyType, warrantyStart, warrantyEnd, and model are all strings parsed out of the API JSON response.

I am not finding anything explaining the behavior of the function's return results; to explain why the same data doesn't behave the same way between the two processes.

@i-i Sorry, meant that in a general sense. My custom function has return result;. I'll edit the question. — ChrisG
@I'-'I could have sworn I tried that, I went through several variations of the nested arrays, but that did the trick. If you want to submit it as an answer I'll mark it correct. Thanks for the help! — ChrisG

TheMaster TheMaster · Accepted Answer · 2018-06-08T15:56:39

Change 3D array to 2D array:

Try changing from:

results.push([[warrantyType],[warrantyStart],[warrantyEnd],[model]]);

to

results.push([warrantyType,warrantyStart,warrantyEnd,model]);

Custom function: return value different than setValues()

1 Answers