Crowdsourcing Appreciation

Every year, for teacher appreciation week, I like to send out a Google Form to our entire community and ask everyone to share some gratitude for our amazing staff. The form goes out in the weekly blogpost, I mention it in the all call, we may even slip it into a Facebook post or two. Every time we do this, we get roughly 1000 responses. Then we use some Google magic to mail merge the responses so that each staff member receives a single email with all the comments shared by our community.

Imagine recieving an email with crowd sourced comments from current and former families, all thanking you for what you have done in support of thier students! #Grattitude

If you'd like some step by step directions on how this is done, well, just keep on reading!

Sincerely,

John Eick



The Steps below will demonstrate how to collect 1000's of comments from the community and merge them automatically into emails that look like the one to the left.

Step 1: Build a Form with the names of all the people you would like to see celebrated.

Step 2: Launch the form into a community.

Step 3: Use some simple formulas to merge all the appreciation into one place.

Step 4: Mail merge the celebrations to email so that each person gets one email containing all the celebrations that were submitted on their behalf!




Click Here for a Sample Form
Click Here for a Sample Spreadsheet

Step 1: Build a Form with the names of all the people you would like to see celebrated.

The key to step one is to use either a Multiple Choice, or a Select from a List, and include all the staff members you want to celebrate. If you allow families to type in the name of the teacher, it will be hard to sort the data later on.

The rest of the form is simple, ask them for their name or family name, this way you can later merge their comment with who it is from. Next ask them to enter some appreciation into a paragraph box.

Step 2: Launch the form into a community.

Step two is the most fun part! Take the link for the Google Form and send it out to the community in every channel you have access to. I even like to send the link out to staff, so they can celebrate one another! Note: let people know when the form will close, and remind them to use the form as many times as possible. Once you are ready, shut the form off and begin steps 3 and 4.

Step 3: Use some simple formulas to merge all the appreciation into one place.

1. Merge comments and Signatures:

Since one column has the name of the family, and another column has the comment, it is fun to merge them together. This will give your email merge a very consistent look. Every comment will have a signature line.

In this example, column C has the family name and column D has the comments. So, we are going to merge them together in column E.

First we will label column E with a header, let's call it: Merging Signitures.

Then, in the first row in column E, we will use the following formula:

=D2&CHAR(10)&"- "&C2&CHAR(10)

Now if you are new to formulas, let me explain. In the first row, below the heading, in column E, we just wrote a formula that tells the sheet what to paste here.

• = sign tells the cell that we are starting a formula.

• D2 just tells the cell to paste whatever is in D2 into this cell. Well for us, D2 is the comment from the family.

• & tells the formula that we have another command coming, so it reads, paste the comment here AND ...

• Char(10) is the command to hit return. So it is going to Paste the comment, the hit return so that when we paste the signature it falls below the comment.

• & tells the formula that something is coming after we hit return.

• "- " says print whatever is in these quotes. Therefore, I am asking for a hyphen. So, we have our comment AND a carriage return AND a hyphen.

• C2 is the name of the cell with the signature, so, we have a comment AND a carraige return AND a Hyphen AND a signature.

Once you have the formula built, just drag the corner of the blue box down, and the formula will auto populate for all rows!

2. Prepare a Mail Merge Sheet:

In this step, you will leave the Response Sheet and start a new sheet where you will merge all the information before sending it out via email.

First, open a new tab on your spread sheet.

Next, Paste all the names of your teachers in Column A.

• You now have a sheet with all your names in Column A and nothing else on the sheet. Each staff member should only be named here once, just like on the form.

Next, since we will be emailing to each staff member,  place each staff member's email address in column B.

Final Step: The Big Merge

Now that you have the names and email addresses all in place, we will merge all comments to column C. The formula looks intense, but it is really useable, here we go:

Place the following formula in column C:

=join(CHAR(10), QUERY('Form Responses 1'!A:E, "select E where B contains'"&A3&"'", 0))

Ok, here is how the magic works:

• =join tells the cell that we are going to Join multiple things
• (CHAR(10),means we are entering a carriage return. This will put a space between each entry. The comma says that another command is coming.
• QUERY('Form Responses 1'!A:E, Tells the cell that we are running a query, or we are looking for something on the sheet named'Form Responses 1', in Columns A through E. So far we have said we are going to Join a space with something that we are looking for in Columns A-E on the response sheet.
• "select E where B contains'"&A3&"'", This is where the magic happens, this describes the Query: paste here the contents of Column E (this is our merged signature column), only when you see the teachers name in Column B that matches the teachers name here in Cell A2.
• ,0 tells this cell if you don't find a match for the name in A2, just do nothing.

Once you run this formula, it will Join a carriage return or a space, with each comment associated with the teacher name in A2.

Next, just grab that magic blue box again in the bottom left corner of the cell, and drag it down so that it pastes the formula on every row where you have a teacher name.

Step 4: Mail merge the celebrations to email so that each person gets one email containing all the celebrations that were submitted on their behalf!

To mail merge from this spreadsheet to email is simple. Just go to the top of the sheet, in the dropdown menu, select AddOns, then click Get Add Ons.

Search for the add on, Yet Another Mail Merge. 

Follow the prompts to add this Add On.

Next, click on the Add Ons drop down at the top of the sheet again, and select Yet Another Mail Merge from the list. Once you launch this Add On, you will be prompted, step by step to create the merge.

Video Support

Below is a video that might help if you get stuck on any of the steps above. Please feel free to use comments or reach out to me directly at johndavideick@gmail.com if you have any questions!

Math and Mindfulness

This past weekend, up in Roseville, California, an innovative and exciting new conference launched: The Math and Mindfulness Summit 2017. The whole gig was the brainchild of Carole Pryor and Marie Criste. For years these two Roseville Joint Union High School District all-stars have wanted to collaborate on a blend of their two passions: Carole is an #EduHero in the Math world and Marie is known far and wide for organizing the most engaging and well planned Professional Development shows in town.

The modern math scene has recently been set ablaze by the work of Jo Boaler and Carol Dweck. These two are revolutionizing the way we all see the math classroom, most notably, due to these two educational researchers, we are all hearing the call for a growth mindset in the classroom and marching to the mantra of, "banishing math anxiety and giving students a roadmap to success," as described in Boaler's book, Mathematical Mindsets. This work is clearly a piece of the catalyst that launched this conference.

Now, for a bit of brilliant pageantry, we introduce a sprinkle of flexible seating and 360 Math, as can only be described by the event's keynote speaker, Ed Campos. You can imagine how this whole thing came together. Upon the foundation of Jo Boaler's work, brought to life by amazing practitioners from across the region and kicked off by keynote speaker Ed Campos: it was like a mathematician's perfect storm.

So, there's the stage, it's set. After this last year of leading powerful PD around equity and access for RJUHSD, the dream team of Pyror and Criste launch the Math and Mindfulness Summit: a blend of that which creates anxiety, coupled with the antidote to anxiety - all in one setting.

Ed Campos started the day with an inspired and heartfelt keynote that had the entire room eating from the palm of his hand. We all broke out to sessions, each was an incredibly balanced blend of Math and Mindful practices that encouraged growth mindsets, self regulation, and intrinsically motivating students. I don't know that I have ever seen or experienced anything quite like it.

I was honored to be asked to close the day with a short closing keynote. My job was simply to inspire the crowd to take action on what they had learned during the day.

Here is a bootleg video of my 12 minute closing keynote. It opens by me saying that I was blown away by this conference, but I truly have to take just a moment and reiterate what an inspirational conference this was. My sincerest congratulations and thank you goes out to Carole and Marie for their vision and their offer to include me in this unique opportunity. If you see this conference come around again, I highly recommend it to anyone interested in experiencing a tribe of people envisioning how the modern math classroom could exist.

Right in the Middle of Something

I'm not sure who signs up to be an astronaut; the job looks like there are some real high moments, but on the downside there is the freeze dried food, long time in confined places, bursting into flames on reentry, and absolutely no way to walk off the job. Not that walking off the job is a good litmus test for what makes a good career choice, but if you are in the middle of orbiting our lunar satellite and you disagree with a call made by your supervisor in Houston, you can not very easily rattle the boss's day with a pithy one liner and do the slow motion movie stroll out to your car in the parking-lot. Being an astronaut in outer-space is one of those gigs that I assume keeps your full attention. I can imagine an astronaut picking up the phone out of habit on the first ring and having to explain to his mother, who is calling for advice with leaking faucet, "Umm, Mom, I'm right in the middle of something, I'll call you back."

Not that the school business is parallel to taking off from Cape Canaveral, but it is a gig that I have been neck deep in for nearly 20 years, and I have increasingly found myself thinking, "Well, I couldn't leave now, I'm right in the middle of something." Do you ever find yourself having the winning the lottery fantasy? You see yourself collecting all that cash. There have been times when I have spontaneously had that thought and I could see myself walking away, but more often than not, I find myself having the lottery fantasy in response to some reality I am facing, and the lottery dream is only a reminder that regardless of whether or not I picked the right six numbers on a lotto ticket or not, the work that I was doing would still have to get done before I left to collect my cash.

That seems to be the truth for most of the educators I know. They get into this gig with the big dream that they are going to have summers off, but the truth it, the job sucks them in. Their afternoons begin to fill with coaching and clubs, their weekends with grading and even though from time to time, a loved one says, "Well it sounds like you are working too hard, maybe you should look for a different job," every educator I know sits up straight at that moment and, more often than not says, "Leave? Oh, I couldn't leave, I am right in the middle of something."

This gig may not pay the most, and sometimes the work is a little messy and overwhelming, but I wouldn't trade our industry for one that was easier to walk away from.