OPERATION: Tech Bingo, Part 2 (Badges)

I wanted a way to disseminate badges that was more hands-off since we have so many teachers in the district. Printing, cutting, and awarding paper badges just would not be feasible. The solution was Badgr. It was easy to add to Canvas and badges are awarded based on module completion. This fit perfectly as each mission was housed in a module.

Badgr leaderboard

Badgr assigns each student in the course an alias. If students prefer their real name appear, they can check a box to make that happen.

Since the course was designed so that students would work towards a bingo, I wanted a way for teachers to keep track of their badges on a digital bingo board. I searched and found the basic idea on Alice Keeler’s website. However, I wanted the badges to appear in the square they were supposed to and not just in a sequential order.

The instructions below are for a similar kind of sheet. This is one I created for a Tech Adventure (more about that coming in later posts). The participants are awarded a piece of a treasure map that appears in the spreadsheet in a particular place in order to create the map.

How to Set Up a Badge Tracker System

Step 1: Create the spreadsheet.

  • Start with creating a folder that will house all of your spreadsheets, forms, badges, etc.
  • Create a new Google Sheet in that folder that will be the Master Badge List.
  • Name five columns as noted below:

Step 2: Get a badge link.

  • Create a folder in your main folder called Badges.
  • Open the folder and create a new Drawing.
  • Name the Badge (Badge A1, for example).
  • Change the page settings of the drawing to 100×100 pixels.
  • Paste your badge into the Google Drawing if you did not create it there.
  • Publish to the web and copy the link.

Step 3: Add badge information to the Master Badge Spreadsheet.

  • Add a Badge Code. (Just make up a five character code such as M9823.)
  • Add a Badge Title.
  • Add a Badge Description.
  • Paste the published link from your drawing.
  • In the “Image” column, click on the cell and paste the following formula:

=image(D2,3)

(D2 is the cell with the published link. 3 is a particular size.)

When you paste the formula, the image should appear in the cell. If you do not see your image, resize the row and column so the image will fit.

Repeat Step 2 and 3 until you have all of your badges in Google Drawings and have pasted the badge links in the Master Badge Spreadsheet. Copy the “=Image (D2,3)” formula in each successive cell in the Image column. E3 should have the formula “=Image (D3,3)”, etc.

Before you leave this spreadsheet, get a shared link for the spreadsheet that is public to all and copy it. Paste it somewhere accessible. You will need it in the next step.

Step 4: Create a Google Sheet for your participants (The Badge Tracker).

  • Create a new spreadsheet.
  • Name two sheets as noted below.

(The Badge List sheet will be eventually hidden from participants.)

  • Open the Badge List sheet and use the following formula to paste Cell A1:

=importrange(“https://docs.google.com/spreadsheets/d/1Vyc_YBU4qFen3wKYYEL8vAriXxwGc-mdvV_QS8xMzdo/edit?usp=sharing“,”Sheet1!A1:D”)

The URL that is in bold will be the shared link (public to all) from the Master Badge List that you copied. Replace the underlined URL with your copied link, copy the entire formula, and paste it in Cell A1. You may have to link the two spreadsheets if you see a #REF error. Just hover and it will give you an option to link.

  • On the Map sheet, add a header, a place to enter badge codes, and a place for your badges to show. Then, determine where you want the badges or map pieces to show up.
  1. Header
  2. Locked cells
  3. Empty cells for badge codes clearly outlined as to where to put code.
  4. Merged cells so badge appears larger.
  5. Adventure 1 piece to show here.
  6. Adventure 8 piece to show here.
  • If you want the Adventure 1 badge piece to show in the top-left area of the 4×3 square, then type this code in that square:

       =if(isblank(B6),” “,image(vlookup(B6,’Badge List’!$A$1:$D$62,4,false),3))

(This formula is looking in Cell B6 for the code entered and comparing it to the Badge List sheet to find the badge associated with that code.)

  • Paste and change the formula in each large cell so the appropriate badge shows up in the right place. You will be changing the cell reference that is in orange.
  • Hide the Badge List sheet by right-clicking on the Badge List name and choosing “Hide sheet.”

Create a Google form for participants to request a Badge Tracker sheet. You can either make a copy of the sheet for each participant or, if you are a script writer, add a script to the Responses spreadsheet so that a sheet is automatically sent via email upon request.