Now, that we have created a Form and are ready to collect data, I want to use the built-in functionality from Google Doc Spreadsheets to create more substantive comments on the assessed skills. The Form will automatically populate the spreadsheet when it is submitted.
Let's enter some test data into our Form to use while we create our customized email message:
Let's enter some test data into our Form to use while we create our customized email message:
Once we hit submit, our data will be populated into the spreadsheet:
From this data we want to expand on our entry for Skill#1, Skill#2, and Skill #3. We want to turn G, A, and B into more substantive comments.
First, we will create 3 new column headings labeled Comment for Skill #1, Comment for Skill #2, and Comment for Skill #3.
Notice that the new column headings are white and not grey like the previous headings (for Timestamp through Skill #3). This is because the grey columns are populated by the Form. One thing to remember is that if you add another question to the Form it will copy over the new column heading you created.
Now create a new sheet in our spreadsheet by clicking on the plus key at the bottom of the page:
Create 2 new sheets, 1 called Email Template (which we will use later) and 1 called Comment Sheet. Make sure to keep the Email Template sheet the second one from the left (important for our future purposes of creating an Email Mail Merge).
Now, go to the sheet labeled Comment Sheet. Here we are going to create the more substantive comments for each Skill that we are assessing and the 3 levels: Good, Average, Bad. For my example, I will use a soccer example for Skill #1 and Skill #2.
Now create the comment detailing each level of ability for each of the skills. For example for Skill #1:
(Notice that I have personalized it with names and gender specific comments. Later, we will edit this to put it in a different format so that Google Docs will Email Mail Merge it.)
Do the same for Skill #2 and Skill #3
Now that we have our substantive comments, we want to paste them back into the Data Collection Sheets. We will use a built-in function, IF, to do this.
You can also find a list of all the built-in functions here (where I got this info):https://docs.google.com/support/bin/static.py?page=table.cs&topic=25273&ctx=tooltip
So, in Cell I2 (which represents the Comment for Skill #1) use the IF function. This will check the entry in F2 (where we assessed Skill #1) to see which comment to use. If it is a G, we use the comment for Skill #1 that we created in the sheet titled "Comment Sheet", the Good comment is in cell B2.
Repeat this function for Comment for Skill #2 and Comment for Skill #3. The only thing you need to change is the cells that you reference. (For Comment for Skill #2, replace F2 with G2 and B2,C2, and D2, with B3,C3, and D3).
This will automatically fill in the cell with the comment we created in the other sheet.
I have detailed my steps in this video as well.
