Build a Commenting system With These 5 Spreadsheet Tips

Google elevates the humble spreadsheet by bundling in forms, multi-user capacity, and the inherent connectivity of the Internet. This combination gives Google Spreadsheet application authoring-like chops. This post is going to explore this idea by making a moderated comment system using a few spreadsheet tricks.

You might ask, “Why?” Well, this system uses some functions that I use a lot, so it's a handy vehicle to talk about them. That said, there are reasons why we've used this at Quaker Ridge. Some sites don’t have built in commenting features, especially sites built on education content management systems. Other platforms, like Google Sites, have comments, but they can’t be moderated. Systems like Disqus are popular and powerful, but give you little control over what is done with your data. This is gross in general, but it’s particularly foul in the K-12 edu space. Finally, it can be more communal than a blog post comment. We can create a meta comment feed that insulates young children from the “I don’t get any comments but my friend gets so many” problem. So here you go, 5 tips to make your own comment system!

Tip 1: Use Validation!

Every good comment system needs a form to submit. My comment form has three questions, a teacher name drop down so that the comments can be filtered by class. An optional text box so that the person commenting can leave a name if wanted. And a paragraph text box for the comment. This last item is validated (click the advanced settings under the question) to 280 characters. I figure that twice the length of a tweet is enough to ask a question or provide feedback, but adjust accordingly.

Tip 2:  Use a QUERY for Moderation

I use this trick often. You’ll also find it in the Twitter Exit Ticket and the Book Review form. A query will take data from one page and display it on another, based on criteria that you declare. This criteria could be a number rage, or specific values, and can use boolean operators such as “OR” and “AND.”  I simply add a “Moderation” column to the right of my form sheet. Then, I add a query to a new sheet that watches this column for a value of ‘yes.’

=QUERY(data!A1:E, "where E = 'yes'",1)

This sheet is what gets sorted by the comment page. Since I use one system for several teachers, I include the “AND” operator to also look for a specific teacher’s name.

=QUERY(data!A1:E, "where E = 'yes' AND B = 'Teacher 1'",1)

It’s useful to learn how to write a query, but until you do, just use Bjorn Behrendt’s very cool EZ Query add-on.

Tip 3: Reverse the Order of the Comments
When someone submits a Google Form, the last response shows up under the previous submissions. Typically, you want the opposite with comments, with most recent submission first. We can use a SORT function to change the order. Add another sheet and sort the approved comment range using the timestamp column (represented by the 1) as the sort by column.

=ArrayFormula(sort('Tip 2: Moderate'!A2:D, 1,false))

Tip 4: Join Columns With Linebreaks

A comment system will often include who made the comment and the date the comment was submitted. Unfortunately, these items are in different columns. We need to join these together and do it in an attractive way. Columns can simply be joined by using the “&” symbol. However, we also want the columns separated with carriage returns - otherwise it would look crowded. The character code for a return is “CHAR(10).” We’ll use two together to create a space between the name and the comment..

=ArrayFormula(sort((" By: ")&'Tip 2: Moderate'!C2:C&CHAR(10)&CHAR(10)&'Tip 2: Moderate'!D2:D&CHAR(10)&CHAR(10),'Tip 2: Moderate'!A2:A,false))

Tip 5: Test for Blank Rows

The last function joined all of the rows, whether or not there was a comment. This left hundreds of orphaned “By:’s” from all those empty rows. Since we only want the function to apply to rows with comments, we can use an “If( ISBLANK( ‘Column’),”  test. This combination will look at a column, 'Teacher 2 Approved Comments'!C2:C in our case, and check each row for blanks. If a blank row is found, then nothing is done ("".) However, if something is found in a row, we can execute the joining formula from earlier to construct the comment.

=ArrayFormula(sort(IF(ISBLANK('Tip 2: Moderate'!C2:C), " ", " By: ")&'Tip 2: Moderate'!C2:C&CHAR(10)&CHAR(10)&'Tip 2: Moderate'!D2:D&CHAR(10)&CHAR(10),1,false))

Ready for Publishing

Now your comment page is ready to publish! Just click “File > Publish to the Web.” Select the comment page to publish and grab the Embed code. Then, paste that into any website that needs a moderated comment system! I’ll often set up the formMule add-on so that new comments will send an email to the teacher. Don’t forget to link to the form, too, and thanks for getting this far! Let me know in the comments if you have any questions.

Turning Google Docs into eBooks with Flipsnack

Sue Luft, one of our ELA helping teachers, challenged me to find a way to improve the “ebookiness” of our Google Doc nonfiction books. I admit, Google docs are unsatisfying to read as an ebook. After some Saturday surfing, I stumbled upon Flipsnack.

Flipsnack’s edu version follows a freemium model and is easy to use.  I love the gratifying page flip and it presents a beautiful full screen view. Pages are represented as thumbnails at the bottom of the screen for easy navigation. The backgrounds are also configurable - I like the nice wood grain. All of our nonfiction books were saved into PDFs and processed with Flipsnack.

The edu version allows you to set up a classroom with student accounts. This isn’t a feature I’ve taken advantage of yet but I can see it being really useful with my older grades. I started with 2nd grade work so I publish the books for our digital library myself. However, I could see our 4th grade students doing this in the future.

When student self publish they will also be able to submit their books to our school’s digital work library themselves. This be more sustainable for me and will allow students to take advantage of Flipsnack’s hyperlink and button features - bringing hypertext into the ebook genre. This is something that I think is appropriate to introduce in 4th grade.

Next weekend I will blog about our ebook shelves and discuss how to make them. I’m excited by the publishing potential of the Docs, Flipsnack, and the digital book shelves combination. Students will be content producers to a real audience from start to finish. Let me know if you have any questions in the comments!

2nd Grade Nonfiction Books Using Google Docs

The 3rd grade nonfiction template that I blogged about earlier in the year went really well. Student books like about Pythons and Michael Jordan are evidence that the template provided valuable scaffolding and did not overwhelm student agency. The success of the third grade books inspired us to adapt a version for the second grade. Our second grade students are new to docs and their first research project (ever) is a bird unit.  The newness of both Docs and online research meant that the template had to be simpler and more guided. Teachers had a choice between two template formats: a comparison between two birds and a focused book on one bird.

Like the 3rd grade templates, the second grade file relies on Google Drawings for text features. Instead of having students choose the features that support the text, the second grade book has diagrams in place for simplicity. Other elements like the dedication and copyright page are also removed to streamline the book.

The books came out terrific. Students created hand drawn illustrations which were scanned in for use in the diagrams. Students had a field day using drawings to creatively design their books, I personally love the life cycle drawings. Although the second grade templates are more structured than the third grade, students were still able to make them their own. I guess that’s my litmus test for a template - whether or not students have room for ownership. Take a look at our galleries below. Leave a comment or question for our authors if the mood strikes you!

Mrs. Iasiello’s Bird Comparison Books

Mrs. Cheung’s Bird Research Books

Animated Student Portraits Using Biteable

Sometimes things just line up. About a month ago I got an email from one of my 5th grade teachers asking for help. Her students had been learning how to ask good questions and interviewing each other.  She wanted a quick way to present this information. We all know that time is at a premium, some projects have to be quick. It just so happened that I read about Biteable that weekend. Kismet.

Biteable is a site that provides a library of quick animated slides. Users decide on the slides that best communicate their message and pair them with short strings of text. The slides are well designed and the brevity of the text (50 characters per slide) reinforces the “show, don’t tell” philosophy of design. Students also choose music to accompany the movie. The end result is something students can be proud of.

We created a single account using the teacher’s email and a generic password. Each student used this username and password to sign into the account and create a Biteable for their interview. The shared account did a few things:

• The teacher had access to all the biteable movies
• The account was connected to the teacher’s YouTube account, something that isn’t turned on in our student sub-organization
• When a movie is ready, each student sends an email via Biteable to the teacher, giving the teacher control over when the movies are “published”
• The shared account meant students had to respect each other’s work. This reinforced digital citizenship and our class rose to the occasion.

Students started by referencing the slide library and planning their movie with a storyboard graphic organizer. The graphic organizer was created in a Google Drawing and can be printed or used digitally.  The storyboards helped students think purposefully about their slide choices and the logical flow of the movie.

The text constraint of 50 characters per slide also presented an interesting teaching point. Students had to synthesize their notes into the most important ideas. Even then, ideas often needed to be stretched across slides. Students were taught to do this smoothly by using sentence connectors. Another lesson developed from the students’ sentence starters which tended to use a pronoun or the name of the child.  Students revised their work by placing the adverb phrase or predicate first.

The finished products are beautiful animated portraits of the interviewed students. Because the process was so easy, the focus of instruction was on craft and not the tool. It was also efficient; students typically finished the first draft of their movie in two days and took another two days to revise. I’m looking forward to using Bitable again, let me know how it goes if you try it with your class!

Open Thinking Pays Dividends

Last Wednesday I participated in a Google Educator Group Hangout with +James Peterson, +Jane Lofton and +Dan Imbimbo. It was meant to be an advanced sequel to the Google Edu on Air workshop about sheet powered Book Review Sites and it turned out to be much more. It was the kind of great conversation that keeps you up for a few nights while you work out what was discussed.

After the Hangout, James posted his hack of the advanced book review site and his thinking floored me. That evening I stayed up to 3 am incorporating his thinking into my own; I can truly say that the project is better and I am smarter as a result.

For background on the original book review site you can read this post or watch the Google Edu Hangout on Air. James took this site idea and brought it to another level:

• He used HTML in the Awesome Table list to include an average star rating next to each book title. which was such a smart way to provide feedback to users.
• He figured out a way to build a sheets function with “join” and “filter” to combine multiple reviews of the same book into an HTML snippet.
• He uncovered the templates feature of Awesome Tables which isn’t in the official documentation

James did a truly excellent job documenting these improvements on his blog.

James’ book review system is targeted to teachers, not students, and his design has different requirements as a result. I needed to incorporate his improvements into my system where posts are moderated and submission notifications are sent to teachers and librarians. My system also asks slightly more of the submitter, including a plot summary. In the near future I’ll write an explicit post about how I incorporated James’ system, but you are welcome to copy the demo sheet to pull it apart now. Full disclosure - there are a couple of clunky things I’d like to streamline, but it still works as advertised.

I’m so thankful for my PLN who is a daily source of inspiration and support. Every once in a while I’m shown how powerfully good this network can be. This was one of those times.

The Elusive Google Site Twitter Feed Found!

I love Google Sites, I really do. I encourage my teachers to use it for their class sites by expounding how great it connects with Google Drive. My pitch falls short when the teacher tries to embed her class Twitter feed and discovers that Sites doesn’t play nice. Never fear, all you have to do is create a custom Twitter gadget for Sites - and it isn’t even hard.

What you need to make your custom Twitter gadget for Sites:

• A simple text editor.  On my Mac I’ll use TextEdit. For Windows you can use Notepad.
• A Google Site with “Attachments” turned on
• A Twitter feed or list that you want to add to your site
• The HTML for your feed gadget

Create Your XML File

Lets start by creating a gadget XML file and open a new document in your simple text editor. You don’t want any formatting, so the simpler the editor, the better. (edit: Since I originally wrote this post it's clear from the comments that some have run into problems with their text editors. The file that you save must be a raw text file and lots of desktop editors don't do this without some fiddling. I recommend using the Chrome App "Text." It's free, it's cross platform, and it works great.) This XML file will be the delivery mechanism for your Twitter gadget. Copy and paste the following code into the new text file:

<?xml version="1.0" encoding="UTF-8" ?> <Module> <ModulePrefs title="Twitter Timeline"/> <Content type="html"> <![CDATA[

//Twitter feed goes here

]]> </Content> </Module>

Then save this file as “twitterFeed.xml.”

Get your Twitter Gadget HTML

Now you are ready for your Twitter feed. Log into Twitter and click on your avatar at the top right of the screen to access the setting menu, then click “Settings.” Once in the Settings window, click the last item in the left sidebar, titled “Widgets.”  Then, click the “Create New” button on the top right.

The “New Widget” screen gives you lots of control over how your gadget will work. Customize your gadget and click “Create Widget.” Then, copy the code on the bottom right. Return to the XML file that you created earlier and replace the text “//Twitter script will go here” with the feed code you got from Twitter. Now save the XML file.

Host Your Gadget

Hosting your new XML file is simple, all you need is a public Google Site. I’d recommend using the site where you are adding the Twitter Gadget. Scroll to the bottom of the Sites page look for the link to “Add files.” If this can’t be found you need to either turn on attachments (Settings > Page Settings) or sign into the Google Site.

Click “Add files” and attach your new XML file. Then, right-click, or command-click, the download icon to the right of the file and select “Copy link address.”

Add Your Gadget to Sites

Edit the page where you want the Twitter feed and click “Insert.” After this, click “More Gadgets.” Finally, click “Add Gadget by URL” and paste in the Twitter gadget XML link. Once you press the “Add” button you’re done: you’ve successfully created a Google Gadget for Twitter!

A Rebuild of the Moderated Twitter Form

A few months ago I blogged about a system that would allow students to tweet to a class Twitter feed after teacher moderation. Since that post, the good folks at the +New Visions for Public Schools Cloud Lab rebuilt Form Mule (an important component in the system) to work in the new Google Sheets. This post will explain how to build the Twitter Moderator system using the New Sheets, which I highly recommend you use. A few teachers asked for a video tutorial. I like a tutorial that I can read so I created both. I hope it's helpful!

This recipe has five components:

• A simple Google Form linked to a Google spreadsheet
• The FormMule Addon from New Visions for Public Schools
• A Gmail account created specifically for this process
• An If This Then That recipe
• A classroom Twitter account

First, create a simple Google form with a paragraph text question for the exit ticket. This PDF from Classroomfreebies was the inspiration. I used data validation to set the maximum character count of the question to 140 (this is a simple but important step.) Click “Require [Your Domain] to view this form” and “Automatically collect respondents username” if you are using this with students. Now, click “view responses” at the top of the form edit window to open the Spreadsheet attached to the form.

Then, add a column labeled “Moderated” to the responses sheet and install the New Visions FormMule Addon. Click “addons” in the menu bar, then “formMule”, then “Launch.” Once launched, select the Form Responses sheet for the source of merge data. Then, activate the time based trigger switch and set the trigger to run every hour. Click “Next: Templates and Send Conditions.”

We are only going to be sending one type of email. Change the send condition to point to the “Moderated” column. I usually use “Not Null.” This means that anything typed in the “Moderated” column will send the corresponding tweet to your Twitter account. If you want to be more specific, use a command like “yes.” Then, click “Next: edit templates.”

The email template is very simple. Click into the “To:*” field and add the GMail address that you created for this project. Click into the “subject” field and set this as well. Personally, I like using the Timestamp as the subject. You may also want to use the username. Then click into the “body:” field and then click the “What would you like to tweet about today?” tag. Finally, click “Preview and send all.” The Google side of the system is now done! Now we need to set If This This Than That (IFTTT) to send moderated tweets to Twitter.

Log into IFTTT, your new GMail account, and your class Twitter feed. Return to IFTTT and open this recipe. Once you add the recipe you will need to connect IFTTT to the Twitter and GMail accounts. Once the recipe is activated, scroll down on the edit window to the trigger and set the address to be the same GMail account you used in formMule. Click “Update” and you are done!

There you have it - the updated Twitter moderation feed using the new Google Sheets and formMule Addon. As before, let me know if you use it in your classroom! If you think of a way to make it better, let me know about that, too!