>>Gamification of Science & Leader Board

Find the Gamification of Science website here: http://mariana68.wix.com/adventure5th#!map/cdn7

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 andLeaderboarded, 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 https://goo.gl/

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("http://goo.gl/lnjj1i");IF (AND(B2<=849;B2>=750); image("http://goo.gl/vWDG5F");IF (AND(B2<=749;B2>=560);image("http://goo.gl/r8CoiU");IF(AND(B2<=559;B2>=420);image("http://goo.gl/MCq2UA");IF(AND(B2<=419; B2>=250);image("http://goo.gl/qQRxrZ");IF(AND(B2<=249; B2>=170);image("http://goo.gl/YtKEjl");IF(AND(B2<=168; B2>=85);image("http://goo.gl/XKdggM"); IF(B2<=84;image("http://goo.gl/JNWjSf") ; "")))))))) - 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("http://goo.gl/f8Gjbj"); 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.