Saturday, June 21, 2014

Leaderboards with Google Spreadsheets :)

I have spent the last week gamifying my 5th grade. I developed my theme, posted everything up and so forth. Then I came upon the "Hall of Honor", which is basically the leader board for my game. I had previously created a leaderboard for my blogs (see Gamification, starting really small), but this time, I wanted the sheet to automatically assign all badges (images) in response to inputting the XP scores.

I knew that I could just use something like Edmodo to keep track, but that meant that I would have to create a bunch of small groups and assign the badges manually. As much as I love Edmodo, this seemed like a hassle, and I could just see myself forgetting or clicking one student instead of another. I turned to my trusty internet in search of something already made, and although there were several sites that came up (Badgeville and Leaderboarded, for example), they did not meet my most basic need - "FREE".

So, with a very rudimentary understanding of Google Spreadsheets and the help of Youtube tutorials, I developed my own.

What the students see:

What the students don't see, and might be of interest to you:

The spreadsheet has one dedicated page for the leader board and then 9 others for the different units.
The data is pulled from the different sheets using the ImportRange function
All image URL's were shortened using
The images were placed inside the cells using =image("URL")
The ranks were assigned using formulas like :

=IF(B2>=300; "Supreme Grand Master";IF (AND(B2<=349;B2>=201); "Blogging Grand Master";IF (AND(B2<=200;B2>=161);"Master";IF(AND(B2<=160;B2>=121);"Magus";IF(AND(B2<=120; B2>=81);"Adept";IF(AND(B2<=80; B2>=61);"Journeyman";IF(AND(B2<=60; B2>=41);"Initiate";IF(AND(B2<=40; B2>=21);"Apprentice"; IF(B2<=20; "Novice" ; ""))))))))) - For text
 =IF(B2>=850; image("");IF (AND(B2<=849;B2>=750); image("");IF (AND(B2<=749;B2>=560);image("");IF(AND(B2<=559;B2>=420);image("");IF(AND(B2<=419; B2>=250);image("");IF(AND(B2<=249; B2>=170);image("");IF(AND(B2<=168; B2>=85);image(""); IF(B2<=84;image("") ; "")))))))) - For images
I could not figure out how to use just one formula for text and images AND that responded to the value of an imported range, all at the same time, so I solved the problem by populating an extra set of columns using import range (not shown to students) just with the values and then used =IF(M2>=100;image(""); IF(M2<=99;"Locked";)) to display either the image or the word locked on the Unit cells. This made the look quite cluttered, so I went with white text on those cells.

My end result, a sheet that responds to and assigns badges automatically as soon as I update the individual values for a student. Since all is tied together, I can also sort the columns any which way I need without worrying about "messing up" someone's badges or XP points. Just be aware that changes are not instantaneous; it sometimes takes a little while to load and update everything.

If you would like to view the full spreadsheet, and perhaps make a copy to modify to fit your needs, click on the image.

I will still need to input the individual XP for the assignments myself, but I have saved myself from the tallying and badge awarding. I also think that if I'm careful about naming conventions with the students, I might just be able to automate the input of XP directly from Flubaroo graded assignments, or from Google Form assignments. Now wouldn't that be a dream?

If you find this useful, or if you have found other ways to do this, leave a comment. I know that I will be grateful.


  1. Wow! This is great!! I think I will make a copy (and bookmark this page!) in case I have time this summer to look into this further!!!

    1. Awesome. If you decide to do it and run into snags, I'll be glad to help.

  2. This looks awesome! What gaming profile do you use?

  3. Love this! Love the site you created! I have been teaching gamified sixth grade science for two years and have experimented with a variety of tools -3D Game Lab, MOODLE, Schoology, Googledocs- as a platform for my game, but haven't been really happy with any combo so far. This summer, my plan is to move to a website/Google Apps format. Stumbling across your Wix, Edmodo, Googledocs and really cool spreadsheet
    with images is quite inspiring. Best of luck. Please keep blogging. I'd love to read more!

  4. I love this! I'm using it with a book club for adults this summer to encourage participation. One problem I've had is that when I insert my leaderboard spreadsheet into my Google Site, the images don't show up. Have you ever had problems with that when you embed the spreadsheet into another site?

    1. I have not had any trouble with the images appearing. Mine is embedded on a WIX free site, and it does take a while to load up, but eventually it does. Could it be the URLs of the images you are using?

  5. Thank you for this... This is GENIUS! will this work on ipad?