I've been trying to do calculations based on prices on some crypto markets. In the mean time I try and learn to write a custom function in the script editor that gets the ticker data. So far, I can get the data with the following code;

    function getkrakenbtc() {
    var myUrl = "";
    var jsonData = UrlFetchApp.fetch(myUrl);
    var jsonString = jsonData.getContentText();
    return jsonString;
    }

But this function gets all the data into one cell like this:

{
    "error": [],
    "result": {
        "XXBTZUSD": {
            "a": ["6828.90000", "1", "1.000"],
            "b": ["6822.40000", "3", "3.000"],
            "c": ["6828.30000", "0.45700000"],
            "v": ["8345.28377914", "11241.98107284"],
            "p": ["7079.63828", "7171.18596"],
            "t": [22419, 30041],
            "l": ["6751.00000", "6751.00000"],
            "h": ["7432.70000", "7529.70000"],
            "o": "7410.10000"
        }
    }
}

Then I then get the part that I want with the help of formulas. How can i get the a or b item of the above array using javascript?

I've been trying to do calculations based on prices on some crypto markets. In the mean time I try and learn to write a custom function in the script editor that gets the ticker data. So far, I can get the data with the following code;

    function getkrakenbtc() {
    var myUrl = "https://api.kraken./0/public/Ticker?pair=XBTUSD";
    var jsonData = UrlFetchApp.fetch(myUrl);
    var jsonString = jsonData.getContentText();
    return jsonString;
    }

But this function gets all the data into one cell like this:

{
    "error": [],
    "result": {
        "XXBTZUSD": {
            "a": ["6828.90000", "1", "1.000"],
            "b": ["6822.40000", "3", "3.000"],
            "c": ["6828.30000", "0.45700000"],
            "v": ["8345.28377914", "11241.98107284"],
            "p": ["7079.63828", "7171.18596"],
            "t": [22419, 30041],
            "l": ["6751.00000", "6751.00000"],
            "h": ["7432.70000", "7529.70000"],
            "o": "7410.10000"
        }
    }
}

Then I then get the part that I want with the help of formulas. How can i get the a or b item of the above array using javascript?

• javascript
• google-sheets
• editor

• You could convert you json string to an array, and then acces your data from this array – Liora Haydont Commented Apr 4, 2018 at 17:49
• To get properties from a JavaScript object, use either dot or bracket notation, as necessary. If you have no clue what dot or bracket notation is for object property access, then you need to spend some time actually learning about JavaScript objects. – tehhowch Commented Apr 4, 2018 at 17:53
• @tehhowch Actually I know little about notations and I already tried this: return XRPUSDObject[0][2] or return XRPUSDObject[1][2] but both either gives me nothing on the sheet or #ERROR msg – onur rr Commented Apr 4, 2018 at 18:03
• I think I've found out: "jsonString.result.XXBTZUSD.a[0]" will do. Thank you for making me solve this :) – onur rr Commented Apr 4, 2018 at 18:16

2 Answers 2

Convert jsonString to an object with JSON.parse(). Then you can use dot or bracket notation to access the value you want. Here's an example.

function getAandB() {
  var jsonString = getkrakenbtc();
  var jsonObject = JSON.parse(jsonString);
  var a = jsonObject["result"]["XXBTZUSD"]["a"];
  var b = jsonObject["result"]["XXBTZUSD"]["b"];
}

function getkrakenbtc() {
  var myUrl = "https://api.kraken./0/public/Ticker?pair=XBTUSD";
  var jsonData = UrlFetchApp.fetch(myUrl);
  var jsonString = jsonData.getContentText();
  return jsonString;
}

If you want a custom function that can be used in the sheet, you can adapt the above according to the Custom Functions reference documentation. For example:

/**
 * Get the array of values defined by 'ticker' and 'letter' from the selected cell, which contains valid JSON.
 *
 * @param {String} ticker Example: "XXBTZUSD"
 * @param {String} letter Example: "a" or "b"
 * @param {String} cell The cell containing the JSON
 * @return The array of values
 * @customfunction
 */
function GETVALUE(ticker, letter, cell) {
  var jsonObject = JSON.parse(cell);
  return jsonObject["result"][ticker][letter];
}

Here's the function to get JSON value of any depth:

function getTextAsJson(JsonText, valuesArray)
{
  var node = JSON.parse(JsonText);  
  for (var i = 0, l = valuesArray.length; i < l; i++)
  {
    node = node[valuesArray[i]];     
  }    
  return node;    
}

Usage:

=getTextAsJson(A1,{"result"; "XXBTZUSD"; "a"})