Creating a Leaderboard using Google Drive

(reposted from blog.eipstech.com)

If you're trying gamification and you'd like to have a live leaderboard for a class or an event, the easiest (free) way I've found to do it with a spreadsheet in Google Drive.

Basically this involves creating a form that participants (or facilitators) submit to claim points and setting up a pivot table in the spreadsheet for adding up the points. The pivot table can be made public, embedded in a web page, and/or put up on a screen as a leaderboard.

The first step is to create a form in your Google Drive. The first question should be the participant's name/number, either as a text question (where they have to spell it the same way each time) or Choose from a list if you don't have too many participants. The other question(s) should be Multiple choice, Choose from a list, or maybe Check boxes for claiming which "challenges" they have completed. The easiest way is to have just a single multiple choice question.

What follows are a couple of examples.

Once your form is set up, you'll need to set up the spreadsheet to add up the points as participants click submit. Submit the form once yourself to see what the output will look like, then in the columns to the right you will need to input formulas for calculating points. In the example below the formula in cell D2 is =if(C2="Beginner", 1, 0) and the formula in F2 is =if(C2="Intermediate", 5, 0) . Cell G1 is =sum(D2:F2) of course.

Once you have formulas for the all of the necessary columns in row 2 (the row containing the first submitted form data), you'll need to create a script that copies those formulas to each new row as the form is submitted. I've written about this before. In our example, we'd open the script editor and create a new script like this:

function addFormula() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var startRow = 2;

  var startColumn = 4;

  var numberRows = 1;

  var numberColumns = 4;

  var lastRow = sheet.getLastRow();

  var sourceRange = sheet.getRange(startRow, startColumn, numberRows, numberColumns);

  var destinationRange = sheet.getRange(lastRow, startColumn, numberRows, numberColumns);

  sourceRange.copyTo(destinationRange);

};

Then set the Current project's triggers to be addFormula when the spreadsheet event On form submit occurs. Your spreadsheet should now calculate points for each row, each row will be data from a form submission.

The last step is to create a Pivot table report... in your spreadsheet (under the Data menu). The range in our example is 'Form Responses'!B:G since (from the Form Responses sheet) we want participant names (column B) as well as the calculated values (columns D to G). In the Report Editor you should add the name field under Rows, and the totals under Values, as in this example.

If you're finding that it's not updating, you may need to click "Edit range..." and set the number on the right to be 1000 or more (e.g. 'Form responses 1'!A1:G5000).

You can now Share and/or Publish to the web... your pivot table, since it's now a leaderboard that will automatically update as participants submit the form. You'll also want to delete any unused rows in the Form Responses sheet so that you don't have an extra blank line in your leaderboard.

Let me know if that works for you, of if there's anything I should clarify.