Static Sites With a Google Sheets Back End

Client-maintained content usually means a content management system, but Google Sheets can play that role with far less overhead. The catch is that most implementations fetch the spreadsheet data in the browser on every page view, which adds a runtime dependency and slows things down. A better approach is to pull that data at build time with a static site generator, so the content is baked directly into the HTML files you deploy.

Why Eleventy for This Job

Eleventy is a good fit here because it produces a purely static site with no server-side or client-side application logic to maintain. At build time, Eleventy can fetch content from a Google Sheet, render it into templates, and output a minified HTML file. The result is faster page loads and a smaller attack surface than a site that depends on client-side JavaScript to assemble its own content.

Setting Up the Spreadsheet

Start with a new Google Sheet that acts as your data store. The first row of each column becomes the key you reference in your templates, and the second row holds the actual content. In the demo sheet, each column contains fields like a header and body text for a section of the page.

In the first column, “header” is the reference name and “Please edit me!” is the actual content in the first column.

To make the sheet readable by your build process, publish it via File → Publish to the web in the menu bar.

The URL that appears is not needed; what matters is that the data is now publicly accessible. You will, however, need the unique ID from the sheet's URL for the next step.

Fetching Data at Build Time

With Node installed, grab the project files from the demo repository and install the dependencies:

npm install

Then prepare the data for local development:

npm run seed

You can now run the site locally:

npm run dev

The core of the build pipeline lives in src/site/_data/prod/sheet.js. This module fetches the spreadsheet contents, reshapes them into a JavaScript object, and serializes that object to JSON. The JSON is then stored in src/site/_data/dev/sheet.json so that during development you don't need to hit the Google Sheets API on every request.

Replace the sheetID variable with the unique ID of your own spreadsheet:


module.exports = () => {
  return new Promise((resolve, reject) => {
    console.log(`Requesting content from ${googleSheetUrl}`);
    axios.get(googleSheetUrl)
      .then(response => {
        // massage the data from the Google Sheets API into
        // a shape that will more convenient for us in our SSG.
        var data = {
          "content": []
        };
        response.data.feed.entry.forEach(item => {
          data.content.push({
            "header": item.gsx$header.$t,
            "header2": item.gsx$header2.$t,
            "body": item.gsx$body.$t,
            "body2": item.gsx$body2.$t,
            "body3":  item.gsx$body3.$t,
            "body4": item.gsx$body4.$t,
            "body5": item.gsx$body5.$t,
            "body6":  item.gsx$body6.$t,
            "body7": item.gsx$body7.$t,
            "body8": item.gsx$body8.$t,
            "body9":  item.gsx$body9.$t,
            "body10": item.gsx$body10.$t,
            "body11": item.gsx$body11.$t,
            "body12":  item.gsx$body12.$t,
            "body13": item.gsx$body13.$t,
            "body14": item.gsx$body14.$t,
            "body15":  item.gsx$body15.$t,
            "body16": item.gsx$body16.$t,
            "body17": item.gsx$body17.$t,
            
          })
        });
        // stash the data locally for developing without
        // needing to hit the API each time.
        seed(JSON.stringify(data), `${__dirname}/../dev/sheet.json`);
        // resolve the promise and return the data
        resolve(data);
      })
      // uh-oh. Handle any errrors we might encounter
      .catch(error => {
        console.log('Error :', error);
        reject(error);
      });
  })
}

The module.exports block returns a promise that resolves with the data or throws on error. The fetch is handled by axios, which conveniently rejects the promise on non-2xx HTTP status codes, a behavior the native Fetch API does not provide without manual checks.

The data object contains a content array, and the code loops over each spreadsheet column with forEach(), mapping the column values into named properties before pushing them into that array. The structure is easy to adjust if your sheet uses different columns.

The earlier seed command is what converts the fetched data into JSON via JSON.stringify and writes it to the dev data file.

Rendering the Content

With the data in JSON format, any templating engine can consume it. This project uses Nunjucks templates to iterate over the content fields. A typical loop pulls each item's header and renders it into the page markup:

<div class="listing">
{%- for item in sheet.content -%}
  <h1>{{ item.header }} </h1>
{%- endfor -%}
</div>

Finally, generate the production-ready static files:

npm run build

Make sure a dist directory exists in the project so the build process has a place to write the compiled assets.

Automating Updates

Editing the sheet alone won't change the live site; you still need to rebuild and redeploy. Zapier can close that gap by watching the Google Sheet and triggering a fresh deployment on Netlify whenever a row is added or modified.

After connecting your Google and Netlify accounts in Zapier, create a zap with the trigger set to a new or updated spreadsheet row and the action set to start a Netlify deploy. Once configured, the entire workflow is hands-off: content editors update the sheet, and the static site regenerates and redeploys on its own.