Live SteemMonsters JSON data to Google Sheets

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

I play SteemMonsters, a collectible card battle game on the Steem blockchain. You can see my card collection in PeakMonsters.

^{_{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.}}

^{_{IMAGE CREDITS:
• 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 @calumam. 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 @mrgodby, 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 @mrgodby, 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.}}