Thinking about adding badges to your class? There are many ways to award students badges including using Class Badges. If you would like to organize your own badges here is a system you may want to try that uses Google Sheets.

Google Drive

Create a new spreadsheet in Google Drive (http://drive.google.com).

Create a list of badges. You may want to consider different categories for your badges. Let column A be a code to refer to the badges. You can make the codes logical according to a pattern or random depending on how you want to distribute the badges to students. My list has columns for “Code”, “Badge Title”, “Badge Description”, “Badge Link”, and “Image.”
Screen Shot 2014-11-30 at 1.31.07 AM

Create a Badge with Google Draw

Create a folder in your Google Drive to hold your badges. Create a new Google Drawing in the folder.

Resize the canvas by using the File menu and choosing “Page setup.”
File page set up google draw

You will want a custom canvas size and to change from inches to pixels. I recommend 100×100 pixels for your badges.
Custom

 

Google Draw Page Set Up

If you would like a round badge, hold down the shift key while drawing a circle on the canvas. Create text or images within the circle.
Click Here for a badge sample.

Publish to the Web

Using the File menu of the Google Drawing, choose to “Publish to the web.”Google Drawing Publish to the Web

 

Start publishing and copy the link.
Google Drawing Publish Link

Badge Image

Paste this link in your spreadsheet column under “Badge Link.” In the column that you labeled “Image” you will want to use a spreadsheet function.

=image(D2,3)

The cell to reference is the cell that contains the image link. The comma 3 indicates that the image should be displayed at it’s actual size. =image(D2,1) will resize the image to fit the cell height and width. =image(D2,2) will stretch the image to fill the cell. (You do not want this.) You will want to resize the row heights to accommodate the image sizes.

Click Here for a spreadsheet sample.

Student Badges

For the students to display their badges, first create a Google Sheets template for them to copy. In a new spreadsheet create a new sheet by clicking on the plus icon in the bottom left. Double click on the tab to name the tab “badge list”
2014-12-07_04-15-22

Importrange

The formula =importrange( will mirror the list of badges from your original spreadsheet. Copy the URL of your original spreadsheet that contains your badge list. In the student template on the “badge list” tab in cell A1 type the formula

=importrange(“URL”,”Sheet1!A:D”)

You want to replace the URL with the URL of your master badge list. It is important that you put the URL in quotation marks. I did not include the column with the image of the badge since I do not want the badge images to show up for the students until they earn the badge.
Importrange google sheets alice keeler

Hide the Tab

The purpose of the =importrange( is to allow you to write formulas in the student template that references the badge list. Because you used =importrange, any updates you make to your master badge list will be reflected on all of the student copies.

On the “badge list” of the student template click on the arrow on the tab and choose to “Hide sheet.”
Note: Wait to do this until you are done writing your formulas. You will want to reference this tab later.
2014-12-07_04-26-12

List of Codes

Students will want to list the codes of the badges they earn on Sheet1. Type the words “Badge Codes” in cell A1. Students will then type the code of the badge they earn in column A.

DELETE columns B to Z by highlighting all 25 columns and right clicking on the column indicator to choose “Delete columns B-Z.”
2014-12-07_04-30-31

Badge Display

Click on the plus icon again in the bottom left to create a new tab. Double click on the tab to name it “display badges.”

Click on the “awesome box,” which you will find in the upper left hand corner of the grid. This is the box to the left of the column indicator A and above the row indicator 1. Clicking on the “awesome box” selects the entire spreadsheet. Resize the rows and columns to make squares that will be the size of the badges you want to display.

What we want is for the cell to be blank if no badge has been earned or to display the badge if one has been earned. In cell A1 type a formula the formula

=if(Sheet1!A2=””,””,image(vlookup(Sheet1!A2,’badge list’!$A$2:$D,4,false),3))

What this says is if there is no code in cell A2 on the first sheet to do nothing. Otherwise insert an image by looking up the code in cell A2 of Sheet1. The range to compare this code to is located on the badge list tab in the range of A2 to D. (Use absolute cell referencing by placing dollar signs in front of the A, 2 and D to lock down the location of the table.) Since the badge list is not in alphabetical order you will want to use comma false as part of the vlookup formula. Use comma 3 for the image formula to indicate to show the badge at full size.

Copy and paste

Copy and paste this formula into the cells on the “display badges” tab. Consider that you want the badges to appear in a grid and not necessarily in a list in the A column. Delete extra columns. I chose to display only 6 columns, thus deleting columns G to Z.

Edit the formula in each cell to check the code in A3, A4, A5, etc…

Students Make a Copy

Click on the blue share button in the upper right hand corner and change the sharing rights of the student template to anyone with the link can view. Use Google Classroom to push out a copy of the student template to each student or share the link to the student template and have students use the File menu to make a copy.

As students earn badges provide them the badge code for them to type it into column A on Sheet1. Students can then see the grid of their badges displayed on the “display badges” tab.

Embed Badges

If your students have a digital portfolio and would like to embed their badges on their student website they can publish their own spreadsheets.

Students will go to the File menu and choose “Publish to the web…”
File Publish to the Web

Instead of publishing the entire document, students will choose from the drop down list the tab “display badges.”
Embed google sheets display badges alice keeler

Students will then want to click the blue “Publish” button under the “Embed” option.
Embed and publish

Student can then copy and paste the provided embed code into their websites to display the badges they have earned. The embed code will allow the displayed badges on the student website to be continuously updated. As the student earns more badges the new badges will automatically display where the badges were embedded. Note that there can be a lag for displaying the badge since the published view of the spreadsheet is not in real time. The student may need to wait approximately 5 minutes and to refresh their page to see the current grid of badges.

 Click Here for a sample student template.



style=”display:inline-block;width:728px;height:90px”
data-ad-client=”ca-pub-4474023805431647″
data-ad-slot=”5223744217″>