The Schoenblog

Next year, I plan to do more formative check-ins like this to drive instruction. I used A LOT of Google Forms this year, and often asked students for feedback, but I don’t think I let the data inform our work to the fullest extent. I never want data or technology to get in the way of a conversation with a student--and it can never replace that smile or look that shows real learning and achievement--but it can provide tools to give us more information about student learning than ever before. And when we know better, we can do better.

How do you use data in your assessment and instruction? Share your strategies and experiences in the comments or on Twitter.

More Tips

Protect Data

Collaborating on a sheet? Or just want to avoid accidents? You can protect the data in specific cells, rows, or columns to restrict editing rights. This means that only you, or whomever you allow, can edit the cells. You can also add a warning before editing, too. Right click on any highlighted cells or data and see the options below.

Publish to the Web

Like with any Google Doc or Slides, you can publish sheets to the web to share them as a more finished looking website. This provides you with an embed code, a public link, and allows you to share particular worksheets as necessary. Most of my form results wind up published and public, like my class Genius Hour website, at geniushour.aschoenbart.com, for example.




Thanks for checking out this post and episode 3 of The Schoencast. If you want to learn more about using Google Sheets and working with data, I recommends GFC’s Google Sheets tutorials and Google’s Spreadsheet Functions List. It’s where I got started exploring and playing with the possibilities.

Have any questions about this episode, or about Google Forms or Sheets? Share them in the comments or on Twitter. I will answer any and all questions to help make Sheets powerful for you!

Earlier this year, Google Classroom rolled out a new forms integration, which makes using them with Classroom so much easier. It’s cleaner and more effective than ever to assign a form, but as I’ve learned this week, there are challenges to consider, too. While the new features will save a teacher from scouring rows of sheet data to see which students submitted, they also sneakily copy the form to each assigned class, separating out all data and any add-ons or functions a power user might need.

Forms & Classroom: Old vs. New

In its first year or so, Google Classroom did not have any sort of forms integration. While you could simply click the Drive icon to add a file to an assignment for most Google Apps, Forms were not an option. Instead, the easy alternative was to view the live link to a Form and to add that link to the assignment. Easy as pie.


Teachers had to then View responses in the Google Sheet to track submissions, which can be a hassle, but is made much easier with a quick =(SORT) function.

In the recent Classroom updates, Forms integration was released. And it seemed awesome. Not only could you add a Google Form from your Drive to a Classroom Assignment, but it would Mark the Student work as done upon Form submission. This works just like the Ask a Question feature; when the students submits the work, it is automatically marked as done. Sounds awesome, right? If you are using a form for the basic features and aren’t as interested in working with the data, it’s a huge time saver. But if you’re a power user of forms, or are interested in using Add-ons like Form Mule or Flubaroo, be careful: danger ahead.

The Pros

The ease of integration described above is great. Instead of taking the time to view and sort (manually or with functions) the Google sheet, teachers can just look at the Done vs. Not Done, which updates live as students submit their Forms. This works just like the submission of any other assignments on Classroom.

If there are multiple files added to the Assignment that includes the form, students will need to click Mark as done manually, otherwise this is now automatic.

The Cons

What Google Classroom is really doing with forms surprised me. Instead of assigning the live form link to the assignment, it is actually creating a copy of the form itself within Classroom, just like the Make a Copy option would for any other Google doc. The problem here lies in multiple classes: if you are assigning a form to multiple Classroom classes, it will create multiple copies of the form AND the results sheet. This means that the teacher has to check responses for each class section for which the work as assigned. The ease of the automatic Mark as done probably outweighs this extra step for most teachers, though, making the feature still worthwhile. Unless you love your Add-ons.

Last week, my students used a Google Form for peer editing. They were assigned partners and shared a Google doc with one another. Then, they used a Google Form to provide feedback. The Writer’s Name drop down list selected a student’s e-mail address, and with Form Mule, the work was automatically e-mailed to the writer on submission. I was proud of this one: well designed, effective, and a solid lesson. But then I added the form to Classroom. What I didn’t realize at the time, was that my functions in the sheet and the customization I set up with Form Mule and Copy Down were no longer in effect, since copying the form creates a new results sheet and does not copy the Add-ons and settings.

For me, this is a deal breaker. When I use Add-ons to manipulate data or communicate with users, it often takes a while to program effectively. I install Add-ons, submit dummy data, preview, and revise. Then I send out the form to live online and accept results. To do this once is standard, but I now have to do this three times for my class sections. Teach five classes? That’s five installs, templates, send conditions, etc.

What’s Your Purpose?

Since forms are just tools, like with any other tech, it comes back to purpose. Are you content checking multiple form response sheets? Or is it essential that all your data be in one place? Are you a Google Forms power user, using Add-ons and complex functions? Let these answers guide your choices. For me, linking to a live form is usually going to be more worthwhile. The new integration is powerful, but don’t let it mess with how you view, organize, and respond to data.

  

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.

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.

The starting ideas was fairly simple: a collaborative, crowdsourced and completely public calendar of #edtech events in the area. Here's what I knew:

• I wanted to use a Google Calendar so people could add events to their own calendars
• I wanted to use a Google form and add-ons to manage the automation
• I wanted to leave everything public and collaborative, but still needed the ability to administer and edit
• I wanted to be able to give contributers credit for their ideas and events

The Workflow:

For the most part, I don't know code. I have a growing desire to learn, and used to do web design with HTML and basic java, so I'm by no means a beginner. However, I haven't practiced the skills in years and didn't want to talk that on. So without New Visions Lab and their many wonderful add-ons, nothing would have worked. I use nearly all of their products, and plan to write about the many ways I do so to improve my teaching life.

Here is what I did to make the calendar live:

1. Create a new calendar. This was easy. I created a new calendar in Google Calendar, made it public, and found the address in settings.
2. Install Old Form Mule. Form Mule is a wonderful mail merge add-on that automates e-mails based on form results. The old version, in scripts, also allows for a Calendar merge. With this script, I automated e-mails to the site admins notifying us of new additions, and automated calendar merges. We thought about added an approval/moderation feature, but it seemed to be more trouble that it's worth. Keep it open and public but we still have admin rights to change the calendar.
3. Copy Down. Form Mule wants the date for the calendar event to be in the Date Time format, so I used Copy Down to combine this data into the add-on-friendly format. Every time a form result is submitted, any code copied to cells gets pushed down, adding a new line of data. Copy Down is great because it lets you copy formulas to organize and manipulate new data.
4. Form Router. We wanted to be able to credit contributors by listing their contact info. The obvious was to do this was to pull data from the form results, but it's hard to manipulate in the sheet. Form Router helped by appending form results to second Google Sheet in real time. We could manipulate data, add links to contact info, and publish it without messing up the form results.
5. Google Site. Lastly, we built the Google Site for #EdTechCalNYNJ. This was easy! The calendar, contributor page and form were all seamless to embed. Piece of cake!

If you like the calendar, can use this workflow in your life, ate anything delicious today, or have anything to share, please comment!