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.

Raspberry Pi

Quote:

Will it fit in an Altoids tin?

What is Raspberry Pi?

I had to look it up since I had only heard the name in passing and didn't quite remember the context.

Raspberry Pi is a computer the size of a credit card (and almost as cheap!).  It's made to use in education and will do most basic tasks like word processing and spreadsheets and some graphics. It runs at 300MHz (Pentium 2) but can be overclocked to 800.  For $40, were you expecting alienware?

It's made to run Linux booting off of an SD card, but once it's booted you can run off of an external USB drive. So the possibility is to get a huge solid state drive (large capacity, not large size) and put whatever OS and software you need.  I wonder if I could just get Chrome on there for the google apps?

Here's the link to the FAQ page I read.  All those extra possibilities are what would kick the price up, but still not a huge price tag, especially if you hook it to the TV through the onboard HDMI port.

Thanks to @alicekeeler for the tweet on the sweet deal.  I think I might pick one up just to see how it works and how well it works.  I kind of wish +Ken Daniels and I had seen this a year ago when we were budgeting for computers.

This reminds me of building Heath kit radios on the porch with my dad.  This also reminds me of building my own computers in college.  I managed to build one for $400 and it lasted all the way to 2004!  I bet I could beat that price (and probably the age) with the Raspberry Pi.

It seems like there's an endless variety of ways to customize this computer.  There are different cases, you can add stuff using a USB port, people are adding mini keyboards, etc.

In my classroom, I could hook it up to the unused TV (I use the projector) and connect it to the wifi.  Although, I'm not sure if the IT people at school will let me hook it up to the network.

Finally, no, it does not appear to fit in the Altoids tin. :(  At least, not without specialized modification.

Update:
I just put together an amazon wish list of some items to build a neat little one, and it totaled well under $300, closer to 250, so possibly competitive with chromebook on price depending on how hard you shop for components.  I'm sure there are better deals out there on specific parts than Amazon offers.  I bet if I spent some time on it, I could squeak under 200 and still have some good functionality.  Now just to learn how to program it and what it could be used for beyond word processing.