How to fix the custom function in google spreadsheet when the script can't take input?

Question

I wrote a custom function from the google app script. It's supposed to take value inside from the cell and print JSON data. It succeeded in working in the backend. However, I can't put any input from the front end.

=STOCKC(AMD)

This is the custom function I want to make inside the google sheets. It's supposed to extract data from the JSON data.

    function STOCKC(symbol){
    //this is the testcase for the backend
        //var symbol = "AMD";
        symbol = encodeURI(symbol);
       var url = "https://www.alphavantage.co/query?function=TIME_SERIES_WEEKLY&symbol="+symbol+"&apikey="+api_key;
       var responseUrl = UrlFetchApp.fetch(url);
       var response = responseUrl.getContentText();
       var stockdata = JSON.parse(response);
       var closepricear = new Array();

      if(Object.keys(stockdata).length > 1){
  var help = Object.keys(stockdata)[1];
    for(var i = 0; i < Object.keys(stockdata[help]).length; i++){
      var helpVal = Object.keys(stockdata[help])[i];
      var open = Object.keys(stockdata[help][helpVal])[0];
      var close = Object.keys(stockdata[help][helpVal])[3];
      var low = Object.keys(stockdata[help][helpVal])[2];
      var volume = Object.keys(stockdata[help][helpVal])[4];
      var high = Object.keys(stockdata[help][helpVal])[1];
      var date = Object.keys(stockdata[help]);

     closepricear.push({"close":stockdata[help][helpVal][close]})
    // closepriceard.push(date)
    }
  }
       Logger.log(closepricear);
       return closepricear;
}

The logger.log(closepriear) shows the data I want but the front end won't print.

I get

reference doesn't exist

error at the front end.

This is the closepriear Logger.log() data. This is what i get when i do the backend test. It's a long list of data consist of the following.

[19-07-13 15:52:55:823 PDT] [{close=33.2100}, {close=31.5000}, {close=30.3700}, {close=29.1000}, {close=30.3600}, {close=32.4100}, {close=27.4100}, {close=26.4400}, {close=27.5000}, {close=27.9600}, {close=28.2200}, {close=27.8800}, {close=27.6800}, {close=27.8500}, {close=28.9800}, {close=25.5200}, {close=26.3700}, {close=23.2900},

Can you please provide us with a sample of data that is written into closepricear? It might be crucial to debug your code, judging from the code you posted, there should be no issue after TheMaster's answer is implemented — Oleg Valter
@OlegValter I just edited it. Let me know if you need more or if i provided the wrong thing. — jjunbbug
Thanks, @jjunbbug! That's the issue, do not return Objects as Array elements - they won't be displayed, either stringify or map values as [...,['close',33.2100],...] - should work (not sure what the desired output is, will post an answer after further clarification) — Oleg Valter

Oleg Valter Oleg Valter · Accepted Answer · 2019-07-13T23:35:31

Problem

To understand what is going on when your custom function pastes values, you can refer to setValues() method documentation of the Range class (Google Sheets API for apps script), it works pretty much the same way - when you return values from the function, they are interpreted as a two-dimensional Array like this (single values are interpreted the same as setValue()):

//pseudo-code; [ row1: [col1,colN], rowN: [col1,colN] ]

Solutions

Reformat your Array elements as [33.2100] (if you only need values);
Use JSON.stringify() on your objects before output (if need to preserve obj);

Useful links

setValue() method reference;
setValues() method reference;

How to fix the custom function in google spreadsheet when the script can't take input?

2 Answers

Issue#1:

Issue#2: