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.