How to Build Your Own Blog CMS with Google Sheets and Apps Script

Are you looking for a lightweight way to manage content for a blog or website? Before you dive into the complexities of WordPress or another full-featured CMS, consider a simpler alternative: build your own system using Google Sheets and Google Apps Script. With a little elbow grease, you can create a customized "headless CMS" that is easy to maintain and extend.

In this post, I‘ll walk through how to use these readily-available Google tools to create your own API-based content management system. This approach provides an intuitive spreadsheet interface for managing your blog posts while giving you full control over how that content ultimately gets rendered on your site.

Here‘s an overview of what we‘ll cover:

• Configuring Google Sheets as a content database
• Setting up a Google Form for adding new posts
• Writing a Google Apps Script to expose post data via API
• Deploying your custom API for public access
• Connecting your new CMS to a website or app

We‘ll focus on the backend piece in this guide and link to a working demo at the end. Let‘s get started!

Architecture Overview

Before we jump into implementation details, let‘s take a step back and look at the big picture of how the components of our mini CMS will fit together:

1. Google Sheets will act as the primary database for storing blog post content and metadata. Each row in the spreadsheet will represent an individual post.

2. To make adding new content user-friendly, we‘ll create a Google Form that saves submissions to our sheet. This provides a nice input interface without having to interact with the raw spreadsheet.

3. A Google Apps Script deployed as a web app will provide the backend API for our CMS. This script will handle authenticating requests, retrieving the appropriate posts from the Google Sheet, and serving that data as JSON.

4. The web app will be deployed with permissions that allow anyone to access it, effectively creating a public API.

5. On the frontend, our website or app can make requests to the API and use the returned JSON to render the blog posts. The website itself can be built with any language or framework.

With that context in mind, let‘s get our hands dirty setting this up! We‘ll use a basic blog with posts that have a title, category, author, and content as our example.

Configuring Google Sheets

All of our blog content will be housed in a Google Sheets spreadsheet. Here‘s how to get it set up:

1. Create a blank spreadsheet in Google Sheets.

2. Set up column headers for each property of your blog posts. For our example, we‘ll use the following columns:

• Title
• Category
• Author
• Content
• Published

3. The Published column will be a checkbox that indicates if a post is ready to be published or still a draft. This allows you to work on new posts in the spreadsheet before publishing them to your live site.

Setting Up the Google Form

We want to make it easy for content writers to compose new posts without having to work directly in the spreadsheet. Google Forms provides a great solution:

1. Open your spreadsheet and click on Tools > Create a Form. This will generate a blank form that is automatically linked to your sheet.

2. Add form fields that map to the columns you set up in your spreadsheet. Be sure to select the appropriate input type for each (e.g. "Short Answer" for the title, "Paragraph" for the post body, "Checkboxes" for the published status).

3. Configure any additional form settings you‘d like, such as email notifications or response validation.

4. Share the form with your team of writers/editors. When they submit the form, a new row containing the post data will automatically be added to your spreadsheet.

Creating the API with Google Apps Script

Now for the fun part: transforming your spreadsheet data into an API that your website can consume! We‘ll write a Google Apps Script that reads from the sheet, filters and formats the data, and returns it as JSON.

First, open the script editor by clicking on Tools > Script Editor from your spreadsheet. Name your script project something like "Blog CMS API." Next, we‘ll walk through the key components of the script code.

Reading Post Data from the Sheet

We first need to read in our blog post data from the linked spreadsheet. Add the following code to your script:

function getPosts() {

  // ID of the Google Sheet to read from (replace this with your own!)
  var SHEET_ID = ‘1234abcd‘;

  var sheet = SpreadsheetApp.openById(SHEET_ID).getSheets()[0];
  var rows = sheet.getDataRange().getValues();

  var header = rows[0];
  var posts = rows.slice(1); // Remove header row

  // Transform rows into an array of objects keyed by header names
  posts = posts.map(function(row) {
    var post = {};
    header.forEach(function(col, idx) {
      post[col] = row[idx];
    });
    return post;
  });

  return posts;
}

This function retrieves all the rows from the first sheet of a specified spreadsheet. It assumes the first row contains the column headers.

The map operation then transforms the raw row data into an array of post objects. Each object uses the header row values as keys for easy property access.

Filtering and Shaping Post Data

Before we return our post data, we want to give consumers of the API some options for filtering the results. Add the following code to your script to support retrieving only published posts and posts for a particular category:

function filterPosts(posts, isPublished, category) {

  return posts.filter(function(post) {
    var publishedMatch = isPublished ? post.Published === ‘Yes‘ : true;
    var categoryMatch = category ? post.Category === category : true;
    return publishedMatch && categoryMatch;
  });

}

You can imagine adding many other filter options here depending on your needs (e.g. filtering by author or date range).

We also want to clean up the post objects we return to API consumers. Let‘s remove any extraneous or non-public properties:

function formatPosts(posts) {
  return posts.map(function(post) {
    return {
      id: post.ID, 
      title: post.Title,
      category: post.Category,
      author: post.Author,
      content: post.Content
    };
  });
}

Connecting the API

To expose the blog posts at an API endpoint, we‘ll configure a doGet function that will be executed whenever the web app URL is requested. Add the following to your script:

function doGet(request) {

  // Get request parameters  
  var isPublished = request.parameter.published === ‘true‘;
  var category = request.parameter.category;

  var posts = getPosts();
  posts = filterPosts(posts, isPublished, category);
  posts = formatPosts(posts);

  var response = {
    posts: posts
  };

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

}  

This function acts as the entry point for the API. It reads any URL query parameters from the request to customize the returned posts. By default, it will return all posts across categories, including drafts.

The posts are then filtered and formatted per our utility functions before being wrapped in a response object. Finally, the results are stringified and returned with a JSON MIME type.

Deploying the API

We‘re almost ready to start consuming our new blog post API! The last step is to deploy the script as a web app:

1. From the script editor, click on Publish > Deploy as web app.

2. Set the access permissions to "Anyone, even anonymous." This will allow your API to be publicly accessible.

3. Deploy the app and copy the provided URL. It should look something like https://script.google.com/a/macros/YOUR_DOMAIN/s/SCRIPT_ID/exec.

4. (Optional) To make the URL more friendly, you can set up a custom domain mapping using a solution like the Google Apps Script GitHub Assistant.

That‘s it! You now have a working API to power your blog. You can test it out by appending query parameters to the URL like so:

• https://script.google.com/a/macros/YOUR_DOMAIN/s/SCRIPT_ID/exec?published=true returns only published posts

• https://script.google.com/a/macros/YOUR_DOMAIN/s/SCRIPT_ID/exec?published=true&category=News returns only published posts in the "News" category

Next Steps

With your Google Sheets-powered CMS in place, you‘re ready to integrate it into your website or app. Depending on your frontend stack, you can fetch the post data client-side using the browser‘s fetch API or server-side in your website‘s backend code.

Here are a few suggestions to continue developing your CMS:

• Add a caching layer in your Google Apps Script to improve performance
• Implement authentication to protect drafts or make the API private
• Automate rebuilding your static site when new posts are published
• Extend the spreadsheet with more metadata like publish dates or featured images
• Support uploading images to Google Drive and referencing them in posts

I hope this tutorial has shown you the potential of using Google Sheets and Google Apps Script as a lightweight CMS. It‘s a great option for smaller sites, prototypes, or simply to maintain control over your content.

Be sure to check out the companion demo and GitHub repo for a closer look at a working example. Now go build something great!