Using formRanger to update a Parent-Teacher Conference Scheduler

It is that time of the year. What time is that do you ask? Pumpkin Spice Latte time is what my Inbox is telling me from Starbucks...unfortunately they do not have the Pumpkin Spice Latte in Singapore so I need to move on and think of better times...like Parent-Teacher Conferences!

There is no better time to use the power of Google Automagic (to steal a line from +Jay Atwood ) in my mind than for scheduling Parent-Teacher Conferences. I hate the email exchange to find a common time because it never seems to work and takes forever to setup. So I spent my time instead creating a workflow that uses vlookup magic along with a Google Form. It went well last year, but the only bad part was that it would not eliminate a time choice when it was full.
Now we can programatically and scriptomagically change the question choices in a form using +Andrew Stillman's awesome formRanger script.
You can check out +Jay Atwood's great tutorial on formRanger here on Google+. It solves the problem of parents choosing a time slot that is no longer available.

Here's how:

Here is a template where you can make a copy: Get a copy of my template

I create a spreadsheet that has a list of my teacher times available:

This will later be used to create the schedule for the teacher to look at to see who they will be meeting.

Then I use the Concatenate formula to create all of the possible teacher times in 1 column in another tab in the sheet (column J in the picture below):

 In the next column (K) I use the vlookup formula:

=if(isna(vlookup(J2,'Raw Data'!$C$2:$C512,1,false))=TRUE, " ", vlookup(J2,'Raw Data'!$C$2:$D$521,2,false))

Essentially, it looks in the form responses for the Timeslot and teacher choice. If the teacher hasn't been chosen yet, it leaves Column K blank, " ", If it has been chosen, it will paste the name of the person who chose that time slot (which comes from the next question on the form.

The picture below shows this change:

In Column L, I use vlookup again:

=if(isna(vlookup(J3,'Raw Data'!$C$2:$C$520,1,false))=TRUE,J3," ")

 Essentially it looks in column K to see if it is blank. If so, then I copy the timeslot from Column J and it is allowed to be a choice on the form. If not, then I make it blank, " ".

Then, I clean up the form choices. If I don't do anything the dropdown choices on the form questions will look like swiss cheese with all the blanks where the choices have already been selected. 

So, I clean it up by using the Unique formula to eliminate all but 1 blank in Column M:

=UNIQUE(L2:L49)

Now, use formRanger to push Column M out to the form question and set your time trigger to refresh the form on form submission and it will run automagically!

Now that the form is working, I make my schedule back on the tab that it all started with and pasted this formula under the teacher heading at the times specified:

=if(isna(vlookup(concatenate(B7," ",$C$5),'LC1 TimesAvailable'!$J$2:$K$193,1,false))=TRUE, " ", vlookup(concatenate(B7," ",$C$5),'LC1 TimesAvailable'!$J$2:$K$193,2,false))

In the picture below that formula found that Mr. McGowan Testing chose time 8:25 for Teacher 1 in the form and it pasted it in there.

This fully completes the cycle. I use formula references so I just enter the Teacher Name in the Schedule tab and call those to populate the data for the form entries. 

Things I would like to improve:

1. Make a script to create a popup window with instructions

2. Create a google calendar to add events and publish the calendar with updates with each event

3. Create a script that will populate the template with the appropriate amount of teachers and the requisite data.

4. Somehow automate adding the columns to the formRanger script for questions, this might be a script or a beefier version of formRanger??

Any other suggestions/ideas, ask me on my Google+ page: gplus.to/MrMath or on twitter: @jmacattak.