# 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:

- 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)

Step 1: Make a Google Form with the following

• Name
• Some form of multiple Choice
• Or Click to make a copy of this one

Step 2: Open the Response Sheet and configure FormMule

• Launch Form Mule and Follow Configuration

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

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.

JohnDavidEick@gmail.com

@John_Eick

CapCue Board Member