Web Developer, Open Source Software Enthusiast, Coffee Roaster, Sports Fanatic and Writer

Static Site Comments with jQuery and Google Forms/Sheets

  • Posted: Mon Aug 17 2015 17:07:36 GMT-0500 (CDT)
  • Updated: Tue Aug 25 2015 14:17:51 GMT-0500 (CDT)

Tags:

Another alternative to Disqus for static site comments, with native HTML5 form feeding Google Forms via AJAX and existing approved comments displayed via jQuery's .load() method on the Google Sheets backend.

Update

If you are using this in conjunction with my "A Simple(r) jQuery Sticky Footer" solution, call the initial stickyFooter(); function as a callback to the getComments(); function, i.e. getComments(stickyFooter);.

Background

This was inspired by a workaround I came up with while developing an Amazon-style 5 star feedback system for a large PC, server, and network appliance manufacturer for its LMS. There, The developer of the LMS also hosted it for us, and wouldn't grant access to a database (nor the Java backend to access it, without charging a pretty penny to develop it themselves). Likewise, the scheduling, budgeting, and interdepartmental procedures to have our internal IT set up a solution were neither time- nor cost-effective.

What I came up with, using my limited access (writing front-end JavaScript, CSS, and HTML only) was to feed a Google Form (via jQuery's .ajax()) with the user's 5-star rating , comment, and email address. In a separate AJAX call, I got the average star amount (again, similar to Amazon), calculated within the form's Google Sheet backend.

YASSBCSTTSWYA (Yet Another Static Site Blog Comment System Thingy that Starts with "Yet Another")

OK, so I haven't seen many. There's Poole, which requires GitHub access. There's Kaiju, which requires Go, Socket.io, and MongoDB. Jekyll has (at least) one, but I'm not using Jekyll. Then there are Discourse, Disqus (not open source), and others I haven't tried. I decided to go my route because:

  1. I don't want to sign in with GitHub to set up/manage my comments. I have a Sheets app on my Android phone and tablet that I can use to approve/delete comments collected with my method.
  2. This requires no database nor server-side language whatsoever on your server. You can build your static site, with comments, using literally just a static web server. Aside from setting up the Google Form and managing the Google Sheet, everything else is just front-end code.
  3. I wanted to showcase the technique I devised at the aforementioned tech giant, without using the same code (which I wasn't given permission to open source). This way, I'm safe (I think. If this page disappears, you know why).

OK, let's get started.

1. Set up Google Form

Go to Google Forms to create a new form.

Set up the fields you will want in your form, such as name, email, comment, etc. Mine looks like this:

Google Forms Fields

Set up your field types, validation, and required fields ("Comment" is an obvious choice) as you see fit.

Once set up, click the "View live Form" button in the toolbar above the form.

2. Get info necessary for AJAX submission

In the live form view, you'll need to grab the name attribute for each field you plan on submitting to the Google Form from your website's form (or JavaScript function, or Handlebars YAML front matter, as I'll do with "Post Title" in a bit). For my form, I need the first four fields: "Name", "Email Address", "Comment", and "Post Title".

3. Go to Google Sheet back-end

Back in the editing window/tab for your Google Form, click the "View responses" button in the toolbar above the form. This will open the back-end Google Sheet where your form submissions are recorded. Right now, the sheet only has one workbook (which Google calls a "sheet", called "Form Responses 1" (which you can see on the single workbook tab at the bottom of the page). The first cell of every column is the header for that column, and its name will match the given form field feeding its entries starting with its second cell. Here's what mine looks like:

Google Sheets First Workbook

You'll notice a few columns which do not corespond with my Google Form fields: "Timestamp", which is created by Google Forms to put a timestamp on each form submission, and "Approved", which allows me to keep unapproved comments from reaching my page's comments section. More on that next.

4. Create a second workbook (optional)

This step is optional, but highly recommended. As previously mentioned, I have an "Approved" column to manually approve comments before they show on my site. I also have a second workbook that filters out all rows which do not have "yes" in their corresponding "Approved" cell (I renamed this workbook to something more obvious than "Sheet2", "Approved"):

Google Sheets Second Workbook

Notice that I have cell A1 highlighted, and that the fx bar above doesn't read "Timestamp", but rather a query which pulls columns A ("Timestamp"), B ("Name"), D ("Comment"), and F ("Post Title") from the first workbook ("Form Responses 1"), filtering the results by only those which have a "yes" in column E ("Approved") and ordering by column A (again, "Timestamp"). Google Sheets will automatically populate the proper amount of columns based on those selected in the query ("select A, B, D, F"), and rows based on the number of form responses which fit the filter ("where (E='yes')).

In addition to filtering based on posts I have approved, this extra workbook/"sheet" has the potential to drastically reduce the amount of data jQuery will need to both "import" (.load() method) to make available for your site's comments display, and that which jQuery will need to parse to present it.

5. Publish your Google Sheet

At the top of the Sheet page, there is a menu like you'd find in a desktop app. In the "File" menu, click "Publish to the web..." and a modal/"popup" window appears:

Publish Google Sheet

In the "Link" section (the default tab), change the first dropdown from "Entire Document" to the name of the workbook/"sheet" you want to publish, displayed in the dropdown by name (in my case, the second or "Approved" workbook) and click "Publish".

You will then get a URL to the public view of your spreadsheet. Copy that URL, as you'll need it for your .load() method.

6. Finally! Start creating the comment section on your site!

First, let's lay out the HTML. Keep in mind that my site is built with the Zurb Foundation CSS framework. Most of the CSS selectors will be irrelevant to you, unless you also use Foundation. Those which are relevant have "comment" or "sheets" as part of the class name or ID. Here's how mine looks (pay attention to the comments):

<div class="small-12 column">
  <h4>Comments</h4>
  <!-- This div will contain all comments parsed from the workbook loaded in the next div -->
  <div id="comments">
    <!-- This paragraph will be removed if any approved comments exist for this page, and replaced by them -->
    <p>No comments so far...</p>
  </div>
  <!-- This div will be hidden by CSS until user submits a comment. This div will then preview said comment -->
  <div id="post-response"></div>
  <!-- This div will be hidden by CSS, and only exists to load your public sheet -->
  <div id="sheets-load"></div>
  <!-- This div contains the form to post a comment -->
  <div id="post-comment">
    <!-- Note the lack of "action" and "method" attributes -->
    <form id="comment-form">
      <fieldset>
        <legend>Leave A Comment</legend>
        <label id="comment-name-label">Name:
          <input name="name" type="text" id="comment-name">
        </label>
        <label id="comment-email-label">Email:
          <input name="email" type="email" id="comment-email">
        </label>
        <label id="comment-text-label">Comment:
          <textarea name="comment" id="comment-text"></textarea>
        </label>
        <!-- This label and input will also be hidden by CSS -->
        <label id="comment-website-label">Website:
          <input name="website" type="text" id="comment-website">
        </label>
        <!-- This input does not receive user input, but we need the name attribute -->
        <input type="hidden" name="entry.918346186" id="comment-posttitle">
        <!-- Reset the form -->
        <input type="reset" class="button large dark-gray" id="comment-reset">
        <!-- Submit (via jQuery .ajax(), does nothing without) -->
        <button class="large" id="comment-submit">Submit</button>
      </fieldset>
    </form>
  </div>
</div>

If you look at my comment form at the bottom of this page, you'll notice that there is no "Website" field (nor label). That field (hidden by CSS) is a very crude honeypot, an extra safeguard in addition to the requirement of enabling JavaScript to submit the form. I'm a firm believer that bots should be punished for being bots, not humans for being humans.

Now the jQuery (keep in mind that I use Handlebars templates, hence the funny {{...}} blocks). Again, watch for comments:

/** google-forms-comments.js */
function postToGoogle() {
  var field1 = $('#comment-name').val();
  var field2 = $('#comment-email').val();
  var field3 = $('#comment-text').val();
  var field4 = {{posttitle}}
  // This is why the "Website" label and input are hidden. IF filled, the form doesn't submit.
  if ($('#comment-website').val().length === 0) {
    // AJAX POST submission to your Google Form
    $.ajax({
      // Note the URL - your public Google Sheet
      url: "https://docs.google.com/forms/d/17QR6prW_BA8XOCLN21dB01ltr3JTuNrXqEwdsG6e5Mo/formResponse",
      // name attributes from the Google Form
      data: {"entry.1770072770": field1, "entry.1979279519": field2, "entry.1951982637": field3, "entry.918346186": field4},
      type: "POST",
      dataType: "xml"
    });
  }
}
function custommsg() {
  // Hide the comment form
  $('#post-comment').css("display", "none")
  // Populate with a preview of the user's comment (pending approval)
  $('#post-response').html('<div class="comment"><p>If approved, your message will appear as follows:</p><h5>' + $(this).prevAll('td:nth-of-type(2)').text() + '</h5><p>' + new Date()/* Now */ + '</p><p>' + $(this).prevAll('td:nth-of-type(3)').text() + '</p></div>');
  // Show preview of user's comment (pending approval)
  $('#post-response').css("display", "block");
}
// Call the functions above when the "Submit" button is clicked
$('#comment-submit').click(function() {
  postToGoogle();
  custommsg();
  $('#comment-submit').attr('disabled', true);
  return false;
});
// Load the public view of your Google Sheet (or the selected workbook thereof)
function getComments(callback) {
  $('#sheets-load').load("//docs.google.com/spreadsheets/d/1E0KDOnFPM8Icvs2Fnv24nT8N01WM_WeAUvjUfpWRnXg/pubhtml", function() {
    // Filter Sheet to match only comments submitted from this page, based on Post Title
    $('#sheets-load td:contains(' + $('h1').text() + ')', function() {
      // Handlebars-specific: Filters (again) public Google Sheet to build comments in a ".each()" loop if the "Post Title" cell matches this post's title
      $('#sheets-load td:contains(' + $('h1').text() + ')').each(function() {
        $('#comments').append('<div class="comment"><h5>' + $(this).prevAll('td:nth-of-type(2)').text() + ' <small>' + $(this).prevAll('td:first-of-type').text() + '</small></h5><p>' + $(this).prevAll('td:nth-of-type(3)').text() + '</p></div>');
      });
      // Clear the "No comments so far..." message, but only if approved comments exist for this post
      $('#comments .comment').each(function() {
        $('#no-comments').remove();
      });
    });
    callback();
  });
}


Let's create the CSS (or Sass, in my case):

#comments {
  .comment {
    /* Separate comments from one another with a border */
    border: 1px solid #DFDFDF;
    padding: 10px;
  }
  .comment:nth-of-type(odd) {
    /* Make "striped table" style for multiple comments */
    background-color: #F8F8F8;
  }
}
#post-comment {
  form {
    fieldset {
      /* Foundation-specific. Safe to disregard */
      background-color: #F8F8F8;
      #comment-website-label {
        /* Hide "Website" label and nested "honeypot" field to prevent actual users from filling */
        display: none;
      }
      legend {
        /* Foundation-specific. Safe to disregard */
        border: 1px solid #DDD;
      }
    }
  }
}
#post-response, #sheets-load {
  /* Hide #post-response until populated with a preview of user's comment submission. Hide #sheets-load always */
  display: none;
}


Finally, call the getComments(); function (below all HTML content):

<script>
  $(function() {
    getComments();
  });
</script>


If used in conjuction with my stickyfooter.js:

<script>
  $(function() {
    getComments(stickyFooter);
  });
</script>


I'm sure I'll find lots of things to tweak, but as-is I have a functional comment system for my Assemble-generated static site.

Comments Powered by Google Forms

No comments so far...

Leave A Comment