I want to share some steps by which anyone can use Google Spreadsheet as a database without using any extension!
Reasons to use Google Spreadsheet as a database in your app:
1)Google Spreadsheet is huge and almost 3x or more faster than Airtable database.
2) And also there is a limitation in Airtable database of using maximum 1200 rows only if we use the free plan.
Steps to use Google Spreadsheet as a database:
Create a spreadsheet in Google Spreadsheet. Here’s the link of it- https://docs.google.com/spreadsheets/u/0/
2)Rename the spreadsheet on the top-left corner.
3)Type the column names as much as you want in the first row.
4)Click on ‘Tools’ and click on the ‘<>Script editor’ section.
5)There you can see some coding. Delete them all & paste the following code:
function doGet(e) {
return ManageSheet(e);
}
function doPost(e) {
return ManageSheet(e);
}
function ManageSheet(e) {
//CREATE NEW RECORD
if (e.parameter.func == “Create”) {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheets()[0];
var data =[e.parameter.Name, e.parameter.PhoneNumber,e.parameter.Gmail];
sh.appendRow(data);
return ContentService.createTextOutput("Success");
}
}
6)In the code section, you can see the line-
var data =[e.parameter.Name, e.parameter.PhoneNumber,e.parameter.Gmail];
Here the words(ex-Name, PhoneNumber) after parameter are actually the column names. So you have to replace the words with your column names in the code section. Also you can add more columns as your need by adding- “e.parameter.yourcolumnname” without qoute with a comma(,).
7)After coding, click on ‘deploy’ and change the “Who has access” to Anyone & the “Configuration” to web apps. Click on deploy.
8)You’ll get a link like that- https://script.google.com/macros/s/***************************************/dev
Copy that link and save it in your notepad because it’ll be necessary in the next steps.
9)Now from the above link, you have to make a url in your app. You can store it in a global variable.
10)The url will be:-
11)Now to store value:-
“Click share and public the link” - this is for your spreadsheet, your spreadsheet sharing option needs to be set to “anyone with the link - can view” as a minimum.
You use HTTP GET in your app blocks, why do you have doPost() in your script ?
Else if we want to get a specific row from our spreadsheet, then you have to write the following script in your Script editor:-
function doGet(e) {
return ManageSheet(e);
}
function doPost(e) {
return ManageSheet(e);
}
function ManageSheet(e) {
//CREATE NEW RECORD
if (e.parameter.func == “Create”) {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheets()[0];
var data =[e.parameter.Name, e.parameter.PhoneNumber,e.parameter.Gmail];
sh.appendRow(data);
return ContentService.createTextOutput("Success");
}
else if ( e.parameter.func == “ReadOneRecord”) {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheets()[0];
var rg = sh.getDataRange().getValues();
var outString = ‘’;
outString += rg[parseInt(e.parameter.id)].join(’**’);
return ContentService.createTextOutput(outString).setMimeType(ContentService.MimeType.TEXT);
}
}
And also we have to add some additional blocks in our app:
I found some great resources on web that you can use:
http://gsx2json.com – you will get pure json from any google spreadsheet with only 1 minute of setup with absolute free.
https://baserow.io --alternative to Airtable and google spreadsheet with full CRUD api online with unlimited rows/table…Also you can can host your own as it’s opensource.
Yes. I haven’t compared to other DB options which is available in kodular but sure performance is very good even in multiple web request simultaneously.
