Search code examples
google-apps-scriptalpha-vantage

Google scripts and Alphavantage json query: TypeError: Cannot read property

I try to fetch data from AlphaVantage stock market API with this code:

function importjson (){     
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("daily data");
   var day = new Date(),
      MILLIS_PER_DAY = 1000 * 60 * 60 *`enter code here` 24; 
   var urlvix = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=xlb&apikey=KEY",    responsevix = UrlFetchApp.fetch(urlvix),
      jsonvix = JSON.parse(responsevix);
// var date = jsonvix["Meta Data"]["3. Last Refreshed"];
   var yesterday = Utilities.formatDate(new Date(day.getTime() - MILLIS_PER_DAY), "GMT+1", "yyyy-MM-dd"),
      curdate  = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd");
   var open = jsonvix["Time Series (Daily)"][curdate]["5. adjusted close"],
        close = jsonvix["Time Series (Daily)"][curdate]["1. open"],
        volumetoday = jsonvix["Time Series (Daily)"][curdate]["6. volume"],
        volumeyesterday = jsonvix["Time Series (Daily)"][yesterday]["6. volume"];
 // Logger.log(date);
  Logger.log(curdate);
  Logger.log(yesterday);
  if  (volumetoday > (volumeyesterday + volumeyesterday*1.1) ){
  Logger.log("distribution");
  }
  else {
  Logger.log("keine distribution")
  };
  Logger.log("open: " + open);
  Logger.log("close: " + close);
  Logger.log("Today´s volume: " + volumetoday);
  Logger.log("Yesterday´s volume: " + volumeyesterday);
  }

Some times when i run the code i get this error:

TypeError: Cannot read property "2018-09-13" from undefined. (Zeile 17, Datei "Libary")

or if i use var date = jsonvix["Meta Data"]["3. Last Refreshed"];

`TypeError: Cannot read property "["3. Last Refreshed"]" from undefined.` (Zeile 17, Datei "Libary")

The Json comes as this : 

Meta Data   
1. Information  "Daily Time Series with Splits and Dividend Events"
2. Symbol   "MSFT"
3. Last Refreshed   "2018-09-13 13:37:27"
4. Output Size  "Compact"
5. Time Zone    "US/Eastern"
Time Series (Daily) 
2018-09-13  
1. open "112.1200"
2. high "113.7250"
3. low  "112.1200"
4. close    "112.8050"
5. adjusted close   "112.8050"
6. volume   "14261782"
7. dividend amount  "0.0000"
8. split coefficient    "1.0000"

The thing is ,i dont get the error everytime i run the code just sometimes, which makes it kinda unreliable.

Is there any mayor problem with this code?

Thanks

Solution

  • The API doesn't always return the JSON you posted. Sometimes it returns:

    { 
"Information":
"Thank you for using Alpha Vantage! Please visit https://www.alphavantage.co/premium/ if you would like to have a higher API call volume."
}

    As you can see, There's no ["Meta Data"] or ["Time Series"]["2018-09-13"] key here. The only key is ["Information"].

    So, jsonvix["Meta Data"] will return Undefined, when you've exhausted your quota. You need to write code to make calls slowly and/or refetch after waiting a few seconds using Utilities.sleep() or opt for a higher volume with AlphaVantage.

    References: