Leaderboards with Google Apps, an update

A while back I talked about using Google Spreadsheets to create a leader board. The main drawback I saw in my original version is that students (and me) were unable to see clearly who was in the lead unless I sorted it, which at times created errors in some of the cells. I continued to tinker with it and discovered the pivot table function. This was the answer!

 Here is a video tutorial that shows just how to create the self-ranking leader board.

The formulas that I used to create this are -

Importing ranges of cells:

=arrayformula(index(Leaderboard!A1:A38))

Conditional formula for images:

=IF(C2>=15401, image("https://goo.gl/3Pqxfr"),IF (AND(C2<=15400,C2>=13601), image("https://goo.gl/thtuwd"), IF (AND(C2<=13600,C2>=11901), image("https://goo.gl/2yWS3J"),IF(AND(C2<=11900,C2>=10301),image("https://goo.gl/I76A2i"),IF(AND(C2<=10300,C2>=8801),image("https://goo.gl/ahUpyM"),IF(AND(C2<=8800,C2>=7501),image("https://goo.gl/XyVodq"),IF(AND(C2<=7500, C2>=6301),image("https://goo.gl/ORNbNs"),IF(AND(C2<=6300, C2>=5201),image("https://goo.gl/2VYg9Q"),IF(AND(C2<=5200, C2>=2501),image("https://goo.gl/87vY96"), IF(C2<=2500,image("https://goo.gl/aVgK1f")))))))))))

Modified embed code - I highlighted the part you add:

<iframe src="https://docs.google.com/spreadsheets/d/1YeCoYjAp_n6d7q9UkmV3-WJ24OtMsHfLR43ryLmy6vA/pubhtml?gid=990555132&amp;single=true&amp;widget=true&amp;headers=false&amp;gid=0&amp;range=A1:D39" width="450" height="2350" ></iframe>

Of course, you can always make a copy of the document I used in the tutorial and modify it to suit your needs.

I invite you to play around with it. For example, you can add avatars or other images using

 =image("https://goo.gl/Bv19zc")

You will again need to host the images in a published document, shorten the URLs, and remember to modify the range, width and height if you are going to embed it anywhere. In this document, I show the avatars in the pivot table, and just like before they are ranked by XP.

I have not found a way to also include items or badges in the pivot page. When I try it, the totals are pushed to the very end, creating a rather messy look. However, you could always publish them in the leader board page, which keeps them in alpha order and could actually be an even better idea.


If you've found different ways of doing this or need some help, leave a comment. Don't be shy. We are in this journey together.