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.
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.”
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.
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
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.
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.
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”
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
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.
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.
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.
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
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.
If your students have a digital portfolio and would like to embed their badges on their student website they can publish their own spreadsheets.
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.