Customized email newsletters for every kid with Google Forms

Make solid connections with families by creating custom email newsletters for every student. It's easy with Google Docs, Sheets and Forms. (Public domain image via Pixabay.com)

Make solid connections with families by creating custom email newsletters for every student. It’s easy with Google Docs, Sheets and Forms. (Public domain image via Pixabay.com)

Classroom newsletters are kind of hit and miss. If you’re sending photocopies of them home in students’ backpacks, I’ll bet your success rate is less than 50 percent. (And that’s being generous.)

There’s a big reason I believe that these newsletters — which could be great parent/teacher communication tools — fall on deaf ears (or blind eyes).

They’re not personalized. If parents knew there would be comments unique to their children, they’d be much more likely to read them.

That’s possible now — for free. And it doesn’t take much more time than a regular newsletter.

Using a combination of Google Docs and Google Sheets (with an option to use Google Forms to input information), you can create personalized email newsletters with custom comments for every student.

This is a suggestion Alice Keeler and I share for recruiting support from parents in our forthcoming book, Ditch That Homework. (As of publication of this post, we have just sent the manuscript off to the publisher and are hoping it’s published this summer.) In the book, we write that parents are an important ally in ditching homework and supporting the work that’s done inside the classroom.

So, how do you do it? There are a couple of ways.

One way is to use Alice’s Parent Newsletter add-on. It’s a script for Google Sheets that she created herself. There’s a template you can copy and a blog post description.

My favorite way to create custom newsletters (and a suggestion of Alice’s) is to use the Autocrat add-on. It’s a way to take data from a Google Sheet spreadsheet and merge it into a Google Docs template automatically.

Autocrat is very versatile. I’ve written about how you can use Autocrat and Google Forms to create “The Magic Automatic Lesson Planner.” There are other Autocrat suggestions in this post on practical ways to use Google Forms in schools.

So … how do I do it?

It’s pretty simple. Check out the steps below:

1. Design your newsletter in Google Docs. Create a new document and start filling it with the information you want to pass along to parents. When you come to a place where you want to provide custom information for each child, just mark that with a merge tag.

  • Wait … what’s a merge tag? This is how Autocrat will know where to put your custom comments for each student. A merge tag might look like this: <<name>>. That’s the one I used in my newsletter to add student names to each unique custom newsletter. It doesn’t matter what word or words you put in your merge tags, so use something that you’ll be able to remember. (i.e. <<goals>> for a section on goals for that particular student)

Click here to see my example, or see the image below. I’m a word guy more than a design guy, so mine is pretty basic. Jazz yours up with images from your class, clip art, color, etc.

newsletter google doc

2. When you’re done designing your newsletter, you have a decision to make. How do you want to type all of the comments for each student for all of your custom newsletters? There are two basic options:

  • Type them directly into a Google Sheets spreadsheet.
  • Enter them in a Google Forms survey that you create just for your own use.

If you choose the Google Sheets route, create a new Google Sheet. Make the top row of your sheet the headers for all of the columns of data that you want to use. (See how I did that here. I used student name, a general note about the student, what they’re good at and their goals — four columns.)

This is a nice option because you can type all of your student names and email addresses into the sheet once. Then, when you create your next newsletter, you can copy the names and email addresses and paste them below so you don’t have to enter them all over again.

If you choose the Google Forms route, create a new Google Form. When you make this form, you’re creating a custom survey that you can use over and over to insert new comments quickly. Click the “+” button and add the kinds of fields/questions that you want. (Click here to see my example. Note: Please don’t send me requests asking for editing access to this form. They’re really easy to create. Just make one yourself.)

Some suggestions:

  • If you’re typing full-sentence or paragraph-length comments for the newsletter, you should probably add the “paragraph” fields for each of those.
  • For anything you’ll type that’s just one word or a short phrase, “short answer” fields are best.
  • For student name and email address, one option is to create a “dropdown” field and enter their data there — one for name and one for email address. That way, when you enter comments for each student, you won’t have to type his/her name and email address each time — just choose it from a dropdown menu.

Whenever you add data through your Google Forms survey, that data is automatically saved to the form (click the “Responses” tab). You can also create or choose a spreadsheet where you want the data to go, too. To do this, from the “Responses” tab, click the little green spreadsheet icon. You’ll choose whether you create a brand new spreadsheet for the data or you add it to an existing one. I almost always create a new spreadsheet to go with my Forms surveys. (You’ll want to do this before you use Autocrat in step 4.)

newsletter input form

3. Start entering data. If you’re using a Google Sheet, start typing data — name, email address and comments — into your sheet. If you’re using a Google Form, start typing data into the form and submitting once for each student. It will be collected and put into a spreadsheet automatically. (Note: I like to add some data to myself so that I get a copy of the newsletter in my own email and make sure that it worked correctly. So you might add your own name, email address and some dummy data to send to yourself.)

newsletter data sheet

4. Set up Autocrat to put data into the newsletters. You have your newsletter designed in Docs, right? And you’ve typed personalized data for all of your students? Great.

It’s time to make the magic happen.

Open your Google Sheets spreadsheet of data. In the menu bar, choose “Add-ons” and “Get add-ons”. Search for “Autocrat” in the search bar. It will say “autoCrat” by New Visions CloudLab. Click the “+ Free” button and it will be added to your sheet. (Note: If you’ve used Autocrat before, you won’t need this step.)

In the menu bar, choose “Add-ons” > “Autocrat” > Launch. Click the “New Job” button and you’re off to the races!

Step 1: Job name. No one will see this but you. Example: “Mr. Miller’s newsletter.” Click “next.”

Step 2: Choose a template. This is the document you created earlier with the merge tags in it. Click “From Drive” and find the document you made earlier. Click “next.”

Step 3: Map source data to template. This sounds complicated but it’s not. You just tell Autocrat what data to put in the document for your merge tags. For each merge tag, choose what data you want to use from the dropdown menu next to the words “maps to column”. (See below.) Make sure you scroll down and do all of them. Click “next.”

newsletter map source data

Step 4: File settings. This is where you decide what kind of file you’ll create for each student and how its file name will be created. My suggestion: Click on the blue bar on the left and copy the merge tag for student name. Use it in the file name along with the date/month of the newsletter. (See example below.) Choose whether you want to create a PDF or Google Doc. (I usually choose PDF, but it’s up to you.) Finally, you want multiple documents, not a single document. Click “next.”

newsletter file settings

Step 5: Choose destination folder(s). This is where all of your personalized student newsletters will be saved in your Google Drive. Go to your Google Drive (drive.google.com) and click “New” > “New folder …” and make a folder for your newsletters. When you’re done, come back to Autocrat and click the “+ Choose Folder” button to choose that folder. (Note: When you choose a folder, don’t double-click it. That opens the folder. Single click it to select it. Then click the blue “Select” button at the bottom.) Click “next.”

Step 6: Add dynamic folder reference (optional). Skip this step. Click “next.”

Step 7: Set merge condition (optional). Skip this step. Click “next.”

Step 8: Share docs and send emails. Where it says, “Share Doc?” choose “yes.” It’s probably easiest to leave the first three options as they are, but you can definitely change them. (Share doc as: PDF; Allow collaborators to re-share: Yes; Send from generic no-reply address: No.)

Below that, you’ll craft your email to parents. You’ll want to copy the email address using the blue bar on the left. (Click the blue bar and click the merge tag for the email address. When you click it, it copies it so you can paste it later.) Paste that into the “To:” field.

Type a subject line for your email. Parents are going to be more likely to open it if it has their child’s name in the subject line. Again, use the blue bar on the left to copy the merge tag for student name. Put it in the subject line. Example: “<<name>>’s March newsletter”

Then, type your email. Remember, every parent gets the same email, so write something that fits for everyone. When you’re done, click “next.”

newsletter write email

Step 9: Add/remove job triggers. This is how you tell Autocrat when and how to send your emails. For our newsletter purposes, you don’t want to run on form trigger. (This only works if you’re adding data to the sheet from a Google Form, and it would send a newsletter every time you submit the form. Unless you want to do that …) You also don’t want to run on time trigger. (This makes Autocrat look for new data to create newsletters every hour, every six hours, etc.)

So to keep things simple, just leave both of these as “no.” Click “SAVE.”

5. Send those newsletters! Once you click “SAVE” above, you’ll be returned to the “Existing jobs” screen, which looks like this:

newsletter existing jobs

You can find the “Existing jobs” screen by clicking Add-ons > Autocrat > Open in the menu bar.

Click the “play” button to create all of your newsletters and send them! (That’s the little triangle button.)

Note: It takes Autocrat a while to create and send them all. You can watch the progress on your spreadsheet … it makes notes of what it’s doing to the right of all of your data in your spreadsheet.

Your emails should be on their way!

What do I do the next time I want to send a newsletter?

Easy peasy …

If you’re adding data with a Google Form, just use that form to enter data for each student for your new newsletter. Go to your newsletter document and update whatever you need (i.e. new announcements, etc.). Then go to Add-ons > Autocrat > Open and run Autocrat to send the new newsletters.

If your’e adding data with a Google Sheet, copy all the student names and email addresses and paste them below the old data. Type new comments for each student. Go to your newsletter document and update whatever you need (i.e. new announcements, etc.). Then go to Add-ons > Autocrat > Open and run Autocrat to send the new newsletters.

Wash. Rinse. Repeat … as many times as you want to create new newsletters!

Wow. That was really easy!

Wasn’t it? It’s a simple approach to building relationships at home, giving parents some specific feedback on their kids and keeping everyone in the loop.

Have any questions? Did you get stuck? Leave a comment below and I’ll do my best to help … or email me (matt@DitchThatTextbook.com).

For notifications of new Ditch That Textbook content and helpful links:

Interested in having Matt present at your event or school? Contact him by e-mail!

Matt is scheduled to present at the following upcoming events:

 DateEvent / Event DetailsCity / More Info
+ 10/03/2017—10/04/2017
"The Digital PIRATE - Ditch That Textbook" 
  Private Event
+ 10/06/2017
8:00am-3:00pm
GooglePaloozaVandalia, IL
 Sponsor:ROE #3 
+ 11/07/2017
8:00am-3:00pm
Goshen Local SchoolsGoshen, OH
  Private Event

Leave a Reply

Your email address will not be published. Required fields are marked *

4 thoughts on “Customized email newsletters for every kid with Google Forms

  1. Love this – could be a game changer for parent communication for a lot of teachers. Would it be possible for you to make a screencast of the process?

  2. As a secondary teacher, newsletters are not typically the norm, but as I read this I was thinking of a way to boost parent communication at the secondary level. With 150 or so students, personal comments for a newsletter may seem like an insurmountable task, but what if the form was shared with students for them to fill in information about how they feel they are doing in class. The teacher could go back and personalize for students whose parents need to be directly contacted, or could write personalized messages to a few kids from each class. What potential hang ups do you see with this?