Using Google Sheets as a JSON back-end database for applications I'm building is one of my favourite ways of using (or misusing) Google Sheets!

I used to use Parse.com, which Facebook sunsetted (well they made it open source, but that made it a lot harder to use).

The advantage of apps like Parse and Airtable is that they're an easy way to manage tables of data in the cloud and access them like a database from your application.

When building web applications, it's really convenient to have a database where it's super easy to edit and access the data.

If I have to spin up my own SQL server, I then have to deal with

  • Managing a server (including resources, security, and stability)
  • Entering the data — I might have to build my own front-end!
  • Paying for the server — at least $5 a month from a cloud-hosting service

That's so annoying.

That's why I prefer to use Google Sheets as my back-end database, at least dueirn ghe prototyping phase.

How to use Google Sheets as a prototyping database

Take this sheet for example.

example Google Sheets table used as back-end for database

Fully written out, the URL of this sheet is https://docs.google.com/spreadsheets/d/1d1DTH9fNJd6kiIZIkcMQjnGBvUJfxHDi4igyseR6cZU/edit#gid=0.

The simplest way to interact with this is to convert the table into a json flat file.

You do this by changing the last /edit section of the URL to /gviz with a few options.

That'll download a JSONP file directly to your computer.

But how do you evaluate that JSONP data?

Cleaning JSONP Data into JSON

If you open up the JSONP file linked above, you'll see that it's not pure JSON.

It's actually wrapped up in some other data. It's like this: `/*O_o*/
google.visualization.Query.setResponse({
... then with a bunch of other stuff... then });.

And that's not all. The data is replete with information about the version, then what's a column and row... it's a mess. Argh, there's so far to go!

But don't worry, it's quite easy to clean.

The core way to clean into regular JSON is to add a header to your request. You can't do this in a browser, but you can do this on the command line of a Mac or Unix computer (or a Windows one if you have curl installed.

To get the original URL: use the command curl 'url' (with those single quotes). In this case curl 'https://docs.google.com/spreadsheets/d/1d1DTH9fNJd6kiIZIkcMQjnGBvUJfxHDi4igyseR6cZU/gviz/tq?tqx=out:json&tq&gid=0'.

To get the JSON data, you need to add the header X-DataSource-Auth to your request. See Google's guide for more info.

Using curl, you do:

curl -H 'X-DataSource-Auth: true' 'https://docs.google.com/spreadsheets/d/1d1DTH9fNJd6kiIZIkcMQjnGBvUJfxHDi4igyseR6cZU/gviz/tq?tqx=out:json&tq&gid=0' 

This data is still not structured perfectly. It has a random gibberish string at the beginning ()]}') and it's still JSON that describes a table. You'll need to parse that out some other way.

Once you do, your data will look like this:

{
    "version": "0.6",
    "reqId": "0",
    "status": "ok",
    "sig": "707447619",
    "table": {
        "cols": [{
            "id": "A",
            "label": "Fruit",
            "type": "string"
        }, {
            "id": "B",
            "label": "Animal",
            "type": "string"
        }, {
            "id": "C",
            "label": "Day",
            "type": "string"
        }, {
            "id": "D",
            "label": "Amount eaten",
            "type": "number",
            "pattern": "General"
        }],
        "rows": [{
            "c": [{
                "v": "Apples"
            }, {
                "v": "Monitor lizards"
            }, {
                "v": "Monday"
            }, {
                "v": 5.0,
                "f": "5"
            }]
        }, {
            "c": [{
                "v": "Apples"
            }, {
                "v": "Snakes"
            }, {
                "v": "Monday"
            }, {
                "v": 2.0,
                "f": "2"
            }]
        }, 
        ...
JSON data downloaded from Google Sheets

Alternatives to downloading and evaluating JSONP

Another approach is to define a Google Apps Script function in your application that lets you request data from it.

I wrote a whole guide to using Google Apps Script to make an API out of your Google Sheet.

In a nutshell, you can write code and a doGet() and doPost() function to pull data from your Google Sheet. It returns JSON that your app can then parse.