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.
Fully written out, the URL of this sheet is
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:
- Final URL:
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*/ ... 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
To get the original URL: use the command
curl 'url' (with those single quotes). In this case
To get the JSON data, you need to add the header
to your request. See Google's guide for more info.
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:
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
doPost() function to pull data from your Google Sheet. It returns JSON that your app can then parse.