Tournament scorekeeping using Google Sheets

I’ve had the idea of posting this for a longer while. A few comments about the scorekeeping in Hungary Pinball Open, and for me the inability to watch any of the standings at home finally made me to spend time to post this. This probably is nothing new or interesting for all of you using the more high quality systems (big shoutouts to Karl for the DTM system and Andreas for matchplay.events), but at least in Europe many still seem to use paper sheets or local Excel spreadsheets for the scoring calculations.

In short: if you are using any tournament format that needs a spreadsheet to calculate the standings, why not use Google Sheets (GS)? In comparison to local Excel docs etc, you instantly have all the data online to share with others, have backups and easy rollback features readily available, and you don’t even have to host anything yourself. There are some requirements about your computer performance and internet connection capacity, but in majority of the cases these aren’t any issue.

Thanks to a good friend of mine with crazy skills in everything about spreadsheets, we’ve been using GS for basically everything regarding pinball tournament scorekeeping in Finland for several years. The Finnish Pinball League with matchplay qualifications and single elimination playoffs, and many of the other annual tournaments with various limited entry or matchplay qualification formats use his more or less automated Google Sheets. This friend of mine has even formalized his work to a point that you can make your own sheets copying his base sheets to run your own tournament:

The input sheet https://docs.google.com/spreadsheets/d/1N2PPMBefHUNzFqbfbgx7TEe7_rwWubJS2r-vz28WTKs/edit#gid=688929683
The public sheet https://docs.google.com/spreadsheets/d/1VeRJL4iacbX2AYuKtTzZrue_MYaFQgMEAYsGMp5CuS0/edit#gid=1828622396

The input document is the “backend” document, where you input all the data. The public document is the document that you can share for others to see the standings online. When submitting the entries, you basically just need to find the correct sheet for the game and input the player number and the score. Everything else is done automatically and the Info sheets of both of the documents probably provide you with enough information to get started with.

Using Google Sheets with a mobile device isn’t that convenient, so with these you probably still would need to have a paper to write down the scores on a machine and then give the paper to someone who inputs the entries to the Google Sheet. Still it’s way easier than how many of the tournaments seem to collect and input their data. In our case the need for papers was eliminated by a friend of mine developing an awesome browser app, that inputs all the data to GS.

In K15 Open 2016 we had this even more crazily awesome in-house produced scoring app (http://scores.k15.fi ) which also automatically input the data to Google Spreadsheets. All the tournament data in GS can be seen at http://live.k15.fi. Having used GS to practically every tournament, it’s been easy to archive all the tournament result links to a single archive page http://k15.fi/past.php

In match-play tournaments like Finnish Pinball League we have a laptop available for players to input the matchplay results to GS, which then makes all the necessary calculations for points and standings. See the latest Finnish Pinball League event standings at https://docs.google.com/spreadsheets/d/15k8o-j4pfjbCFDehOapOPdwmq8RuDKpjsEqeCzESTV4/edit#gid=1

Oops, this ended up being longer post than I thought. Sorry about that. If the provided base sheets would help you to organize a tournament, feel free to make your own copies.

7 Likes

Thanks for sharing. It’s always useful to see the infrastructure other tournament directors have created.

Just one clarification…

If you prefer to work in Excel, you can also share the spreadsheet publicly on the internet. You can either use the Excel Web App (the web-based version of Excel). Or you can use a locally installed version and publish it to the web.

These require you to have a Microsoft account, however, so you can use OneDrive (Microsoft’s equivalent of Google Drive).

The advantage to this is that you can continue to use your existing Excel spreadsheets and take advantage of Excel’s more robust feature set. The disadvantage is that you will need to purchase a license for Excel.

Lest anyone think I’m trying to start a Google Apps vs Microsoft Office debate, let me just state that they both have their strengths and weaknesses. Having worked in spreadsheets every day for 20+ years as a financial analyst, I wouldn’t want to give up either one. They are both pro tools.

2 Likes

I’ve experienced that myself, and discovered a great tool: Google Forms. From Google Sheets itself, you can select “Form” from the Insert menu, and get started building a mobile-friendly input solution, that players or scorekeepers can visit with their mobile browser. It will create a new sheet, and you can use that as your input sheet. New rows are created in that sheet as scorekeepers submit the form.

I’ve found that some of the form input types are not great on mobile (and render differently between iOS and Android). For example, I think maybe drop-down lists don’t work well on iOS, but radio buttons (“multiple choice” as Google calls them) are better (I forget, it looks like my form attached to my friend’s sheet got deleted)? In any case, a little experimentation should sort that out.

We haven’t used it in our little home league because we have no cell or wifi connectivity where we play (so we just type it into an offline version of our GS).

2 Likes

Do you think it would be possible, and sensible, to generate pingolf scorecards via google forms so groups can enter data directly to my scoring spreadsheet?

1 Like

Yes, yes, and yes.

1 Like

As someone who’s reasonably well-acquainted with the Google Sheets web app, the mobile app is “meh” at best. You’d think Google would go out of their way to make it more palatable on Android, but maybe that’s why it’s no longer pre-installed on my Pixel (it was on my old Nexus 6). :slight_smile:

1 Like

I figured @stevevt would comment here, but the Pinball Coop in VT has used Google Sheets/Forms for score tracking in several tournaments. Here’s their selfie league leaderboard (an embedded Google Sheet). They use a multiple choice Google Form for entering results from a bank of games, with the caveat that a selfie must also be sent by email corroborating the score. Super user-friendly and easy to use. As an aside, I think they’re moving away from user-entered scores to TD-entered scores to meet the WPPR 5.3 reporting requirements in 2017; not sure if the system will move out of Google Sheets at that point.

2 Likes

Thanks, @drdudelol!

The Google Form and Google Sheet solution works very well, assuming there’s internet access. For 2017, we’ll be giving our tournament officials access to the Google Form for score entry at the time the game is played, and we’ll be skipping the selfie component.

@fletchtb is our web administrator, and he’s done a great job putting this all together. He’s probably the one to ask if anyone needs more details.

EDIT: Forgot to mention that we’ve also used this solution for a handful of 1-day events.

4 Likes

[quote=“ericwag, post:2, topic:2200, full:true”]
Thanks for sharing. It’s always useful to see the infrastructure other tournament directors have created.

Just one clarification…

If you prefer to work in Excel, you can also share the spreadsheet publicly on the internet. You can either use the Excel Web App (the web-based version of Excel). Or you can use a locally installed version and publish it to the web.

These require you to have a Microsoft account, however, so you can use OneDrive (Microsoft’s equivalent of Google Drive).[/quote]

Back in the day in Portland, we would scorekeep with Excel, and project them onto a screen. No Microsoft account needed.

On-site process:

  1. We export the html files from excel (I think we used a macro so it was just a simple command)
  2. We switch to an FTP client and upload the html files to the FTP site.
  3. We have Firefox open on the projected computer, with a tab loaded for each HTML file saved on the hard drive
  4. We have Firefox in fullscreen mode to maximize the number of rows visible.
  5. We run a slideshow plugin in Firefox to cycle through the tabs, refreshing each tab when it is loaded.

Example: http://rosecitypinball.com/showdown/2012/open/

EDIT: And looking through my emails from this time, there are about 80 back-and-forths of us trying to figure out how to format it so that it was legible, the page didn’t scroll, and the sponsor logos were visible. I don’t think we attempted to get it to look nice on mobile devices.

3 Likes

Resurrecting this old thread with a quick shoutout to Ad Jonker and everyone else in the Dutch Pinball Masters crew. A beautiful way to use Google Spreadsheets for the huge matchplay tournament standings:

(BTW, the tournament final is currently live at https://www.twitch.tv/jrepinball )

EDIT: wrong spreadsheet link changed to the correct one.

4 Likes

My latest Google Sheets project was to archive all IFPA 16 results. All these sheets can be found via shortlink https://tinyurl.com/ifpa16data

I had already made a live Google Sheet of playoffs in IFPA 15 in Canada. After seeing some usefulness in this data back then, I decided to try to collect everything, including also Epstein Cup, qualifications and tie-breaks this year. Even though the methods for inputting the data were quite primitive, all the data ended up being online quite quickly providing nice support for the amazing live streaming we were able to enjoy during the entire event.

Big thanks to my good friend Joonas Haverinen, who watched the event back home and followed my private google photos folder, where I uploaded every qualification group paper sheet of the tournament and Joonas inputted this data to google sheets. Joonas also helped filling in some playoff bracket scores based on the information shown on the stream. The rest of the work was done by me during the event, which was actually quite fun and relaxing, as I love to watch high quality pinball anyway.

Many cool things could be done with this data by for example making high score lists of every machine throughout the event, finding matches with victories by really small margin or someone totally crushing all the opponents etc. It could make sense to form a live-updating overall qualifying stangings page to the sheets also, but since this data is officially available on IFPA website, I don’t think even that is necessary. After all, even with the current data the number of sheets is quite big and having more sheets would make everything even more unorganized.

Happy to hear any feedback and ideas for improvements about this.

5 Likes

Thank you for doing this! I’m reworking the data (by hand so it’s taking a while) so I can submit the match results to Match Play Ratings. For past IFPAs only the finals bracket results are available so this is much much better

2 Likes