CapCUE 2017
Saturday, September 30 • 9:00am - 9:50aUse Google Forms to Connect with Community
Participants will build Google Forms with: Auto-Email & Mail Merged Responses, Use Photo Uploads for Item Selection, and Add-ons that will both Personalize and Automate every educator's Connection with their Community.
Agenda:
1. The Google Form Sign Up with the Auto Response
- Using Auto Eliminating Choices (Advanced)
2. Building a Form Using Photos as selections
- Directing to pages based on selection
3. Crowdsourcing Appreciation and Mail Merging
- Merging one line at a time
- Merging all responses into a single email (Advanced)
1. The Google Form Sign Up with the Auto Response
Click Here for a sample Form
Click Here to view a sample Response Sheet
Step 1: Make a Google Form with the following
- Name
- Email Address
- Some form of multiple Choice
- Or Click to make a copy of this one
Step 2: Open the Response Sheet and configure FormMule
- Click Add Ons from the menu bar
- Get Add Ons
- Select FormMule or Click Here to install on Chrome
- Launch Form Mule and Follow Configuration
- Question Eliminator (Advanced)
Step 1: Set Up the Spreadsheet
On a separate tab
- Column A: place all answers to a drop down question in Column A (Title Column Pre-Count)
- Column B: use the countif formula to search the response sheet to count if an answer has been selected. (Title Column Count)
=COUNTIF('Form Responses 1'!D:D,A2)
This formula translates to:
1. "=" Hey Cell, you will equal the following
2. "COUNTIF('Form Responses 1'!D:D" Hey Cell, look in the form response sheet, column D, and count...
3. ",A2)" This is what we want the cell to count - In A2 will be the answer you want to eliminate.
- Column C: Use the =If formula to determine if an answer should be posted of if it should be eliminated. (Title Column Post)
=IF(B2>0,"",A2)
This formula translates to:
1. "=" Hey cell, you will equal something
2. "IF(B2>0,""...Hey Cell, if cell B2 equals larger than zero, then someone must have selected this answer already, then you should equal "" - which means you equal nothing, therefore, nothing will post to the form from this cell.
3. ",A2)" Hey cell, if B2 is greater than zero, post nothing, but if not, post A2.
Step 2: Use FormRanger on the Form
Now that the spreadsheet is counting responses, and willing to eliminate answers once they have been selected, we just ask FormRanger to pull answers off the sheet into the form.
On the form, use the add on Form Ranger.
- Launch Form Ranger
- Follow steps to assign the "Post" Column as the answers to a multiple choice/drop down question.
2. Building a Form Using Photos as selections - The Raffle
Click Here to View the Raffle Form
Click Here to View the Response Sheet
Click Here to make a copy of an Example Form
Step 1: Prepping the Form
- Build a form, inserting pictures into a multiple choice answer
- Use page breaks to make a unique pages for each selection
- Use goto page based on answer
Step 2: Prepping the Sheet
- On the response form, merge Name & Bid by basket
- Use a formula such as =IF(E2<>"",E2&" - "&B2,"")where:
- E2 is the bid
- B2 is the Name
- Use the CopyDown AddOn to copy this formula down on every submission
Next:
- Organize a new tab to put all the basket responses in order using:
- =ARRAYFORMULA('Form Responses 1'!I2:I) where column I is the column with the bid
- Post the link as view only
3. Crowdsourcing Appreciation
The big Merge: imagine sending out a link to hundreds of families asking them to appreciate present and past teachers, and then thousands of feedback came pouring in. Well, in about 15 minutes you could merge all the feedback for each person into one cell on the spreadsheet, then mailmerge that to an email for each staff member. I call this, the big merge.
Click this Blogpost for the step by step details.
CapCue Board Member
CUE Lead Learner
Google Certified Teacher
Google Certified Educator
CUE Rockstar Faculty