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.

EIPS eSports Tournament 2015

image credit: D. Tomeldan

Last Friday (December 11, 2015) EIPS hosted it's first official eSports tournament at Salisbury Composite High School. There was a total of about 120 junior high and high school students in two parallel competitions, League of Legends 5v5 (team) and Super Smash Bros. Brawl Project M 1v1 (individual). Competitors represented Ardrossan Junior Senior High, Bev Facey Community High School, Clover Bar Junior High, F. R. Haythorne Junior High School, Fultonvale School, Salisbury Composite High School, Strathcona Christian Academy Secondary School, and Vegreville Composite High.

While competitors were charged $10 each to cover the cost of prizes and a donation to the Stollery Children's Hospital Foundation at the behest of Nitza's Pizza.

the prize table

Nitza's Pizza provided pizzas for lunch.

Microsoft provided Xbox One consoles for casual gaming.

Winners of the Project M competition (left to right): King (SAL) in first, Crow (SAL) in second, and HDPizzas (AJS) in third.

Unfortunately due to time constraints the LoL tournament bracket wasn't completed, but the judges consulted the statistics and decided to award the prizes to Team GHZ from SCS.

Team GHZ

setting up early in the morning

students checking in and reporting game outcomes

Project M competition screens

Project M on the south side

League on the north side

some LoL student shoutcasters

The day was live streamed via YouTube for wide angle and Project M, and Twitch for LoL. Unfortunately many comments on both streams were somewhat toxic, so commenting was disabled on YouTube and moderated by EIPS staff on Twitch. You can also view a flyover video of the experience.

Participants were expected to abide by a code of conduct, and we didn't have any behavior issues from competitors or spectators.

As organizers we had expected that parents and staff would be questioning the value of a video game tournament during a school. We were prepared to talk about how students are often pulled out of classes for traditional sports tournaments, that many of the benefits of sports are also evident in esports, and that these students are often under-served by traditional extracurricular activities. However we were surprised at how positive and encouraging everyone has been about this event.

We've been encouraged to start planning the next event. Hopefully there will be another grades 7 to 12 tournament in the spring, as well as an elementary tournament at some point soon. Games being considered are Rocket League, Mario Kart, Halo 5, and Hearthstone. Obviously the latter two are rated "Teen" and won't be used for elementary tournaments.

Bulk generation of custom short URLs for prefilled Google forms

One of the departments at work wanted to send out pre-filled Google Forms to parents based on a CSV file that they generated from their database.

Check out this Python script I wrote for them that generates a shortened URL for each line in the CSV file.

Feel free to use and share, and to ask for clarification where documentation or comments are lacking.

Just a reminder, of course, to consider FOIP or other privacy implications associated with any data being submitted via online forms or stored in the cloud.

Automated Drive Imaging for Xubuntu Install

Instead of sending old laptops to ewaste, we are experimenting with having students install Xubuntu on them. So we made a class set of installation DVDs and had a three classes (Grade 4, Grade 6, and Grade 9) install Xubuntu on some older Dell Latitude machines (D530, E6400, and E6410 respectively). That seems to be going well so far, but that's a topic for another post.

Where we needed to change tactics was with some Dell Latitude 2100 machines. Our set of install DVDs wouldn't work because they are 32 bit and don't have optical drives. We consider netboot install, but that seemed overly complicated. So a USB-based solution... the easy answer is to just install Xubuntu from USB Stick, but since the devices are mostly identical perhaps an imaging solution.

Enter Clonezilla.

There are two options here. One option is similar to netboot, but we chose instead to create a few USB drives that would boot the computer and automatically clone a previously-created image on to the hard drive.

So now all a student (or teacher) needs to do in order to set up a new machine (or "fix" a machine that a student has "adjusted") is to insert the USB drive, turn on the computer, press F12, and boot from the USB Storage Device.

For those interested in how the USB storage devices were constructed, we started from this post. For everyone else, check out this cool graph.

16-channel relay box controlled by a Raspberry Pi

It's not quite finished yet, but it's in a usable state. This isn't full instructions, but hopefully enough to get you started if you're interested. Please comment below if you'd like more information.

Some of the Parts:

8-Channel 5V Relay Board (two of these)

Raspberry Pi, SD card, associated cables

female to female jumper wire for connecting Raspberry Pi GPIO pins to relay board pins

thick wire for connecting relays to outlets

nine outlets

CNC or laser-cut front plate from this drawing

LightShow Pi software (haven't tried this yet)

An example Python script to flash through the relays one at a time:

import time

delayTime = 0.25

import RPi.GPIO as GPIO

GPIO.setmode(GPIO.BCM)

pins = [2, 3, 4, 17, 27, 22, 10, 9, 14, 15, 18, 23, 24, 25, 8, 7]

for pin in pins:

    GPIO.setup(pin, GPIO.OUT) 

    GPIO.output(pin, GPIO.HIGH)

    time.sleep(delayTime)

    GPIO.output(pin, GPIO.LOW)

    time.sleep(delayTime)

GPIO.cleanup

Digital Citizenship Resources

Cross-posted from our previous blog site.

Here are some digital citizenship resources that are available for staff use:

One of our favorite resources is Common Sense Media. It includes resources sorted by grade or by theme. They have videos and downloadable (PDF) lessons. There are also eight iBooks for use on iPads or other Apple devices.

Media Smarts is another good site for digital citizenship classroom resources and lesson plans. There are Alberta-specific resources for all grades, and a set of videos and lesson plans related to media literacy.

As well, check out the NHL's Future Goals Program.

See also DigitalCitizenship.net and the Alberta Education Digital Citizenship Policy Development Guide.


Of course the main thing is to have these discussions with staff and students.

Elementary Music and Notes in Sonic Pi

In this activity we are going to play some notes with Sonic Pi.

The song "Johnny Works With One Hammer" is a fairly simple tune, listen to someone singing it:



Did you hear how the sound goes higher and lower? Sonic Pi will play higher sounds when you tell it to play higher numbers, and lower sounds with lower numbers. Try pasting this into Sonic Pi and pushing play to see how it sounds:

# Johnny part one
use_bpm 200
play_pattern [53, 53, 53, 57, 60, 53, 53]

Each of those numbers is a different sound, what we would call a different note. You heard higher numbers as higher sounds, which are higher notes. The use_bpm part is just telling Sonic Pi how fast to play.
But let's get back to our song. We've seen that we can play high and low sounds by telling Sonic Pi to play different numbers.

The number pattern for the first part of "Johnny Works With One Hammer" would be:
53 53 53 57 60 53 53
55 60 60 57 53 53

I've done the first line of the song, try pasting it into Sonic Pi and then completing the second line of numbers.

# Johnny part one
use_bpm 200
play_pattern [53, 53, 53, 57, 60, 53, 53]
play_pattern []

Did that sound like the song we are trying to play? It was close, but some of the notes are supposed to be longer than others. So instead of play_pattern we can use play_pattern_timed, like this:

# Johnny part one and two, with timing
play_pattern_timed [53, 53, 53, 57, 60, 53, 53], [0.25, 0.25, 0.25, 0.25, 0.5, 0.25, 0.25]
play_pattern_timed [55, 60, 60, 57, 53, 53], [0.5, 0.25, 0.25, 0.5, 0.25, 0.25]

The second list of numbers on each line is the amount of time that Sonic Pi will play the note for. Notice that the note 60 will be played for a longer time (0. 5 seconds). In musical notation we say that the shorter notes are "quarter notes" because they take up a quarter of a bar. The longer notes here are half notes. See what the notes would look like for this part of the song.

A quarter note is sort of a filled-in circle, a half note looks kind of hollow. Now let's try to make our program easier for humans to read by storing the note patterns and timing patterns before we actually play them:

# Johnny part one and two, with timing
play_pattern_timed [53, 53, 53, 57, 60, 53, 53], [0.25, 0.25, 0.25, 0.25, 0.5, 0.25, 0.25]
play_pattern_timed [55, 60, 60, 57, 53, 53], [0.5, 0.25, 0.25, 0.5, 0.25, 0.25]

That's starting to look complicated, but hopefully you can see how it can be broken down into parts. There are four "arrays" there, which are basically lists of numbers. The first one is called notes1 and it contains the notes for the first line of the song. The array called timings1 is a list of how long to play each of those notes. Then we just say play_pattern_timed notes1, timings1 when we want to have Sonic Pi play that list of notes with those timings. Let's try it including variables as well. Will this play the same song?

# Johnny with variables and arrays
quarter = 0.25
half = 0.5
notes1 = [53, 53, 53, 57, 60, 53, 53]
timings1 = [0.25, 0.25, 0.25, 0.25, 0.5, 0.25, 0.25]
notes2 = [55, 60, 60, 57, 53, 53]
timings2 = [0.5, 0.25, 0.25, 0.5, 0.25, 0.25]
play_pattern_timed notes1, timings1
play_pattern_timed notes2, timings2

In this example, we said the time for the shorter notes (quarter notes) should be a quarter of a second (0.25) and the time for a longer note (half note) should be half of a second (0.5). We could also have said:

quarter = 1.0/4
half = 1.0/2

or even:

quarter = 1.0/4
half = quarter/2

In case you're curious, the reason it is 1.0/4 instead of 1/4 is that we need to tell Sonic Pi that we want the numbers to be type float instead of integer. If we said1/4 it would equal 0. Don't worry about this yet though.

Hopefully you are starting to see that there can be a lot of math in music. So far we have done the first half of the song "Johnny Works With One Hammer". If I show you the musical notation version of the whole song, do you think you could translate it into something that Sonic Pi could play? It's a little bit like figuring out a code, you need to figure out what numbers mean the same thing as the notes in the musical notation.

You can also try out different instruments by putting a line like one of these at the top of your code:

use_synth :pretty_bell
use_synth :fm

So now you can make music with Sonic Pi. Try some other songs, and tell your friends.