Google Sheets Blogging CMS, part 1

This is the first post in a three part series on using Google Sheets as the database for  a blogging CMS. In this post, I’ll explain the motivations for building the system. In the second post, I’ll walk you through the Google Sheet itself and the Google scripts (their version of js) that drive it. In the third post, I’ll share the website that displays the blog, and the code behind it. My guess is that interest in the three pieces will vary for different audiences, so I wanted to encapsulate the descriptions.

At the beginning of the summer, I met with Prof. Jenel Cavazos about introducing blogs into her Psychology 1113 course. This freshman level introduction to psychology is one of the largest classes on campus with 900 students enrolled in a typical fall semester. Additional sections of PSY1113 taught by other instructors often enroll another 300 students. 

Screen shot of the OU Create landing page at https://create.ou.edu.
OU Create landing page

1200 student blogs in a single course is a lot. At OU, we have a Domain of One’s Own project, where every student, staff and faculty member can sign up for a web domain with 5GB of storage. OU Create currently has about 5000 users running more than 6000 websites in their domains. To support this, we work with Reclaim Hosting to run five virtual private servers on Digital Ocean. We spin up a new Droplet (VPS) for each 1000 users to spread the minimize the strain on the servers and make sure a server outage doesn’t take down the whole system.

Increasing our user base by 20% and spinning up a new VPS for a single class each semester would strain our resources, so we started brainstorming other options for how we could provide a blogging platform for the class.

Possible Blogging Options

For one of our architecture classes, we use a single course blog, and all of the 100-300 students login as users to blog in that one site. However, that number of users already strain the ability of a website to handle simultaneous logins and posts. With 900 students potentially logging in to submit posts before an assignment deadline, we didn’t think this option would be stable enough.

Simply using WordPress.com and other free blogging platforms would work and would move the traffic onto a distributed network of servers. However, it would be difficult to support the students with any technical issues that might come up. It would also be difficult for Prof. Cavazos and the other instructors to keep track of the 1200 URLs for their students.

We thought about having the students just write in Google Docs or other cloud based word processors. However, this option would sacrifice the open audience of blogging. One of the benefits we see in blogging is that it encourages students to move from an audience of one (the instructor) to a potentially vast audience. The meta cognitive task of thinking about audience changes what and how we write. It can also support a sense of efficacy for students in that they are publishing their work and contributing to the knowledge base of the web.

Our Blogging Interface

Since none of the off-the-shelf options quite fit our needs, we decided to try to build something new. The design constraints for the blogging project were:

  1. A simple user interface, both for the students and the instructors
  2. Don’t crash our servers or spend a ton of money standing up a new server for 1200 users
  3. The blog should be public facing
  4. There should be a commenting functionality

The easiest UX that we’ve come up with is front-end blogging from a form. A user simply fills out a form with their name, the title of their blog post, the text of their post, and an image for the post. There’s no login and no need to navigate the UX of WordPress or another blogging service.

We’ve played with this concept of front-end blogging for a while now. Alan Levine has built an open sourced tool called TRU Writer that even provides this type of front end interface on a WordPress site. My colleague, Keegan Long-Wheeler, has built similar form interfaces into several of his more recent faculty development websites. However, these front-end interfaces still rely on the WP database to handle the submissions, and thus they would both be potentially overwhelmed by 900 submissions when an assignment is due.

For my new system, I wanted to use a Google Form to replicate the form in Alan’s system but shift the burden of traffic onto more robust Google app servers. However, Google Forms don’t allow you to upload images or use a Rich Text Editor to write extended text blocks. This would mean our blog posts would have no links, lists, embeds, or anything else that makes them more than just a text file.

Instead of Google Forms, I decided to use Google Scripts to build my own custom form in HTML and push the information from the form into Google Sheets. Accepting file uploads and rich text and saving them to a Google Sheet was not easy (at least not for me), but I will share the code and details on how I built this in the next post in the series.

Custom form served by Google that posts form results to Google Sheets

One potential problem with front-end blogging is that anyone can fill out the form and potentially fill the site with spam. However, my custom form is served from a url that is not indexed by the Google Search engine and not linked from any public facing websites. It is however embedded in our learning management system for the class, Canvas. Thus students write their blog post in the LMS, and it shows up on the website.

Blog Site

The custom form provided the interface for writing blogs and storing the data in a Google Sheet. The next problem was displaying these posts as an actual blog site.

For this step, I used Google’s API engine to expose the Google Sheet data as a json file. I then called that json file into my website using jquery, and read the data into an index.js file. Then I parsed and paginated the data, and passed it into an html container for display on an index.html page. I copied this site architecture and tailored it for each of the 16 sections in the 900 person PSY1113 course. Thus we have 16 team websites, each displaying the blogposts for the 50-60 students in that team. Again, I’ll give a walk through of the actual code in the third post in this series. For now, here’s a screen shot of the Team Ainsworth site (each of the teams is named after a noted psychologist) and a second screen shot depicting how demo data is rendered as blog entries.

Screen shot of the Team Ainsworth website
Screen shot of the Team Ainsworth site. Hopefully, by the time you’re reading this, students will have begun to populate the site with posts.
A screenshot demonstration of how placeholder blog post entries are displayed using jQuery
Placeholder blog post entries are displayed using jQuery

From the index page for each site, you can click on the link for an individual blog post to read that post. These links actually all point to a single blog post page. However there’s a blog post ID “parameter” attached to the url for this page that tells the system which row of data from the Google sheet to display for the post. Javascript within the blog post page pulls that data and then uses it to build the display for the particular blog post you want to read. The third party commenting service, Disqus, provides a commenting interface to leave feedback on the given “blog post” and keeps the comments separate using the blog post ID parameter.

In addition to the public facing site, this build also provides the instructors for the course with the Google Sheet itself as a space for reading student blog posts. You could filter the sheet to see the contributions of a certain student or all contributions submitted in a given timeframe. You could graph the number of blog posts or number of words that meet a given criteria and you could easily export the text set for text analysis. You could also use the json for the entire blog set to create alternative views and visualizations that highlight linkages and themes. 

Potential Applications

I think this Blog via Sheets tool is going to work well for the 900+ students in PSY1113 because it 1) is easy to use, 2) won’t crash, 3) presents their work on the open web, and 4) has commenting.

I could see this tool being used by anyone who wants a fairly inexpensive blogging platform for 500-2000(ish) people. A high school or college could tailor the code and tweak the css to spin up their own blogging platform. I could see an office like ours (Office of Digital Learning) using this to share our work both internally and with the broader digital learning community. I could also see Personal Learning Networks (PLN’s) using this type of interface to create ad-hoc blog communities. 

Previous

Getting Ready for Gutenberg

Next

Google Sheets Blogging CMS, part 2

6 Comments

  1. This is super slick, John, and an elegant solution for a simple writing space. I’m going to be looking at this more closely thinking of ways to do more with json in an out of WordPress.

    Anxious to see how it flows out; the one hitch might be how people can edit something they wrote (or maybe that comes with the google form front end??)

    Nice alchemy!

    • John Stewart

      Yeah, right now, there’s no way to edit something that you wrote. I’m going to try to put out the next post in the series tomorrow or Wednesday. I think you’ll like the tool I used for rich text input – quill.js. I haven’t figured out security and verification with it, but in terms of Wikis and easy input, it’s pretty fantastic.

    • I generally like where John is taking this idea and the fact that he’s actively experimenting and documenting what he’s coming up with as potential solutions. While I do like some of the low-tech angle that he’s taking, I’m not sure, based on what he’s written, how some of it will come out within the broader spectrum of DoOO or IndieWeb-related technologies.

      For example:

      How easy/hard will it be for students to own/export their data after the class?
      How might they interact if they’re already within the DoOO cohort or already self-hosting  their own space?
      What are the implications for students of maintaining multiple spaces with a variety of technologies and therefor overhead?
      I’ve never had a lot of luck with Disqus, which I find to be heavy and often has problems with auto-marking all of my content as spam. I’ve definitely found it to be an issue with using for POSSE workflows. Worse, with the introduction of specifications like Webmention to the DoOO space, students could be writing their responses to classmates and teachers on their own sites and thereby owning all of that content too, but with Disqus, this just isn’t possible.

      I’ll reserve judgement for once I’ve seen some of the code and further ideas in parts II and III as I suspect he’s likely taken some of these issues into account.

      We’ve played with this concept of front-end blogging for a while now. Alan Levine has built an open sourced tool called TRU Writer that even provides this type of front end interface on a WordPress site.  

      I’m curious if John, Alan Levine, or others have yet come across the concept of Micropub? It generalizes the idea of a posting client and interface so that it could work with almost any CMS-related back end. I could see people building custom micropub clients for the education space, or even using some of the pre-existing ones like Quill, InkStone, or Micropublish.net. Many of them also use JSON or form encoded data that they could also be using with platforms like the one John describes here. The other nice part about them is that they’re flexible and relatively open in more ways than one, so they don’t necessarily need to be rebuilt from scratch for each new CMS out there.

  2. Taylor Kendal

    JS, this is awesome! Very creative solution to a widespread need in academia. I could see this becoming a scalable project that layers nicely with Cogdog’s ever-evolving alchemy, Hypothes.is, DOOO, and a host of other highered hacks.

    Looking forward to the evolution and happy to help support as needed.

    • John Stewart

      I haven’t tried hypothes.is on the pages yet, but I will do once we have some live data. Disqus is working so my guess is that H will also be able to use the parameters to tell posts apart.

  3. Man, this is so cool! Found your page randomly while googling how to build a CMS using google apps. I’ve built a few silly apps using Google Apps before, like this: https://thevanitymetric.com/random-startup-business-idea-generator/, but this is some next level stuff – the interface alone is great!

    Can’t wait to see what more arrives!

Leave a Reply

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

Powered by WordPress & Theme by Anders Norén

css.php