One of the most powerful combinations I’ve discovered in my time as an Instructional Technology Coach involves Google Forms, Google Sheets, and formMule. I’ve used this combination more than any other in my teaching and time as an ITC. Here’s a flow chart of the results of this combination when creating a Student Grade Check Form.
As you can see, this process is broken up into a few steps. I will walk you through the process of how you can successfully create a Student Grade Check Form that will email the student’s parent(s) upon form submission.
In order to do this, we’ll need to…
- Create a Google Form
- Submit a form.
- Connect the Google Form to a Google Sheet.
- Install the formMule add-on in Google Sheets
- Configure formMule
- Check your e-mail.
- Roll out to students.
1. Create a Google Form.
When creating a Google Form for student grade checks you can tailor your questions to fit your specific needs. I’m going to assume that you know how to create a Google Form. If you don’t, click here for a great tutorial from Google.
You want to make sure that you are collecting the student’s name, parent(s) name, and parent(s) email address…those are crucial.
From there, you can customize the form to fit your needs. Here are the questions that I created as an example.
NOTE: I “required” every question. You may not need to do so, but I chose to make students answer every question.
If you’d like to ensure that student’s are being trustworthy, you can turn on “collect e-mail address” under settings of the Google Form. This will automatically collect the e-mail address of the user that is logged into the device when accessing the form. You can also restrict the form to only users of your domain as well (The form would not be viewable to the general public…only students and staff of your district.) See below.
2. Submit a Form
It doesn’t have to be real. Go to your live form and submit one…just to make sure everything is working properly.
3. Connect The Google Form To A Google Sheet
When editing the form, click on the Responses Tab.
Click on the Green Google Sheet Icon to “Create Spreadsheet“.
You have the option of “Creating a New Spreadsheet” or linking this form to an “existing spreadsheet”. If this is a new form, choose “Create A New Spreadsheet“, name it, and click “Create”
4. Install the formMule Add-On in Google Sheets.
The spreadsheet will automatically open up. Click “Add-Ons” in the menu and navigate down and click on “Get Add-Ons“.
This will open up a list of all the add-ons available to download for Google Sheets. Search for “formMule“. Click on the blue box that says “+Free“. (mine says manage because I already have it installed). It’s going to ask for you to give it permission to access your Google account. Make sure you allow it to do so.
5. Configure formMule.
Click the “Add-Ons” Menu, navigate and hover over “formMule“, slide over and click on “Launch” to open formMule.
Upon launching, you need to select the source data spreadsheet and choose whether you want the e-mails to be sent on form submission or at a specific time of day.
I generally select “Form Trigger” because I want my e-mails to be sent immediately on the submission of the form. You can also choose to “Log a case number” or “Form Edit URL” but we won’t be doing so for this tutorial. Click “Next: Templates and send conditions“.
formMule allows up to 15 different e-mail templates to be set up. For this tutorial, we need 1. You can name each e-mail template as well but because this is a simple merge, we will not do so. The condition for the email to send is any detection of new rows being added to the Google Sheet (every time a user submits a form). Click “Save Template Settings“. There will be some things going on in the background. When they are finished, click “Next: edit templates“.
When creating your email template, you can simply click on the merge tag buttons on the right side and they will be inserted into your template. For example, I clicked in the “To” field and then clicked on the “Parent E-Mail” button so the tag will be used to populate the “To” field with the “Parent E-Mail” that was inputted by the form submitter. I chose to add a teacher’s email to the CC: field and a principal’s email to the BCC: field. Remember, parent’s will be able to see the CC: field but will be unable to see the BCC: field. These are optional. I also used “no-reply” as my “Reply-to” so that the e-mails come from a fake address and not my own. This was save you some Gmail headaches by doing it this way. In the Body of the email, I craft what I want my e-mail to say utilizing the tags on the right when I need info from the submitted form. HTML coding can be used to spice up the email. For simple HTML coding, I like to use W3Schools.com
Click on the “Preview this” button to see what your email will look like.
If you like how the e-mail template looks, click on “Preview and Send All”
If everything’s working correctly, you’ll get a preview of what the e-mail will look like.
If you’re happy with it, click “Send Now“.
The e-mail will send and all future submissions will send as well. If you look at the furthest column on the right, you will see that formMule created a new column to let you know when the e-mails were sent and whether they sent successfully. This is a great place to double check to make sure student’s typed in their parent’s email address correct. This is also a great tool to track parent communication because it time-stamps every e-mail that goes out.
6. Check your e-mail (if you included yourself in the CC: or BCC:)
Make sure you received an email and that everything looks the way you want it. If it does, you can roll this process out to students. If it doesn’t you can always go in and change your e-mail template.
Here’s what the e-mail looks like in Gmail…
7. Roll out to students
Determine how you’d like to use this “Student Grade Check”. If your school has a homeroom, you can use it there…or during Study Hall…whatever fits your needs.
NOTE: There is a way to avoid having students type in their parent’s email addresses using another Google Sheet and some formulas. It is a little work on the front end but it will prevent you from getting “undeliverable” e-mails because of a mistyped e-mail address. I’ll save that for another blog post…