Thanks to Mr. Bates (@Brandon_J_Bates) for the inspiration for this post. I hope it inspires something great!

So many of us in the gamified universe are into creating and sharing items. The distance part of our current Pandemic Learning (can we call it Social Distance Learning?) and the goal to limit sharing physical objects makes providing items a bit more tricky. I haven’t mastered this yet but all of my potential solutions revolve around creating Google Sheets based Student Dashboards. I know – when you only have a hammer all your problems look like nails. The connectivity of Google Sheets though allows me to “flip switches” from a master sheet that can then be applied to all of the students’ personal dashboard sheets. There is quite a bit of prep work but I’m going to try to lay it out here in one big post. Click on these links to see other posts on some of the main functions I will use- the VLookup and Nested If Statements, I will also be discussing how to link 2 sheets together and If-Then Statements.

The Master Sheet

If you are going to create an Item Shop you will need to have a Master Sheet that you control. All of the student sheets will be linked to the Master Sheet. My Item Shop is a Tab in my Google Sheet based Gradebook.

In this Item shop I have created a 3 Column “BattlePass” items which I have modeled after Fortnite. As students level up they automatically earn the items which are then displayed on their dashboards. I have not put any Common or Rare Items yet but for our purposes the “Allies” on the far right can be thought of as “Items”. I have to “Flip a Switch” for the allies to go into the student’s dashboards. The Three Column design is important for the VLookUp function I will use later and the reason I have these on the Master Sheet is so that when I need to make changes (if there was an error or I add an item) I can change this one Tab and it will automatically update to the students’ sheets.

Importing the Items to the Student Sheets

The Next Step is to import this Tab’s information into the Students’ Dashboard Sheets. This is accomplished with an Importrange Function. The string of numbers and letters in the URL of the Master Sheet is used to connect the two sheets together. The Template’s URL is “https://docs.google.com/spreadsheets/d/1NXikKZY8c23IGjGwHhM6zRI0qThVeXn6KHcXJDFYLwI/edit#gid=1811442168” so the dashboard’s importrange function is =importarange(“1NXikKZY8c23IGjGwHhM6zRI0qThVeXn6KHcXJDFYLwI”,”Item Pics”!A1:M). The string of letters and numbers tells the function which G. Sheet to pull from, Item Pics tells the function which Tab to look at, and A1:M is telling the function which Rows and Columns to bring back.

This is on the Far Right hand side of the Visible Tab in the Student’s Dashboard.

I have included basically the entire “Item” tab from the Master sheet. Because this is an importrange function any changes I make on the Master sheet will be automatically updated here.

Importing Data to Turn Items On and Off

The next step is to get the data off of the Master Sheet to the Personal Dashboard that will be used to create the various functions and switches. In my student dashboards I have a “Student Data” Tab that I hide before sharing it with students. In the two images below you can see I import from 2 different tabs. in Cell A1 I am importing information that is either cosmetic or grade related. In cell A2 I am importing from my “Items” tab. It is this tab that is the key to my item delivery.

The Master Sheet “Items” tab – The Item “Switches”

Each student in the Items tab has a row. Each cell in the row is either Blank or the number “1” and each column is represents one of the items (in this case an “ally”). If I add a number above “0” a function on the dashboard will add the item to the student’s inventory.

In this screenshot the student in Row 2 will receive a “Random Skin Award”, Rare Item 1 and 6, and Ally 1.

This data is importranged into the Student Data Tab of the student dashboard.

Turning on the Item in the Dashboard

All of the inputs to the Item Tab will be done through the Master Sheet; however the Student Dashboards draw the information from the Imported information on Dashboards “Student Data” Tab. Using an If-Then statement I can use that data to look up and turn on the item I want to display.

I start by “flipping the Switch” for Ally 1 by adding 1 in this column.

This will be imported into the “Student Data” Tab on the Students Dashboard.

I then have a function set up in the Dashboard’s “1” Tab (which is the tab visible to students) that will recognize this addition and “turn on” the item card.

The Function is =if(‘Student Data’!A13>0, image(W4),).

The Function is saying that if cell A13 on the Student Data tab is a number greater than zero then display the image that is in cell W4 of the current tab. Because I have added a 1 then “Student Data” Tab cell A13 is greater than zero which “turns on” the function. One small thing not to overlook – adding the comma after the (W4) tells the program to not display anything if the logical function is FALSE. Here is what Cell G8 looks like when I removed the comma.

So the function is turned on and is set to display whatever is in cell W4. Remember that is where I had imported the “Item Pics” information in the set up.

The function is =if(‘Student Data’!A13>0, image(W4),) and which means that the “image(W4)” part is also a function within the if statement. the image function tells the cell to display the image published Google Draw image at the URL I have posted in W4. The picture displayed in V4 is actually that same image(w4) function and is there so I can see the image in the Master Sheet.

Hiding the Work

After all of this I hide the “Work” columns. This keeps the kids from seeing and copying the unearned items.

It looks like it ends at Column H

Final Thoughts

I am sure that there is more efficient function programming but this works. It allows all of my changes and updates to be made in the through the Master Sheet. Having to update the tabs of EVERY student dashboard EVERY time I made a goofy error or made a new item would be too much. It also allows me to keep a record of which items students are supposed to have and not which ones they received from friends or by figuring out the functions.

I have this process automated for the BATTLE PASS based on the “level” in cell D2. You might be able to figure it out but I think that will be another post. There is also a random “exclusive costume” function that involves the same “switch” mechanic but based on a number generator. Again, that might be another post but you might be able to figure it out.