Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Creating Badges with Google Sheets

Creating Badges with Google Sheets

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.

YouTube video

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.

YouTube video

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″>

46 thoughts on “Creating Badges with Google Sheets

  1. I’m having some trouble with getting this to work, in particular when you say “Deleted B-Z.” That doesn’t seem to make sense, since the formulas are referencing stuff in these cells. So up to that point, it’s working great, but you lose me at the Badge Display section.

  2. This is awesome. If you could create a checklist or a little tutorial video I think that would help a lot of the teachers who I know this would be a bit too advanced. However, one teacher could do the work and pass it on to others. Nevertheless, awesome!

    1. It is definitely a little bit advanced. Hopefully I have all the step by step in there. Do this project I think it will really help you develop a lot of useful skills.

  3. This is wonderful!! Do you know if there is a way to use a digital badge as an icon with a student email address? I think it would be a great way to recognize the students with perfect attendance or a GPA above a certain level. It could always be added to their auto-signature; but wondering if it is possible to have it with their email address…any ideas/thoughts?

    Thank you for sharing!
    Lori

  4. Thank you so much for sharing all of your great knowledge with everyone. I appreciate it so much! I have presented on Google Classroom and have used information that you have shared on your blogs. Here is a badge I made for teachers. Feel free to use it: http://goo.gl/nyjrXc

  5. Have you used ForMule to send out badge codes based on certain answers on a Form? I’m trying to find different ways to get student’s codes quickly and without hundreds of email requests. Thanks for all your amazing information and helpful tips.

  6. I am stuck on the =importrange portion. I did exactly what you said about 10 times, checked the link, check the spelling, and made every possible change I could think of, but the student sheet gives me an error rather than linking my sheet. I was able to get it to #N/A rather than #ERROR, but its still not working. I am doing this within a school domain so I am not sure if that affects it? Any thoughts? Thank you for your time!

    1. You need to make sure your sheet is not private. If it was private and then you change the visibility the =importrange can be a little fussy. Try it on a blank new sheet to test it out.

      1. Thank you, I did have it in shared mode but I found the problem. For some reason when copying the formula over and inserting my URL is was deleting a quotation mark or adding a parenthesis so the many times I tried it had something missing or added that was making the formula not work. Thank you for your time, this was a great resource and I love that the students can embed it on their online portfolios!

  7. Hi Alice, this is so helpful–thank you so much for creating and sharing. I am having trouble with this step: =importrange(“URL”,”Sheet1!A:D”) I checked my URL, made sure that it was inside the quotes and then had someone else check as well (creating their own sheets and following the steps themselves). We both got errors, any suggestions?

  8. This is excellent, thank you for sharing this. When you hide the badge list tab, is there a way to block students from re-opening it in their copy of the spreadsheet?

    1. No, if the students know how to open hidden tabs (most won’t) you can’t stop them. It doesn’t really matter. I just hide is so they don’t accidentally mess it up.

      1. I’d like to add an extra column that automatically provides me with the badge codes for each student depending on which badge the student gets. This would just save time without having to look up the badge code before sending it to the student. Is there a formula that would do this?

          1. Ok, I’ve worked out a formula for this now thanks. Would there be any way that you can think of which would automatically populate students’ badge speadsheets with digital badge images without the need for them to write down badge image codes? In other words, as you do your grading on one assessment sheet, the process of awarding badges becomes completely automated. I really like your system, but I’m just concerned that some older students might be tempted to cheat by copying and pasting codes not assigned to them. I’ve made a YouTube video for teaching colleagues based on your approach (https://www.youtube.com/watch?v=tGG_q_QpfRg), but ideally I’d like to do away with the need for badge codes.

  9. Hi Alice,
    Thank you so much for all your posts. I’ve been doing my own personal summer PD by reading all your great tips and tricks.
    I’m stuck on displaying the badges. I’ve created the badges, published them, and copied the links. However, the image does not show up in my master document, nor in the student copy either. I copied your badges and tried inserting those, and they show up just fine.

    What could I be doing wrong?

  10. Will this work? Create the student template, distribute it using Google Classroom as an assignment, have the students publish the spreadsheet, embed it on their Google Site webpage, then TURN IN the sheet on Google Classroom. Then I could click on a student’s sheet, insert the code, and then the badge would appear on their Google Site?

  11. Can you send the student copy out via an assignment in Google Classroom, have the students embed it on their webpage, then they “Turn In” the sheet and I can add the codes? Will that also update on their website? Thanks!

  12. Hi Alice,
    With the new google sheets, I have to “allow access” to the hidden badge list for the importrange function to work for the student badge list to populate. I like the idea of hiding the sheet but if I give each of the students’ directions to do this they will know where it is and how to copy/paste their code. Is the only way to fix this to go to each student’s computer and do this manually? Do you have a creative fix?

    1. Hopefully the benefit is intrinsic, in the learning and not in the HAVING a badge. Earning something is an accomplishment. This system is not intended to lock out access to the badge list. Truth, almost any digital badge you can right click and save the image and use it however you want anyway.

  13. Hi Alice,
    I had previously tried creating badges through Google Drawings and then publishing them to the web. When I pasted the URL, the image would never show up. I finally figured out the problem- it won’t work using a school Google account. I tried it again using my personal account and it worked! I don’t know if there’s some privacy setting on the school address which prevents it from showing up. So you have to publish the badge using a personal account and then paste that URL back into the sheet in your school account.

    Just thought I’d share in case anyone else had trouble with this step. Thanks so much for your helpful blog!

      1. I am SO LOVING making badges now! Thank you for this! But yes, I know that here in York Region (Ontario) our “GAPPS” accounts are locked down by our domain admins, so we can’t transfer ownership to anyone outside our own domain, or publish to the web for links like you built into your spreadsheet or…. or…. etc. I am doing everything from my “teacher Gmail” account now. So frustrating, but there are work-arounds.

  14. Hi Alice Keeler. I´d love to level up my next unit adding badges and quests as a ´formative´ journey through the History of Earth. Can you help me wrap my head around equating levels/badges to grades? I´m thinking if a student hits a particular badge number or level they have demonstrated their understanding at a ´proficient´ or 3.25 (out of 4) level, for example Do you have any suggestions? Thanks! @jbrink802

      1. I’m having the same issue- gave the source file correct permissions and I’m still getting a “formula parse error”… checked everything I could think of to check in the formula itself…

  15. I’m super excited to make this badge sheet happen. However, i get an error message with the vlookup link, a Formula Parse Error. Not quite sure what I am doing wrong, everything worked perfectly up until that point. Any ideas? Thanks for posting this!!

  16. This is great. I am adapting this system for our new makerspace. I thought I might share one formula that I wrote which saves you from editing all the formulas in the ‘Badge Display’ sheet. It is kind of long but it fills across and down nicely. Basically it does some math on the column and row number of a cell, uses address() to create a cell reference as a string and then uses indirect() to convert that string into a cell reference. Then it uses the if(), image() and vlookup() like your formula:

    =if(indirect(address(column(A1)+row(A1)*6-5,1,4,,”Badge Codes”))=””,””,image(vlookup(indirect(address(column(A1)+row(A1)*6-5,1,4,,”Badge Codes”)), ‘badge list’!$A$2:$I,9,FALSE),3))

    Hopefully that is helpful to someone.

  17. Alice, I am trying to create the Student Badge Display sheet but I’m running into an error that I can’t quite figure out. For the “if” statement, I’ve typed everything exactly as you’ve entered it but am coming back with “#REF!” due to “Unresolved sheet name ‘Sheet1′”. Any suggestions?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 21st or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session as we learn how to revolutionize your assessment strategies. We will dive into the essentials of crafting high-quality rubrics that go beyond traditional scoring methods to offer rich, meaningful feedback. We’ll learn about the components of an effective rubric and how to mathematically ensure the accuracy of your assessments.

Exit this pop up by pressing escape or clicking anywhere off the pop up.