- Name
- Student ID
- Parent/Guardian Email address (optional)
- Purpose for attending
- Teacher
- Desired area of assistance
In addition to gathering the desired information, our goal was to send an email to the parent/guardian and teacher in order to keep them informed about their student's progress. While I knew a Google Form was a quick, easy way to get this information, I also knew it was presumptuous of me to assume that all students would know their parents' email address in order to enter it into the form. In addition, I knew I would need to utilize a Google script in order to send automated emails to both the parent/guardian and the teacher. With this knowledge, my search began for the following:
- A Google script that worked well with Google Forms in order to send emails upon submission
- A formula to use in order to create a lookup, based on student ID numbers, for the primary parent/guardian email address
Step 1.....Finding a Script
In my classroom, I use various scripts in order to promote efficiency, student learning, growth, and collaboration. A few examples include gClassFolders, Flubaroo, Autocrat, and Doctopus. With my previous knowledge, I decided that Autocrat might be a good option for this project. If you are unfamiliar with Autocrat, it is a script created by Andrew Stillman that performs a mail merge based on information in a Google Spreadsheet. However, in my research for this project, I found an even better option for our Academic Resource sign-in system: Formmule.
Formmule is yet another wonderful script written by Andrew Stillman that is a "useful building block for creating your first systems improvement in a school if you want to use forms and automated emails and calendar appointments to get the right people in the loop around student success" (http://youpd.org/formmule). After watching a few instructional videos about Formmule, I decided it was my best option for this project and started playing around with the script. To my delight, it worked perfectly for our project, and I was able to set up an automatic email to parents/guardians, teachers, and the student after this student pressed submit on the Google Form, upon entering the Academic Resource Center. Formmule has numerous options available to users, and while I didn't experiment with all of them, I am very excited about the power of the script for the Academic Resource sign-in system and for future school projects.
Step 2....Finding a Formula
After learning how to properly utilize Formmule, I now moved onto my next obstacle: Figuring out how to create an automatic look up of parent/guardian email addresses based on the student's ID number. I knew that I wanted to use the student ID numbers as the look up guide, but I was unsure of how to go about it. After talking to my technology mentor, I learned about query formulas in Google Spreadsheets and how I can utilize these formulas in order to look up information on other sheets, and then plug them into the information I will receive in my Google form. I must admit...I am terrible when it comes to using Google spreadsheets due to my lack of experience with formulas. So after playing around with the query formula, I was extremely delighted to see that if I have student data, the possibilities are endless when it comes to manipulating the data to fit my needs. So...I now needed student data.
Our district's student information system is obviously a separate program. With this, I knew that I needed a list of the students, their ID numbers, and their primary parent/guardian's email address. With the help of the assistant principals, I was able to have access to a spreadsheet with this data. After receiving the data, it was finally time to put it all together and see if the Google Form, script, and formula worked together in order to create an effective entry process for the Academic Resource Center.
My Process..
- I created the Google Form (Before creating the form, I recommend brainstorming what information you will need in order to perform the mail merge. For example, I knew that I needed the student's ID number, for the CSV lookup, and their purpose for attending. I also needed the teachers' email addresses because our administration wanted to send teachers emails based on their students' participation. This is important as you run the script and compose your email within the script)
- Next, I chose a destination/new spreadsheet for the responses.
- In the response spreadsheet, I then added a new sheet at the bottom and copy and pasted all of the student data I received.
- Back in the sheet with my Google Form responses, I added a new column that I labeled "Parent/Guardian Email CSV" and typed in the query formula. (Here is an example of the query formula for my spreadsheet: =query(Sheet2!A:E;"Select E WHERE A ="&C2&"") - This formula asks to pull information from Sheet 2 - the student data - and it directly tells it what cell to pull from based on the student ID number)
- I installed the Formmule script from the script gallery and completed the necessary steps in order to set up the script to automatically send an email to the parent/guardian (based on the query lookup formula), the student, and the student's teacher. **I included a link to the Formmule instructional video I used in order to complete the Formmule process at the end of this post.
- Due to my query formula, I had to use an advanced option of the Formmule script in order to copy down my formulas as students pressed the submit button. (This was my biggest headache, as I couldn't figure out why it wasn't working. I needed this advanced option in order to send an email to the parent/guardian since I was looking it up on a different sheet.)
The Outcome...
Numerous attempts....some frustrations....modifications of the form, spreadsheet, and formula...
However....
IT WORKED! And it worked just how I wanted it to: A student walks into the Academic Resource Center, completes the brief Google Form, presses submit, and the parent/guardian, teacher, and student receive an email almost instantaneously that the student has attended the Academic Resource Center in order to get help in ____ subject. The script allowed me to create my own message to include in the body of the email, and it also allowed me to personalize the email based on the tags from the student responses; therefore, the email included the student's name and the subject he/she received help in during the Academic Resource Session.
This process reminded me of solving a puzzle: it was insightful, yet frustrating...but when it finally worked, all of the prep work was worth it. I continue to be impressed and amazed with the power of Google, Google Scripts, and formulas.
Looking forward to my next puzzle and learning more about the power of technology!
Click here for an Instructional Video from Youtube about using Formmule
