bank on this

Building and Designing a Classroom Economy (With Google Sheets and Forms)

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.

Step 2: Copy this sheet to use later

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

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.

  1. Set your form to automatically collect student email addresses. You do not need a question that says "What is your name?"

  2. Create one (and only one!) question that is multiple choice. You'll put the different items that students can purchase from the store here.

  3. 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

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

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

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.

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.

  1. Install the "Doctopus" add-on from the Sheets Add-On store.

  2. Create a new roster of your class. Be sure to type (or copy) their email addresses exactly you want them to be.

  3. Under "Desired Sharing Arrangement", click "individual -- all the same"

  4. Under "Assigned student access level", change it from "allow edit" to "allow view only"

  5. 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

  • 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

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.

  1. Set your "Finances" sheet to "Anyone with the link can view" (within your district)

  2. Open up the link to the first student bank account and find cell "G12". It's right below where it says "Purchase".

  3. We're going to be using a formula called "IMPORT RANGE". It lets data from one spreadsheet be linked to data on another spreadsheet.

  4. 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")

  1. This will update each student bank account EVERY time a purchase from the store is made.

  2. Do this for each student bank account, and TA-DA, you have a class store that runs itself.