How to use Google Sheets as JSON Database for Web Apps

Using Google Sheets as a JSON database for applications I’m building is one of my favourite ways of using (or misusing) Google Sheets. Basically, this is an easy way of keeping your data in Google Sheets (where it’s easy to edit), and building a back-end for your applications.

I used to use Parse.com as a quick-and-easy back-end database. But then Facebook sunsetted it (well, they made it open source, but that made it a lot harder to use).

The advantage of apps like Parse, Airtable, and now Google Sheets 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 during the prototyping phase.

You might also be interested in my guide to using Google Apps Script to use a Google Sheet as an API that returns JSON.

How to use Google Sheets as a prototyping database

Take this sheet for example.

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.

• Original URL:

https://docs.google.com/spreadsheets/d/1d1DTH9fNJd6kiIZIkcMQjnGBvUJfxHDi4igyseR6cZU/edit#gid=0

• Change /edit#gid=0 to /gviz/tq?tqx=out:json&tq&gid=0
• Final URL:

https://docs.google.com/spreadsheets/d/1d1DTH9fNJd6kiIZIkcMQjnGBvUJfxHDi4igyseR6cZU/gviz/tq?tqx=out:json&tq&gid=0

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*/<br>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 '<a href="https://docs.google.com/spreadsheets/d/1d1DTH9fNJd6kiIZIkcMQjnGBvUJfxHDi4igyseR6cZU/gviz/tq?tqx=out:json&tq&gid=0"><code>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"
            }]
        }, 
        ...

The above is what the JSON data downloaded from Google Sheets looks like.

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.