Saturday, September 5, 2015

Google Form Add-on: copyDown

"Get your Google Form submissions up on the dance floor with the power of 
formula-copy-down!" 
~Andrew Stillman

Are you doing the copyDown dance? If not, you'll be dancing happily when you realize the power that this add-on adds to Google Form submissions especially when used in conjunction with a mail merge feature. 

Here's a scenario I faced: Parents sign up for our school's Library Book Club using a Google Form. That's a time-saver, you might say. True, however, as I use their submissions along with these add-ons: autoCrat and formMule, I run into a few merging troubles. For instance, sometimes parents don't capitalize their child's name, or they use full caps. I also need to be able to use certain pronouns depending on if the child is a boy or a girl--I really don't want "Which pronouns do you use with regards to your child?" to be a question on my Form! The solution: copyDown.

Before I go on and on with how it all works . . . try it for yourself first. Go to my form and complete it and make sure to include your true email address. Try it as a parent and then again as the staff member option to see how autoCrat and FormMule work. Either full cap or don't capitalize your names so you can see what happens with copyDown. Check your email! If you are interested . . . come back and read on to see how it's done! Click here for the Form. I'll wait for your return . . .

The Form obviously captures all your responses. Now you need some responses fixed or figured out. That's where copyDown comes to the rescue because you can't put formulas on each line in anticipation of submission responses . . . form submissions will erase the waiting formulas. 

Here's how it works:
Create your form. 
Go live and complete your form to get one row of submissions. 
View your responses. 
Install the Google Sheets add-on copyDown.
Now add the necessary formulas to the immediate right of your form responses. 
Here are the formulas I used:
If a parent types their child's information without capitalizing or full caps the name, the formula =proper(C2) (where C2 is the cell containing the first name) will correct it. 
Since parents included a son or daughter response, I use these three if statements to get the correct pronouns to use in the merge document:
=if($B2="Daughter","she",if($B2="Son","he",if($B2="I'm a staff member","")))
=if($B2="Daughter","her",if($B2="Son","his",if($B2="I'm a staff member","")))
=if($B2="Daughter","her",if($B2="Son","him",if($B2="I'm a staff member","")))
For the birth date, I use this formula: =E2  
You can then format the date to only display the month and date. (Format> Number> More Formats> More Date and Time Formats> choose Month and Day. 
You can take a look at the positioning of the formulas in my Sheet: The Sheet

Once you have all the necessary formulas, go to Add-Ons and launch copyDown. copyDown will use row 2 submissions and look for formulas there. Click the switch to turn it on. You will see a list of your formulas. For me, I usually always select Paste as Values (this eliminates the formulas once they have been calculated leaving only the results in each cell). To make it easy, you can scroll down to the bottom of your list and Select/Deselect All. Then click Save Settings. copyDown places a column in your document with a purple heading. copyDown is triggered by each form submission. Test it again and see how it calculates your formulas. The purple headed column will also give you status of completion for each submission. 

If you gave my Form a test run, you also saw autoCrat (for the student response merge) and formMule (for the staff response email) at work! My sample Sheet also shows the status of each of these Add-Ons. These are two other really powerful Add-ons that you should know. To learn more now, visit: New Visions Cloud Lab and look under Add-Ons.