bank on this
Building and Designing a Classroom Economy (With Google Sheets and Forms)
Embedded Files
Step 1: Set your template.
Step 1: Set your template.
Design a bank account template using Google Sheets, or just use this one. You'll be making a lot of copies of this for your class / grade level, so make sure that it's how you want it BEFORE sending it to your class. Put it in an easy to find folder once finished.
You're more than welcome to design this on your own, but I figure you'd probably want to get the hard part out of the way. Click here to make a copy that you can access in your Google Drive later.
Name this sheet "Finances"
Step 3: Make your store
Step 3: Make your store
Go to https://forms.new to create a Google Form that you'll use for your store. In order for the Google Sheet to work, it must be set up the following way.
Set your form to automatically collect student email addresses. You do not need a question that says "What is your name?"
Create one (and only one!) question that is multiple choice. You'll put the different items that students can purchase from the store here.
Each item that students can purchase from the store must be formatted a certain way or else the "Bank" won't work. Feel free to copy and paste the different example class store items onto your sheet below.
Class Store Example Items
Class Store Example Items
Help out another teacher - $150
Lunch bunch - $100
Wear a hat during class - $75
Extra recess - $125
Class Party - $250
Step 4: link your form to your spreadsheet
Step 4: link your form to your spreadsheet
The responses from this form need to go to the "Finances" spreadsheet that you made your own copy of above. To do this:
Open your form and click "Responses"
Click the three dots next to the green Google Sheets icon.
Click "Select Response Destination" then "Select Existing Spreadsheet"
Find the "Finances" Spreadsheet that you made a copy of and click it. Ta-da!
Step 5: Rename the "Responses" Sheet
Step 5: Rename the "Responses" Sheet
You'll now have a sheet on your "Finances" spreadsheet called "Form Responses" (or something like that). Double click on that sheet, and change it to "Purchases" (all capital letters).
Take a deep breath. We're at the magic part.
Take a deep breath. We're at the magic part.
Step 7: copying and sharing the bank accounts
Step 7: copying and sharing the bank accounts
On your "Finances" Sheet, go to the tab called "Account Links". We're going to be running an "Add-on" called "Doctopus". It sounds scary, but it's not so bad once it gets up and running.
Install the "Doctopus" add-on from the Sheets Add-On store.
Create a new roster of your class. Be sure to type (or copy) their email addresses exactly you want them to be.
Under "Desired Sharing Arrangement", click "individual -- all the same"
Under "Assigned student access level", change it from "allow edit" to "allow view only"
I'd check the box that says "Editors cannot change sharing permissions" for good measure!
6. Find the folder that your "Bank Account Template" is in, and click the FOLDER, not the item.
7. Click the Bank Account template that you put in there earlier.
8. Choose the folder and naming convention that you want each bank account to be called.
9. Take a deep breath and cross your fingers.
10. Click "Run Copy and Share"
If what you just watched made you think "OMG I HAVE NO IDEA WHAT YOU JUST DID THAT HURTS MY HEAD DON'T YOU KNOW IT'S SUMMER TIME!?!??!", here's another option:
Copy and share each Bank Account sheet manually by clicking "File --> Make a Copy" for each student in your class.
Paste the link to each student's "Bank Account" on a different line of the sheet.
Step 8: add student emails to the "finances" spreadsheet
Step 8: add student emails to the "finances" spreadsheet
Open up your "Finances" spreadsheet and click the sheet called "Import".
Copy and paste each student email address in the spots on row 1. This will sort and filter purchases as students make them. Don't type or delete anything else on this sheet or you'll break the formulas that are running in the background.
Step 9: connecting each "bank account" to the "Finances" sheet
Step 9: connecting each "bank account" to the "Finances" sheet
Full disclosure: this is the part that takes a little bit of time, but will make your life easier once the school year gets started.
Set your "Finances" sheet to "Anyone with the link can view" (within your district)
Open up the link to the first student bank account and find cell "G12". It's right below where it says "Purchase".
We're going to be using a formula called "IMPORT RANGE". It lets data from one spreadsheet be linked to data on another spreadsheet.
In cell G12, we're going to be using the following formula:
What you're typing:
=importrange("link to finances sheet","Import!range below each student email address on finances sheet")
Example:
=importrange("https://docs.google.com/spreadsheets/d/1hk8WoB284MN1VkjxX7nSK899Do5C1FDBkhZo3qV14fQQc/edit#gid=1349731027","Import!h3:k")
This will update each student bank account EVERY time a purchase from the store is made.
Do this for each student bank account, and TA-DA, you have a class store that runs itself.
Google Sites
Report abuse