Monday, September 9, 2013

Say Hello to the POWERFUL @Google Form :D

Have you used Google Forms yet this school year? You should. You need to. Everyday in every way. Here's your three minute video for creating Google Forms.

Need some general ideas for using it in the classroom? This will get you started:

  • Create a form for open house that asks for parent name, email, phone, best time to contact, child's name, allergies, etc. Now you have this wherever you go from field trips to the beach. You also have a list for copying and pasting parent email addresses. 
  • Use a form for Peer Feedback of an in-class presentation
  • Take your class paperless and use it for an answer sheet with a verbal quiz (think spelling test). 
  • Still keeping a reading log? HELLO Google Forms with your beautiful time/date stamp.
  • Speaking of time/date stamp, create a form that acknowledges help/failing grade/inappropriate behavior. Have students complete it to also acknowledge the behavior. Include drop down questions and text for them to personalize it as to WHY it happened. 
  • Paperless storyboarding and story building.
  • Inventory! Everything from books to serial numbers. 
  • With a little experience, they make GREAT create your own adventure makers!
**Update: Features released from Google today. Click here**
SO many great uses for Google Forms. I may need to come back to this topic and show a couple in more detail. Today I want to show you how to set-up a large scale inventory or record keeping system with multiple forms and ONE master.

Scenario:
As the Instructional Technology Director, I oversee 38 campuses that literally span the entire state of Texas. As part of our Chromebook deployment, we offer our students an OPTIONAL theft/loss policy. Each campus needed to have access to their own data on which students purchased the policy. I needed access for each campus. 

Plan:
For record keeping purposes, every campus used a Google Form to track which students purchased the policy. It was a simple form that asked for student name, serial number, and payment method. The campus registrar and principal were then each given access to the spreadsheet for that form. 

Problem:
I needed the information from all 38 spreadsheets (they were created as part of the Forms) and did not want to rely on copy/paste and did not want to visit all 38 spreadsheets individually. 

Solution:
I created a Master Workbook in Google Docs. 
For every campus, I created a separate spreadsheet inside the Master Workbook. Each spreadsheet then automatically pulls in the data from each of the 38 separate spreadsheets created from the Google Forms. How?

Inside cell A:1 of each spreadsheet inside the Master Workbook I used the following formula:
=ImportRange("0Azci-Fzpp56KdHVkaWRqcl9OTWlxRGhWdFk7CxdOV0E","A1:Z3000")
Broken down the formula is as follows:

  • =ImportRange    <----- That is the formula
  • ("SpreadSheetID",   <---- Notice the parenthese and the quotation marks. The comma has no space in front of it. The SpreadSheetID is found in the URL of a spreadsheet. For example, this spreadsheet:
    https://docs.google.com/spreadsheet/ccc?key=0Amz5tB87ZkRDdEhMcWVTSXcxYWlONmctVlg2ZWtmNXc#gid=0
    has a SpreadSheetID of 0Amz5tB87ZkRDdEhMcWVTSXcxYWlONmctVlg2ZWtmNXc
    *The ID is essentially everything between the the key= and the #gid=0
  • "Range") There is NOT a space after the comma. Notice the quotation marks and parenthesis. I generally just make up a really large number to capture all the responses I will have on the form. 
And that's it! Now all the data from all 38 separate forms automatically fills into my Master Workbook. If you run into any problems or need any help, feel free to contact me or ask a comment below!

No comments:

Post a Comment