Google sheet custom function returns undefined Values sometimes

Question

I have wrote a function to return latest weekly closing price of a stock in Google Sheet Apps Script.
But when using this in Google Sheets some cells are getting undefined data. But the same cells are getting values correclty sometimes. Cant understand whats the problem here. Also is there any option to debug my code when I execute the function from googlesheet cell?

function getWeeklyClosing(stockName){

  var date =new Date()
  var endDate = Utilities.formatDate(new Date(), "GMT+1", "yyyy/MM/dd")

  var startDate = Utilities.formatDate(new Date(date.getTime()-10*(24*3600*1000)), "GMT+1", "yyyy/MM/dd")

  var url ='https://www.quandl.com/api/v3/datasets/BSE/BOM'+stockName+'?start_date='+startDate+'&end_date='+endDate+'&collapse=weekly&api_key=3VCT1cPxzV5J4eGFwfvz';
  var options =
      {
        'muteHttpExceptions': true,
        "contentType" : "application/x-www-form-urlencoded",
        "headers":{"Accept":"application/json"}
      }; 
  var response = JSON.parse(UrlFetchApp.fetch(url, options))
  var weeklyEma=response.dataset.data[0][4];
  return weeklyEma;
}

Answer 1

This answer corresponds to question rev 3 which had as title "How to use Promise in appscript?"

The title of the question is asking about an attempted solution rather than the actual problem ( an X-Y problem). The assumption that UrlFetchAp.fetch is asynchrous is wrong ( See Is google apps script synchronous?); the actual problem is getting undefined values on certain cells.

The solution will depend on the what you want to do when the the fetch response is causing the undefined values. One alternative is to replace the undefined values by "" (an empty string) before sending the values to the spreadsheet that will cause having an empty cell on Google Sheets.

By the other hand, it could be that the API you are querying is not returning the JSON that you think, so first you have to understand it and then set the rules about how to send the result to the spreadsheet as not always it's possible to transform a JSON into a simple table structure.