Building a Comment Section with Free Tools

Google Spreadsheets and Google Apps Script

Google Spreadsheets is basically the Google equivalent of Excel — a spreadsheet application for your daily or business needs. For each Google Spreadsheet, you can have a Google Apps Script written in JavaScript for automation. Both tools have free-tier options.

The main point here is that the Google Apps Script can be deployed as a web app, able to serve files and data, and has easy connection with Google Spreadsheets. For this project, I only care about the data, so the Apps Script functions as an API for me.

Choosing Google Apps Script

There are some free-tier services that allow the implementation of APIs and databases out there, so why Google Apps Script?

Well, I admit that I didn’t research enough at first and ended up with something I knew for sure was free and simple. Now that I have already spent a good amount of time making it work, I’m going to ship it as it is for now.

Still, I think it has some advantages. I am dealing with Google, a well-known provider that I can trust, and Google Spreadsheets is such a simple "database" to edit that I could set up a web project for someone with little tech experience, and they would still be able to manage their data. Provided that they have ever had a Google email account, they will probably feel familiar with the tool. Therefore, learning about these tools can be good for some freelancing clients.

Using doGet(e) and doPost(e)

Google Apps Script uses doGet and doPost as endpoints for the HTTP methods GET and POST. It’s not as flexible as web frameworks available for Node.js, which let you create as many endpoints as you want, but it’s sufficient for my needs right now.

However, there’s a problem: Apps Script was made mainly for the Google ecosystem, and there are some limitations when dealing with other domains.

For instance, I could not make my website access the doPost endpoint — the CORS (Cross-Origin Resource Sharing) settings on my browser were not allowing communication with doPost. To solve this problem, I would need to set the response header of doPost, but Google does not allow that for security reasons.

My solution was to use an email service as my POST request… embarrassing, I know.

EmailJS

EmailJS is a service that allows you to send emails from your front-end JavaScript code to your email inbox. Browsers don’t allow you to send emails directly from front-end JavaScript for security reasons, so EmailJS acts as a middleman. Your HTML form sends a message to the EmailJS servers, which then forward the message as an email to your inbox.

Once the email has arrived, its message can be brought into the spreadsheet by automation written in the Google Apps Script code.

But now we face another problem: Apps Script does not trigger actions based on email arrival. I could set a timer so a script would check my inbox periodically, but that solution seems very unoptimized — a short time interval would cause Apps Script to work in vain many times. A long time interval would cause users to see no immediate result after submitting a comment, making them think the comment section was broken.

My solution for this was to check the inbox every time a GET request is made. In the doGet function, the code first checks the inbox for new emails with the subject "blog_comment", brings their content into the spreadsheet, copies the table, turns it into a JSON file, and then sends it back as a response.

This approach makes fetching slower, but it’s good enough for now.

My App Script code for now

function doGet(e) {
  // check for new comments mails to add to the spreadSheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const threads = GmailApp.search('subject:blog_comment is:unread');

  threads.forEach(thread => {
    const messages = thread.getMessages();
    messages.forEach(msg => {
    const name = msg.getFrom().split(' <')[0];
    // I use ##end## to separete some parts of the email body
    // You dont need to write it on your comment. It is placed there by default 
    const [postId, message] = msg.getPlainBody().split("###end###");

    sheet.appendRow([postId, name, message]);
    });
    thread.markRead(); 
  });

  //returing the data as a json
  const data = sheet.getDataRange().getValues();
  const postId = e.parameter['post_id'];
  const filtered_data = data.filter(row => String(row[0]) === String(postId));


  var jsonData = [];
  for (var i = 0; i < filtered_data.length; i++) {
    jsonData.push({
    PostId: filtered_data[i][0],
    Name: filtered_data[i][1],
    Message: filtered_data[i][2]
    });
  }

  return ContentService.createTextOutput(JSON.stringify(jsonData))
    .setMimeType(ContentService.MimeType.JSON);

}

            

Neocities Content Security Policy (CSP)

Now, with my backend set, it was time to make some request tests. They failed... Apparently, Neocities — my website host at that time — had CSP settings that forbade fetching resources from other domains. I was only allowed to fetch from the Neocities domain.

Since Neocities does not allow access to or editing of the CSP, I found myself forced to pursue another host. Now I am using Cloudflare. Here, I can set my own CSP, and it’s also a free hosting option. It’s not a community like Neocities, but I wasn’t relying on that anyway.

On Cloudflare, I can connect my host to my blog’s GitHub repository, so when I update my master branch, I automatically deploy the new version of my blog to production. Nice! Maybe one day I’ll have a more complex pipeline to write about.

Now, my request tests are successful.

Dynamic Front-end

Maybe this subject brings to mind frameworks like ReactJS, but that’s not my task ticket today. My goal was simply to receive a JSON from the backend with the comments data, and for each comment, display a Bootstrap card showing the user's name and message. I implemented that with pure JavaScript, HTML, and Bootstrap (CSS).

My FrontEnd JS code for fetching and displaying comments

async function fetchData() {
  // Fetching comments data
  const parameters = `?post_id=${document.getElementById("post_id").value}`;
  const response = await fetch(`https://script.google.com/macros/s/AKfycbzN3qxsiTKoi2sVrRE6QzhTWWp4qYEGzsBpiDtz7U0cfjbkEpP-w16S_r3rL_Fbzl3Q/exec${parameters}`);
  const data = await response.json();

  // Hiding the messaga 'Loading comments...'
  document.getElementById("loading_comments").classList.add("d-none");

  const comment_section = document.getElementById('comments');
  
  // Creaing cards for each comment data
  data.reverse().forEach(row => {
      comment_section.appendChild(createCard(row.Name, row.Message));
  });
}

// Running function on page loading
window.onload = fetchData;
          

function createCard(name, message){
  const div = document.createElement("div");
  const div_flex = document.createElement("div");
  const h3 = document.createElement('h3');
  const p = document.createElement('p');
  const icon = document.createElement('i');

  div.classList.add("card","card-body","my-1");
  div_flex.classList.add("d-flex");
  icon.classList.add("bi","bi-person-circle", "d-inline");
  h3.textContent = `${name}`;
  h3.classList.add("d-inline","mx-2");
  p.textContent = `${message}`;


  div_flex.appendChild(icon);
  div_flex.appendChild(h3);
  div.appendChild(div_flex);
  div.appendChild(p);

  return div;
}


          

Security

Now my blog system has an entry point for malicious intent, and I can't just ignore it.

There are important measures I need to implement, such as:

• Validation
• Sanitation
• Moderation

There may be more, but that’s a topic for another post.