Missing Assignments
In this project we are setting up a system to enable teachers to easily notify parents when students are missing assignments.
In this project we are setting up a system to enable teachers to easily notify parents when students are missing assignments.
- The teacher uses a simple form to submit a list of all assignments
- Another form is used by the teacher to submit students missing assignments
- One spreadsheet is used to collect the form submissions from both forms
- On the spreadsheet the teacher has a Contacts sheet with the student names, student email, parent email, and resource teacher emails if needed
- On the Missing Assignment form submission page, we use some formulas to pull in the information from the contact sheet and the assignment sheet.
- The FormRanger add-on is used to auto build the list of students, assignments, and reasons for missing onto the missing assignment form, to save the teacher time in maintaining this form.
- The FormMule add-on is used to email however the teacher wants, and is customizable to allow for different email templates for the student, parent, and resource teachers if needed. The email can be formatted however one wants so that it looks very professional. It takes very little of the teachers time to report these missing assignments once the system is all setup.
This is a sample of the email that a parent, student, or resource teacher will automatically receive as soon as the teacher fills out the missing assignment form.
This is a sample of the email that a parent, student, or resource teacher will automatically receive as soon as the teacher fills out the missing assignment form.
I have used FormMule and some HTML tags <b></b> to make this look nice. FormMule pulls in the data from the sheet, including the data that is merged using the VLOOKUP formulas to enable the system to work and email parents on form submission.
This is the form a teacher uses to auto add assignments to the ASSIGNMENTS MISSING list on the Missing Assignments form.
This is the form a teacher uses to auto add assignments to the ASSIGNMENTS MISSING list on the Missing Assignments form.
On the Missing assignments form, the Student Name, Assignments Missing, and Reason are all auto updated by FormRanger as the teacher adds students, assignments, and reasons to the sheets.
On the Missing assignments form, the Student Name, Assignments Missing, and Reason are all auto updated by FormRanger as the teacher adds students, assignments, and reasons to the sheets.
Here are the formulas that are used in the spreadsheet to make all this happen. The first Query is used to build the assignmentlist sheet and sort the assignments that the teacher submits with the assignment form into an alphabetical list of assignments sorted by title. One could also sort instead by Due Date. Teacher can decide.
Here are the formulas that are used in the spreadsheet to make all this happen. The first Query is used to build the assignmentlist sheet and sort the assignments that the teacher submits with the assignment form into an alphabetical list of assignments sorted by title. One could also sort instead by Due Date. Teacher can decide.
The next three formulas are all added to the Missing Assignment Form Submission sheet in order to bring in the Parent Email which comes from the contacts sheet, the assignment description, and the due date, which come from the assignmentlist sheet. The last formula needs the added TEXT formula to force the due date to a readable date instead of a numeric string.
The next three formulas are all added to the Missing Assignment Form Submission sheet in order to bring in the Parent Email which comes from the contacts sheet, the assignment description, and the due date, which come from the assignmentlist sheet. The last formula needs the added TEXT formula to force the due date to a readable date instead of a numeric string.
=QUERY(assignments!A:D, "select A,C,B,D order by C")={"Parent Email"; ArrayFormula(IF(LEN(B2:B)>0,(VLOOKUP(B2:B,contacts!B:G,5,0)),))}={"Description"; ArrayFormula(IF(LEN(B2:B)>0,(VLOOKUP(C2:C,assignmentlist!B:D,3,0)),))}={"Due Date"; ArrayFormula(IF(LEN(B2:B)>0, TEXT((VLOOKUP(C2:C,assignmentlist!B2:C,2,0)),"MM/DD/YYYY"),))}This is the sheet that I use to create all this. Feel free to make a copy.
This is the sheet that I use to create all this. Feel free to make a copy.
