The complete guide to using Google sheets as database

Let’s talk about the mighty Google sheets. Mighty? Yes! It is one the best spreadsheet apps on par with Microsoft Excel. Actually, Google sheets is better than Excel in few cases because of its features like Apps script, Timed triggers, Form triggers, Google sheets API, importXML etc. Let’s talk about different advanced features of Google sheets, its limitations, alternate solutions and how to use those features and how to use Google sheets as database!

Apps script platform for developers

Don’t even get me started on the number of integrations available with Google sheets App script such as MailApp, DriveApp, DocumentApp, SlidesApp, CalendarApp etc!

There are so many integrations available to explore within google sheets but most of the features are unused because most users and developers don’t even know that there’s so much to explore in Google Apps Script! Check out the Apps script documentation here – Google Apps script documentation

Usage and limitations of Apps script platform

As mentioned earlier, Apps script has a lot of integrations available within the Google ecosystem. Apps Script is really good when you want to share and modify data between Google’s products. Let me give you a few examples

Generate a PDF invoice and send it to your tenant every month(DriveApp, MailApp, and Timed trigger)

Sync events between Google sheets and Google Calendar

Shorten URLs using URL Shortener Service

Import Google and Youtube analytics to Spreadsheet

These are just a few examples. The possibilities are endless! It’s all good as long as the data is within Google products. Once you want to read or write data outside of Google, however, the problems start.

Let’s say you have a list of employees and their metrics in a google sheet and you want to display it on your company website. How to use Google sheets as a database? This is when Google sheets API comes into the picture!

Google Sheets API

Google Spreadsheet API can be used to read and write data to google sheets. Sheets API can be consumed to use google sheets as database!

But It is so damn complex! From Authentication to API endpoints to API response structure, Its a pain in the ass 😅

Problems with Google Sheets API

Needs OAuth for accessing data in private sheets (Generally It is not a problem but In this context, It is!)

If you are a developer then you surely know that this API response is not really useful!

So what are the alternatives to Sheets API? I’ve tried a few libraries which simplify the API response and provide some useful methods but most of them are deprecated and support only public sheets and also don’t support writing or updating data in google sheets.

Finally, I found this platform called Sheetsu. One platform that simplifies google sheets API to a great extent and helps to use google sheets as database.

Introducing Sheetsu, A sophisticated platform built on Google sheets

Sheetsu is by far the best platform I’ve seen that leverages most of the capabilities of google sheets. Let’s see what features does Sheetsu offer and how we can use them!

Sheetsu is a platform for both developers and non-developers. Some of the features can be used by anyone without coding experience and some advanced features like JSON API can be used by developers to convert google sheets as database.

Important features of Sheetsu :

Google sheets to HTML table

HTML form to Spreadsheet integration – Of course, This feature is already built-in with Google forms.

Now Go to your google sheet and add your data which can be read using the JSON API. Sample data shown below

That’s It! It is as simple as that in just 4 steps. You’ve converted your google sheet into a Database with Full CRUD (Create, Read, Update, Delete) support API

Understanding Sheetsu JSON API

Let’s understand how Sheetsu’s JSON API can be consumed. This API supports GET, POST, PUT, PATCH and DELETE methods. Data can be filtered using search queries, set the limit and offset for pagination support. It is as good as an actual API with a full-fledged database.

API requests can be secured using API key and can also disable methods if required. Let’s say you want to disable updating and deleting of data then you can disable those methods and you are good to go!

Reading and filtering data from google sheets database

Reading data is just making a simple GET request to the API. Click on the link below to see the response.

Practical example – Learn by building a blood donor app

Beginners 👶 who don’t have experience working with APIs might be wondering how to actually use these in a web app or mobile app or website. So I made a simple practical example to help the beginners understand better.

It’s a simple web app where users can see the list of people who are ready to donate blood and they can also submit their own details for donating blood.

Users can search based on blood group and city. All the data is stored in a google spreadsheet and all new submissions create a new row in the spreadsheet. All this data is consumed using Sheetsu API

Here’s how the spreadsheet looks

So There’s Name, city, phone, group, and address columns.

I’ve used Sheetsu’s Javascript web client library to simplify the process of calling APIs. All this library does is make calls to the JSON API as explained in the previous section but it helps a lot by providing simple methods to read, write and query data with as little code as possible.

All you need to do to include this library is add a script tag to your HTML file

When to use Google sheets as database

Google sheets is not a full-fledged database so when should you use google sheets as database?

Small to medium scale hobby projects which don’t require authentication or user management

Quick prototyping and testing

Let a sales team or HR team use google sheets as database and quickly display some data on the company website.
(Checkout Sheetsu’s Table feature for this)

Wrapping up

This is a long blog post that I’ve written after a long time 😵 I tried to put out as much information as possible with regard to using Google sheets as database. If I’ve missed something or if you have any questions or feedback then do let me know in the comments below!