Google Forms & Sheets – Olympic Scoring – Part 1

What is with me and “multiple part” posts?

OK and just a fair warning this series is going to get a little technical with formulas and linking multiple worksheets together to get a scoreboard of sorts for an “Olympic” event our school is putting on. I’ll do my best to break this down and try to help you connect the dots but if you need some help reach out to me and I will be happy to assist

This first post is just the background info and some basics that you should know or need to learn about Google Forms & Sheets before going forward. so let’s dig in.

Olympics

A lot of schools do this and call it different names. I’ve heard it called field day, activity day, royal rumble and so on. Basically it is a day where the school breaks into teams and performs events, either head to head or individually and then at the end of the day a winner is awarded based on their performance.

So this part in itself is busy enough and has a lot of moving pieces and it takes a certain person with good organizational and leadership skills to pull this off. My hat is off to those people.

At the beginning of the year those people came to the IT department and asked for a way to automatically calculate points for each event. What was happening was that calculating and organizing the results was taking too long at the end of the day, and, well, when you have an event like this, 30 minutes of downtime can spiral out of control real quick, so they needed a better solution and we have created that.

Our set up is pretty simple. We have 15 countries (teams) and 15 events. That means that each team will do each event individually. So we need to be able to capture those results and have a real time calculation of these events. The team that performs best at an event is awarded 15 points. Likewise the team that performs the worst is awarded 1 point.

Google Forms

The solution is Google Forms. I’ve made a very simple Google Form. The first page asks what event is being scored.

Then the second page asks for the country (team) name and how they performed.

We will get into making the form itself on the next post but even if you have never used Google Forms before then you should have no problem figuring this out. It is pretty simple.

For those who do not know, the results of a Google Form can be saved onto a Google Sheet. Google Sheets is a spreadsheet program (like Excel). Here is my example I worked on to give you an idea of what a spreadsheet looks like.

Google Sheets

This is where the real magic happens. To put it simply all the results from the Google Form are saved on a Google Sheet. All the heavy lifting is done here through a variety of different Google Sheets functions.

So I have four different types of worksheets. A worksheet is a different spreadsheet within the same file. So in the picture below you can see seven of the worksheets in this file.

Here are the four different worksheet types I use.
1. Responses – This one worksheet is where all the form submissions are saved. They are sorted by time but really you will not be touching this data much at all.
2. Event types – I made a worksheet for each event. This data is fed from the Responses worksheet. So in total I made 15 total events but really it is a lot of copying so not a bunch of work. Each event will house all the results for each country in that event
3. Country totals – I made a worksheet for each team. This will house (you probably guessed it) all the results for that particular country. Again, I made 15 worksheets of this type but again, it was a lot of duplication so not a bunch of work
4. Totals – This last type is just one sheet. It shows all the point totals for each team and then ranks them.

Skills

• When researching this I saw a number of examples but many had people inputting their own scores directly into a spreadsheet which helps a little but was not enough of a time saver for us. I saw others that wrote their script for Google Sheets. This is a bit beyond me at the moment so I decided to try and build my own (which I did) and here are the skills I used to build it.
• Google Forms
  • How to create a form that “branches.” This is very basic and took very little time to learn (15 minutes)
• Google Sheets Functions – Functions are commands that tell the sheet to do various tasks like add a column of numbers, look up other cells and report them back into another cell and so on.
  • Named ranges – This is very simple. You select a range of cells and then you can name that range. So when you want the data from cell A1 to cell D30, you can highlight all that data and name it. Then you no longer need to type out that range over and over again. This is especially helpful when working with multiple sheets (15–30 minutes)
  • Query – This pulls and filters data from the Responses worksheet onto a new worksheet. It is one function per sheet and updates automatically when a form is submitted. This is a little tricky but definitely learnable to someone with little spreadsheet experience (1–2 hours).
  • Vlookup – I learned Vlookup when teaching Excel. It basically looks for a reference number in one column and then reports back a value in a different cell. Confused – so was I. This takes a good a little while to learn if you’ve never done it before (1–1.5 hours)
  • Small and Large – These functions lets you “order” by smallest or the largest. It is very easy to learn (30 minutes).
  • Sum – Super duper simple. This will add the numbers up in a variety of cells automatically. It is usually one of the easiest and first functions most people learn.

Now take these times with a grain of salt. You may be pretty good with Sheets and these times may be 0 minutes for you. Also understand I am not an expert here. I had some basic knowledge, a goal and determination and I was able to learn how to create this. I am sure there are more efficient and better ways to do this and I look forward to learning those.

Part 2 of this post will be setting up the Google Form. It’ll be out tomorrow or Wednesday. So look out for that.

Source: IT Babble Blog and Podcast