How to use Google spreadsheet as a database

When we want to have dynamic data, but cannot generate a complex and robust database, we use Opensheet. But why do we use this API?


Opensheet is an open-source repository, hosted API for getting Google Sheets as JSON that facilitates the process to connect a Google spreadsheet and use it as the data source of your application.


Return the Google Spread Sheet as a JSON file, converting each column and row to objects and values, allows to have a dynamic JSON file that can be used by common JavaScript methods.


Based on the documentation on GitHub - benborgers/opensheet: 💾  An API to get a Google Sheet as JSON. To use it, the first row of your Google Sheet should be a header row (here’s an example). Link sharing must be turned on, so anyone with the link can view the Google Sheet.


The format for this API is: 

https://opensheet.elk.sh/spreadsheet_id/sheet_name

For example: 

https://opensheet.elk.sh/1o5t26He2DzTweYeleXOGiDjlU4Jkx896f95VUHVgS8U/Test+Sheet  

 

Our Solution


We generate a Google spreadsheet file in which our first row are the headers that will indicate the fields of the JSON, followed by each one below their respective data.

Image
Progress example

The JSON file would look like this:

Image
Json file sample

Here is a CodePen sample of how we implemented this API functionality:  

https://codepen.io/alarez/pen/yLpzZqd

By using this in our JavaScript, we generate a function “getFromAPI” with the parameters: URL, callback. Included with a variable called obj which will be our object to which we will date the data coming from the JSON.

Image
JSON
Image
JSON database

In this way, we obtain the JSON that we can use as our database.