Using Google Sheets and 11ty for Programmatic SEO

Above is a detailed video that shows how you can create 100s of programmatic pages by using Google Sheets and the 11ty static site generator. You can access the entire code shown in the video by going to this GitHub repository.

Prerequisites

I failed to mention these in the video above, but for this to understand, you need to have a basic understanding of the following things:

• HTML and CSS
• Markdown and Liquid
• 11ty static site generator
• Programmatic SEO (you can start learning from here)

And you need the following tools installed on your computer:

• Node.js runtime environment, and
• Any code editor like Visual Studio Code
• Script to deploy your Google Sheets as a JSON web app

How to do pSEO with 11ty and Google Sheets

While you can watch the video and learn everything, I will briefly explain how exactly all this works — taking data from a Google Sheets file and then publishing 100s of pages by using the static site generator 11ty.

The entire process can be divided into the following 3 steps:

1. Preparing the data in Google Sheets
2. Deploying Google Sheets as a JSON web app
3. Setting up the Eleventy (11ty) site

Let’s go through each of them…

1. Preparing the data in Google Sheets

First, you need to have your dataset prepared in a Google Sheets with all the data points that will be needed.

Apart from the actual data points, you must add slug, related links, lowercase and/or plural/singular versions of topics, etc. — I have talked more about the topics in this blog post about data preparation.

2. Deploying Google Sheets as a JSON web app

Once you ensure that your dataset is all-ready, it’s time to deploy the data as a web app that yields the entire data as JSON. And while it sounds like a very complicated thing to do, it’s actually very easy with the following script:

// Create JSON URL
function doGet(req) {
  var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange().getValues();

  var output = [];

    for (var i = 0; i < values.length; i++) {
    var row = {};
    
    if (i == 0) {
      continue;
    }
    
    for (var j = 0; j < values[0].length; j++) { 
      if (!values[i][j] || values[i][j] === '') {
        continue;
      }
      row[values[0][j]] = values[i][j];
    }
    
    if(Object.keys(row).length > 0){
      output.push(row);
    }
  }
  
  return ContentService.createTextOutput(JSON.stringify({ data: output })).setMimeType(ContentService.MimeType.JSON);
}

In your Google Sheets file, navigate to the Extensions > Apps Script option, delete any existing code, paste the above script, and save by giving it a name. After that, you just have to deploy the script as a web app by clicking on the Deploy button (I’ll recommend watching the video at the top of this post to understand that part better).

From here, you will get a URL that gives you access to the entire data as a JSON output.

3. Setting up the 11ty site

If you’re new to Eleventy or static sites in general, I will recommend going through their tutorial section to have a basic understanding of what it is and what you can do with it. And if you don’t want to create a 11ty site from scratch, they are tons of starter projects as well that you can choose from.

Once you have a site set up on your computer, you just have to create a .js file inside the _data folder, and copy-paste the following script:

// Responsible for fetching data
const EleventyFetch = require("@11ty/eleventy-fetch");

module.exports = async function() {
    let url = `YOUR_GOOGLE_SHEETS_JSON_DEPLOYED_URL`;
  
    const response = await EleventyFetch(url, {
      duration: "1d",
      type: "json"
    });
  
    return response.data;
};

And of course, replace the placeholder with your Google Sheets JSON URL that I talked about earlier. And after that, you would be able to create pages from the data that you receive from your Google Sheets.

Again, as this is a very technical step, I will recommend watching the video at the top to set up this section correctly.

Once the website is ready locally, you can push the code to GitHub and since it’s a static website, you can host them on platforms like Netlify and GitHub Pages for free.

That’s it.

If you get stuck somewhere, kindly feel free to let me know in the comments below.