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.

Class Poem on Padlet

Just recently, while attending the Fall CUE conference at American Canyon High School, I learned about a neat tech tool called padlet.  This website allows a teacher to create a digital posterboard for the classroom.  This way, your poster (created by you or, in this case by my students) can stay forever as part of their digital portfolio instead of disappearing from my limited wall space during the next unit.

I'm pleased to share what my class came up with today as part of our unit on Cultural Identity.

Our Class Poem

Enjoy.

#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.

Donor's Choose Project for Mr. Hyland's Class -- Update!

Exciting Opportunity! 

My Donor's Choose project, Classroom Computers for Common Core Language Arts, is eligible for a three-day-only offer from the Bill & Melinda Gates Foundation. To celebrate teachers and the amazing work we do, nearly all projects will be half-off through August 24.


Please donate if you are able, or share with people you know who might be interested in supporting my class and our students:

• The half-off match will last for three days, starting August 22 and ending August 24 at 11:59 PM Eastern Time. To receive funding from the Bill & Melinda Gates Foundation, other donors must complete 50% of your project funding during that time.
  
• Funding from the Bill & Melinda Gates Foundation isn't guaranteed.Funds will only be applied to your project if other donors complete the rest before the offer ends on August 24.

Here is a short link (also above) to my project page http://goo.gl/2hfQWo

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.

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.

Creating an Automatic Emailer from a Form Submission

I'm very happy that I could impress myself and solve a little problem at the same time: Raising two kids requires large amounts of communication and more time than is in the day.  I put together a little Google Apps Script (taken mostly from tutorials and Codecademy.com) that will save me time and allow some speedy communication of important kid-facts.
The problem was simple and solvable in several low-tech ways--some less clunky than others--however, this high-tech method is transferrable to education (hence my post on this blog).

I believe I could have used something like the autocrat script to get the same result.  But the way I did it allowed me to learn some javascript code.

Here's the original problem: I have two kids, so I look after one in the morning and my wife looks after the other.  She needs to know what I took care of in the morning and at night with that kid so nothing important gets missed each day.  This is pretty complex depending on how sick or fussy or tired each child (and each parent!) is that day.  Asking single questions for each topic took up too much time.

I solved this using a google form, reachable from my phone, and a script that emails both my wife and I when I submit the form.  The email displays a simple table with information on what I did with our child that day.

Below I've attached the code. If anyone knows how to pull the headers off of a Google Sheet and turn them into an array in javascript, that would be useful (in the "Properties" variable in line 5).

This can be used in education very simply if you have a tracking form that students fill out, it can email the whole team and/or mom and dad to inform how often a student is submitting the form.  For example, if they are coming in for after school tutoring and you need to track and document that.  I can see some ways to use this type of thing for back to school night with parent meeting requests as well.  Another functionality that Google allows is to trigger the script (i.e. send the email) on a time trigger (e.g. once a day) or on edit if the spreadsheet is edited.  This could be useful for collaborative writing projects in an English class or as a summary email for a project where you are tracking some kind of form-submitted data.  Basically, anything you used paper for 15 years ago you could collect frequency data on and get an email.  I could think of how to use this type of script in a small business as well.

Autocrat is, of course, a much more robust script that could be used more flexibly.  With this project, I was able to customize it for my uses and pick fancy colors for my email, etc.  Someone with more time than me (i.e. the website club at school) could expand this short code easily and add in some graphics to support the school. That would make a pretty neat looking response email.


Here's the code:
//Thanks to this code: https://gist.github.com/anonymous/4960553
function OnSubmit(e) {
 
  var ownEmail = "your@email.com"
  var NewSubmission = {}
  var Properties = ["DateTime","Time of Day","Data1","Data2","Data3","Data4","etc."];

  for(var i in Properties){
    NewSubmission[Properties[i]] = e.values[i];
  }

  var table = "<table style=\"border:2px solid black;width:500\"><thead><tr><th colspan=\"2\">What did I do today?</th></tr></thead><tbody>";
  var j = 0;
  for(var Properties in NewSubmission){
    j=j+1;
    if (j % 2 == 0) {
      table += "<tr  style='background-color:orange'><td>" + Properties + "</td><td>" + NewSubmission[Properties] + "</td><tr>";
    } else {
      table += "<tr style='background-color:black'><td style='color:white'>" + Properties + "</td><td style='color:white'>" + NewSubmission[Properties] + "</td><tr>";
    }
  }
  table += "</tbody></table>"
  var emailText = "Oh hey there!" + "<br/>" + "Here's what we did this " + NewSubmission["Time of Day"] + ":" + "<br/>" + table + "<br/>" + "<br/>Love from FormBot"
  var advancedArgs = {htmlBody:emailText};
  MailApp.sendEmail(ownEmail, "Daily Digest",
                  "",advancedArgs);
  MailApp.sendEmail("another@email.com", "Daily Digest",
                  "",advancedArgs);
}

Setting up the Room!

This is a room in progress:

East Wall

Here are some pics of what the room looked like in mid-build.  I went in to campus yesterday to clean a little and move the furniture around so it looked more like a classroom.  As you can see, it still has a long way to go.  It could use some additions to the walls, although I've made the desks look a lot nicer than these pictures show.  Also, I could use some butcher paper on the pegboards.  Following this time-consuming work, I need to go get the pacing guides and curriculum maps for English 10 and Algebra 1.  Hope to see some eager faces on August 15th!  

West Wall

We have a small space and I hope that the way I moved the bookshelves and cabinet around from the last teacher will open up the space, because I really don't like cramped rooms!

North Wall

This setup will be cozy, but there's a lot to work with and I've been promised a few student computers along the side there (West Wall).
I can't wait to get some bulletin boards up and start planning some lessons.
I'm really looking forward to this new year.