Site icon Coding is Love

The complete guide on how to use Google sheets as a database

Ranjith kumar

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 a 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

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 a 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

  1. Needs OAuth for accessing data in private sheets (Generally It is not a problem but In this context, It is!)
  2. Needs an API Key for reading data in public sheets (Documentation doesn’t even mention that! Reading data in Sheets API)
  3. Complex API response structure
  4. Vague Documentation

Here’s a sample response for a simple list with 2 rows of data.

{
"range": "Sheet1!A1:D5",
"majorDimension": "ROWS",
"values": [
[
"Name",
"City",
"Phone",
"Group"
],
[
"Ranjith",
"India",
"123456",
"A+"
],
[
"John",
"London",
"123456",
"B+"
]
]
}

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 a database.

UPDATE (June 2020) : Sheetsu has been shutdown now.

Please use https://sheet2api.com/ as an alternative which has a similar API. Meanwhile I’ll update the article with clear instructions.

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 a database.

Important features of Sheetsu :

We’ll be talking about JSON API and how to use it to build a simple blood donor app via google sheets as a database.

Getting started with Google Sheets JSON API

Let’s do it step by step! Learn by doing 😍

  1. Go to Google sheets and quickly create a blank spreadsheet.
  2. Go to Sheetsu and login using your google account. Just 2 clicks 😎
  3. Click on create JSON API from Sheetsu dashboard and paste the URL of spreadsheet that you created in step 1. Sheetsu will create a JSON API for this spreadsheet. It looks something like URL below 
    https://sheetsu.com/apis/v1.0bu/7fe59afe565a

  4. 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.

https://sheetsu.com/apis/v1.0bu/7fe59afe565a/

Filtering can be done by passing parameters to /search API. Let’s say you want to get all the rows with city name “Bangalore” Then the request would be something like this –

https://sheetsu.com/apis/v1.0bu/7fe59afe565a/search?city=Bangalore

Search is case-sensitive!

If you want to limit results like – Get only first 10 results then you can do it using limit parameter

https://sheetsu.com/apis/v1.0bu/7fe59afe565a/?limit=10

Writing or updating data using sheets API

Creating a new row in google sheets via API can be done by making a POST request with row data in the request body and Content-Type header with value application/json.

Sample Request in cURL format :

curl -X POST \
  https://sheetsu.com/apis/v1.0bu/7fe59afe565a \
  -H 'content-type: application/json' \
  -d '{
        "name": "Ranjith Kumar",
        "city": "Bangalore",
        "phone": "91-8812345678"
}'

Multiple rows can be added by sending an array of objects instead. Updating rows can be done in a similar way using PUT and PATCH methods and deletion can be done using DELETE method.

Read the full documentation of Sheetsu API here – https://docs.sheetsu.com/

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.

Here’s how it looks. You can check the live demo at Blood donor demo

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

<script src="//script.sheetsu.com/"></script>

I’ve used Bulma CSS library to quickly style the web app.

And finally, I’ve used Vue.js Javascript library to simplify showing and hiding the data.

It doesn’t matter which Javascript library I’m using. This article is all about how to use Google sheets as a database. Let’s go through the code and see how can we consume Sheetsu API in a web app.

Here’s the complete Javascript code for the app.

var app = new Vue({
el: '#app',
data: {
addBlood: false,
donors: [],
loading: false,
donating: false,
donorName: "",
donorPhone: "",
donorCity: "",
donorAddress: "",
donorGroup: "",
searchGroup: "A+",
searchCity: ""
},
methods: {
search: function () {
app.loading = true;
var searchQuery = {
group: encodeURIComponent(app.searchGroup)
};
if (app.searchCity) {
searchQuery.city = app.searchCity;
};
Sheetsu.read("https://sheetsu.com/apis/v1.0bu/7fe59afe565a", {
search: searchQuery
}).then(function (data) {
console.log(data);
app.donors = data;
app.loading = false;
},
function (err) {
console.log(err);
app.donors = [];
app.loading = false;
});
},
donateBlood: function () {
var donorData = {
name: app.donorName,
phone: app.donorPhone,
city: app.donorCity,
address: app.donorAddress,
group: app.donorGroup
};
app.donating = true;
Sheetsu.write("https://sheetsu.com/apis/v1.0bu/7fe59afe565a", donorData, {
}).then(function (data) {
console.log(data);
app.donors.push = data;
app.donorName = "";
app.donorPhone = "";
app.donorCity = "";
app.donorAddress = "";
app.donorGroup = "";
app.donating = false;
alert("Thank you for submitting your details.")
},
function (err) {
console.log(err);
app.donating = false;
});
}
}

})

Code explanation

Look at the search function once.

search: function () {
app.loading = true;
var searchQuery = {
group: encodeURIComponent(app.searchGroup)
};
if (app.searchCity) {
searchQuery.city = app.searchCity;
};
Sheetsu.read("https://sheetsu.com/apis/v1.0bu/7fe59afe565a", {
search: searchQuery
}).then(function (data) {
console.log(data);
app.donors = data;
app.loading = false;
},
function (err) {
console.log(err);
app.donors = [];
app.loading = false;
});
}

Now look at donateBlood function

donateBlood: function () {
var donorData = {
name: app.donorName,
phone: app.donorPhone,
city: app.donorCity,
address: app.donorAddress,
group: app.donorGroup
};
app.donating = true;
Sheetsu.write("https://sheetsu.com/apis/v1.0bu/7fe59afe565a", donorData, {
}).then(function (data) {
console.log(data);
app.donors.push = data;
app.donorName = "";
app.donorPhone = "";
app.donorCity = "";
app.donorAddress = "";
app.donorGroup = "";
app.donating = false;
alert("Thank you for submitting your details.")
},
function (err) {
console.log(err);
app.donating = false;
});
}

You can go through the complete code here – https://github.com/codingislove01/blood-donor-google-sheets

When to use Google sheets as a database

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

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 a database. If I’ve missed something or if you have any questions or feedback then do let me know in the comments below!

Exit mobile version