I've found the easiest way is using the GetRest Plugin for LibreOffice.
You can use separate cells, one for pulling the data, one for formatting it, and so on. However by combining functions (or creating a macro), you can achieve quite a bit of formatting in a single cell.
Example: Getting Current Bitcoin Spot Price
For this, I'll be using the Coinbase API, many of their calls do not require authentication.
https://developers.coinbase.com/api/v2#get-spot-price
- Download and install the plugin. You will have two new functions:
GET() which accepts the API endpoint as an argumentPARSEJSON() which accepts two arguments:
- JSON source. This can be any local or online file as long as it is proper JSON. We'll be using the
GET() output.
- The structure/hierarchy of the JSON file pointing to the specific value you want.
HTTP Request
GET https://api.coinbase.com/v2/prices/:currency_pair/spot
For the currency_pair, I want the BTC value in USD, so that will be replaced with BTC-USD
JSON Response
{
"data": {
"amount": "1015.00",
"currency": "USD"
}
}
Using Two Cells
In A1:
=GET("https://api.coinbase.com/v2/prices/BTC-USD/spot")
In A2, parse the JSON response. The parsing works at the object name level, separated by a period. This is passed as an argument into the function in one of two ways, if the JSON contains an array, we specify the name of the array and object index as arrayName.get(i).objectName. Our example is just an object with two "key":"value" pairs, so the format is objectName.keyName:
=PARSEJSON(A1, "data.amount")
Which reads as:
23966.93
Using One Cell
Method is essentially the same, however instead of passing the cell as the first argument, we're passing the entire GET() function:
=PARSEJSON(GET("https://api.coinbase.com/v2/prices/BTC-USD/spot"), "data.amount")
Formatting
The JSON response passes a string value, which is wrapped in a function, so you cannot any of the options within that cell to format it currency or a number.
However I still want to have the "$" in front of it on my spreadsheet, so we can concatenate strings to add this prefix, our resulting functions looks like this:
=CONCAT("$",PARSEJSON(GET("https://api.coinbase.com/v2/prices/BTC-USD/spot"), "data.amount"))
This fits into once cell, can be updated by pressing F9 (be mindful that every refresh to an open API endpoint is taking some of their server bandwidth, so try to limit the amount of refreshes. If you can re-use a cell without having to create another call, that's even more considerate.)
