Earn Your DIY Badge: Create a Badging System using Google Forms and Sheets

A (Considered) Case for Gamification

At Motivf, everybody does business development. For the first trimester of each year, we make that requirement into a company-wide professional development activity. True to our roots as experimenters who embrace the uncomfortable, this trimester we’re asking everyone to put themselves out there. Go meet people and talk to them. Dig into an area that you’re passionate about and add your voice to the conversation. We try to make our internal training initiatives descriptive, not prescriptive. We set up a playground and ask people to play in it.

This trimester, we made the objective clear: find your audience; the people whose problems you are passionate about solving. We have a weekly call where someone introduces a method that works for them, and a long list of potential activities to consider. Each person has a teammate for accountability who they check in with daily.

We wanted to find a way for people to have visibility into what their colleagues were doing to drum up some competitive spirit, but I didn’t want to spend our weekly meeting giving each person 3 minutes to describe what they did that week. I brought up the idea of a badging system. We were offering up a wide variety of potential activities (write a blog post, write a white paper, speak at events, attend events, host a workshop…The list goes on). Our managing director pointed out that I’m already doing a lot — I probably shouldn’t add tracking and maintaining everyone’s activities throughout the Banzai to my plate. But, still, a badging system actually made sense for this. It would provide the visibility we needed, it would encourage people to branch out into other activities, it would break the big-picture objective into attainable pieces. I wanted a badging system.

I decided that I would work on it if I could make it meet three conditions:

1. It had to maintain itself. Once it was set up, I wanted it to just be a link that people could click whenever they wanted.
2. I didn’t want to add any new platforms. We use a lot of tools every day. We don’t use an LMS and I didn’t want to give people another login. If I couldn’t incorporate it into Slack or make it SSO with their Google account, it wasn’t going to work.
3. It had to be free. 100% free. No trial accounts, no free tiers.

I started out by asking myself what I would ask the participants to do. I could probably connect HubSpot to an LRS and pull in data from there, but a lot of the activities needed to be self-reported. I decided that asking everyone to fill out a form each week tracking their activity was a light enough lift. That meant a Google Form was an option. A Google Form that fed into a Google Sheet seemed like a good start, and once I added a pivot table to group responses by email address, I knew this was the solution.

The How

Step 1: Google Form

I started out by creating a form that asked people to enter a number for each activity. The most important thing here is that I required their email address, which I automatically grabbed if they were signed in. We use G Suite for our work, so generally employees are signed into their work email. Restrict it to a domain if you can, otherwise you may have to check in every once in a while and combine emails for those people who accidentally use their personal email address.

I also put some dummy data in using a few different email addresses.

Step 2: Connect the Form to a Google Sheet

If you’ve never connected Google Form results to a Google Sheet, it’s a simple click.

Step 3: Create a pivot table

Having dummy data in the spreadsheet made this step easier. This is also where one of the biggest tricks came in. I needed to have a list of everyone who was participating. I’ll need to make some changes if anyone joins in later. I threw all of the email addresses that the form could expect to pick up on the results sheet. Then I created a pivot table. The pivot table groups the results sheet by email address, so it keeps a running total of the submissions. I added a column for each form question and locked the sheet. This is my source data for any of the visuals that come later.

Step 4: Create badges

I’m about to get this one

Luckily, I had one of our graphic designers, Steven Drum, who could design badges for me. Each category had at least 2 or 3 badges based on the number achieved. I uploaded all of these to AWS. I started out trying to link them from Team Drive, but it didn’t work for me. AWS worked, so I stuck with that. AWS also gives you semantic URLs, which makes the next step easier.

Step 5: Create the Leaderboard

The first column of the leaderboard lists the names of the participants. I populated this by copying the emails from the pivot table and just typing the name over it. It’s a group of 25, so this wasn’t too burdensome. You could always require a name in the form and pull from there, but the manual method worked for me.

With a list of the badges that I needed, the source data, and the links to the badge images, I was able to use some formulas to make the images appear if certain conditions were met. The first badge is for entering contacts in HubSpot. You get your first badge when you have 5 contacts entered, then new badges appear for 15, 25, 50, 100, and 200. The formula, pasted below, references the cell in the source data sheet (“Totals”) and, if it meets the condition, displays the badge. I dragged the formula down to fill all of the rows, then made a similar formula for the next activity.

=IFS(Totals!B3>199,IMAGE(“IMAGE-URL"),Totals!B3>99,IMAGE("IMAGE-URL"),Totals!B3>49, image(“IMAGE-URL"),Totals!B3>24, image(“IMAGE-URL"), Totals!B3>14, image(“IMAGE-URL"), Totals!B3>4, image(“IMAGE-URL"), TRUE,)

Step 6: Publish

Google Sheets lets you publish to the web. This reduced the quality of the images but made it easy to share the board with the participants. It refreshes in real time, and as people fill in the form the badge populates automatically. It’s hands-off, except for a minor adjustment here and there.

I’m testing out not showing what badges are available because we have a guide of potential activities. Another option would be to include a greyed-out version of the badge if the formula returns a false value.

The system has had the intended effect. People are branching out and focused on converting that blank space into pixels. We rolled out the leaderboard after the first or second week of the initiative, and the increase in activity was clear. When a colleague mentioned that she was going to write a blog post, I jokingly mentioned the badges. Her face turned serious as she said “I want those badges”.