Tuesday, April 23, 2013

Creating a Parent Teacher Conference Scheduler using Google Spreadsheets and Scripts

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.

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
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: Template
2. 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.


Friday, April 19, 2013

gClassFolders is awesome!

**This post has been sitting in my draft folder for about 2 months, so I finally just published it...***
So Andrew Stillman +Andrew Stillman is a GoogleApps Script genious. He has collaborated with Bjorn Behrendt to create the ultimate workflow for Google Apps for Edu (GAFE). As I previously posted, I love using formEmailer. I have extended the Scripts capabilities to utilize more powerful aspects in my math class integrations.

Here is a running diary (a la SportsGuy on ESPN: Example here.) of my integration of gClassFolders version 2.0.

Friday, February 15
See this tweet from @astillman:

Super excited about the new sharing of folders for student assignments, so I click on the link to get the Script!

Checked out the documentation here and realized how powerful the integration will be. I have been using various workflows to organize my student data along with sending them personalized assignments and graded rubrics (more to come in future posts).

Clicked on the link to "Make a copy of gClassFolders2.0"

Refreshed the browser to make the gClassFolders custom tab show up.

Used the pushData console to push the student data into the gClassRoster tab from my Master Student List Spreadsheet.

Here is a great video that shows a lot of the capabilities of Google Scripts in school capacities. pushData can be found by searching the Tools->Script Gallery
Realized that using pushData would not format the data that is pushed in the correct columns due to the way I had them formatted in the Student Master List sheet. So, I reorganized the columns to match up with the gClassFolders headers.

Finished reorganized header data in source spreadsheet and pushData into gClassFolders2.0!

Installed gClassFolders2.0 and created my folders for 117 different students!!

UWC Middle School Conference Workshop Motivation

One of the main things I try to work on in my teaching is making technology do the heavy lifting for me. I like to have all of my students be challenged for many reasons (like keeping on-task behavior), but the over-riding reason is I don't want students to lose their love of math.

Once I move out of their ZPD, students will lose focus. No matter how streamed (or leveled, or tracked, or whatever term you use to separate classes by student ability level) your classes might be, there are still going to be students on each end of the performance spectrum. I want to keep those students engaged and challenged (which are not mutually exclusive in my opinion).

Throughout my teaching career, I have developed a few quirks that shape my legacy (so I don't think I have a massive legacy, but if any of my former students got together these quirks would be the things they would all talk about). I have always dreamed of a time when every homework assignment I give is in the form of a math problem, where the solution is the homework problems the students need to do. You know, do math to figure out what your math homework is!

This is time consuming and challenging depending on the course that I teach and I have never gone full bore with it and used it consistently (even though I talk about it every year in every class, I can never get around to it...maybe next year!)

What I have started doing is giving my students a problem they need to do for homework, where the solution is the seat they need to sit in during the next class. (If anything, I figure 1 problem is low enough that I should get 100% participation.)
My classroom has 24 desks (I know great factors to come up with different "pod" (my wording for group) sizes). I usually do pods of 2 or pods of 4 depending on the assignments. So, my solutions are 1 through 12 (each pod number having 2 seats associated with it) if I have pods of 2, or the numbers 1 through 6 (each pod number having 4 seats associated with it). Sometimes, I will use 0-5 or 0-11 since I believe 0 is a natural number and want to indoctrinate the students early on it :)

I don't have the time or willpower to create 24 different problems each night for each of my 6 classes that are relevant to the content for each class (another trait that I try to use). I figured out a way to have Google Apps leverage in my favor.

UWC Middle School Conference Workshop Goals

Here are the goals we want to accomplish in today's Workshop:

1. You should be able to leave the Workshop with Google Drive folder structure for your classes. Bringing class lists (preferably in spreadsheet form so we can copy and paste it) and your favorite digital device.

2. You will be able to send a custom email message from a spreadsheet.

Here is a brief outline of what I will be discussing:

  • Learn about Google Apps For Education (GAFE) Resources
    • The Templates I use
  • Examples of how I used GAFE to differentiate in my G6-9 math classes
    • Seat Problem
  • The power of using Google Spreadsheets
    • Create Random (or if you like not so Random) Group Allocations of different sizes
    • Custom Email Mail Merge using FormEmailer
  • gClassFolders2.0 is an amazing tool
    • Automatically setup Google Drive folders for each class
      • 3 types of folders created: All class view, All class edit, Individual folders
      • Differentiation with gClassFolders
  • Working Time!
  • My favorite resources
    • Script Resources
    • Google+ page
    • Blogs and Twitter
  • Google Apps Certified Teacher Program
Bonus Info:
  • Learn how you can have an unlimited number of email addresses easily come to your Inbox.
  • Learn how you can send emails from a cool sender name.
  • How to use FormEmailer to automatically grade assessments!!
  • FormEmailer to create Parent Teacher Conference Scheduler
  • FormEmailer to create assessment reports based on a rubric (or student comments for grade reports based on a rubric)