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}, 

Answer 1

Issue#1:

AMD doesn't exist at the frontend. It's a #REF error(reference doesn't exist), because AMD isn't defined. If AMD is a plain string literal and not a named range, you need to quote it, just like you did in JavaScript.

=STOCKC("AMD")    

Issue#2:

The return variable closepricear is a array of objects. You need to return a single value using JSON.stringify(closepricear) or a 2D array:

closepricear.push([stockdata[help][helpVal][close]])