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.
The JSON file would look like this:
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.
In this way, we obtain the JSON that we can use as our database.