LibreOffice Calc – Retrieve and Access JSON Object via HTTP

httpjavascriptjsonlibreoffice-calcmacros

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.

https://developers.coinbase.com/api/v2#get-spot-price

  1. Download and install the plugin. You will have two new functions:
  • GET() which accepts the API endpoint as an argument
  • PARSEJSON() which accepts two arguments:
    1. JSON source. This can be any local or online file as long as it is proper JSON. We'll be using the GET() output.
    2. 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.)

Related Question