This is a rough #linux #howto guide showing how I accessed the Steempunk.net API data from LibreOffice Calc. I am using LibeOffice v5.1.6.2 and OpenJDK v8 (1.8.0 rev 181) for this #howto, and I cannot guarantee the same results on any other versions. In fact, I cannot guarantee the same results even if you are running the same versions as I am. Java is a messy thing, and I normally try to avoid it. ๐
โถ Check your Java
I will assume that you already have both of the packages above aleady installed. The scope of this #howto doesn't include installing any software, sorry. One thing you should check, however, is that Java is enabled in LibreOffice before we go any further. To do this, go to Tools
โจ Options
โจ LibreOffice
โจ Advanced
and make sure that the radio-button beside your installed Java runtime environment is selected, as per the screenshot.
โท Install the extension
Now, I told you a little lie to make you feel at ease about this procedure. Unfortunately, there is a little bit of software to be installed. We need the third-party extension itself which will enable LibreOffice to handle JSON data streams. For this part, I am using the LibreOffice GetRest Plugin by Dmytro Bazunov. You can download the latest version of the *.oxt file (LibreOffice extension filetype) from here (direct link, 2.5MiB). Once you have downloaded the file, you need to install it in LibreOffice. To do this, go to Tools
โจ Extension Manager...
โจ Add
in LibreOffice and locate the *.oxt file which you downloaded. Once it is installed, it should look something like the screenshot here.
โธ Connect to the API
The next step is to connect to the #Steempunknet API endpoint that you are interested in. For this example, we are going to use the new list of SPN Fighters, with their usernames and registration dates (https://www.steempunk.net/api/spn/v1/fighters?details=1
). In a new spreadsheet, in cell A1, type...
=๐ถ๐ด๐(๐ท๐๐ฟ๐ด๐๐ป๐ธ๐ฝ๐บ("๐๐๐๐๐://๐ ๐ ๐ .๐๐๐๐๐๐๐๐๐.๐๐๐/๐๐๐/๐๐๐/๐๐ท/๐๐๐๐๐๐๐๐?๐๐๐๐๐๐๐=๐ท"))
When you press ENTER โ
the URL should resolve, and you should see an unformatted string of JSON data, as in the screenshot.
โน Request data from the API
The next step is to specify which data from the unformatted string of JSON data in cell A1 we want to display in each cell. Before I do this though, I have added some column headings and a bit of formatting to my spreadsheet. I have opted to shrink the JSON output in cell A1 to fit the cell width, and to hide it away using matching foreground and background colours. Simple but effective. You can make yours white on white if you like, but I made mine grey to visually remind us that cell A1 is where the JSON data is. At this point it is a good idea to open the JSON data in your web browser as well, so that you can easily see the names of the fields that you want to work with. In cell A3, type...
=๐ฟ๐ฐ๐๐๐ด๐น๐๐พ๐ฝ($๐ฐ$๐ท,"๐๐๐(๐ถ).๐๐")
When you press ENTER โ
the URL should resolve, and you should see the ID number of the zero'th element of the array of JSON data. If you change the ๐๐๐(๐ถ) to be ๐๐๐(๐ท) instead, then you will end up with the "oneth" elementh. That is not quite the same as the "first" element, because arrays start their numbering at zero, not one. Anyway, the same applies for ๐๐๐(๐ธ), ๐๐๐(๐น), etc. Also, to specify a different field, just change the fieldname after the ๐๐๐() part of the instruction. For example, we can get the .๐๐๐๐๐๐๐๐ field for the "zeroth" element of the array by typing in cell B3 the following...
=๐ฟ๐ฐ๐๐๐ด๐น๐๐พ๐ฝ($๐ฐ$๐ท,"๐๐๐(๐ถ).๐๐๐๐๐๐๐๐")
You now have all the tools you need to fill up your spreadsheet with rows and columns. To make things easier, you can copy/paste the formulas between the spreadsheet and a text editor and use macros or search/replace to generate the rest of the formulas you will need to populate the rest of the spreadsheet. I hope this is useful.
โข The image used for the post thumbnail is a remix by ๐ฃ๐ป๐ฒ๐ผ๐บ๐พ๐ฎ๐ต๐ฆ๐ฑ๐ช๐ป๐ฎ. It includes the dog-1773712 image created by GraphicMama-team, which has been released under a Creative Commons Public Domain Dedication 1.0 Universal (CC0 1.0) license.
โข The image used for the horizontal rule separators throughout this post is an original created by ๐ฃ๐ป๐ฒ๐ผ๐บ๐พ๐ฎ๐ต๐ฆ๐ฑ๐ช๐ป๐ฎ, using the (previously) red, white, and black triskellions, inspired by Mฤori tukutuku panels.