More thoughts on automation

As I sat in the allergy office and watched the nurse enter data into my record, I immediately realized another use for autocrat in my daily work. Any time a task is repeated in the same way, I should be able to get a machine to do it. The trick is getting the information into a format the machine can use and then into a format the humans can make sense of. I might have solved half of those problems while waiting for the doctor to come.

In my job we give formal academic testing to each student every third year. There are a number of different sub-tests and even different main tests that the district likes to use. All of the tests and scoring programs I've used will spit out an electronic report in either (or both) a Word document or a rich text file (that's the .rtf extension).  I need to learn how to get a machine to parse through the text file, convert the standard scores from tab delimited text into spreadsheet data, and then run an autocrat-style mail-merge into the generic report template.  The spreadsheet could also talk to a student data information system (info dumped from SEIS?) and use the student's unique ID number to look up gender and then put in the correct gender pronoun in the tedious parts of the report--replacing the "his" for "her" is always difficult or at least time consuming.

I would leave the summary and recommendations up to the report writer, so the final document that the computer spits out cannot yet be a pdf.  Autocrat can handle that, however.

I think this would be a highly useful tool for case managers who are interested.  One benefit is that not everyone has to use this system if they don't want to.  Also, it should be quick and easy (relatively) to set up and get working now that I know a little of what I'm doing with the scripts.  The first step might be the hardest for me--making the computer parse a text document and convert.

This little project will be on the back burner until around March 2014, I think, due to some meetings coming up . . .

Streamline the Paperwork to Save the Sanity of the Case Manager

My department chair asked a while ago for info on data collection using google forms.  I presume she has gotten pretty far in the process and that the data has started pouring in since the question today was how to display the results.  I have put up some screen shots below of how and where to click to get the automatic version of data summary that google provides for free.  Anything much beyond that will require a little more spreadsheet wizardry.  I'm getting out my robe and wizard hat now.

First, I'll show you how to create a form and then it will make more sense how to get information out of the response spreadsheet.

First, in your Drive account, click on "Create" and then "Form":

Simple enough.  Google will ask you to title the form and then you can create some questions.  There are some awesome tutorial videos on how best to use each type of google form question. +Melissa Hero has some good ideas if none spring to mind. (But then, if you had no ideas for how to apply these tools, you wouldn't be looking around for tutorials.)

Next, put in your questions.  You can also add in data validation, and many other neat tricks, no computer programming class required.

You can make questions that work from check boxes or drop down menus or radio buttons or large text boxes and many other types (e.g. date and/or time; although, the date and time questions display all funky when you pull them out of spreadsheets since it's stored as a time code of some sort so computers will read all the way down to the millisecond).

Google will automatically create a spreadsheet to collect all the responses.  Each column header is named for a question you created.  The far left column will always be a timestamp when the "submit" button was clicked.  This is useful as a paper trail.  I also learned a little trick to add a unique ID down one column.
The formula is this:
=arrayformula(Row(B:B))

I use the B column since Google steals the A column for the timestamp and the left hand column is the normal spot for a Key ID in (for example) a database.  With the arrayformula down the whole column, I can later look up any row of response data I want.  It's very neat.

Back to the summary of data!
You can get to the summary from the responses spreadsheet by clicking on Form menu and then Show Summary of Responses.

Alternatively, you can get to the summaries from the form itself (in edit mode, not the live form that users see) by clicking on Responses, then Summary of responses.








I put in some fake sample data:

Here is what the responses look like for my fake data:

That was cool.  I hope that helped and was actually what I was asked for.

Now for some real wizardry.  We have the technology, we can rebuild him, stronger, faster than before!
Using two google forms (one for Special Ed case managers and one for Gen Ed teachers to post feedback), we can replace the current paper system of giving and receiving IEP feedback.  That's not accurate, all mine are electronic right now.  I'm just using MS Office docs with forms to fill out which is only cumbersome when new versions of the software comes out and half the staff can not upgrade (public school budgets, etc.).

With a different revision of the current feedback system, I would insert a link into my MS Outlook calendar invites that go out to Gen Ed teachers. Here is the link to the sample email headed to Gen Ed teachers to invite them to the meeting.  Please make comments on it. Remember, this email is automatically generated and filled with the correct meeting info etc., so I don't have to write 26 individual invites for my individual students times 6 teachers each.  Microsoft allows you to do the same thing.  There are a number of advantages to google so the department could debate which they'd like to use.  I'm honestly ok with either as long as the whole department is doing the same thing.  I love uniformity, I think it makes us look professional to parents and teachers and I think the Gen Ed staff appreciates when we have our systems in place smoothly.  (Our current system is really good at VHS, I want to shed as much paper as possible and the little timestamp feature when Gen Ed teachers click submit is a nice perk!)

After teachers get that email, they click the link to the feedback form that would create the final pdf attached to the IEP in the computer.  Here's a sample feedback form.

There are many other uses for this type of functionality in a school system.

Thankful for a Great Day

I had a great day teaching last week and I have to share it.  This was one of those days when the students start off not getting it and each attempt you make falls flat on the floor two feet in front of you.

This is not the whole context, though.  I did have a pretty successful day for my first two classes with few interruptions, some learning, but little to write home about either.  My co-teach class is always more lively and fun--I'm trying to steal some ideas, I swear! But what made it a great teaching day was my 6th period Algebra 1 class.  Usually a challenging time for students to learn Algebra.
This day last week, my lesson presentation was falling pretty flat.  At one point, I reversed direction and taught a completely opposite method to solve the same type of problems and two of my students light bulbs went on.  They got excited and rushed through the rest of the classwork I had posted on the board.
It was a good feeling and one of the main reasons I love teaching.

A Week to Save 20 Minutes!

So, I have responded to feedback regarding my auto emailer (posted about earlier) by adding 4 lines of code that will automatically pull column headers from my spreadsheet.  First the code and then an explanation of what it does:

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var headers = sheet.getSheetValues(1,1,1,-1);
  var Properties = headers[0]

Since I'm new at this Javascript thing, it's highly possible that most people could accomplish the same thing with a shorter amount of code.  Also, I'm not even sure I need the first two lines.  If I understand it (doubtful), the first line gets the active sheet--my sheet, but it could be any sheet that the code is attached to, so now I can transport the code.  The second line gets the first sheet (the little tabs at the bottom of the spreadsheet, this is the difference between a "workbook" and a "sheet" in excel)--so just in case I have content on the second sheet, my script won't get confused.  The third line makes a two dimensional array object (I'm sure that's not the right term) with content starting from the 1st row, 1st column, 1 row deep, and all the way to the last column with content ( the -1 argument says "find the last column with content).  Because it's a nested array, I had to pull out the header content by setting Properties equal to the first element of the array.

In the previous version you'll notice that I had hand-written the elements in the Properties array.  What that meant was if I changed my headers on the spreadsheet--or really, the question titles on the google form that populates the spreadsheet--my script would still send an email with the original headers.  I would have to change them all by hand.  My new code makes the script pull the headers each time automatically, so if I change them between emails, it will automatically be correct on the next email.

This became necessary when my product tester requested some new data be collected with the form.  I'm too lazy to rewrite that long list of headers (I currently have like 25 on the live form!), so instead of spending 20 minutes retyping a list, I spent the better part of a week on w3schools and google script tutorials and other javascript sites learning what I needed.  "A week to save 20 minutes!" you say, but it was a week to save a 20 minutes that will with certainty be repeated.  The unknown is how often and how many times I'll have to repeat it.

My next task will be to make the same type of change for the if, then statement that currently has a "k < 12" parameter in it.  The 12 could easily turn into 13 if I change my questions again and I want the script to know when to cut off the data table in my email.

Behavior Data Collection

Well, the revolution has begun.  Quietly and a few teachers at a time.  I sent out a behavior tracking form (Google Form) for a student's Behavior Contract by using a shortened link from goo.gl . All of the teachers used the link on the first day to track behavior.  Not a single teacher asked for the paper form (I also offered that in case someone did not want to use the link).  It could have been a combination of the particular teachers this student has (they're more willing to use tech?) and/or the particular behaviors we're tracking (they're affecting grades in your class: use my form to get more support!).  I'm anxious to see if the participation level continues.

One neat thing is that I was able to provide instant feedback for the whole day to my school psych who is about to be a Google convert.

Another small move in the right direction was that another teacher and I in the department have made a de facto policy shift that should simplify everyone's lives and paperwork in the long run.  We, due to some rare moon alignment of circumstances, have the ability this semester to make a specific change to just under half of the students we serve.  This will be enough of a start that our co-workers will judge for themselves how effective the change is.

Sadly, I can't share my google form results publicly due to confidentiality.  When I get a chance, I will take a screen shot of the summary of results and scrub the names.
I hope that in the future we can use automatic methods for a big portion of our case management and data collection.