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 [tag:urlfetch], you'll understand that your question is wrong — TheMaster, Oct 13 '19 at 16:42
Possible duplicate of [Are there promises on Google Apps Script?](https://stackoverflow.com/questions/36561735/are-there-promises-on-google-apps-script) — Anton Dementiev, Oct 13 '19 at 19:37
@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](https://stackoverflow.com/a/58366287/1595451) — Rubén, Oct 13 '19 at 20:42
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, Oct 14 '19 at 00:39
@TheMaster Ok, now understood about urlFetch. but is there any solution for my problem now? how can i get data correclty? — Ameerudheen.K, Oct 14 '19 at 00:56
@AntonDementiev I have updated question and i think its not duplicate now — Ameerudheen.K, Oct 14 '19 at 01:48
1. Read custom function optimization in the official documentation. 2. Have you considered that the data is not provided by quandl? — TheMaster, Oct 14 '19 at 01:53
Looking at the [Quandl Documentation](https://docs.quandl.com/docs/in-depth-usage), in the first example response it appears that the section where the data is stored is `dataset_data`, in your code you use `dataset.data[0][4]`, shouldn't it be `dataset_data.data[0][4]`? — AMolina, Oct 14 '19 at 07:23

Rubén · Accepted Answer · 2019-10-14T01:57:50.717

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.

Rubén · Answer 2 · 2019-10-14T01:41:55.137

UrlFetchApp.fetch(url) returns a HTTP response object. This response include a HTTP response code that could 200 for a successful fetch but could return other codes due to multiple reasons.

Some people in the past have suggest the use an algorithm called exponential back-off, like in this case Error message: "Cannot connect to Gmail". Be careful to not exceed the 30 seconds execution time limit.

RESTful HTTP response codes

Google sheet custom function returns undefined Values sometimes

2 Answers2