The Schoenblog

John’s blog above basically sums up the whole first part of my process. I don’t want to be the guy who mentions complex ideas and doesn’t explain them but you can find so much at his site, along with some of my recent posts on add-ons or tools that help this process work. The basics of Form Mule and If This Then That and how they are used for this project are detailed on the Schoenblog at The SchoenCast Episode 01: Form Mule and How to Automate Blogging Workflow with #IFTTT.

The Form

As usual, it all starts with a form. This one is simple, and can be found on my class website. A copy viewable to the public can be found at Tweet Me! Public.

Notice the directions: The student name and class hashtag will automatically be added to the Tweet. The Tweet paragraph text box uses data validation, found in the advanced options, to limit the characters so that Tweet can never be too long. All of this can easily be customized in your form to meet your needs.




Most similar workflows I’ve seen then require the teacher to open the Google Sheet form results and enter some sort of manual trigger for Form Mule to send the e-mail that triggers the #IFTTT recipe. Still with me? Good.

Tweet Approval

For this project, I wanted to keep it clean and streamlined, never requiring the teacher to go to the Google Sheet. Here’s how it works:

Form Mule sends an automated e-mail to the teacher whenever a Tweet Me! form is submitted. This body of this e-mail looks like this:


Here it is in the Form Mule template:




The Pre-Filled URL referenced above is actually a link to a new form. Visit Alice Keeler’s post linked at the start of this article for more about pre-filled forms. With a Pre-Filled URL that looks like the line below, the link actually connects to a new Google Form for approval.



Find a viewable copy of that form here: Tweet Approval Form. Basically, the Approval Form pulls in the students’ Tweet, and allows the teacher to approve it through e-mail only. See the form screen shot below:



When I click Submit, and Yes is selected on the Approval drop down menu, Form Mule then sends this Tweet to activate the #IFTTT trigger. I use two templates here:



If the Tweet is approved, the e-mail is sent to the e-mail that automates the Tweet through an #IFTTT recipe with a merged subject line of:



If Revise is selected, the student is e-mailed to ask them to revise their Tweet.

In the end, here is the result:

The Big Picture

This addition might not add any crazy functionality but I think that using Forms only makes the process so much cleaner. John’s ideas work beautifully, and combining them with the Pre-Filled Forms makes this really powerful. Plus, with some simple additions to the forms and functions, it can be adapted to meet the needs of multiple teachers. By selecting a teacher or class period, the form can work to automate Tweets to different accounts or with different hashtags.

I hope this walk though was helpful--and I hope it made sense. Most of my high school students are on Twitter but I’m finding this project incredibly useful for those who are not, not allowed to be, or are too young to Tweet on their own. As the teacher, I still have control, but the automation is simple, quick, and clean.

To prove it, I made this quick screencast below. A second episode of the SchoenCast wasn’t my plan here, but it felt like a video explanation was needed. Let me know how it goes.

The SchoenCast Episode 02: Automating Tweets from Forms

  

While most Google Apps have add-ons, for my money sheets and forms have those game changers that can really impact workflow, make your jobs more efficient, and transform your work. Below, find some of my favorite add-ons with brief explanations and applications. If you have one to add to the list, reach out on Twitter or in the comments!

Most Google Apps have an Add-ons menu on the top toolbar. Click Add-ons → Get Add-ons to start exploring!

The Best of the Best

Choice Eliminator, as the title suggests, removes an option from a multiple choice question after it has been selected, depending on your preferences. Each question can be customized depending on your intended quantity of responses.

I use Choice Eliminator to have students select groups and topics for projects. I share the responses sheet with them, and the add-on is used to keep all of that management seamless and electronic.





Form Mule is a mail merge utility. It combines the data across the rows of a spreadsheet into a template you create, and e-mails it to whoever you decide.

Use it to send form results to a user, e-mail students comments from a forms rubric, or to notify yourself when students submit form or sign up for appointments. Find much more on Form Mule on the first episode of the Schoencast here.





Autocrat is a document merge tool. It works similarly to Form Mule, in that it combines and merges data from a sheet or form. However, the power of Autocrat is that is can create .pdf or Google Docs that can be attached to an e-mail, fitting a .doc template you create.

Use Autocrat to send PD certificates or quiz results when participants complete a form. Many conferences do this to automate the certificate process. Or use it to merge those ugly rows of data into a nice, clean document from a student survey that you might not have really looked at since the first week of school. One day, I’ll write about my self-paced, automated quizzes that are based on Autocrat.





Copy Down copies formulas in a Google Sheet down a column.

Use Copy Down to automatically apply custom formulas to form results. For example, create a basic sum formula to total grades from a form quiz. Then, use Copy Down to fill that data when the form is submitted each time. Combine it with Form Mule to automate the return of the feedback. Since each new Google Form response actually inserts a new row, Copy Down is essential if you plan to use any formulas and accept new responses.





Flubaroo automatically grades a Google Form multiple choice quiz.

Use it to grade quizzes (duh!). The add-on compares your results of a submission as the key with student submissions, grades them, and provides some basic analytics on the data. It highlights problematic questions, low scoring questions, and can even e-mail scores and /or answer keys to students. Additionally, Flubaroo can be set to run automatically upon submission, and can now even be used for scoring open ended responses, too.

Bonus Round: 

Doctopus is an incredible Google Sheets add-on used to distribute templates. When paired with the extension Goobric, it’s incredibly useful for using rubrics to grade writing through Google Drive and Classroom. It deserves it’s own post, though, so while it’s incredible, I’ll keep it brief this time.

The More the Merrier

One of the great things about Google Apps is that new updates, products, extensions, and add-ons are coming all the time. The screenshots below show some of the ones I use in addition to my top 5. Number 6-10 could easily be a post on it’s own, too. Explore the Chrome store and make your life easier and teaching better with the power of the Forms and Sheets add-ons.

Welcome to post number 52 on the Schoenblog. If posts were weeks, it would almost be a year. Since last April, I’ve shared 52 posts and kept to my 2 per week schedule for the past few months religiously. Back when I started, my friends pushed me to write, and when Dani came up with the name Schoenblog, I took the leap.

Now, all these posts later, they’ve pushed me to take another jump forward.

If you read this blog, you know that I love forms and sheets and any way to automate feedback and communication to students and parents. I get excited about app-smashing and my mind is always racing with ideas and applications. I’m always sharing these ideas, along with my lessons and programmed forms, with my #PLN, specifically my #EdJusticeLeague Voxer group. We talk all day and ask questions, share best practices, and help each other continue to move forward with positivity and new ideas. As they reminded me, talk is great, but it’s so much better to see the learning in action.

Introducing….

The Schoencast: You Guide to EdTech Workflow

My goal is simple: to create short screencasts sharing how I use online tools to improve organization, communication, feedback, and automation to impact teaching and learning. Each episode will include a walk through or tips about an edtech tool and a classroom application. Then, after each video, I’ll follow up with more applications that I will explore in the months ahead.

This is my first attempt a screencast for a public audience and I would love your feedback and input. What worked? What didn’t? What can I do better? What topics would you like to see explored on the Schoencast?

The Schoencast Episode 1: Form Mule

Form Mule is a mail-merge tool, found in the Google Sheets add-ons. In this cast, I demonstrate how to use to automate a customized e-mail when a form is submitted. Below the video, find more ways to use Form Mule.

More Ways to Use Form Mule

• Send rubrics to students from Google Forms
• Have students provide peer feedback and e-mail to students
• Thank students for submitting work or information
• Combine with #IFTTT to automate Tweets
• E-mail students the next activity or assignment
• Provide targeted feedback based on a quiz in Google Forms
• Send individualized e-mails to parents
• Automatically send e-mail updates to administrators about student performance

Have a comment about the first ever Schoencast? An idea for a future episode? Want to learn more about an application listed above? Comment away or find me on Twitter @MrSchoenbart.

Many teachers I work with appreciate Google Classroom for the way it helps manage the workflow of creating, sharing, reading, and responding to assignments. However, Classroom isn’t as helpful when dealing with students submitting late work, making up work from absences, or revising assignments. It’s easy to check for submissions in classroom when I’m ready to read work, but what about afterwards? There is no real system to make it easy for teachers to know when additional work has been submitted after you have read or returned the assignment. Let’s make our lives easier.

I wanted to be notified when students submitted work that I wouldn't need to check in on manually. I try to read student work quickly, and if a student submits a day late or revises a piece, it’s easy for me to miss. I’m sick of the hard to track mix of e-mails, in person reminders, and a hodgepodge of notifications. I also wanted to collect information from my students and push them to reflect on what they need help with and how their work has or needs to improve.

The How

Google Forms makes a system for this sort of notification easy. I created a form titled the Assignment Submission Form for students to use every time they submit work after the due date for any reason. This way, there is one place to check for new submissions rather than searching the many assignments I have in Google Classroom repeatedly. View a copy of this form here. Like it? Make a Copy for yourself--adapt as necessary.

The form is on the main page of my class website and is linked with QR codes around the classroom. Then, using Form Mule, I’ve set up automated e-mails whenever a form is submitted. I never need to go out of my way to check for anything, always know what’s submitted when, and can hold all students accountable.

The e-mail above was my test submission so the answers are vague, but it’s enough to get the idea, right? 

The Questions

The questions on the form also provide me with some valuable data. I tell my students that it’s more important to me that they learn than when, so I offer unlimited revision opportunities and no late penalties for students who seek and accept extra help. This form helps me keep track of how and when students pursued extra help, what their challenges were, and how they overcame them. I also have a bad habit of forgetting when I give extensions when student work is late or simply a make up from an absence; the questions here solve this problem.

My Assignment Submission Form is a great help in managing workflow, communicating clear expectations, and collecting data about student work. Feel free to use it or adapt from it, if you find value in it, too. If you do, please comment or Tweet @MrSchoenbart to let me know!

View the form or Make a Copy to use or edit on your own.

2. View Form Results.

You can either program this whole process to automate itself or wait and do it after students complete the form. If you are automating, fill out the form with some dummy data, like I did below.

3. Add a Formula Column

View my results spreadsheet here if it helps. Columns A through F are filled with the basic form results. Now, in column G I added a column titled Birthday?.

In cell, G2, copy and paste this formula: 

=if(month(E2)&day(E2)= month(today())&day(today()),"SEND","")

The formula above assumes that cell E2 has the first student’s birthday in it. Then, it says if the month and day of today match the month and day of E2, then SEND. If today is the student’s birthday, the cell will actually read “SEND,” otherwise it will remain blank. This is essential for the next step. The formula looks a little complicated, but you don’t need to change anything unless your birthday column is not E. Thanks a lot to Alice Keeler for help with the formula. Believe it or not, my first draft was much more complicated, and she gave me the one above, which is much simpler and elegant. Follow her for all the best Google tips.


Now you need to fill that formula down the entire column. The simple way to do this is to click the cell, grab the blue square in the right corner, and simply drag down the column after the data is submitted in the form. This means have the students complete the form, then fill the code. Alternatively, check out the Copy Down add-on for Sheets to automate this if you want to have the formula fill each cell in the column as data is submitted live.

Form Mule Sheets Add On

Form Mule is a fantastic mail merge add-on for Google Sheets. It plugs the information from a row and merges it into an e-mail to match a template you design.

For this task, Form Mule is used to merge the data into the subject line of an e-mail.

4. Install Form Mule

In sheets, click Add-ons on the top menu (between Table and Help). Then click Get add-ons. Search for Form Mule, click the green Free button, and install it. Follow the directions to authorize it to use your account.

5. Launch Form Mule

Once you install an Add-on, it’s there to stay and can always be found on the Add-ons for that particular Google App. For this task in Form Mule, most of the default settings are where we want things. Once you launch, a box like the one below should pop up. Select Form Responses 1 as your sheet with the merge data (or the appropriate one if you’ve made any changes).

To help automate the process, turn on Time Trigger. Select Day timer and pick a time for the process to run every day. I suggest using a time that students are in school, or at least presumably awake, because this is the time that the Tweeting process will automatically run.

When your settings match mine below, click Next

5. Edit the Send Conditions and Template

Form Mule will run for every submission unless we set a specific condition; we only want it to run when the current date matches the student birthday. To do this, we need to create a send condition. On the Send Condition drop down menu, find the BIRTHDAY? column we created a few steps back. In the blank box on the right, type SEND. This will match the formula from step 4, which means that the e-mail to automate the Tweet will only send if the dates match.


Next, click Save and then Edit on the bottom of the panel.

Finally, we have to tell Form Mule what to send in the e-mail, and it’s fairly simple. The only fields that need any text are To: and Subject:.



In To:, type the new e-mail you have created for automating Tweets.

In Subject:, we need to type the message that you want Tweeted. What you type here will be the exact message Tweeted, but the student information from the form will replace the information in the brackets.

For example, this subject line:

Happy Birthday, <<Last Name:>> <<First Name:>> <<Twitter Handle:>>!
Will Tweet (which you can see by clicking preview in the window above):


Feel free to adjust the subject line to meet your needs. Include a class hashtag or other message. You can merge any additional information from the form as necessary, too, but make sure to be mindful of the 140 character limit.

6. If This Then That

Finally, we are ready. We have used a Form to get the information, used a Sheets formula and add-on to automate an e-mail to wish the student a happy birthday on the right day. Now, we need to make that e-mail trigger a Tweet.

To do this, we will use If This Then That recipe gmail to twitter. The key here is to create your #IFTTT account using the same e-mail you entered in the To: field of Form Mule. Once you do this and authorize the Twitter channel, you are almost done.


Once the recipe is turned on, enter your e-mail address in the Trigger field. This is your normal e-mail--the same Google Account you used for the Form, Sheet, and Form Mule. In Action, it should say Subject, just like it does below. Basically, this means that when the account e-mail (the new one you created) receives an e-mail from you (which is what Form Mule will do on the birthday), Tweet out the message.

Happy Birthday to You

So to recap: The form results populate the sheet, which copies the formula to verify the birthday. Then, when the birthday occurs, the formula fills the column with SEND, triggering Form Mule to merge the row into a birthday message, sent to your new e-mail address. Finally, #IFTTT, with the gmail to Twitter recipe, sends the subject line of the merged e-mail to Twitter. Still following?

Happy Birthday, Bruce Wayne @IAmBatman!

— Adam Schoenbart (@MrSchoenbart) September 16, 2015

I know it seems complicated, but trust me: it gets easier. App smashing with add-ons and #IFTTT is a powerful tool for responsive blogging and automating the process. If you give it a try, let me know how it goes. If you have any ideas to make it better, I’d love to know that, too.

An easy one is this recipe: Blogger to Facebook. If I publish a new post on Blogger, automatically post it on my Facebook feed. For each recipe, I can see how many times it’s run to double check my work, can turn it on or off, or check the activity log as necessary. 

I like to think of it like a variation on app smashing, and with some trial-and-error and creativity, you can make a lot of cool things happen. Here are some easy recipes I use and why you might want to try them out, too.

Blogger to Facebook

I’m enjoying Blogging and want to grow my audience. An easy way to this is to promote my posts more on social media. This recipe will automatically share my Blogger posts on my Facebook stream. I can customize the post itself (title, text, link, etc.) and it does the rest. Find similar recipes to post to Twitter, Instagram, etc., or pretty much from any social media site to another.

Create Twitter Lists Around a Hashtag

One simple recipe is to create a Twitter list based on a hashtag. For example, I want to see who posts using the #edcampMVille to see what Twitter traffic is generated for our upcoming event. This recipe will add anyone who tweets with the hashtag to a Twitter list so I can see who is engaging with us on social media. There are similar recipes to favorite and retweet, too.

Twitter to Google Sheet

I love organizing data on Google sheets. In addition to creating a Twitter list in the example above, I can track users and Tweets based on a specific hashtag to a Google Sheet (and many other similar options--evernote, pocket, etc.). I wanted to reach anyone who Tweeted with #nyedchat to share information about #edcampMVille; this recipe allowed me to track those users so I knew who to reach out to and tag in my posts. With some creative work in Sheets and another IFTTT recipe (see the next bullet!), I was able to automatically Tweet at users to invite them pretty easily, raising awareness for our event. Apply this to any event, hashtag, or target audience you want to reach and you’re good to know.

GMail to Twitter

This one is really cool and involves my new favorite pasttime: app smashing. By combining this recipe with the Google Sheets add-on, Form Mule, I can automate my Tweeting based on certain conditions. For example, when someone adds an event to #EdTechCalNYNJ, I wanted to automatically thank them. 

To do this, I successfully smashed the recipe above with Form Mule. When someone adds to the calendar via a Google Form, Form Mule automatically sends a mail merge to a GMail account with the following subject line: 

Thanks <<Name>> <<Twitter Handle>> for adding <<Event Name>> in <<Location>> on <<Date>> to #EdTechCalNYNJ edtechcalnynj.com

Form Mule is triggered whenever the form is submitted and the <<Tags>> above are replaced with the actual information. 

Then, the #IFTTT recipe above does the rest. The recipe is programmed so that whenever my e-mail address sends an e-mail to a secondary address, then Tweet out the subject line. Voila! The key here is to have a secondary e-mail just for the recipe’s automatic Tweeting.

Thanks @apgovme for adding EdCamp_CNY in NY on 10/3/2015 to #EdTechCalNYNJ! http://t.co/JVOLXABsGi

— Adam Schoenbart (@MrSchoenbart) August 19, 2015

This last example is my favorite. Combining add-ons with #IFTTT leads to some really powerful results and a lot of fun customization on my end. I encourage you to give it a shot.


I’m going to follow up this post soon with a more advanced look on responsive Tweeting with #IFFTT, like the example above. How do you use #IFTTT?

I was excited to learn If This Then That and play with recipes. I love the idea using tech to make this work more efficient.

Events List: The other major update was to provide a list of all of the events featured on the calendar. You can peruse each month, but what if you want to search for EdCamps? 

Thanks to Awesome Table, this was easy! I made a new worksheet and transposed the information I needed. I'm so excited about this sites gadget. You can now index all of the events and search by name, date, type, or location. Want a conference in NY in May? That's easy. Check it out! I also learned to play with the SORT function on sheets to alphabetize the list. This simple function will save me so much time sorting student data from forms; I feel silly for not finding it earlier.