Sometimes you don't have a full understanding of somethings functionality or capacity, but you still think 'I can do this'. Eventually you wind up down a rabbit hole and come out learning something new.
This time it is thanks to Google Sheets, XML, and wanting easy access to a bunch of boardgame's details.
So I'm getting ready to head to a small boardgaming convention for the second time. There will be a game library you play games from, a great way to try out different games. Last year we tried some games based on recommendations, which is great, but other times we were basically selecting games cause the title sounded interesting.
The problem: I'd like easy access to all the board games information, on a spreadsheet I can have saved to my phone.
Original thought: Googles sheets allows me to save a spreadsheet to my phone for on and offline use, it has a built in importxml function, and boardgamegeek.com has an API that export XML data. Plus the conventions website has a list of their games, a few even with the boardgamegeek links.
So this should be simple right? ... Yes, for a very small sample.
I need the ID number for the game on boardgamegeek. These numbers are in the links so it is easy to split them out.
So put this is cell A1:
https://www.boardgamegeek.com/boardgame/129437/legendary-marvel-deck-building-game
Then use this formula to get just the number:
=SPLIT(A1, "abcdefghijklmnopqrstuvwxyz:/.-")
The other way is if I just have the exact name of the game I could use importxml to get the ID#:
=IMPORTXML(CONCATENATE("https://www.boardgamegeek.com/xmlapi/search?search=",A1,"&exact=1"), "//boardgame/@objectid")
Great now I got the ID number and can get XML stats from:
https://www.boardgamegeek.com/xmlapi/boardgame/<ID#>?&stats=2
Using this formula if my ID # is in A2 and I want to get the playing time:
=IMPORTXML(CONCATENATE("https://www.boardgamegeek.com/xmlapi/boardgame/",A2,"?&stats=2"),"//playingtime")
I can adjust that to get anything I can from min and max players to average rating and the game's weight. However I got to a put where there was to many of these that it would cause an error cause they were each separate simultaneous calls, especially when I left the sheet and came back it would want to refresh all of them.
So this is there the script editor came into help. Not only could I combine all the elements for one game into one request, I could also do multiple games at one time! So after working on it for a bit this is what I came up with:
function boardgamegeek(id)
{
//output an array
var output = [];
//url strings
var preurl = "https://www.boardgamegeek.com/xmlapi/boardgame/";
var posturl = "?stats=2";
var ids = id[0].toString(); //add the first id string
//for each id number combine them to the string
for(i = 1; i < id.lenght; i++)
ids.concat(",", id[i].toString());
//get the full url
var url = preurl+id+posturl;
//fetch and parse the xml
var xmlFeed = UrlFetchApp.fetch(url).getContentText();
var xml = XmlService.parse(xmlFeed);
//for each game
for(i = 0; i < id.length; i++)
{
//get wanted info
var rows = xml.getRootElement().getChildren("boardgame");
var row = rows[i];
var names = row.getChildren("name");
for(j = 0; j < names.length; j++)
if(names[j].getAttribute("primary")) //get only the primary name of the game
var name = names[j].getValue();
var minplayers = row.getChild("minplayers").getValue();
var maxplayers = row.getChild("maxplayers").getValue();
var playingtime = row.getChild("playingtime").getValue();
var age = row.getChild("age").getValue();
var averageweight = row.getChild("statistics").getChild("ratings").getChild("averageweight").getValue();
var average = row.getChild("statistics").getChild("ratings").getChild("average").getValue();
var mechanics = row.getChildren("boardgamemechanic");
if(mechanics.length > 0) //grab all the mechanics
{
var mechanic = mechanics[0].getValue();
for(h = 1; h < mechanics.length; h++)
{
var temp = mechanic;
mechanic = temp.concat("\n", mechanics[h].getValue());
}
}
else
mechanic = " ";
var categories = row.getChildren("boardgamecategory");
var category = categories[0].getValue();
for(k = 1; k < categories.length; k++) //grab all the categories
{
var tempcat = category;
category = tempcat.concat("\n", categories[k].getValue());
}
//add all that info to one layer of the array
output.push([name, Number(minplayers), Number(maxplayers), Number(playingtime), Number(age), Number(averageweight), Number(average), mechanic, category]);
}
//output the array
return output;
}The last step to all of this was copy all these values to a new sheet, so the formula wouldn't try to update and to make it easier for mobile access.
Here is the end result that I shared with the community: https://docs.google.com/spreadsheets/d/1kRmvAvvem3SChRfdldJTgGpPvtj-2yzqcE2CR-HnIAQ/edit?usp=sharing
On my version I also setup some additional color conditions for columns like rating and weight, along with some filters and notes.