I haven't been writing on here lately as I've been very busy he;ping to build a cooperative bicycle touring platform that I'm helping to start. But I thought I would write down a quick tutorial since I've basically built the whole database to run for free on top of Google Sheets.

Now the only really good reason to do this is that (1) it is free and (2) it can be edited very easily by non-developers. And I already know that if this platform takes off there will need to be a lot of reworking to make it scalable. But for now, this is working great and I just wanted to show how it's done.

How is that even possible?

If you're thinking running a whole website off of Google Sheets is impossible, you'd be right. The website itself is built using Jekyll to serve a static website, which is hosted (for free of course) on Github Pages. This has become a pretty standard way to host a free website for tech savvy (and less tech savvy) people in recent years.

But the website I'm building doesn't want to just be a static site that we update by adding commits to a git repo! Our site is designed to be a place to upload routes, cycling resources, and travel logs for people to find ways to bike tour in the local area around their home. And we hope someday for people to be able to even add private resources, like property that can be used for camping and things like that.

Typically if you want people to submit their own content to a website, you will have to build your own backend database. But this means that as a developer I would need to build all the tooling for posting content to a DB. And importantly, most ways of doing this require some operating costs for storage and data transactions with the database.

So as a scrappy startup we decided we'd like to short cut all that. And based on a previous project where I pulled some data from a Google Sheet, I thought, why not try hosting the content in an editable Google Sheet and running the whole site from there?

What could go wrong?

It's all in the javascript

The entirety of this relies on a few trick of javascript. Basically, all that needs to happen is for me to make the Google Sheet public so that anyone can view it. Then in the frontend of the webpage, I just run a little bit of javascript that downloads the contents of the spreadsheet, scans through it, builds the appropriate html, and adds it to the webpage.

    fetch("")
    .then(res => res.text())
    .then(text => {
        meta_result = JSON.parse(text.substr(47).slice(0, -2))

That's the magic line right there. I don't know exactly why that works, but I copied it from this stack overflow and nothing lit on fire.

At this point, I have a data structure with each row and column. I access the ith row and jth column with the following.

meta_result.table.rows[i].c[j]

At this point it becomes very project specific. For me, I just iterate through the rows and generate the custom HTML based on that content. You can see how it works in all it's glory detail on the Github repo for the site, though at the moment it is very very messy.

So what about user input?

So we're now displaying data from a google sheet, but we still need to manually input that data into the spreadsheet. This is great for us since we know how the sheet is set up, but it would totally not work for a random person who wants to submit material to our site.

So to get around this we used another simple hack that I've applied to multiple previous websites, which is just making a custom form that submits to a Google Form. I've covered how this works in a previous blog post so I won't rehash it here.

Now we haven't fully closed the loop by having our form submit directly to the sheet that hosts our content. We thought about it, but based on the amount of random spam that comes through your average form, we figured it'd be best to have a manual check process to copy and paste content over.

That said, I'm thinking about making a little script that copies from one spreadsheet to another. All in all, I'm really happy with the system and am looking forward to launching the project and getting more people biking in their area.