Cannot retrieve the value of the parameter output

Question

I am using ImportJSON function in Google sheets to import data values from AlphaVantage API.

For example, the query string: https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=MSFT&interval=5min&apikey=demo

returns:

{
    "Meta Data": {
        "1. Information": "Intraday (5min) open, high, low, close prices and volume",
        "2. Symbol": "MSFT",
        "3. Last Refreshed": "2019-02-13 16:00:00",
        "4. Interval": "5min",
        "5. Output Size": "Compact",
        "6. Time Zone": "US/Eastern"
    },
    "Time Series (5min)": {
        "2019-02-13 16:00:00": {
            "1. open": "106.8400",
            "2. high": "107.0000",
            "3. low": "106.7200",
            "4. close": "106.7800",
            "5. volume": "855430"
        },
        "2019-02-13 15:55:00": {
            "1. open": "106.9100",
            "2. high": "106.9350",
            "3. low": "106.7800",
            "4. close": "106.8300",
            "5. volume": "336088"
        },
        "2019-02-13 15:50:00": {
            "1. open": "107.0800",
            "2. high": "107.0821",
            "3. low": "106.8500",
            "4. close": "106.9300",
            "5. volume": "279939"
        }
    }
}

I want to display the last updated "closing price" value in my Google sheets cell. So I am using this query:

IMPORTJSON("https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=acc.ns&interval=1min&apikey=OJATWN8EM09KRWZS", "Time Series (1min)/4. close" )

But it is showing

Node not available" error.

Can anyone help me out on what I am doing wrong?

Why bother with the complicated parser and xpath? Just use the first part of the script and you have a JSON object? `var res = UrlFetchApp.fetch(url); var content = res.getContentText(); var json = JSON.parse(content);` — Chris, Feb 14 '19 at 07:53
Hi Chris, I am a newbie in jSON. Could you please let me know what string should be put in the google sheets cell to retrieve the closing price data? — Surya Kumar Paul, Feb 14 '19 at 08:36
You're missing the date key. `Time Series (1min)/2019-02-13 16:00:00/4. close` Obviously you need to modify the script. Look into `Object.keys()` and arrays. — TheMaster, Feb 14 '19 at 08:58

score 0 · Answer 1 · answered Feb 14 '19 at 09:14

It may be easier for OP to just take the key parts of that script and avoid xpath. This code will get the json from a url that is passed to it and return an array of the 4. close values. With the array you can append the values to a spreadsheet. This is easy enough to find how to do with a little Google-ing.

function getCloseDataFromURL(url){
  var res = UrlFetchApp.fetch(url); 
  var content = res.getContentText(); 

  var json = JSON.parse(content)["Time Series (5min)"]; //Just get Time Series section...
  var data = [];

  Object.keys(json).forEach(function(item){ //Get the timestamp keys...
  data.push(json[item]["4. close"]); //for each "4. close" item get the value and add it to an array
  });

  return data; //data is an array of the 4. close values.
}

Cannot retrieve the value of the parameter output

1 Answers1