As I have stated earlier on my site, I love using Google Scripts. I created a workflow using Google Forms and Spreadsheets to create a Parent Teacher Scheduling Tool.
The main engine of the scheduler is the FormEmailer Script and its ability to automatically run (a huge shout out to Henrique Abreu who developed the script!). Here are the steps I used to deploy the scheduler to the parent community.
2. Click on File->Make a Copy to get a copy of your own so you can Edit the spreadsheet.
The main engine of the scheduler is the FormEmailer Script and its ability to automatically run (a huge shout out to Henrique Abreu who developed the script!). Here are the steps I used to deploy the scheduler to the parent community.
Resources needed:
My Google Spreadsheet Template (with attached Form): Spreadsheet Template
My Google Site Template to embed Published Sheet and Form: Site Template
Short version of how I do it:
1. For each teacher, make a copy of the Template.- Change the times that teacher is available in the Scheduled Times Availability sheet
- Edit the Form to reflect these times
- Go into the Script Editor and set the Trigger to have the FormEmailer Script run automatically (only need to do this 1 time)
- Publish the Scheduled Times Availability sheet to the Web. Copy the link to it.
- Go to a Google Site and Paste the link and then Embed the Google Form below it.
- Repeat for the next teacher
2. If you only wanted to have 1 schedule with all of your teachers, you could just put:
Teacher Name, Wednesday, April 24 3:30-4:30
Teacher Name, Wednesday, April 24 3:30-4:30
instead of
Wednesday, April 24 3:30-4:30
It won't affect how the tool works as long as you put the EXACT choice in the form (I copy and paste to eliminate any typos or spaces missing).
More detailed explanation of how I do it:
1. Click on the link to get my template: Template2. Click on File->Make a Copy to get a copy of your own so you can Edit the spreadsheet.
3. Now you need to figure out the timeslots you want to be available for the conference. Go to the Schedule Times Availability sheet by clicking on that tab on the bottom of the sheet.
4. To change or add a timeslot, just type it into a cell on the Scheduled Times Availability sheet. You will need to copy the formula from cell B2 and enter it to the cell to the right of any time slot you add. This formula is what fills the timeslots with the parent's name:
=if(isna(vlookup(A2,'Raw Data'!$G$2:$H$90,2,false))=TRUE," ",vlookup(A2,'Raw Data'!$G$2:$H$90,2,false))
Essentially this formula looks at what is typed in the cell directly to its left. It then goes to the Raw Data sheet and looks for a match (the chosen timeslot, which can be found in column G on the Raw Data sheet) and copies from the Raw Data sheet the name of the person who requested that slot (the entry in column H).
It will only find the first instance of this time and will not write over it if a person requests it at a later point in time.
5. Now we want to add the timeslot choice into the Form so a parent can choose it. Click on
Form->Edit Form.
6. For the 1st question on the Form, where all of the time slots to choose from, enter all of the times from the Scheduled Times Availability sheet. The nice part of the new version of Google Forms is that it allows you to copy and paste a list from a spreadsheet to enter as values for the Choose From a List question type.
I create all of the available timeslots first and then just copy and paste them in.
7. If you want to collect any other data from your parents you can enter them as questions on the form and they will fill into the Raw Data sheet.
8. After you finish editing the Form you can close it and return back to your spreadsheet.
9. Everytime you copy the spreadsheet you need to enable the FormEmailer script to run. You can do this by clicking on FormEmailer->Settings:
The popup window will ask you to Authorize the script:
Once you click OK, you will be able to run the script.
10. Now that the script is live, you want to make it run automatically. The best way to do this is by using a Trigger. You can access the trigger by clicking on Tools->Script Editor to go to the script editor
From there, you click on Resources->Current Project's Triggers
You want to "Add a new Trigger"
You want to add a Time Driven trigger with a Minutes Timer that runs Every Minute.
I also like to add a notifications email in case anything goes wrong, it will email me Immediately.
Now your script will automatically run every minute!
11. What will the script do? It will send an Email that you will create in the FormEmailer->Setting tab that will fill in the email with data from the spreadsheet. More importantly, it will copy down a formula from Row Data rows G:I from the formulas typed in Row 2 in those columns. You can change this in the FormEmailer->Settings->Advanced tab if you would like:
In my template, I have a formula in G2 that just copies what the desired timeslot is for the form entry from the parent:
The formula in H2 is the Parent's Name:
And as a precaution, the formula in I2 is the order in which the parent submitted their form in case there are any discrepancies.
These formulas are essential to the tool running successfully since they are the columns that the Time Sheet looks into to fill the time slots.
12. The FormEmailer will automatically run and looks for the FormEmailer Status. If the Status is empty the script will run on that row data, so if you need to re-email a parent just delete the contents in that cell (only the FormEmailer status cell in that row) and it will re-run.
Now, your tool is ready to deploy. It should collect data from the Form and populate the Raw Data sheet. The FormEmailer Script will automatically run every minute on any row data where the Status is empty. When it runs, it will copy down the formula from G2, H2, and I2 into that Row. Then at the same time, the formulas on the Scheduled Times Availability sheet will look for any new times chosen and enter the parents name into the Time Slot.
13. Create the email message to be sent. FormEmailer will send an email as it processes a row. To create the email message click on FormEmailer->Settings
If you want to add data from the row, use the dropdown tab in the upper right to choose the data you want to use and click on the placeholder next to the appropriate box.
14. Now that you are done we need to make a way for parents to see the Scheduled Times Availability and then choose a time on the Form. I used a google sites page, but you can embed both on any webpage using HTML.
The main part is to publish the Scheduled Times Availability sheet to the web. Click on File->Publish to web.
I have found that it is most effective to publish all sheets and not just a specific sheet, it will update more seamlessly.
Once published, Google will automatically republish when changes are made but only about every 3 or 4 minutes, not instantaneously. This is why it might take a few minutes for the full process of a parent submitting a response to a form, getting the email, and seeing their name reflected in the Timeslot they chose.
Once you publish the sheets, you need to get the html link to access the cells you want to publish. In this case it is only the Scheduled Times Availability Sheet.
In the template case, the only cells I need to show are from A1 to D18 (if you add more times you will need to change this to display the appropriate timeslots)
Then, copy and paste the link to see the published cells. You can just paste this link in any browser (or embed in any webpage with html).
15. Now you are done! Create a webpage that shows the Available Times and then I put the Form below for parents to choose their preferred time slot.
Here is a template site: Template Site
Have fun and please contact me if you have any questions.
.JPG)