Arjun Kalburgi to the right of a sharp leaf

learning, finding, designing, developing impact

I’m more lazy than you — backends with Google Spreadsheets

March 09, 2019

As most developers I’ve gone on a journey of how I can make websites faster and easier. I’ve experimented with WordPress, HTML5UP, Github Pages, and now I’ve got a new tool.

Say hello to Sheety, the shitty database API (as far as databases go) that’s also the best at being fast and easy.

Sheety was made by Phillip Caudell “because he hates making backends” and honestly it’s the greatest thing ever for front end developers like me.

Let’s look at an example. I’ve rebuilt my old project, TheRadList, to use Sheety so that my friends can easily edit the restaurant content on the website.

TheRadList with data from Sheety TheRadList with data from Sheety

The Spreadsheet

I’ve moved all the data from a JSON var in the JS of TheRadList (yes, that’s how far I went to avoid using a backend) to this Google Spreadsheet:

My friends have edit access to this spreadsheet and I made them a custom link to make it easier to find: https://theradlist.com/data

The API

Putting the Google Docs Publish Link into Sheety’s API tool results in the following API link for me to use!

https://api.sheety.co/c1471c68-f293-46d9-ab4c-ca5e24e6e24e

With all the data as objects in a list, it’s easy for me to read via JavaScript.

The Javascript

I copied the example on the Sheety website to load the data from the API.

var template;
$(document).ready(function () {
$.getJSON(
'https://api.sheety.co/c1471c68-f293-46d9-ab4c-ca5e24e6e24e',
function (data) {
template = Handlebars.compile($('#item-template').html())
$('#list-places').html(template(data))
})
})

It uses Handlebars to load the data into the specific #item-template I made and then into the div I need.

Other Things

The Filters

Unfortunately Sheety doesn’t have any endpoint logic, so I can’t retrieve specific objects or filter using the API. So instead I save the data into a variable and do simple list filter operations. Unfortunately none of the filter data is present at the moment so they all return an empty list.

Nested Objects

I did another project with Sheety where I needed to my data to have some nested objects and/or lists within the object. Sheety’s CSV style is obviously not suited for this. My solution was to create more Google Spreadsheets for the inner-data and use a primary_id to link all the data together. Then combine objects in JS onLoad. Who ever thought Database class would come in handy?

Updating Data

After the Spreadsheet is updated, Google/Sheety take some time for the changes to be reflected. Sometimes it’s instant but sometimes it takes a couple minutes. I don’t recommend using it for chat apps 😂

The Results

Visit TheRadList today to see Sheety in action! Well it’s not that exciting, you won’t even see it, but it’s lazy and that’s what counts.