How to: Email your Teachers from a Form Submit

I lost about an hours worth of typing and pictures in this post, so I'm going to type up another quick version that won't be as cool.  Trust me, it was awesome.

+Ken Daniels asked me about making autoCrat (Thanks for this awesome tool, +Andrew Stillman and the New Visions School team), the awesome sheets add-on, email his teachers at his school.  I don't know his particular situation--when I set my version of this up, it was to invite teachers to a meeting based on a student's schedule.  So there was a lot of tables and looking up going on.

The easiest way is to have the user enter the email addresses into the form you are using to collect data, but I hate having users make mistakes and also having them manually look up and retype stuff that the computer should do for us.

Here's my solution:

AutoCrat will run off of a form submit now, so that's cool and that's how I'll use it here.  I like to make the form as simple as possible.  I've made a sample one that collects the teacher's name.  

You'll need a spreadsheet list of teacher email addresses to look up from and the names you get from the form submit should be exactly the same (that's another problem altogether).  Again, when I used this type of thing, there was a step in between where my form collected student data and then looked up teacher names and teacher emails based on that (student names are much less prone to errors in my experience since teachers are used to entering them exactly--or you could easily use the student id number which reduces potential for error a bunch).  Here's a sample.

In my email sheet, I've exported the complete addressbook from Outlook for my school site.  To help the look up, I've made the leftmost column into a list of the teacher names in the same format as the teacher names appear in our student database.  There's a couple ways to do that, I currently prefer the =JOIN() command until I learn a better method.

Then I made a named range from the columns.  Technically for this lookup, you only need two columns--however, you could also do this lookup from a much bigger table if you needed. The named range makes it easier to write the formula for looking up emails, and allows you to add or subtract staff members each year as people retire/get hired without having to go back and manually change every single formula.

When you connect a form to the sheet, it will create a new sheet.  As each response is listed, it will erase all data in the newest row.  This means that any neat formulas you have in the response collection tab will be deleted every single time someone submits a response.  The way to deal with this that I've found has been to make another sheet and use the =IMPORTRANGE() command.  Images below.  

Once the range is imported, it will auto-update with new submissions and it will allow you to make formulas in the columns to the right of your imported data.  Now we can lookup effectively.  

After importing the range, I make a column that includes teacher name data exactly as it appears in the email lookup sheet. See the =JOIN command below.  In the next colunn, I've used a =VLOOKUP command to lookup the perfectly formatted teacher name in our named range at column 6 (where the emails are sitting).

You can look up any of these commands with the google help file and it explains pretty clearly all the options and parameters for each.

I like to wrap my formulas with =IF(ISTEXT(), my current formula, "") so that I can copy down the formula to all cells in the column and it won't display errors and #N/A and be ugly.

Getting autoCrat to email is now simple, you can have it send an email to multiple recipients or whatever you want using the dollar tags (for example: $emailAddress ) as one of the options in that add on.  each time a response is submitted, autoCrat will run and fill in the email with whatever you tell it and send to all the dynamically changing emails that pertain to your current submission.  I find this to be a little bit of extra work, but it avoids the problem of hard-coding a bunch of emails that will eventually change.

I hope this is helpful to Ken and anyone else who might need it.  I know it's a down and dirty version, and it will help if you've got a mid-level familiarity with excel/spreadsheet functions. i.e., this "guide" is not very newbie friendly, but here it is.

I know if Ken needs anymore support on it, we'll end up with a GHO and I'll attempt to record that and link it here as well if we get there.

Batting Order Rotation

Thanks to this code that I borrowed, I was able to create a batting order rotator for my son's Tee Ball team.

You can see it in action here.

I can see how this might be useful for elementary classes that want to rotate line leader and class jobs frequently through the day etc. etc. and also possibly in high school for seating charts (although there's other programs for that--usually attached to your grade book program) or for lab work in science class and things of that nature.

It surely would have taken me 1000 years of study to get to those few lines of code in JavaScript to rotate an array (the ones in there with the % sign I think are the trick, if I understand what I'm looking at!).  I was doing it just fine in a spread sheet thanks to this video, however, pulling from a live spreadsheet and displaying on a web page was beyond my skills at the moment and seemed like too many steps.  Then I thought I could create an array pretty easily in JavaScript and it should be a sinch to essentially follow the same neat procedures from that youTube video to lookup the correct index for each new batter.

#FallCUE Day 1 Report

On the first day of #FallCUE this is what we learned:
American Canyon had insufficient bandwidth for the number of educators and their devices on campus this morning.  By the afternoon, most problems were corrected--either people had logged off or connected to their phone's hot spot. (note: this was fixed by Saturday, I never found out what the issue was)
Past that glitch in the beginning, there was much to learn.  We went to 5 sessions and burned our brains out on google forms, scripts like form Ranger, green screen videos in the classroom, using add-ons to give students feedback on their writing, building my class website and embedding videos in google forms.

I loaded up on plenty of apps, twitter people to follow, and the real winner of the day was the number of new methods for giving feedback on student writing.
I found out that google classroom is only for schools with GAFE so it's not even worth looking at until I convince someone at my school to advocate the powers that be for that situation.

Session 1 was a run down on automating your work flow with formRanger and formMule scripts (scripts by Andrew Stillman) presented by @John_Eick. Here's a link to his resources which included a step-by-step screen shot of how to set up your documents and sheets.  Very useful for a teacher who is new to this type of thing.  This was the session where the wifi was the worst and got better at every session following this.  John was a really energetic presenter and even I, a relative expert on Stillman's old scripts, learned a few tricks to use in practice.

For session 2 and following, +Peter Hyland  and I split up to hit more sessions and because he had different goals for the PD since his classroom situation with technology is vastly different than mine.  I went to John Eick's next session on embedding videos in google forms and Peter went to the Hour of Awesome.
Embedding videos looks like a sweet way to have students take notes while watching a short video, then the teacher is able to quickly look at the notes and give some feedback on the quality of notes, etc. etc.  There are about 3 uses I thought of in my classroom and the only limitation would be technology access.  Could address that by "flipping" the model" and having those that are able do the video notes at home and others do it in class.  That method would require some planning for the kids not actively taking notes in the classroom.
Peter said the Hour of Awesome was awesome and his notes make it sound exciting. Ask me for the link and I'll share the notes with you.  It covered multiple Google products and a few of the case uses for each that could speed up repetitive teacher tasks and improve the speed of student feedback.

The theme of Day 1 was faster feedback to students.  The teacher has to do a little more time consuming prep and setup on the front end, however it's worth it so kids get a faster and better idea of how they're doing with anything from writing to math to biology notes.  Session 3 with +Kristina Mattis (@KristinaMattis)for me covered some add-ons in google docs that allowed collaboration, composition, editing, and publishing of student work with more modes than just text.  Session 4 with +Cate Tolnai (@CateTolnai) showed me some template examples of how to organize my course content around my class website.  This will be most useful to me this year and next.  Finally, I visited the green screen session and got another round of figuring out how to make some videos and--even more importantly--how to use this type of project in my classes to meet standards.

Overall, an awesome day for information.  The spotty wifi and long lunch line were not enough to dampen the good things soaking in to my brain.
More reports later.

Case Management Documentation and GAS Part 3.5

Part of being a Special Education Teacher is giving  parents progress updates on goals at least as often as their non-disabled peers.  At high school, that means once per quarter.  So doing goal updates this week and next has put my side project on break for a minute.  In spite of that, I was able to solve one small problem with my script.  I was able to get the script to take the IEP date information (from whatever the case manager puts in) and have it spit out a pretty and formatted text string (instead of a weird date code that computers read!). This solves a little problem I was having at the tail end where the printout would show a long date string including time--which I didn't want on that piece of text.

As an aside, goal updates are another beast that might benefit from some automation during certain parts of that process.  That's a long term thought I'm letting simmer and cook on the back burner.

Keyboard Shortcuts for Google Docs

I continually have to look these up, so I'm holding the link here on this site.
I find the help button in Docs does not have all the shortcuts I'm looking for, most notably the "insert comment" shortcut key which is "command+option+m".   Less clicking when I get my students using Docs for their writing. :)

This will become important if I ever get my next task off the ground: daily warm ups using Google Docs which should get some form of feedback (i.e. comments) from me (perhaps on a weekly basis. . .).

Link to the shortcuts

Sorry, no pics for this one.

Class Feedback Results

To follow Larry Ferlazzo's example, I'm posting my class feedback results from my end of year suvey. I'll put the questions followed by the highest percentage rating, just to save typing since I don't want to take the time to screen shot the charts and graphs that google forms made for me.

 I didn't split these by class period or anything, although I think for next year I'll take the trouble to make a different form for separate classes to get a better idea. This will be really important for me next year since I'll be co-teaching with two different teachers and teaching 4 different subjects. I'm excited for the challenge.

• I felt challenged by this class: 41% neutral
• I felt like Mr. Hyland respected me: 46% agree
• I felt like Mr. Hyland gave me useful feedback: 70% agree
• I felt like Mr. Hyland was fair: 63% agree
• Mr. Hyland had high expectations of me: 50% agree
• I tried my hardest to do my best in class: 37% each (tie) strongly agree and agree
• My grades reflected the quality of my work: 52% agree
• Mr. Hyland encouraged me to do my best: 43% agree
• I felt safe in Mr. Hyland's class: 54% agree
• The class was well organized: 59% agree
• I enjoyed being in this class: 41% agree
• I always followed Mr. Hyland's instructions: 41% neutral

 I also had 5 short answer questions where students could type a response. Some did not, and others made what seemed like sarcastic comments (hard to tell if they were serious). However, there are some gold nuggets that told me what I needed to hear from students (that's the point after all!). Many of these comments referred to our penultimate activity--watching Star Wars after a series of lessons on the Hero's Journey, so if you follow the link below and scan those comments, you'll note some fanboys.

 I also co-teach one period, so I included my co-teacher and co-teach class on the questionnaire. You'll see her name in there as well. Here's the link to my full results, and--as un-scientific as they are--I'm proud of them this year. I always have plenty of room to improve. My goals for next year include inching forward on tech integration in my classroom lessons.

More Google products for educational uses.

I enjoy the ability of Google+ to send me posts that I can really engage with.  Of course, I have connected with some fun things and games for enjoyment on Google+.  So I get a lot of that in my feed which is nice so that I can decompress from work.  I have also added a good number of education related groups and ed-tech groups.  Sometimes I flip past those posts if I want to avoid melting my brain from too much work-thought.

Today, a post grabbed my attention because it was asking for a solution that I know how to provide.  I left a short comment, knowing that I also wanted to post here on the topic.  The poster, +Jennifer Fox, asked for some follow up so here's what I put together--I hope it was what she was looking for:

Original problem:  "Is there a way to sort responses from a Google Form as they are being gathered on the response sheet?"
My response: Use either VLOOKUP or IMPORTRANGE functions to copy the data to a second sheet and filter/sort that sheet.  Then the original collection sheet continues to get live responses and you can still organize your data.
My example:  I created a sample google form to collect fake data with numbers and letters, i.e. something to sort multiple ways.  Then I went to the spreadsheet where all the responses were collected and created a second sheet (named "The Magic").  On the second sheet, I used the importrange function to copy the data and then I selected all three columns of data (this makes sure that when you filter, the rows of data stay together) and applied a filter.  Now I can sort and filter the data and new data from the form automatically comes in whenever the users complete the form.
I also got to learn a little from the other comments in the thread--specifically, that there is an SQL-style query function in google sheets.
So, that was a win all around!