Google Sheets Blogging CMS, part 2

This is the second post in a three part series on using Google Sheets as the database for  a blogging CMS. In this post, I’ll walk you through the Google Sheet itself and the Google scripts (their version of js) that drive it. In the first post, I explained the motivations for building the system. 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.

Inspired by Tom Woodward and Martin Hawksey, I’ve been using Google Sheets as a database for various projects for a while now. I’ve written scripts to collect Hypothes.is web annotations from their APIsave the choices of players going through choose-your-own-adventure Twine Games, identify unused Domains of One’s Own within OU Create, and track my own writing both here on my blog and on various article and book projects. Google Sheets basically provide CSVs that can be written and read via API.

The Limitations of Google Forms

When we were brainstorming the interface for Prof. Jenel Cavazos‘s psychology 1113 class blog, I wanted to use a form that didn’t require a login to collect blog posts and store them in a database. I also wanted a form that wouldn’t strain OU Create’s servers. Alan Levine’s Splot forms would do a great job of collecting the blog posts, but I worried that if all 950 of Prof. Cavazos’s students submitted assignments at the same time, it would crash our OU Create servers. Google Forms satisfied both conditions and seemed an obvious choice.

One problem is that you cannot submit files via Google Forms. We wanted students to be able to submit ‘featured images’ for their blog posts in the way that WordPress uses featured images. Google Forms also doesn’t have a way of collecting rich text in long form text entry fields.

A blog with no images and no rich text isn’t much of a blog, so I decided to create my own form instead of using Google’s. At first, I thought I would write and host a stand alone form and connect it to Google Sheets using Martin Hawksey’s HTTP Post methods. During the research for this idea, Tom Woodward suggested I look at Amit Agarwal’s work on handling file uploads within a form built in Google Scripts.

In his post and his blog more broadly, Amit Agarwal showed how to build a traditional html form in Google Sheets and use it to upload images into Google Drive. Agarwal also built a really clever interface in WordPress that will put together these custom forms. This is a great way to get really functional forms, but it requires purchase of licenses, and I wasn’t sure that Agarwal would release the code that I needed to connect the sheets with my final website. So, I used the method that Agarwal discussed in his blog post for accepting file uploads and spliced it with Hawksey’s work on writing to Google Sheets from Google Scripts. In the end, I created a form that collects all the information needed for a PSY1113 blog post, stores the ‘featured image’ in Google Drive (per Agarwal), and then records the information for the blog post to a row in a Google Sheet (per Hawksey).

Google Script Code Walk Through

Clicking on this link will create a copy of the Google Sheet I created. From within that sheet, you can click on Tools>Script Editor in the menu bar to work with both the custom form and the Google script for this project. In the next post in this series, I’ll share the code for the website, so that you can stand up a copy of the entire project and do whatever you want with it.

I’ve also put the code in a GitHub repository. You’re welcome to copy, fork, read-along, or do whatever you want with that (within the parameters of a GNU GPLv3 license). Below I’m going to walk through a few pieces of the code that I thought were particularly interesting.

function doGet(e) {
  var output = HtmlService.createHtmlOutputFromFile('forms.html').setTitle("Post to the PSY1113 Blog");
}

Within the Google Scripts, there are two files: server.gs and forms.html. The server.gs file is the primary file, the one that Google is running when we set up our web app. The key function within the file is this doGet which calls our form for the project. Rather than getting data from an external source, we create a forms.html file and get the data directly from it.

Once someone has submitted the form (I’ll discuss the form itself in a minute), the server.gs file runs a couple of functions in sequence. The first function, called uploadFileToGoogleDrive, takes the uploaded image and stores it in the sheet creator’s Google Drive in a directory called ‘Files Received.’

var dropbox = "Received Files";
var folder, folders = DriveApp.getFoldersByName(dropbox);
    
    if (folders.hasNext()) {
      folder = folders.next();
    } else {
      folder = DriveApp.createFolder(dropbox);
      folder.setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
    }
    
    /* Credit: www.labnol.org/awesome */
    
var contentType = data.substring(5,data.indexOf(';')),
        bytes = Utilities.base64Decode(data.substr(data.indexOf('base64,')+7)),
        blob = Utilities.newBlob(bytes, contentType, fileName),
        file = folder.createFolder([lastName, title].join(" ")).createFile(blob),
        id = file.getId();

This bit of code checks to see if you have a directory called ‘Files Received.’ If not, it creates the directory. Then it creates a subdirectory based on the last name and title collected in the form. It then stores the image from the form in that directory, and records the ID for the image. Because this is the first function, this version of the code requires an image upload. If we wanted to make the image upload optional, we could rewrite a few lines to decouple the two functions so that either could be run at the time of form submission.

In it’s current form, once the file has been stored the second function, sheetRowGenerator, is called. This function is adapted from Hawksey’s work. It takes all of the information from the form and the uploaded file and writes that information to a row in the Google Sheet that is attached to this Google Script. My version of the script looks at which section (house) of the class the student is in, and writes the data to the sheet for that section.

function sheetRowGenerator(firstName, lastName, title, blogText, house, assignment, assignmentText, id) {

    var lock = LockService.getPublicLock();
    lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
 
try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(house);
 
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    row.push(nextRow); //using the row number as an ID for the blog post
    row.push(new Date());
    row.push(firstName);
    row.push(lastName);
    row.push(title);
    row.push(blogText);
    row.push(house);
    row.push(assignment);
    row.push(assignmentText);
    row.push(id);
    // more efficient to set values as [] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());

The outer pieces of this code, get the key and ID of the sheet that you’re working from. They pass this information into the inner functions which find the next blank row of that sheet, and then write the long list of variables to the sheet. The last variable pushed, the ‘id’ is actually the id of the image that the user uploaded. The image can be viewed if we plug the id in at the end of ‘https://drive.google.com/uc?export=view&id=’. I’ll return to this point in the next post when I walk through the code for the blog website.

So in sum, we have Agarwal’s code to store the uploaded image in our Google Drive account, and a modified version of Hawksey’s code to store the information for the blog post in a row of our Google Sheet. Next, let’s look at the Form itself.

Custom Google Form for Blogging

Above, I noted how the server.gs file calls the forms.html file to create the form. This forms.html file is a fairly typical html file, and I’ve included some css and js in mine to get it to look and act how I want. I’m sure that I could have separated these pieces out into other files. If I’d had more time, I’d have written a shorter form file, but this one works. You can see and fill out the demo form here.

Screen shot of a custom form created with Google Scripts.Custom form created with Google Scripts

Within the form itself, the two interesting pieces to me are that I’ve used Materialize CSS and Quill.js. Materialize was a carry over from Agarwal’s work. I like how the field labels react as you enter information, and the way that they offer visual cues to verify your inputs.

Quill was the more exciting find in this project. Quill JS is a jquery-based, rich text editor. It stores both your work and the history of changes in that work in a proprietary json format called a ‘delta.’ Here’s the js code to instantiate the Quill container:

      var quill = new Quill('#editor-container', {
        modules: {
          toolbar: [
            ['bold', 'italic'],
            ['link', 'blockquote', 'code-block'],
            [{ list: 'ordered' }, { list: 'bullet' }]
          ]
        },
        placeholder: 'Compose an epic...',
        theme: 'snow'
        });

You can get some notion of how Quill might be used as a Wiki here. Quill’s deltas are hard to work with, because you have to write the json parsing code to extract the html for display. They seem to want you to use Quill for both input and display. That would allow for an interesting post-publication editing interface, but it’s not what I had envisioned. Instead, I used a function called inner html that returns a json string of the html written by the user. I passed this json string into a cell of my Google Sheet and then passed it to my blog site for rendering.

When a user is done filling out the form and clicks submit, the form checks to see if all the fields were filled in and then passes the data back into server.gs. Sorry for the length of this bit of code. I think it could be optimized, and feel free to skip it if you don’t care. 

var file, 
          reader = new FileReader();

      reader.onloadend = function(e) {
        if (e.target.error != null) {
          showError("File " + file.name + " could not be read.");
          return;
        } else {
          var firstName = $('#first_name').val();
          var lastName = $('#last_name').val();
          var blogTitle = $('#blog_title').val();
          var blogText = quill.root.innerHTML;
          console.log(blogText);
          var house = $('#house').val();
          var assignment = $('#assignment').val();
          var assignmentText = $('#assignment').find(":selected").text();
          google.script.run
            .withSuccessHandler(showSuccess)
            .uploadFileToGoogleDrive(e.target.result, file.name, firstName, lastName, blogTitle, blogText, house, assignment, assignmentText);
        }
      };

      function showSuccess(e) {
        if (e === "OK") { 
          $('#forminner').hide();
          $('#success').show();
        } else {
          showError(e);
        }
      }

      function submitForm() {

        var files = $('#files')[0].files;
        var firstName = $('#first_name').val();
        var lastName = $('#last_name').val();
        var blogTitle = $('#blog_title').val();
        var house = $('#house').val();
        var assignment = $('#assignment').find(":selected").text();
        
        if (firstName.length === 0 || lastName.length === 0 || blogTitle.length === 0 || house.length === 0 || assignment.length === 0) {
          showError("Please fill out form completely");
          return;
        }
        
        if (files.length === 0) {
          showError("Please select a file to upload");
          return;
        }

        file = files[0];

        if (file.size > 1024 * 1024 * 2) {
          showError("The file size should be < 2 MB.");
          return;
        }

        showMessage("Uploading file..");

        reader.readAsDataURL(file);

      }

      function showError(e) {
        $('#progress').addClass('red-text').html(e);
      }

      function showMessage(e) {
        $('#progress').removeClass('red-text').html(e);
      }

You can see that the file size for uploads has been limited to 2MB. Generally, I don’t use large images for the web, to minimize load times. Also, we have 950 students, and we’re planning on as many as a dozen blog assignments. With 11,400 blog posts, you could quickly consume gigs of storage. 

Rather than running php or HTTP POST call to send the data to some form of storage, this file calls the uploadFileToGoogleDrive function and passes all of the collected data to that function as parameters.

Takeaways

Here are the steps for creating your own copy of this project and getting it running. I elided a few things in the walk through above, but (hopefully) this has all the steps:

  1. Create a copy of the Google Sheet I created.
  2. From within that sheet, you can click on Tools>Script Editor in the menu bar to access both the custom form and the Google script for this project.
  3. Within the Form, customize the list of Sections (lines 63-70) and Assignments (lines 77 & 78)
  4. If you add any fields or change any fields, make sure to update the functions starting on line 142 of the forms.html file to collect and pass the correct information from your form.
  5. Once you’re happy with how everything looks, click on the server.gs file, and select the Setup function from the ‘Select Function’ drop down menu. Hawksey wrote this function to help get your scripts authorized within your Google account and make sure the functions are properly linked to your Google Sheet.
  6.  Select ‘Publish>Deploy as Web app’ from the menu to make your project live. Set the Project version to ‘new’ and republish as new anytime you make a change to your code. Also set the ‘Execute the app as’ to ‘Me’ and the ‘Who has access to the app’ to ‘Anyone, even anonymous.’
  7. Once you hit publish (or update) you will get a Current web app URL for your form.
  8. From your Google Sheet, clear out any demo data you don’t want.
  9. Click on ‘File>Publish to the web’ from the menu to open access to your sheet to use it as a DB. Make sure the settings are set to ‘Entire Document’ and ‘Web Page.’ 

What I learned from this project is that you can host a form from Google Scripts, use it to build a (headless) database, and then call that database remotely for other websites. By hosting the script as a Web App (publish as Web App), you get a  secure, https URL that can collect data.

The quota limit that I’ve found is that we can’t exceed 100 form submissions per 100 seconds. I’m a little worried that we might also be limited to 500 images uploaded per day. If that is the case, I will rewrite the functions above to make the image upload optional or allow for image URL links as opposed to uploads.

Google Sheets provides a fairly easy and malleable interface for managing a CSV and can be publish the data as a JSON for easy access in other sites. In the next post, I’ll show how I call the sheet and display the data using jQuery.

Previous

Google Sheets Blogging CMS, part 1

Next

Getting Started with WordPress in OU Create

3 Comments

  1. This is something I had halfway-played with for a high school course last year and never ended up publishing. Using Sheets to run a backend is really powerful and we’re actually using a similar method to handle our professional development registration page.

    Once thing I’ve run into is multiple writes from time to time, meaning a form is submitted by the client and you get duplicate data. It’s a bug we’ve had in our app for several months now that I can’t seem to find. My guess is that it has something to do with my `lock` not calling correctly.

    I’m curious: did you do the page styles yourself? Or did you use a template to put the blog homepage and layout together?

    • John Stewart

      I’ll keep an eye on that duplicate data. I’m hoping, as you suggested, that the lock prevents it, but we’ll see.

      The styles for the website are based off of HTML5Up’s Editorial theme. The styles for the Google Form are Materialize CSS with a little bit of tweaking.

      • Just thinking on the fly, but another potential application of this would be an introductory web design course. Students could learn the principles of front and back end scripting easily and then abstract up into PHP or Node JS. It is pretty amazing what you can make with a spreadsheet and a few lines of script.

Leave a Reply

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

Powered by WordPress & Theme by Anders Norén

css.php