The complete guide on how to use Google sheets as a 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 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

  • Send auto-reply to user when they submit a google form using MailApp
  • Mail scorecard to students every month where score data is in a Google spreadsheet (MailApp and Timed triggers)
  • 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 sheets API funny

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

crying interview kid

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 API

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 :

  • Google sheets to HTML table
  • HTML form to Spreadsheet integration – Of course, This feature is already built-in with Google forms.
  • Google Sheets to JSON API (Full CRUD Support)
  • SDKs and client libraries in many programming languages like Ruby, Node.js, PHP, Python, Javascript web client etc

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. Sheetsu JSON API

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

Blood donor app 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

google sheets sample data

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;
});
}
  • Search function searches for blood donors based on blood group & city and sets the donor list to app.donors which shows the list in HTML
  • First app.loading is set to true to show a loading spinner while API call is being made.
  • I’m using encodeURIComponent function in Line 3 because blood group has special characters like + which needs to be URL encoded. Remember to encode your search parameters if it has special characters.
  • In Line 9, I’m using Sheetsu’s library to read the data by passing searchQuery
  • When the API call is successfull, API’s response is set to app.donors and is displayed in the app

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;
});
}
  • donateBlood function runs when users submit “Donate my blood” form along with their details.
  • User’s data is set to donorData object.
  • Use Sheetsu.write method to add a row to google spreadsheet. A simple Thanks you alert is shown when the API call is successful and donor is added to the list!

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?

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

Ranjith kumar
5 2 votes
Article Rating
Subscribe
Notify of
guest

31 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
nazjonson
nazjonson
6 years ago

Your article has very unique and reliable information on new topic so i am really impress with your post.

Thanks

William
William
6 years ago

Hi,
I would like to use Google Sheet as a database on WordPress, simply to display the table, without using the Google Sheet embed native system. Do you think this is possible?

Raihan
6 years ago

HI, friend

Thank you for this post but I need some information can I use this code in localhost or my pc server, If possible but how?

Tomas van Rijsse
Tomas van Rijsse
6 years ago

Thanks for the insights!
I thought about using Google sheets for a project which would hold loads of private data. If I could store that data in sheets from the user itself it would give users ownership over their own data. The only concern I have is performance. What where your experiences on that part? Are queries slow or acceptable?

Terry
Terry
6 years ago

Good Stuff!! I can’t wait to do it!

Mike
Mike
6 years ago

Thanks.. I’m struggling up the learning curve. Hoping to be able to use google sheets for a food truck business database.

Nick Bilotto
Nick Bilotto
6 years ago

i tried doing this and i couldnt get it to work, so i copied your code exactly from github to do a demo with yours (using my own sheetsu links and google sheet) and it still didnt work

is that code accurate, the demo doesnt seem to work anymore either

Jordan Kadish
5 years ago

Hi Ranjith, I’ve just read through your article, you did mention about private sheets: will it be possible to GET data from a read only sheet for my python application if the sheet is private? How does it refresh tokens or whatever it must do so that I don’t need to re-authenticate every month? If I have a company sheet that I have readonly access to, can sheetsu get that data?

Rajdeep
Rajdeep
5 years ago

Sheetsu is great but limited to only 200 requests a month for free account. Not recommended even for small-scale projects

Luis Montano
Luis Montano
5 years ago
Reply to  Ranjith kumar

Hey Ranjith,
Any additional news on this limitation? I hope Sheetsu has increased the number of searches to more than 200.

Max Graham
Max Graham
5 years ago

Hey Ranjith, great article! Is it possible to have multiple blood types in one cell?

Say I search for A+, could it return a hit for a cell that had A+, B-, O+?

Thank you!

Max Graham
Max Graham
5 years ago
Reply to  Max Graham

Adding asteriks on either side of the search will pick up on anything within that cell

group: encodeURIComponent(‘‘ + app.searchGroup + ‘‘)

Kamal
Kamal
5 years ago

I am looking for a solution to store scan data (thru scanner) in google sheet and before storing to google sheet the data should be validated in master google sheet. Can you please suggest or guide any example.

Misha
Misha
5 years ago

May I know does Google Sheet has a server? Is Google is their server?

SHARIF ABDUL RAHMAN BIN SAREH HASAN
SHARIF ABDUL RAHMAN BIN SAREH HASAN
5 years ago

How can I develop an app that tracks an item? My database has been set up on the Google Sheet. When the client type the tracking number, the page will search the Google Sheet and display all the data according to the date

Emmanuel G. Dahnweinh
Emmanuel G. Dahnweinh
5 years ago

Thanks for all educative write ups. I am highly interested in learning how to create and manage database, I am so new to it but have great skills in excel.
Pls suggest for me what resources to start up so as not to be jumping the gun.

xssoft
xssoft
4 years ago

HI, I tried the script and even downloaded the source code but the script is not working.

Can you please help me in creating a script that will remove the blood group type and make location as an only search string to fetch records from your blood group example code.
No formatting required.

Percy J
Percy J
4 years ago

Sir/Madam,

Say I have a data in Spreadsheet with Students’ Roll No, Name and Subject -wise Marks. Can I use the above to create a search for Exam Marks based on Roll Nos?

If yes, further can I merge this search with an HTML Page?

Thank you.

Ken
Ken
4 years ago

sheet2api is great! thanks

Ashish Anil Dhage
4 years ago

Hi Ranjith sir, I am Ashish, in my google sheets i want to use macros in such a way that it copies B1:E2 and pastes values (Ctrl+Shift+V) in A1:D2. I want this to repeat every minute from morning 9 to afternoon 4pm.
Please note that E1 cell =CONCATENATE(HOUR(NOW()),”:”,MINUTE(NOW()))
E2 cell =GOOGLEFINANCE(“AAPL”,”PRICE”)
Now you understand that im trying to create an intraday chart from the googlefinance live price shown (delay doesnt matter). If i run the macro of {copies B1:E2 and pastes values (Ctrl+Shift+V) in A1:D2}, it shows this :
Error The coordinates of the source range are outside the dimensions of the sheet.
Can googlefinance function not be macro triggered. But if I manually press Ctrl+Shift+Alt+1, that is macro shortcut, it works. but the trigger doesnt work.
Please help I am badly stuck. Your help would be heartily appreciated.

Geoffrey Callaghan
2 years ago

you can use https://faborm.io to submit static website forms to google sheets,