Category: spreadsheet

Remember Tabletop.js? We just covered it a little bit ago in this same exact context: building editable websites. It’s a tool that turns a Google Sheet into an API, that you as a developer can hit for data when building a website. In that last article, we used that API on the client side, meaning JavaScript needed to run on every single page view, hit that URL for the data, and build the page. That might be OK in some circumstances, but let’s do it one better. Let’s hit the API during the build step so that the content is built into the HTML directly. This will be far faster and more resilient.

The situation

As a developer, you might have had to work with clients who keep bugging you with unending revisions on content, sometimes, even after months of building the site. That can be frustrating as it keeps pulling you back, preventing you from doing more productive work.

We’re going to give them the keys to updating content themselves using a tool they are probably already familiar with: Google Sheets.

A new tool

In the last article, we introduced the concept of using Google Sheets with Tabletop.js. Now let’s introduce a new tool to this party: Eleventy. 

We’ll be using Eleventy (a static site generator) because we want the site to be rendered as a pure static site without having to ship all of the under workings of the site in the client side JavaScript. We’ll be pulling the content from the API at build time and having Eleventy create a minified index.html that we’ll push to the server for the production website. By being static, this allows the page to load faster and is better for security reasons.

The spreadsheet

We’ll be using a demo I built, with its repo and Google Sheet to demonstrate how to replicate something similar in your own projects. First, we’ll need a Google Sheet which will be our data store.

Open a new spreadsheet and enter your own values in the columns just like mine. The first cell of each column is the reference that’ll be used later in our JavaScript, and the second cell is the actual content that gets displayed.

Next up, we’ll publish the data to the web by clicking on File → Publish to the web in the menu bar.

A link will be provided, but it’s technically useless to us, so we can ignore it. The important thing is that the spreadsheet(and its data) is now publicly accessible so we can fetch it for our app.

Take note that we’ll need the unique ID of the sheet from its URL  as we go on.

Node is required to continue, so be sure that’s installed. If you want to cut through the process of installing all of thedependencies for this work, you can fork or download my repo and run:

npm install

Run this command next — I’ll explain why it’s important in a bit:

npm run seed

Then to run it locally:

npm run dev

Alright, let’s go into src/site/_data/prod/sheet.js. This is where we’re going to pull in data from the GoogleSheet, then turn it into an object we can easily use, and finally convert the JavaScript object back to JSON format. The JSON is stored locally for development so we don’t need to hit the API every time.

Here’s the code we want in there. Again, be sure to change the variable sheetID to the unique ID of your own sheet.

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);
      });
  })
}

In module.exports, there’s a promise that’ll resolve our data or throw errors when necessary. You’ll notice that I’m using a axios to fetch the data from the spreadsheet. I like that it handles status error codes by rejecting the promise automatically, unlike something like Fetch that requires monitoring error codes manually.

I created a data object in there with a content array in it. Feel free to change the structure of the object, depending on what the spreadsheet looks like.

We’re using the forEach() method to loop through each spreadsheet column while equating it with the corresponding name we want to allocate to it, while pushing all of these into the data object as content. 

Remember that seed command from earlier? We’re using seed to transform what’s in the data object to JSON by way of JSON.stringify, which is then sent to src/site/_data/dev/sheet.json. 

Yes! Now have data in a format we can use with any templating engine, like Nunjucks, to manipulate it. But, we’re focusing on content in this project, so we’ll be using the index.md template format to communicate the data stored in the project.

For example, here’s how it looks to pull item.header through a for loop statement:

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

If you’re using Nunjucks, or any other templating engine, you’ll have to pull the data accordingly.

Finally, let’s build this out:

npm run build

Note that you’ll want a dist folder in the project where the build process can send the compiled assets.

But that’s not all! If we were to edit the Google Sheet, we won’t see anything update on our site. That’s where Zapier comes in. We can “zap” Google sheet and Netlify so that an update to the Google Sheet triggers a deployment from Netlify.

Assuming you have a Zapier account up and running, we can create the zap by granting permissions for Google and Netlify to talk to one another, then adding triggers.

The recipe we’re looking for? We’re connecting Google Sheets to Netlify so that when a “new or updated sheet row” takes place, Netlify starts a deploy. It’s truly a set-it-and-forget-it sort of deal.

Yay, there we go! We have a performant static site that takes its data from Google Sheets and deploys automatically when updates are made to the sheet.

The post Creating an Editable Site with Google Sheets and Eleventy appeared first on CSS-Tricks.