This post is a follow up to my previous post detailing how to create a Student Grade Check using Google Forms, Sheets, and the formMule add on to send e-mails to parents. The process as outlined works well but there are a few things to consider when building the workflow.
- Are student’s going to correctly type their parent’s email address? An incorrectly typed parent e-mail address will not deliver the grade check to the parent and the teacher’s account will be flooded with undelivered e-mail notifications.
- If you choose to use a dropdown menu for students to choose their name, what prevents them from choosing another student and thus send out a fake grade check to parents of another student?
A solution to this problem is to automatically pull the parent e-mail addresses based upon the student that is logged into the form. Here is how you can solve this issue by using another Sheets add-on called copyDown. It’s a little more work on the front end but I think will create less headaches down the road.
-
Modify the Student Grade Check Google Form
When building your form, remove all questions asking for student/parent identification.
Click on the gear icon to bring up your form settings
Click the check box to collect e-mail addresses of those that fill out your form (this will be your verification that the parent information you are pulling belongs to the student that fills out the form). If you’d like to send a copy of the completed grade check form to the student, you can also check the response receipts checkbox.
Click on the “Responses” button on your Google Form.
Click on the spreadsheet icon to access the spreadsheet that houses all your collected form data.
-
Modify the Responses Google Spreadsheet.
On the bottom left portion of your spreadsheet, click the plus sign to create a separate sheet. Name this sheet “Data“.
On your Data tab in the Google Spreadsheet, create 3 columns: Student Name, Student E-Mail, & Parent E-Mail. Type in all your student names, e-mails, and parent e-mails in this “Data” sheet. If you have multiple parent e-mails, simply separate them with a comma and formMule will send out the email to both listed.
On the “Form Responses 1” sheet of your Google Spreadsheet, Add 2 Columns immediately to the right of the collected Student E-Mail Address. Name them “Student Name” and “Parent E-Mail“.
-
Insert Formulas into Spreadsheet
We’ll need to type in two formulas in the fields looking for the Student’s Name and Parent E-Mail. In this example, we will type the formulas in cell C2 and D2.
The formula in cell C2 should be =VLOOKUP(B2,Data!$A$2:$C$1000,1,False)
Let me explain what the formula does. VLOOKUP is going to look up information in another area of the sheet or another sheet and pull information that matches your search criteria. In this example, we’re telling the formula to look at cell B2, which contains the e-mail address of the student filling out the form. In this case, it reads mike.wielgus@tcusd2.org.
The next part of the formula =VLOOKUP(B2,Data!$A$2:$C$1000,1,False) tells the cell the look at all of the information in the “Data” sheet from cell A2 through C1000 to see if it matches the e-mail in cell B2. The third part of the formula =VLOOKUP(B2,Data!$A$2:$C$1000,1,False) tells the cell to pull information from the column listed here. In this case, if the “mike.wielgus@tcusd2.org” e-mail is found in the A2:C1000 range, it will pull the information from the 1st column of the Data sheet which happens to be the name of the student “Mike Wielgus“.
The last part of the formula =VLOOKUP(B2,Data!$A$2:$C$1000,1,False) tells the cell if this is an ordered list or an unordered list. This is an unsorted list, so type False.
If done correctly, the Student Name that matches the Student E-Mail from the Data sheet should be pulled over into your Form Responses 1 sheet.Now, we need to do the same to pull the Parent E-Mail that matches the Student E-Mail/Student Name from the “Data” sheet. The formula will be the same with the exception of telling it to look at column 3 instead of column 1 because the Parent E-Mail is in the third column of the “Data” sheet. =VLOOKUP(B2,Data!$A$2:$C$1000,3,False)
If everything is functioning correctly, the Student Name and Parent E-Mail columns should contain the information that matches the student e-mail of the person who submitted the form…in this case “mike.wielgus@tcusd2.org”.
-
Install, activate, & set up the copyDown Google Sheets Add-On
Next, we need to tell the sheet to copy these to VLOOKUP formulas down as each new submission comes in. In order to do that, we need to install the “copyDown” add-on. Search for, find, and install the copyDown add on. Then open it.
A dialog box will open on the right side of the Google Sheet. First, turn the button “On“. copyDown will search your sheet for formulas and tell you what row it found your formulas. In this case, it found formulas in row 2. Make sure you “Save Settings“.
copyDown will add a column to the end of your spreadsheet to let you know if the formulas successfully copied down.
-
Freeze Your Formula Row
In order to prevent accidentally deleting the formulas that will be copied down, I recommend, “Freezing the Formula Row” by going to the Edit Menu –> Freeze –> 2 rows. This will create a thick gray line and all submissions will appear below this line.
From here, just create your e-mail templates using formMule in my previous post and you should be good to go.
-
Test with a few students to make sure it works, then release to your entire class.
The benefit of using this method for the Student Grade Check form is that it pulls pre-entered (by the teacher) Parent E-Mail addresses based on the student that is logged in filling out the grade check form. It’s a little more work on the front end but I believe will greatly decrease having to spend much time fixing parent e-mail addresses after the form is released plus it ensures honesty by collecting the e-mail address of the student logged in.
For more information about the VLOOKUP formula, you can click here.
For more information about the copyDown add on, you can click here.
edtechCREATIVE 