“Gamification.” We recognize the benefits (even through all the hype) and we would like to add it to our courses. But where to begin? It is not enough just to add a game to your course. We need a way to make it social. After all, bragging rights go a long way towards reinforcing learning.
I think one of the easiest ways to tip your toe into eLearning gamification is to add a Leaderboard to your course. Leaderboards remind learners that they are not alone, others are completing the same training. They also create some friendly competition and let learners see how they rank among their peers. And often, if they rank low, they will go back and try again on all their own.
But how to add a Leaderboard? And how to do it for free? Google Sheets and a little code provide the solution. I know a lot of you use Google Docs every day. You may not realize that Google allows users to develop add-ons and applications on top of Docs using Google Apps Script (GAS). GAS looks a lot like JavaScript and it hooks into Google Docs to enable you to create all sorts of new tools. I have used it in the past to create printable certificates, detailed course interaction reports, and even a sort of scaled down LMS.
I decided to use Google Sheets and GAS to create a sort of database and API for a Leaderboard. With GAS I was able to turn a spreadsheet into a web application that will receive data from a course and send back a list of the top ten users. I added some JavaScript to my course to handle the sending and receiving from that end. I packaged it all up for you and wrote some directions, which you’ll find below. This week I am adding a Leaderboard to Storyline 2. (See the live demo.) If you are familiar with JavaScript and feel adventurous you can adapt this to work for Captivate, Lectora, HTML5, etc. And soon I will post code and videos for those tools as well.
The files linked in the resources section of this post have the GAS code and JavaScript in them. Using the instructions below (and watching the videos) you will be able to learn how to create a leaderboard for eLearning with Google:
- Create a Google Sheet
- Add GAS to the Sheet
- Setup the Sheet
- Deploy the Sheet as a Web App
- Add the required variables to your Storyline 2 course
- Trigger the JavaScript to send and receive the data
- Display the Leaderboard to learners
Instructions for Spreadsheet
Create new empty spreadsheet and save it with whatever name you like.
We will be adding some Google Apps Script (GAS) and deploying it as a Web App.
- Click: Tools>Script Editor
- Delete the default code
- Paste in new code from Leaderboard GAS file
- Save (Enter a project name)
- Click: Run > setup
- An Authorization Dialog will appear. Click Continue
- Click Allow (It is safe because it is your app. You can see/edit all the code)
- Click: Publish > Deploy as web app
- Set Execute app as to ‘me’
- Set access to ‘anyone, even anonymously
- Click Deploy
- Copy and save the URL; you will need it for the JavaScript
- Click OK
Instructions for Storyline 2
First update the JavaScript provided with the URL you copied when you deployed the code above as a web app. It is easier to edit the JavaScript in an editor before you paste it into Storyline. I recommend Sublime Text 3.
- Open the JavaScript file.
- Replace the YOUR_URL_HERE text with the URL you saved earlier.
- Save the JS file.
The JavaScript we’ll use needs to access some variables in the Storyline course. We will create them using the names I used in the sample. If you already have one or more of these with some other name see the Advanced Variables section of these instructions.
In Storyline create three variables:
- userScore (number)
- userName (text)
- topTen (text)
In your course you will need to set the values of userScore and userName. Probably let the user enter their own name. And get the score from a game or interaction.
Display the topTen variable wherever you want the leaderboard to appear.
Once you have set the user’s score and name, use a JavaScript trigger to send the data to the spreadsheet and retrieve the top ten list. This trigger could be on a button, when a slide starts, etc. Create your trigger and paste the JavaScript included with the download into Storyline (make sure you update the URL)
Publish your course and test it from a web server or LMS.
Advanced Variables:
If you already have a variable for one of the three items above you can update the JavaScript to use it (rather than my names). Lines three through five of the JavaScript file are used to set the names of the variables.
Resources
JavaScript for Storyline Trigger
Let us know what you think of the leaderboard in the comments below! And while you’re at it, try out this live example and see how you rank!
Update 2/11/16: The instructions for Adobe Captivate and Lectora can now be found in this blog here:
How to Create a Leaderboard for eLearning with Google (Part 2)
James Kingsley has worked in the eLearning Industry for over 15 years. He has won several awards for combining technologies to produce better eLearning. He is an Articulate MVP. James is the Senior Technology Architect for eLearning Brothers and the Co-Founder of ReviewMyElearning.com.
This looks great. How do I use it with lectora?
Thank you for this – it’s great to see some of the more in-depth functionality that’s available.
If I can offer a bit of feedback, what I’d like to see with an article like this is what the end result looks like (e.g. an example of the leader board) right at the start. Unfortunately I had no concept of what we’d end up with until the last minute of the second video. That’s a lot of detail to scan through to find out if the result is something I want to replicate.
Cheers, Michael.
Good feedback, Michael. It was my choice to have James stick the live example at the very end of the text. (You didn’t even have to watch one of the videos to see it in action, but that’s not really clear until you’ve read everything.) I’ll go ahead and stick a link towards the top of the article as well for people who are looking to see a demo beforehand.
Hi Melissa!
James is working on getting code and instructional videos set up for other authoring tools as well. Keep an eye out for it soon!
Thank you Melissa, I will have examples for Lectora and Captivate soon. The only difference between them is how you access the tools variables. For Lectora it will be something like VaruserName.getValue() and VartopTen.set(topTenMsg)
Michael, good point. Thanks Thomas for moving it.
It would be great if you could add a additional piece in the article to highlight what you’d need to change do something similar in Adobe Captivate.
Hi AM!
James is working on putting together some instructions for how to do this in Adobe Captivate. Hopefully, we can share those with you soon!
Thanks for putting this together James. I have recently been attempting a similar thing with Adobe Captivate, however I’m struggling with the coding (not my specialty.) Can I add to the call for a Captivate how to…
Thanks for your efforts and I look forward to the next one
Wow! This is amazing! Can’t wait to see your post with code and instructions for creating this Leaderboard using Captivate!
Have you done posts about how you used Google Docs and GAS in the past to create printable certificates and detailed course interaction reports? I’d love to learn how!
This is a great example of integrating Google Docs into Storyline 2. As an eLearning developer, I can see a number of ways to use this example.
I’d love to see more examples of using Google Docs and Storyline 2.
@All: Captivate and Lectora examples will be posted soon!
@Alicja and @Jim: I don’t have any other posts on the subject (yet). I have used GAS for a few solutions in the past. Even as a sort of LMS and as an interaction tracker. The interaction solution also used GAS to send an email to the user’s supervisor with the results!
This is excellent, especially after two days at the UK Learning Technologies show where all the buzz was about gamification!
I will try this out on Monday.
One question sprung to mind: is there any way to format the leader board results so they line up in columns and control the spacing and font etc?
Thanks for detailed write up and videos – excellent job James!
@Adrian, I’m glad you found it useful!
You could indeed format the results however you want. I didn’t want to over complicate the example so I pushed all the results into one course variable. That approach doesn’t allow a lot of formatting. A better way to do it might be to have 10 variables (one for each leader).
If you are experienced with JavaScript you can make some changes to the script (lines 30-41) to tweak how those results look.
Enjoy!
As promised; the Captivate and Lectora solutions are posted here: http://elearningbrothers.com/how-to-create-a-leaderboard-elearning-google-part-2/
This is amazing! I got it to work (I’m using Storyline) but the text in the topTen variable text box is displaying really small – so small it’s unreadable. I’ve tried increasing the font size, changing it to something like Arial, changing the text box so it doesn’t autofit but no luck. Just wondered if you had any ideas? Thanks for a great tutorial 🙂
@Susan It sounds like maybe the text box is too small? Unfortunately Storyline does not scale the box to fit variables (even if you set the box to auto-size). So you end up making the box pretty big to accommodate potentially large blocks of text.
I followed all the steps exactly but couldn’t send and receive data to / from storyline -> google spreadsheet. i downloaded your files and just edited the js trigger in story file and updated it with my own spreadsheet’s web app url. neither the storyline file nor spreadsheet is getting updated.
i created three columns in spreadsheet as : ID, score , Timestamp
copy + pasted the GAS code in the script editor and deployed it as a web app setting it to everyone, anonymous. then i just edited the js trigger in story file with the new web app url and uploaded it to a web server. but it’s not working. i tested it on different browsers and computers.
can you please point out the error ? i really want to set up the leaderboard for my course.
thank you.
I have implemented in my LMS and it’s working absolutely fine. Thanks for the great tutorial.
Feature request:
However, i found out that it would have been great if i could have added a refresh / reload button on leaderboard slide which will automatically scan through all the user scores and update it in realtime while the window is still open. At the moment it does not reflect if some other user have taken the same course at same time. i have to restart the course to see the update.
Issue:
I am facing an issue where it’s adding duplicate name of same user. Means if i retake the quiz as same user, it will add my name 2nd time and two users of same name are being showed in top 10 list. i want to remove the name automatically from the 1st attempt so that only one name will be showed in list. Can it be done through some coding in the spreadsheet and / or storyline ?
Thanks.
Thanks for the great instructions. I noticed that the date on the leaderboard shows as DD/MM/YYYY. Is there any way to change that to MM/DD/YYYY?
Hey Marisa!
If you look into line 39 of the code under the JavaScript for Storyline resource, that’s where you’ll find that option. It starts by looking like:
var recordDateString = recordDate.getDate() + "/" + (recordDate.getMonth() + 1) + "/" + recordDate.getFullYear() + " " + recordDate.getHours() + ":" + recordDate.getMinutes();You would just need to swap it to:
var recordDateString = (recordDate.getMonth() + 1) + "/" + recordDate.getDate() + "/" + recordDate.getFullYear() + " " + recordDate.getHours() + ":" + recordDate.getMinutes();As a point of interest, the reason why there’s a “+ 1” for the Month is that when it retrieves the number for what month it is, January starts at 0 and not 1. Anyway, hope that helps!
I have implemented in my LMS and it’s working absolutely fine. Thanks for the great tutorial.
However could you help me with the following two things?
1.) Feature request:
However, i found out that it would have been great if i could have added a refresh / reload button on leaderboard slide which will automatically scan through all the user scores and update the rank in realtime while the window is still open. At the moment it does not reflect if some other user have taken the same course at same time or may be few secs after me. i have to restart the course to see the update.
2.) Issue:
I am facing an issue where it’s adding duplicate name of same user. Means if i retake the quiz as same user, it will add my name 2nd time and two users of same name are being showed in top 10 list. i want to remove the name automatically from the 1st attempt so that only one name (the current attempt) will be showed in list. Can it be done through some coding in the spreadsheet and / or storyline ?
I shall be grateful if someone helps me with this.
Thanks.
Awesome example. I’m really not a coder and to have this is very useful.
I would just like to know two things though:
1. How do I get a final score to be submitted? (the user would thus not input the score but the system would get it from the SL- results slide)
2. Is there a way to track the progress of the user? (If I had “10 stages” would it be possible to track the stages that the user is progressing through?)
Thanks again
Wonderful tutorial – thanks so much for posting!
I’m having the same issue as Susan – the displayed text for the leaderboard is very tiny. I’ve tried everything I can think of in the storyline file (increased the size of the text where the variable is, made the text box itself very large). The text that is displaying in my published file on the leaderboard looks like about size 10 font – much too small (and looks silly on the huge text box I placed for it). I am not savvy when it comes to javascript – but is there anything I can add to the code to control font size?
Another question – does this only work in the Google Chrome browser? I can’t seem to get it to work in Safari, but it works fine in Chrome. Also, other people have tried on their computers, and again it only seems to work in Chrome.
@Abhishek Sorry it’s hard to troubleshoot this type of thing in the abstract. Could you provide a link to the course?
@Gautam This example provides the basic functionality. To prevent duplicates you would need to add some logic to the gapps script. It would need to check for that user name before creating it. Then you could either update the score or block them, etc.
@Alison 1. Storyline doesn’t give JavaScript access to Quiz Results variables. But you could create a custom variable and then set its value to the value of the quiz score. You would do that with a trigger.
2. I have used this technique to track all kinds of data. You could indeed track progress. This example shows the overall technique but much of the logic would be different for something like progress.
@Wanda Nothing in the JavaScript is controlling the font size so it has to be something in the course.
Safari Bug: It looks like Safari isn’t handling the callback from Google properly. Normally for these types of projects I use a JavaScript library to handle the AJAX calls (like jQuery). It takes care of a bunch of basic stuff and cross browser issues.
Hi James,
Could you indicate us what line of “leaderboard_GAS.gs” we need to change to add/track a new data in Googlespread (for example email).
I guess something like 23, 27 with the new data (email) + 44 (i’m not sure), 141 ?
And after we need to add a new ” var emailVarName = ’email’;” in the begining of leaderboardStoryline.js ?
Thank you to share this tutorial.
++
To Wanda and anyone else having problems with small unreadable text in the topTen box. I fixed it by choosing the little arrow button next to ‘Drawing’ from the Home menu – then selecting ‘Text Box’ and choosing ‘Do not autofit’. This seems to solve the problem.
Loving using the leaderboards. My only problem now is getting it to work in Safari but I see from reading the comments that this is a bug in Safari. If anyone finds a fix for this I’d love to hear about it. Thanks.
hi i followed these instructions. But finally i got the following warning . So any one tell what can i do for that warning resolve?
Warning
“The custom java script feature on this slide has been disabled whilee viewing locally because your browser does not support it.”
Hey Suresh!
This means that you’re going to have to host your published course on a hosting service or local server like WAMP or MAMP to see the Javascript in action. This is usually the case with most Javascript, as browsers don’t tend to like locally-run code like that. (They think it could be malicious, so they just don’t let it happen.) That should solve your problem!
Hi, I love the leaderboard. I’m wondering why the date displays a day later. Example. Today is 10/22/2016 but the result is when: 10/23/2016.
Do you know how I can change this in the script? And do I change the .gs or the js?
Thanks!
Joni
Hi Joni!
The code for this particular aspect of the leaderboard can be found in line 39 of the Javascript file. That would be the first place to start!
Hi
This tutorial is great and is the only example I can find of sending data to Storyline. I simply want to pass variables from a Google Sheet to a variable in Storyline. Can that be done using part of your example?
I would like to have spread sheet columns called Char1, Char2, Char3, etc with one row underneath. I enter a single letter into a cell and that becomes the variable for Char1.
I know that’s a big ask, but this thread is my only hope!
Thanks
Gavin
@Mathias You are on the right track. But you would need to add it in a few places. Another option might be to change the current username variable to use the user’s ID from the LMS. That is almost always their email address.
@Gavin Altering this particular example to that gets kind of complicated. I have a new post planned with for a solution that saves any content to the spreadsheet. I am not sure when I will get that one finished.
Is there a way to do this with Microsoft Excel? Our school district is an Office 365 district and this would be great to do.
Hi Mike!
Unfortuantely, the power of doing this lies in the fact that Google Sheets are in the cloud and can be updated and pulled from using some of the code as outlined in the blog. Excel (as far as I’m aware of) doesn’t quite allow for the same thing. The Google Apps Script is a very important part of getting this functioning!
James-
Are you considering offering a tutorial on creating a leaderboard that would work with an HTML5-created eLearning website?
Thanks,
Jim
Hi brothers,
I am trying to figure how to edit 3 columns in my spreadsheet via the web app.
I know I can do this:
function doGet(){
var runner1 = “test2”
var runner2 = “test2”
var runner3 = “test2”
var runner4 = “test2”
var worksheet =SpreadsheetApp.openById(“My sheets id”)
var sheet = SpreadsheetApp.getActiveSheet();
worksheet.deleteRows(1, 1)
worksheet.appendRow([runner1, runner2,runner3,runner4]);
var sheet = SpreadsheetApp.getActiveSheet();
var Red = sheet.getRange(“A1”).getValue();
}
This works but my knowledge stops here.
I want a set up for training sessions where a red, a blue, a green, and a yellow, team.
The teams will answer a Q&A and as they submit the sheet is updating their column
This in turn will change the position of a runner in another html file being shown on a screen for all to view.
Can you point me in the right direction re the Javascript – I can figure the player.Set stuff myself. I am just lost in fog with how the app gets sent the data and how the Javascript pulls the data.
Kind regard
Luke
James, you are an eLearning God and a personal inspiration! The improvement in engagement I’ve seen when using this is huge. Thank you so much for sharing this!
I thought I’d share something that happened to me when publishing the web-app incase anyone else encounters the same thing.
Weirdly, a few times I’ve tried this method recently, data wouldn’t send or receive and instead, my projects would just show the “Loading…” message. On these same projects, I noticed that the ‘/exec’ address generated by Google sheets contained ‘macros/s/u/0/’. I put the address in my browser, and found out it was incorrect. I figured out that when there’s a ‘u/0/’ in the address, the app wouldn’t work. The correctly working address always seems to just contain ‘macros/s’ (no ‘u/0’ in sight). I now delete the ‘u/0/’ section in the addresses generated and the projects and respective apps work perfectly.
It took me a while, but hopefully it saves someone some time!
Thank you so much to Tom Fowler above. I’ve been having problems getting the leaderboard to work recently and your method has fixed it! So pleased it works 🙂
Will this work for 360?
Hi James,
This is great and is exactly what we are looking for. I’m stuck in the very beginning of your instructions, at pasting in the new code from the leaderboard GAS file. I copied/pasted and when I try to save the file I get this message, for this particular line of code:
Missing ; before statement (line 18, file “Code”)
17 // Create a new property service to maintain variables accross instances.
18 var SCRIPT_PROP = PropertiesService.getScriptProperties();
Any help would be greatly appreciated.
Thanks!
Jaime
Dear every one:
thanks for helping.
I just set up google spreadsheet and GS code, Captivate JS code following :
http://elearningbrothers.com/how-to-create-a-leaderboard-elearning-google/
http://elearningbrothers.com/how-to-create-a-leaderboard-elearning-google-part-2/
This worked. But after I removed the GS code from the spreadsheet, the Spreadsheet still run original code. (I wanted to modify the code for my use, however, the spreadsheet always runs the original code. Then I removed the whole code and see what is happening.)
Thanks for helping.
Love this! It is so great – I am curious about adding an additional column to the leaderboard from a SL variable and see that a couple others have asked for this between Parts 1 and 2 of your tutorial as well. Can you create a tutorial for this?
Many thanks for this!
Hey again,
I have another question but also wanted to comment again to note that I found a workaround for my question. To add a “new column” to my leaderboard, I just concatenated two variables using a separate javascript execution.
My new question (thanks again for providing this awesome resource) is – do you know of a way to refresh the leaderboard without duplicating the submitted data (Same question as Gautam above)? I tried executing the javascript every 10 seconds in my storyline file, but each execution means submitting the same record over and over.
Thanks again in advance,
Morgan