Can I retrieve and access a simple JSON object via HTTP in LibreOffice Calc?

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.)

Solution 1:

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.)

Solution 2:

After 4 years, they still haven't implemented the suggested FILTERJSON().

This is obviously not a real solution, but one that works for now, for very simple cases:

1. Send a request to your API using: =WEBSERVICE("http://example.com/v1/stuff") in eg. cell A1
2. In cell A2 for example, add =MID(A1;SEARCH("rootProperty";A1)+2;10) (remember to change the separator characters (semicolons in my case) to match your locale in LibreOffice)

Explanation:

• MID() will take a partial text from the full response
• SEARCH() will look for the beginning index of the given text, eg. "rootProperty"
• +2 is the offset where to start reading the value (basically substring)
• 10 is how many characters to read from the beginning of the text SEARCH() found (after the +2 offset)

So if for example you get the following response in cell A1:

{
  "id": 12345678,
  "something": "Example",
  "myThing": "Hello",
  "another": "Not needed"
}

And your cell A2 has the formula: =MID(A1;SEARCH("myThing";A1);13)

What the cell would have as value is: myThing": "He Which is 13 characters starting from the first character in myThing, that you searched for in SEARCH().

So obviously you wouldn't want to have the myThing": " part of it. To only get the value Hello from myThing, you would use this formula: =MID(A1;SEARCH("myThing";A1)+11;5).

This will:

1. Find where myThing begins (caveat: if there's two instances of the string, you'll get the first one)
2. Return 5 characters from the offset. So: myThi
3. Then offsets the beginning to be from index 11 (the +11 in the formula)
4. So it skips the first 11 characters (myThing": ") and returns 5 characters from there on, which is Hello

Of course, it's still fairly manual, but at least one workable way for simple things. Good luck!

Solution 3:

Probably better to ask on StackOverflow, but yes, you can retrieve and access/convert a JSON object with Python, which works very well with LibreOffice Calc. You can work the problem in both directions, by either using Python to control LibreOffice, or have LibreOffice run Python scripts, depending upon your needs.

As for converting JSON to something LibreOffice can use (csv), there's a good beginner explanation at Applied Informatics.