Tracking Board Game Plays Part 1: Pulling board game plays from BGG to Google Sheets

Background

My boyfriend and I play a lot of board games so I decided in the fall of 2014 to start tracking the games we play together. It started simple enough: the name of the game and each of our scores. Luckily, there is an expansive website called BoardGamegGeek (BGG) that has information on any board game you can imagine and they happen to allow users to track things like the name of the game you played and the players’ scores. Over time, the data I wanted to collect grew. I created a simple Google Form to collect things like date the game was played, duration, location, game name, if it was the first time playing the game for either my boyfriend or me, and everyone’s individual scores. This was good but I wanted the data to link to my BGG account instead of be just in a spreadsheet. I found an iOS app called Board Game Stats that allowed me to enter all of the data I was currently entering (it even can collect MORE things!) and it automatically pushes the data to my BGG account. This was good but I wanted the raw data in a spreadsheet like I had before so that I could make plots. After some investigation I found it is easier to pull data from BGG than it is to push it there, so I ditched my Google Form and created a Python script to pull all the new plays into Sheets. This allowed me to use the Board Game Stats app to record my plays, let it push the data to BGG, and then I pull it to Sheets using Python.

General Theory of Design

The goal of the Python script was to pull all the information I am interested in about my plays (date, game name, overall winner, winner between my boyfriend and me, duration, comments about the game, location, and everyone’s score) into Sheets from BGG. I already have the play data from my previous plays, so the Python script must connect to my spreadsheet and check the last game that is entered and add any new plays from BGG.

Connecting to Sheets with Python

I will assume that you already have Python installed and are familiar with the language (if not, the Python documentation is a good place to start). I haven’t made the switch to Python 3 yet, so everything is in Python 2.7. In order to connect to your Google Sheets spreadsheet, you will need to install the modules gspread, json, and oauth2client. I prefer to use pip, which makes installing modules very easy: [text] pip install gspread pip install json pip install oauth2client [/text] Google Developer The first step is to get the credentials set up on the Google side and get our .json file. Go to the Google Developers Console. Create a project or click on the default project, My Project. You should now be in your Dashboard. Click on Use Google APIs: In the search bar, type ‘drive’ and click on Drive API. If it is not enabled, click the enable button. Next, click ‘Credentials’ on the left panel and then ‘Add credentials’. We want to choose Service account. Once you click create, it will download our .json file. As the warning says, you want to put this somewhere on your computer that you will know where it is. I have a copy of mine in my Python project folder. Share Your Spreadsheet In order for Python to be able to open and write to your spreadsheet, you’ll have to give it permission. To do this, open your .json file in whatever text editor you prefer (Notepad++ and Komodo are both great text editors for scripting). The .json file should look something like this: [text] { “private_key_id”: “*”, “private_key”: “—–BEGIN PRIVATE KEY—–*\n—–END PRIVATE KEY—–\n”, “client_email”: “*”, “client_id”: “*“, “type”: “service_account” } [/text] Copy the email address after “client_email”. Next, open the google spreadsheet you intend to edit. Click ‘File’ and then ‘Share’. Paste the email address in the appropriate area and make sure to select ‘Can Edit’. This will allow anything using the credentials inside that specific .json file to view and edit that spreadsheet. Connect to Your Spreadsheet We are finally ready to start writing our script! In order to connect to our spreadsheet, we need to grab a few things from the json file that Google generated for us. [python] # connect to spreadsheet json_key = json.load(open(‘your_api_file_name’)) scope = [‘https://spreadsheets.google.com/feeds'\] credentials = SignedJwtAssertionCredentials( json_key[‘client_email’], json_key[‘private_key’], scope) [/python] Next, we will authorize the gspread module to view and edit the spreadsheets as well as tell it which sheet in particular we are interested in: [python] gc = gspread.authorize(credentials) sh = gc.open(“BoardGames2015”) sheet1 = sh.worksheet(“Sheet1”) manipulation = sh.worksheet(“manipulation”) [/python] You should replace “BoardGames2015” with your spreadsheet name and “Sheet1” and “manipulation” with the sheets you want to edit. I created a function called write_cell_colstring to write to a specific cell in a specific sheet based on the name of the column and the row number: [python] def write_cell_colstring (colstring, row, data, sheet): headers = sheet1.row_values(1) col = headers.index(colstring)+1 sheet.update_cell(row,col,data) [/python] Examples If I wanted to put ‘11/11/15’ in the 3rd row of a column named ‘date’, I would simply use [python] write_cell_colstring(‘date’,3,’11/11/15’, sheet1) [/python] If you want to add a new header, you can use: [python] sheet1.update_cell(1,len(headers)+1,value_in_cell) [/python] where headers is an array of all the values in the first row: [python] headers = sheet1.row_values(1) [/python]

Connecting to BGG with Python

Connecting to my spreadsheet was only half of my battle. I needed to be able to feed the data to the spreadsheet from BGG. Luckily, there is a Python module for this! In order to connect to BGG, you need to get the ‘boardgamegeek’ module: [python] pip install boardgamegeek [/python] Connect to BGG Connecting to BGG is relatively simple to use and the guy who makes it is very helpful. The documentation is a little lacking but the few examples he does provide will help you get pretty far. [python] from boardgamegeek import BoardGameGeek bgg = BoardGameGeek() al = bgg.plays(‘mad4hatter’) # my BGG account name alplays = al.play # all of my recorded plays [/python] That’s it! We are connected! Finding the last game recorded I only need to track the newest recorded games. The first thing I need to do, then, is check to see what the latest game added to the spreadsheet was. This should be the very last row in the sheet. [python] # most recent game gamedates = sheet1.col_values(1) #column1 contains all of the dates latestgame = gamedates[len(gamedates)-1] #latest game in string format latestgame = datetime.strptime(latestgame,’%m/%d/%Y’) # convert to datetime lastrow = len(gamedates) #find how many rows are in the spreadsheet [/python] Looping through the new games I want to loop through all of my plays on BGG that are newer than the last game in my spreadsheet (which we already found and stored in the variable latestgame). [python] for games in alplays: #loop through all my plays on BGG # if the date of the play is newer than the last game on the spreadsheet from when we started if games.date > latestgame: [/python] Next, I only really care about games that my boyfriend and I play together. I check the game to see if ‘Troy’ is listed as a player: [python] #get all the players of this game temp = games.players #check of boyfriend played with me troytest = [‘yes’ for players in temp if players.name == ‘Troy’] [/python] Great! Now, if Troy DID play I want to get some of the play data from that game and put it into my spreadsheet. I start off with the easy ones: the date, game name, and duration: [python] if troytest == [‘yes’]: lastrow += 1 #headers = sheet1.row_values(1) write_cell_colstring(‘date’,lastrow,games.date.strftime(‘%m/%d/%Y’),sheet1) write_cell_colstring(‘gamename’,lastrow, games.game_name, sheet1) write_cell_colstring(‘duration’, lastrow, str(games.duration), sheet1) [/python] Next, I want to get a list of the winner(s). If there is more than one winner, I want to join all of the winners into a string, separate the names with a comma and a space, then write that string into the ‘winners’ column of the spreadsheet. [python] winners = [] win = ‘1’ [winners.append(players.name) for players in games.players if players.win == win] winnersfinal = “, “.join(winners) write_cell_colstring(‘winners’, lastrow, winnersfinal, sheet1, headers) [/python] Now, I want to see if that game is new to either of us. For this, I will get a list of all the games each of us have played and see if this game was the first one. If it is, I will add ‘alnew’ or ‘troynew’ to the variable newplayers. Afterwards, I will write newplayers to the comment column of the spreadsheet. [python] tind = [item for item in range(len(games.players)) if games.players[item].name == ‘Troy’] aind = [item for item in range(len(games.players)) if games.players[item].name == ‘Allison’] newplayers = ‘’ if games.players[tind[0]].new == ‘1’: newplayers += ‘ troynew’ if games.players[aind[0]].new == ‘1’: newplayers += ‘ alnew’ write_cell_colstring(‘comment’, lastrow, newplayers, sheet1) [/python] Finally, I want to see which of us placed better at the end of the game. This is a little harder than you would first imagine because the winner isn’t necessarily the person with the most points. In many games, the goal is to get the fewest points! To do this, I first check to see if there are any winners (if no one won, there is no point in going through the motions of figuring out who ‘beat’ the other person!). Then, I get the score of the person who won, my boyfriend’s score, and my score. To figure out which of us preformed better, I take the absolute value of the difference between my score and the winning score (adif) as well as the absolute value of the difference between my boyfriend’s score and the winning score (tdif). If my boyfriend was closer to the winning score than I was (tdif < adif) then he out preformed me. If my score was closer, I beat him. If our differences are equal, we tied. Depending on who beat who, I put their name into a column in the spreadsheet called ‘troyvawinner’. [python] if winners != []: winscore = [float(players.score.replace(“ “,””)) for players in games.players if players.name == winners[0]] tscore = float(games.players[tind[0]].score.replace(“ “,””)) ascore = float(games.players[aind[0]].score.replace(“ “,””)) tdif = abs(tscore - winscore[0]) adif = abs(ascore - winscore[0]) if (tdif < adif): write_cell_colstring(‘troyvawinner’, lastrow, ‘Troy’, sheet1) print ‘troy’ elif (tdif > adif): write_cell_colstring(‘troyvawinner’, lastrow, ‘Allison’, sheet1) print ‘allison’ elif (tdif == adif and tdif == winscore and not(winscore==0)): write_cell_colstring(‘troyvawinner’, lastrow, ‘Troy, Allison’, sheet1) print ‘both’ [/python] Finally, I have a column for every unique player that I play with in order to track their scores. For every player, I first look to see if they already have a column in the spreadsheet. If they do, I add their score to this game. If they don’t, I add their name to the header and then add their score. [python] for players in games.players: # this only adds scores for people already in the spreadsheet. needs to remove ‘ too headers = sheet1.row_values(1) try: headers.index(players.name.replace(‘ ‘,’’).lower()) except: sheet1.update_cell(1,len(headers)+1,players.name.replace(‘ ‘,’’).lower()) print players.name.replace(‘ ‘,’’).lower() headers = sheet1.row_values(1) write_cell_colstring(players.name.replace(‘ ‘,’’).lower(), lastrow, players.score, sheet1) [/python] And that’s it! I will print out ‘Game Added’ at the end. If my boyfriend didn’t play with me, I print out ‘Troy did not play this game with you’ and if the latest game in the spreadsheet is the most recent game in BGG, then I print out ‘You are up to date!’. [python] print “Game Added” else: print “Troy did not play this game with you” elif games.date == latestgame: print “You are up to date!” [/python] In the final part of this two post series, I will show my spreadsheet and explain how some of the more complicated formulas work.