{"id":305240,"date":"2020-03-27T07:37:07","date_gmt":"2020-03-27T14:37:07","guid":{"rendered":"https:\/\/css-tricks.com\/?p=305240"},"modified":"2020-03-27T17:33:18","modified_gmt":"2020-03-28T00:33:18","slug":"creating-an-editable-site-with-google-sheets-and-eleventy","status":"publish","type":"post","link":"https:\/\/css-tricks.com\/creating-an-editable-site-with-google-sheets-and-eleventy\/","title":{"rendered":"Creating an Editable Site with Google Sheets and Eleventy"},"content":{"rendered":"\n

Remember Tabletop.js? We just covered it a little bit ago<\/a> in this same exact context: building editable websites. It\u2019s 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<\/em>, 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\u2019s do it one better. Let\u2019s hit the API during the build step<\/em> so that the content is built into the HTML directly. This will be far faster and more resilient.<\/p>\n\n\n\n\n\n\n

The situation<\/h3>\n\n\n

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.<\/p>\n\n\n\n

We\u2019re going to give them the keys to updating content themselves using a tool they are probably already familiar with: Google Sheets.<\/p>\n\n\n

A new tool<\/h3>\n\n\n

In the last article<\/a>, we introduced the concept of using Google Sheets with Tabletop.js. Now let\u2019s introduce a new tool to this party: Eleventy<\/a>. <\/p>\n\n\n\n

We\u2019ll 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\u2019ll be pulling the content from the API at build time and having Eleventy create a minified index.html that we\u2019ll push to the server for the production website. By being static, this allows the page to load faster and is better for security reasons.<\/p>\n\n\n

The spreadsheet<\/h3>\n\n\n

We\u2019ll be using a demo I built<\/a>, with its repo and Google Sheet<\/a> to demonstrate how to replicate something similar in your own projects. First, we\u2019ll need a Google Sheet which will be our data store.<\/p>\n\n\n\n

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

\"\"
In the first column, \u201cheader\u201d is the reference name and \u201cPlease edit me!\u201d is the actual content in the first column.<\/figcaption><\/figure>\n\n\n\n

Next up, we\u2019ll publish the data to the web by clicking on File \u2192 Publish to the web in the menu bar.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

A link will be provided, but it\u2019s 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.<\/p>\n\n\n\n

Take note that we\u2019ll need the unique ID of the sheet from its URL  as we go on.<\/p>\n\n\n\n

Node is required to continue, so be sure that\u2019s installed<\/a>. If you want to cut through the process of installing all of thedependencies for this work, you can fork or download my repo<\/a> and run:<\/p>\n\n\n\n

npm install<\/code><\/pre>\n\n\n\n
Run this command next \u2014 I\u2019ll explain why it\u2019s important in a bit:<\/p>\n\n\n\n
npm run seed<\/code><\/pre>\n\n\n\n
Then to run it locally:<\/p>\n\n\n\n
npm run dev<\/code><\/pre>\n\n\n\n
Alright, let\u2019s go into src\/site\/_data\/prod\/sheet.js<\/code>. This is where we\u2019re 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\u2019t need to hit the API every time.<\/p>\n\n\n\n
Here\u2019s the code we want in there. Again, be sure to change the variable sheetID<\/code> to the unique ID of your own sheet.<\/p>\n\n\n\n
\nmodule.exports = () => {\n  return new Promise((resolve, reject) => {\n    console.log(`Requesting content from ${googleSheetUrl}`);\n    axios.get(googleSheetUrl)\n      .then(response => {\n        \/\/ massage the data from the Google Sheets API into\n        \/\/ a shape that will more convenient for us in our SSG.\n        var data = {\n          \"content\": []\n        };\n        response.data.feed.entry.forEach(item => {\n          data.content.push({\n            \"header\": item.gsx$header.$t,\n            \"header2\": item.gsx$header2.$t,\n            \"body\": item.gsx$body.$t,\n            \"body2\": item.gsx$body2.$t,\n            \"body3\":  item.gsx$body3.$t,\n            \"body4\": item.gsx$body4.$t,\n            \"body5\": item.gsx$body5.$t,\n            \"body6\":  item.gsx$body6.$t,\n            \"body7\": item.gsx$body7.$t,\n            \"body8\": item.gsx$body8.$t,\n            \"body9\":  item.gsx$body9.$t,\n            \"body10\": item.gsx$body10.$t,\n            \"body11\": item.gsx$body11.$t,\n            \"body12\":  item.gsx$body12.$t,\n            \"body13\": item.gsx$body13.$t,\n            \"body14\": item.gsx$body14.$t,\n            \"body15\":  item.gsx$body15.$t,\n            \"body16\": item.gsx$body16.$t,\n            \"body17\": item.gsx$body17.$t,\n            \n          })\n        });\n        \/\/ stash the data locally for developing without\n        \/\/ needing to hit the API each time.\n        seed(JSON.stringify(data), `${__dirname}\/..\/dev\/sheet.json`);\n        \/\/ resolve the promise and return the data\n        resolve(data);\n      })\n      \/\/ uh-oh. Handle any errrors we might encounter\n      .catch(error => {\n        console.log('Error :', error);\n        reject(error);\n      });\n  })\n}<\/code><\/pre>\n\n\n\n
In module.exports<\/code>, there\u2019s a promise that\u2019ll resolve our data or throw errors when necessary. You\u2019ll notice that I\u2019m using a axios<\/a> to fetch the data from the spreadsheet. I like that it handles status error codes by rejecting the promise automatically, unlike something like Fetch<\/a> that requires monitoring error codes manually.<\/p>\n\n\n\n
I created a data<\/code> object in there with a content<\/code> array in it. Feel free to change the structure of the object, depending on what the spreadsheet looks like.<\/p>\n\n\n\n
We\u2019re using the forEach()<\/code> 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. <\/p>\n\n\n\n
Remember that seed<\/code> command from earlier? We\u2019re using seed to transform what\u2019s in the data object to JSON by way of JSON.stringify<\/code>, which is then sent to src\/site\/_data\/dev\/sheet.json<\/code>. <\/p>\n\n\n\n
Yes! Now have data in a format we can use with any templating engine, like Nunjucks<\/a>, to manipulate it. But, we\u2019re focusing on content in this project, so we\u2019ll be using the index.md template format to communicate the data stored in the project.<\/p>\n\n\n\n
For example, here\u2019s how it looks to pull item.header through a for loop statement:<\/p>\n\n\n\n
<div class=\"listing\">\n{%- for item in sheet.content -%}\n  <h1>{{ item.header }} <\/h1>\n{%- endfor -%}\n<\/div><\/code><\/pre>\n\n\n\n
If you\u2019re using Nunjucks, or any other templating engine, you\u2019ll have to pull the data accordingly.<\/p>\n\n\n\n
Finally, let\u2019s build this out:<\/p>\n\n\n\n
npm run build<\/code><\/pre>\n\n\n\n
Note that you\u2019ll want a dist<\/code> folder in the project where the build process can send the compiled assets.<\/p>\n\n\n\n
But that\u2019s not all! If we were to edit the Google Sheet, we won\u2019t see anything update on our site. That\u2019s where Zapier<\/a> comes in. We can \u201czap\u201d Google sheet and Netlify so that an update to the Google Sheet triggers a deployment from Netlify.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The recipe we\u2019re looking for? We\u2019re connecting Google Sheets to Netlify so that when a \u201cnew or updated sheet row\u201d takes place, Netlify starts a deploy. It\u2019s truly a set-it-and-forget-it sort of deal.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
Remember Tabletop.js? We just covered it a little bit ago in this same exact context: building editable websites. It\u2019s 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 […]<\/p>\n","protected":false},"author":274516,"featured_media":305246,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sig_custom_text":"","sig_image_type":"featured-image","sig_custom_image":0,"sig_is_disabled":false,"inline_featured_image":false,"c2c_always_allow_admin_comments":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":[]},"categories":[4],"tags":[1416,758,12863],"jetpack_publicize_connections":[],"acf":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/css-tricks.com\/wp-content\/uploads\/2020\/03\/11ty-sheets.png?fit=1200%2C600&ssl=1","jetpack-related-posts":[{"id":317308,"url":"https:\/\/css-tricks.com\/using-trello-as-a-super-simple-cms\/","url_meta":{"origin":305240,"position":0},"title":"Using Trello as a Super Simple CMS","date":"July 27, 2020","format":false,"excerpt":"Sometimes our sites need a little sprinkling of content management. Not always. Not a lot. But a bit. The CMS market is thriving with affordable, approachable products, so we\u2019re not short of options. Thankfully, it is a very different world to the one that used to force companies to splash\u2026","rel":"","context":"In "Article"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/css-tricks.com\/wp-content\/uploads\/2020\/07\/trello-board.png?fit=1200%2C600&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":324084,"url":"https:\/\/css-tricks.com\/comparing-static-site-generator-build-times\/","url_meta":{"origin":305240,"position":1},"title":"Comparing Static Site Generator Build Times","date":"October 28, 2020","format":false,"excerpt":"There are so many static site generators (SSGs). It\u2019s overwhelming trying to decide where to start. While an abundance of helpful articles may help wade through the (popular) options, they don\u2019t magically make the decision easy. I\u2019ve been on a quest to help make that decision easier. A colleague of\u2026","rel":"","context":"In "Article"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/css-tricks.com\/wp-content\/uploads\/2020\/10\/ssg-logo-tiles.png?fit=1200%2C600&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":318724,"url":"https:\/\/css-tricks.com\/a-community-driven-site-with-eleventy-building-the-site\/","url_meta":{"origin":305240,"position":2},"title":"A Community-Driven Site with Eleventy: Building the Site","date":"August 20, 2020","format":false,"excerpt":"In the last article, we learned what goes into planning for a community-driven site. We saw just how many considerations are needed to start accepting user submissions, using what I learned from my experience building Style Stage as an example. Now that we\u2019ve covered planning, let\u2019s get to some code!\u2026","rel":"","context":"In "Article"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/css-tricks.com\/wp-content\/uploads\/2020\/08\/weekly-pet-battle.png?fit=1200%2C600&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":296323,"url":"https:\/\/css-tricks.com\/using-github-template-repos-to-jump-start-static-site-projects\/","url_meta":{"origin":305240,"position":3},"title":"Using GitHub Template Repos to Jump-Start Static Site Projects","date":"October 4, 2019","format":false,"excerpt":"If you\u2019re getting started with static site generators, did you know you can use GitHub template repositories to quickly start new projects and reduce your setup time? Most static site generators make installation easy, but each project still requires configuration after installation. When you build a lot of similar projects,\u2026","rel":"","context":"In "Article"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/css-tricks.com\/wp-content\/uploads\/2019\/09\/octocat-copies.png?fit=1200%2C600&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":286226,"url":"https:\/\/css-tricks.com\/netlify-functions-for-sending-emails\/","url_meta":{"origin":305240,"position":4},"title":"Netlify Functions for Sending Emails","date":"April 23, 2019","format":false,"excerpt":"Let's say you're rocking a JAMstack-style site (no server-side languages in use), but you want to do something rather dynamic like send an email. Not a problem! That's the whole point of JAMstack. It's not just static hosting. It's that plus doing anything else you wanna do through JavaScript and\u2026","rel":"","context":"In "Article"","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/css-tricks.com\/wp-content\/uploads\/2018\/11\/email-open-cloe.gif?fit=1200%2C600&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":298530,"url":"https:\/\/css-tricks.com\/the-future-is-bright-because-the-future-is-static\/","url_meta":{"origin":305240,"position":5},"title":"The future is bright, because the future is static","date":"November 20, 2019","format":false,"excerpt":"I've been doing this web thing for money for 10 years this year and although I haven\u2019t been around as long as some folks, I feel like I've seen a few cycles come and go now, so let's say that hot new things are often cynically viewed, initially. This milestone\u2026","rel":"","context":"In "2019 End-of-Year Thoughts"","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"featured_media_src_url":"https:\/\/i0.wp.com\/css-tricks.com\/wp-content\/uploads\/2020\/03\/11ty-sheets.png?fit=1024%2C512&ssl=1","_links":{"self":[{"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/posts\/305240"}],"collection":[{"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/users\/274516"}],"replies":[{"embeddable":true,"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/comments?post=305240"}],"version-history":[{"count":4,"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/posts\/305240\/revisions"}],"predecessor-version":[{"id":305851,"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/posts\/305240\/revisions\/305851"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/media\/305246"}],"wp:attachment":[{"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/media?parent=305240"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/categories?post=305240"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/css-tricks.com\/wp-json\/wp\/v2\/tags?post=305240"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}