How To Create A Database In Google Docs

Blockspring Loves Slack

You should consider a Google Spreadsheet as a database in these scenarios:

Prototype a dynamic web site using only HTML, CSS, and JS. (no back-end)
Create a static site, but have a "CMS" where non-engineers can change data / content on your site. (e.g. build a company info page that pulls data from a Google Spreadsheet)

I created a Blockspring API that makes accessing data from Google Sheets much easier. We'll use this in the implementation section. It's free, performant, etc.

Example Project

Magic The Gathering deck builder

This past weekend, I built a quick prototype of a Magic the Gathering - deck builder. It is a "static" site that only uses HTML, CSS, and JS. It is deployed on Github Pages. For my "database" I created a M.T.G. Card API, which fetches data from a Google Spreadsheet.

Basically there are 25,000 different cards in Magic, and people build their own decks using 60 of them. I wanted a UI where people could search through cards, pick out the ones they wanted, and save their decks. The fun part would be designing the "deck builder" UI. I didn't want to deal with a back-end, so I just used Google Spreadsheets as my database.

Implementation

After Step 4, you will have a fully working API that can be used to get data out of a Google Spreadsheet from Javascript (or any other language). It will take you ~10 minutes, and you will not need to code anything.

After Step 8, you will have a fully working API that can also restrict access to data in your Google Spreadsheet and is easily filterable from Javascript. It will take you ~30 minutes, and you will need to code some simple if conditions (in Javascript).

Create a Google Spreadsheet
Populate your data.
Click "share" in the top-left corner. Then click "advanced". Then change "Link Sharing" to "On - Anyone with the link".
Paste your spreadsheet's URL & a SQL query into Blockspring's Query Google Spreadsheet API. This returns the results as JSON. You can test it out on Blockspring, or call the API from any language.

At this point you can successfully query your Google Spreadsheet from your code. You can stop here. The next steps are if you want to only expose specific parameters or columns in your API.
Open the source code for an existing Google Doc API on Blockspring. Here is a working example Google Doc API you can start with.
On Ln 61, copy and paste your own Google Spreadsheet link.
Most of the code is if statements checking if specific URL parameters were supplied, and building up the WHERE clause. Simply replace these if statements with ones that are relevant to your API.
Click "Publish"

Your API is now live. You can use it in code by clicking the "Run in code" tab on its page.