Further to my previous post about working with the @SteemMonsters data in a Google Sheets spreadsheet, and in light of my previous howto guide for connecting LibreOffice to the live data from @Steempunknet, I thought I might have a go at combining these two techniques into one, and bring the live JSON data from #SteemMonsters into a Google Sheets spreadsheet this time. The first thing I had to do was establish that there were publicly available JSON endpoints for SteemMonsters, as there are for Steempunk.net, so I checked this with @Yabapmatt, and he confirmed that there are, and gave me the details for the endpoint I'll need for the cards. There are other JSON endpoints exposed by SteemMonsters, but this is the only one we'll be dealing with in this #howto guide. The endpoint I'm using is steemmonsters.com/cards/get_details.
โถ Locating a script that can import JSON
The next thing that I had to do is discover how to access JSON data using Google Sheets. As you will see from my previous howto guide, which dealt with LibreOffice Calc, it isn't always a straightforward process and can involve some script installation first. For Google Sheets, I came across this post on Medium by Paul Gambill, and the related GitHub Gist for import_json_appsscript.js. There is also further documentation for this script here.
โท Install the script
As always, you should be very careful when installing scripts, especially if you don't know the author, or don't know precisely what the script does, or if you can't read the code yourself to ascertain its validity and safety. That said, I haven't had too many problems installing scripts in Google cloud services in the past.
As you'll see from that Medium post, the instrctions to install the script in your Google Sheets spreadsheet is quite straighforward...
- Create a new Google Spreadsheet.
- Click on Tools -> Script Editor.
- Click Create script for Spreadsheet.
- Delete the placeholder content and paste the code from this script.
- Rename the script to ImportJSON.gs and click the save button.
- Back in the spreadsheet, in a cell, you can type
=ImportJSON()and begin filling out itโs parameters.
That's all there is to it! Very simple to install. Next we'll look at using it to access the live data from SteemMonsters.
โธ Connect to the API
The next step is to connect to the #SteemMonsters API endpoint that you are interested in. For this example, we are going to use the /cards/get_details endpoint, as mentioned above. To do a simple test of this, we can import the entire table into our spreadsheet in one hit, with a header row, by typing the following into cell A1...
=๐ธ๐๐๐๐๐๐น๐๐พ๐ฝ("๐๐๐๐๐://๐๐๐๐๐๐๐๐๐๐๐๐๐.๐๐๐/๐๐๐๐๐/๐๐๐_๐๐๐๐๐๐๐","","๐๐๐๐๐๐๐๐๐, ๐๐๐๐๐๐๐๐๐๐, ๐๐๐ ๐ท๐๐๐๐๐๐")
When you press ENTER โ the URL should resolve, and you should see an unformatted table full of the JSON data, as in the screenshot. One thing that you will notice is that the Stats get "rolled up" into a single cell per Stat, displaying as a comma-separated array within the cell. Similarly, the Abilities get "rolled up" into a single cell as well. Another thing that I have found is that the is_Starter field isn't handled very well, and the values all come through as "TRUE", which is, in fact, quite false. I think this has something to do with the script not handling boolean logic very well, but I'm not sure.
โน Request specific data from the API
I found that the way the data is brought through in bulk like this isn't very user friendly. For one thing, you can't insert columns between the existing columns, which is something that I do all the time. So instead, I bring in one column at a time from API data, by adjusting the value of the second parameter of the API call. In the above example, our second parameter was left empty, (,"",). This time let's give it a specific query to search for within the results table. Edit what you have in cell A1 to now read...
=๐ธ๐๐๐๐๐๐น๐๐พ๐ฝ("๐๐๐๐๐://๐๐๐๐๐๐๐๐๐๐๐๐๐.๐๐๐/๐๐๐๐๐/๐๐๐_๐๐๐๐๐๐๐","/๐๐","๐๐๐๐๐๐๐๐๐, ๐๐๐๐๐๐๐๐๐๐, ๐๐๐ ๐ท๐๐๐๐๐๐")
Notice here that I've changed the second parameter from ,"", to ,"/id",. This time when you press ENTER โ the URL should resolve, and you should see an unformatted single column of JSON data for the id numbers of the cards, as in the screenshot. We can now do the same for all of the columns. At this point it may be handy to have the /cards/get_details API endpoint visible in a browser window while you work, so that you can just copy & paste the field names into the second parameter of the API call, for each column in your spreadsheet. Also, rememer that the sub-fields for Stats and Abilities need to be specified in the format ,"/stats/mana", and ,"/stats/abilities", etc.
Finally, once you have all of your columns direct from the API, you can do some tricky things with them, like adding autofilters, formatting, and even converting those raw numbers into words. For this latter, I use a formula like this...
=๐ธ๐ต(๐ต๐ธ="๐ท","๐ฒ๐๐๐๐๐",๐ธ๐ต(๐ต๐ธ="๐ธ","๐๐๐๐",๐ธ๐ต(๐ต๐ธ="๐น","๐ด๐๐๐",๐ธ๐ต(๐ต๐ธ="๐บ","๐ป๐๐๐๐๐๐๐๐ข","๐พ๐๐๐๐"))))
Once they are converted to words, you can do some nice conditional formatting to make the spreasheet look pretty. Here's my example...
The textual content of this post is licensed as a Free Cultural Work using a Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license, which grants all the four freedoms listed in the definition of free cultural works, including: โ The freedom to use and perform the work; โก The freedom to study the work and apply the information; โข The freedom to redistribute copies; and โฃ The freedom to distribute derivative works. This license places three key restrictions on those freedoms: โค ๐๐จ ๐๐๐ ๐จ๐ซ ๐๐๐: You must not restrict access to the work using technical measures, or otherwise attempt to impose limitations on the freedoms above; โฅ ๐๐ญ๐ญ๐ซ๐ข๐๐ฎ๐ญ๐ข๐จ๐ง: You must give proper attribution to the author (๐ฃ๐ป๐ฒ๐ผ๐บ๐พ๐ฎ๐ต๐ฆ๐ฑ๐ช๐ป๐ฎ) and retain the license notice; and โฆ ๐๐จ๐ฉ๐ฒ๐ฅ๐๐๐ญ: You must release derivative works under an identical or similar license. The most recent copy of the text of this license notice (with markdown) can be found on Hackmd.
โข The image used for the post thumbnail is a remix by ๐ฃ๐ป๐ฒ๐ผ๐บ๐พ๐ฎ๐ต๐ฆ๐ฑ๐ช๐ป๐ฎ. It includes screenshots from Google Sheets.
โข The image used for the horizontal rule separators throughout this post is from the #SteemMonsters divider set, created by
. It is used with permission, and has been released under a Creative Commons Attribution 4.0 International (CC BY 4.0) license.โข The image used for the SteemMonsters banner is a remix by ๐ฃ๐ป๐ฒ๐ผ๐บ๐พ๐ฎ๐ต๐ฆ๐ฑ๐ช๐ป๐ฎ. It includes the Steem Monsters Logo Refresh image created by
, which is used with permission, and has been released under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0) license. The remix also includes the "Rexxie" and "Goblin Socerer" assets from the official SteamMonsters collection. They are used with permission. The remixed image is released under the same license (CC BY-NC-SA 4.0) as the component part from , as per the ShareAlike terms.โข The image used for the PeakMonsters banner is a remix by ๐ฃ๐ป๐ฒ๐ผ๐บ๐พ๐ฎ๐ต๐ฆ๐ฑ๐ช๐ป๐ฎ. It includes image elements from the PeakMonsters.com website. The remixed image is released under a Creative Commons Attribution 4.0 International (CC BY 4.0) license.