Use Google Sheets as your simple database

Marek Králík
Mar 29 · 2 min read

Does your application or website need to simply to read (and/or store) some data ? Are you going to spin up your SQL or NOSQL database? Then make a middleware layer that connects to that database and translates your requests? No brainer, right? You can do all of that. You have been taught how to do that for years. What If I told you, thats an awful loads of work compared to using service like Google Spreadsheet to store that data. And as a bonus. It comes with somewhat user friendly interface.

Warning: Use it just to do simple things! End emphasis

It can do complex things, but you will hate yourself later for making all the business logic this way. You’ll lose control of the source code, performance, functionality (only whats given). And it might be taken down by 3rd party — either legal or due to deprecation.

Now this is out of the way, we can take a look at the technical solution itself at 4 easy steps. This shouldn’t take you more than 5 minutes:

Step 1) Create new empty spreadsheet in Google Drive

No comments here. If you can’t get past this point, don’t bother to read further.

Step 2) [optional] Create a form (menu — tools — create a form)

Using Google Forms is how you could easily add new items to the table. But you can also write the script using the POST method. There are resources all over the internet how exactly to cope with that. Your starting point will be this:

function doPost(e) {
// store data
}

Step 3) Create a script (menu — tools — script editor)

Contents of the read script: “its just good old javascript”. Just copy & paste it in.

function doGet(e) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var lastRow = sheet.getLastRow();
var cells = sheet.getRange(‘A2:Z’+lastRow);

return ContentService
.createTextOutput(JSON.stringify(cells.getValues()))
.setMimeType(ContentService.MimeType.JSON);
}

What it does is that it takes data from current sheet — Its like your MySQL table. It returns an array of all the rows starting from the 2 (first row is a header). And all the columns from A to Z. Feel free to adjust it to your needs. You can make it so it returns headers to each field, but for my case it would just increase the response file size.

Step 4) Publish the script (menu — publish — deploy as web app)

You can set it up so everybody with or without authorisation could access the script — therefore reading/writing the data.

Thats it. Current data should be accessible through the generated url.

I really hope this will save you some effort next time you create a new app.

PRO tip: Deploying your app to AWS S3 might mean cutting your hosting costs to ZERO.

Marek Králík

Written by

Web Developer, Entrepreneur and Petrolhead