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.

VPEF Grant Thank Yous from My Class

Typing our essays on student desktop,
Chromebook, and hand written.

Earlier this school year, I applied for and was granted some generous funds for my classroom.  We were able to acquire two more Chromebook laptops to bring my class's grand total of available student computers up to seven (or sometimes eight or nine depending on the old ones working properly that day!).  In all, we have five Chromebooks now and those are nice for the students to use due to their speed and since my district set me up with google Classroom this semester.  So overall, this grant has had a large impact on my classes.

Working on a Slide Presentation for
Speaking and Listening Standards

I asked my students to write thank you notes to the Vacaville Public Education Foundation which granted the funds.  It was not lost on them what kind of impact these added computers had for our class and I'm sharing a

Part of our class writing essays.

handful of their notes here. Here's one below, and links to some more from the class.


Here are some of the projects we worked on using these computers this year:

• A class Padlet page "Where I'm From" poem.
• A Slide  presentation for Speaking and Listening skills.
• Some shared brainstorming ideas, in preparation for our essays--here's one:

Starting my first google Classrooms.

For the writing assignments, I was able to use google Classroom thanks to +Dawn Marsh at the district office who set up my classes as part of the pilot this semester!

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.

Sci Fi book choices for 12th grade

About a year ago, I sent a request out for some Sci Fi books for 12th graders to read as part of their unit in short stories.  Here's a link to that post on a different blog which is worth taking a look at just for the few titles I collected in the comments. I've copied the post below:

J’s 12th grade co-teach class is going to do a unit on science fiction short stories and wanted a list of a few good ones. So I’m posting here to get some titles.
I suggested Arthur C. Clarke’s Nine Billion Names of God and Asimov’s Nightfall. I also gave some authors: Zelasny, Heinlein, L. Niven, a couple others.
Nightfall is hardly short at 300+ pages but it fits the genre in other ways.
Any other suggestions from folks?

It's that time of year again, so I've got a couple of students asking me for more suggestions. I emailed this same list to them and I'll again ask for suggestions this year so I can have a wider list every time I send it out.

Any more suggestions?  -- I guess it doesn't have to be a short story, but that is the unit even though the focus for their assignment is on the Sci Fi part, not necessarily the short story part.

Making Technology Run Smoothly

I'm pleased that my question via Twitter made it in to @TechsavvyEd 's #TechDirectorChat podcast segment last week!

Ben had asked for any questions from the gallery for a tech director. Being a lowly teacher and having had to deal with tech and tech departments, I know what I really wanted to ask and the way I might typically ask would not be productive for his venue.   When I find myself complaining about my classroom situations specifically, especially when I'm in professional development (or on a twitter chat), I then try to flip my negativity around and ask a question from the opposite direction.

So for my question for Ben's Director, Pete, I tweeted this:

@techsavvyed hey tech dir., if you could tell us teachers one important thing to make tech run smooth, what would it be?
— Andrew Hyland (@AndrewHyland) March 4, 2015

Pretty vague and open.  Ben and Pete both took a turn giving me an answer in his podcast.  Skip ahead to 13:00 and listen to how well Pete and Ben interpret my vague wording and both give useful answers to help make working with tech in education a little less of the nightmare it can be on the worst days.

Here's the Link.

Thanks +Ben Rimes  for the shout out and the thoughtful response.

For the record, my original intent on the question was the way Pete interpreted it--making sure stuff just works correctly between version numbers, power, internet, all those moving parts.  Ben's interpretation (tech integration into lessons) and answer were what I should have been asking!

Shut off Spell Checker in Docs

This I just found and it might be useful in two or three applications: You can shut off the spell checker in Google Docs. Most people are smarter than I am, and so probably already knew that.  Here's the quote from the help page: 

Spelling suggestions as you typeAs you type, Docs automatically underlines in misspelled words in red. Right-click an underlined word to see the suggested correction and replace the misspelled word.You can turn off the spell-checker by unchecking Show spelling suggestions from the Viewdrop-down menu

Click here for the full page.

There are two times I can use this:
1) During an everyday type activity when I want students to focus on composing volumes rather than editing and fretting over perfection.
2) During California Exit Exam proctoring when a very few number of my students have "use of a word processor with the spell checker turned off" written in as an accommodation during the testing.  Then I can have them sign in to a convenient Chromebook without disrupting the other testers or having to arrange a second adult proctor to escort them to a separate place with a clunky desktop that may or may not work, or possibly has an older version of Word etc. etc.

I hope this is useful for others too.

Testing out embedding a chart.

All the MLK resources you'll ever need from Larry Ferlazzo.

http://larryferlazzo.edublogs.org/2008/12/21/the-best-websites-about-martin-luther-king/