I've located a web API resource that I'd like to access from LibreOffice Calc.
It returns a simple JSON/JavaScript object that's basically just a handful of name value pairs, one of which I want to pull out and put in a cell.
I know programming but have barely ever used spreadsheets. From Googling I still can't tell which programming language I should use for this and whether I would need any external addons or just the functions built in to LibreOffice.
(I'm happy to migrate this question to StackOverflow if it's decided that it really belongs there.)
Best Answer
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.
GET()
which accepts the API endpoint as an argumentPARSEJSON()
which accepts two arguments:GET()
output.HTTP Request
For the currency_pair, I want the BTC value in USD, so that will be replaced with
BTC-USD
JSON Response
Using Two Cells
In A1:
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 isobjectName.keyName
:Which reads as:
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: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:
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.)