How to extract data from an object API response Apps script goole sheets

elbingerr 0
Viewed 31

//I want to achieve "highestBid": "94628.41" displayed in A3 cell - the value of highestBid varies with time.

function response() {
  var url = "https://api.zonda.exchange/rest/trading/ticker/BTC-PLN";
  var response = UrlFetchApp.fetch("https://api.zonda.exchange/rest/trading/ticker/BTC-PLN");
  var obj = { highestBid: "94628.41" };
  type: "Fixed Multiple";
  Status: "Active";
  var result = Object.keys(obj).map((key) => [key, obj[key]]);
  Logger.log(result);
  SpreadsheetApp.getActiveSheet().getRange("A3").setValue(result);
}
response google-sheets api javascript
1 Answers

The most important thing for you to do is to get the response body as a string using getContentText() and parse it as JSON using JSON.parse() so you can access the values easily in JavaScript following the structure of the returned data:

{
  "status": "Ok",
  "ticker": {
    "market": {
      "code": "BTC-PLN",
      "first": { "currency": "BTC", "minOffer": "0.0000468", "scale": 8 },
      "second": { "currency": "PLN", "minOffer": "5", "scale": 2 },
      "amountPrecision": 8,
      "pricePrecision": 2,
      "ratePrecision": 2
    },
    "time": "1663191022379",
    "highestBid": "94563.2",
    "lowestAsk": "94846.78",
    "rate": "94846.78",
    "previousRate": "94566.21"
  }
}

You should also make some other changes:

  • use const instead of var to treat values as immutable and you might also be interested in their scope
  • you should check for the response code using getResponseCode() as if the request fails (which it can for many reasons) you won't be able to access the data or get unexpected data returned. See HTTP status codes - Wikipedia.

For more details on what parsing means and how JSON and JavaScript are connected see this explanation. Here now how you can set the value of highestBid in cell A3.

function response() {
  const url = "https://api.zonda.exchange/rest/trading/ticker/BTC-PLN";
  const response = UrlFetchApp.fetch(url);
  if(response.getResponseCode != 200){
    Logger.log(`The request failed with status code ${response.getResponseCode()}`) 
  } else {
    // get the content of the response body as string (in UTF-8 encoding) 
    const responseStr = response.getContentText("UTF-8")
    // the string is in JSON format so we can now parse it to make it accessible in javascript 
    const data = JSON.parse(responseStr);
    // the hightest bid can now easily be obtained
    const highestBid = data.ticker.highestBid;
    Logger.log(`The highest bid is ${highestBid} until ${new Date(data.ticker.time).toLocaleString()}`)
    // set value in cell A3
    SpreadsheetApp.getActiveSheet().getRange("A3").setValue(highestBid);
  }
}

By the way: Object.entries(result) returns the same result as var result = Object.keys(obj).map((key) => [key, obj[key]]);.

Mushroomator0
Related