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

If you read tag page of urlfetch, you'll understand that your question is wrong — TheMaster
Possible duplicate of Are there promises on Google Apps Script? — Anton Dementiev
@AntonDementiev I don't think that this is duplicate of the referred question as, IMHO, this is a x-y problem as I mentioned on my answer — Rubén
If you are using getWeeklyClosing as the custom function, when the multiple formulas of =getWeeklyClosing(value) are put to the cells, those are run with the asynchronous process. In this case, it can be considered that each UrlFetchApp of multiple custom functions is run with the asynchronous process. But I cannot understand about the relationship between undefined data and asynchronous UrlFetchApp. Can you explain about this? And if you want to run them with the synchronous process, how about giving the values as an array? If I misunderstood your question, I apologize. — Tanaike
@TheMaster Ok, now understood about urlFetch. but is there any solution for my problem now? how can i get data correclty? — Ameerudheen.K

Rubén Rubén · Accepted Answer · 2019-10-13T17:55:18

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.

Google sheet custom function returns undefined Values sometimes

2 Answers