CODEWITHSUNDEEP
javascriptgoogle-sheetseditor
onur rr
onur rr

Reputation: 57

How to get data from JSON in google sheets custom function

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.com/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?

Upvotes: 1

Views: 6176

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18727

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"})

enter image description here

Upvotes: 0

Diego
Diego

Reputation: 9581

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.com/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];
}

enter image description here

Upvotes: 6

Related Questions