EXAMPLE OF CRUD WEB APP IN GOOGLE APPS SCRIPT
CRUD is an acronym that comes from the world of computer programming and refers to four functions that are considered essential to implement persistent storage applications: Create, Read, Update and Delete.
In this article we have tried to buld a CRUD web app that helps us to create, read, update and delete data with apps script and google spreadsheet. We have used google spreadsheet as a data base. we store data in google spreadsheet and modifie it by apps script.
How can we do this?
To get this done I have used apps script, html, javascript languages.
I have also set Id for each new entry to run the function on it.
Lets start doing.......
First create a spreadsheet as following example.
In above example, you can see that first column contains ID that used to controll data. This is unique for every new entry. For unique Id, I have used following code.
varzeroPad = (num, places) => String(num).padStart(places, '0')
varrnd =Math.floor(Math.random() * 500) + 500;
varid = "E"+zeroPad(rnd,6); //unique id for each entry
I have also used 'sheet.getRange(i, 5).setNumberFormat('@STRING@');' to set date as plain text. If you not set it as plain text data not passed while reading.
Lets move, and see complete code.
Paste this whole code in Code.gs file and replace Spreadsheet Id with your Id.
And save.
function doGet(e){
return HtmlService
.createTemplateFromFile('index.html')
.evaluate()
.setTitle("Crud Example");
}
function include(filename){
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
};
var ss = SpreadsheetApp.openById("1pmrZljGdpBAxH7cS6VRFnt1UKMUc6dNCF7uUSM74Cfo");
var sheet = ss.getSheetByName("Sheet1");
//newEntry function
function newEntry(sd){
var zeroPad = (num, places) => String(num).padStart(places, '0')
var rnd =Math.floor(Math.random() * 500) + 500;
var id = "E"+zeroPad(rnd,6); //unique id for each entry
var flag = 1 ;
var lr = sheet.getLastRow();
for(var i = 1;i <= lr;i++){
var vid = sheet.getRange(i, 3).getValue();
if(vid == sd[1]){// checking if Email already exist.
flag = 0;
var data ="This email is already in our data base.";
return data;
}
}
if(flag==1){
sheet.appendRow([id,sd[0],sd[1],sd[2],sd[3],sd[4]]);
sheet.getRange(i, 5).setNumberFormat('@STRING@');
var data = 'Entry successfully made with entry Id:'+id;
return data;
}
};
//read function
function readId(txt){
var flag = 1 ;
var lr = sheet.getLastRow();
for(var i = 1;i <= lr;i++){
var vid = sheet.getRange(i, 1).getValue();
if(vid === "E"+txt){
flag = 0;
var b1 = sheet.getRange(i, 2).getValue();
var b2 = sheet.getRange(i, 3).getValue();
var b3 = sheet.getRange(i, 4).getValue();
var b4 = sheet.getRange(i, 5).getValue();
var b5 = sheet.getRange(i, 6).getValue();
var data =["Data Fetched",b1,b2,b3,b4,b5];
return data;
}
}
if(flag==1){
var data =["ID not found.",,,,,];
return data;
}
};
//update function
function updateId(sd){
var flag = 1 ;
var lr = sheet.getLastRow();
for(var i = 1;i <= lr;i++){
var vid = sheet.getRange(i, 1).getValue();
if(vid == "E"+sd[0]){
flag = 0;
sheet.getRange(i, 2).setValue(sd[1]);
sheet.getRange(i, 3).setValue(sd[2]);
sheet.getRange(i, 4).setValue(sd[3]);
sheet.getRange(i, 5).setValue(sd[4]).setNumberFormat('@STRING@');
sheet.getRange(i, 6).setValue(sd[5]);
var data ="Update successfully made.";
return data;
}
}
if(flag==1){
var data ="ID not found.";
return data;
}
};
//Delete ID
function deleteId(obj){
var flag = 1 ;
var lr = sheet.getLastRow();
for(var i = 1;i <= lr;i++){
var vid = sheet.getRange(i, 1).getValue();
if(vid == "E"+obj){
flag = 0;
sheet.deleteRow(i)
var data ='Id successfully deleted.';
return data;
}
}
if(flag==1){
var data ="ID not found.";
return data;
}
};
index.html, entry.js.html, read.js.html, update.js.html, delete.js.html .
Paste following code according to given file name and save it.
index.html
entry.js.html
<!DOCTYPE html>
<html>
<style>
table, th, td {
border-collapse: collapse;
font-family:times;
}
td,th{width:50%;}
input:focus,select:focus,
textarea:focus {
outline: 1px dashed green;
}
textarea{
width:98%;
height:100px;
font-family:times;
border:1px solid black;}
select,input{
width:95%;
font-family:times;
padding:1px;
border:1px solid black;
}
input:hover{
background:yellow;}
button{width:15%;
font-family:times;}
button:focus {
outline: 1px dashed green;}
button:hover{
background:green;color:white;}
</style>
<body>
<?!= include('entry.js') ?>
<?!= include('read.js') ?>
<?!= include('update.js') ?>
<?!= include('delete.js') ?>
<table border=0 width=600px>
<tr>
<th>CRUD WEB APP: GOOGLE SCRIPT<hr></th>
<th><input id="id" placeholder="Enter ID to Read,Update or Delete Data"><hr></th>
</tr>
<tr>
<td>Name:<input id="name"></td>
<td>Email id:<input id="eid"></td>
</tr>
<tr>
<td>Apply for:
<select id="selected">
<option>Select </option>
<option value="Web Developer">Web Developer </option>
<option value="Form Designer">Form Designer </option>
</select>
</td>
<td>Date:<input type="date" id="date"></td>
</tr>
<tr>
<td colspan="2">Note:
<textarea placeholder="Write something....."id="note"></textarea></td>
</tr>
<tr>
<td colspan="2"align="center">
<button onclick="submit()">New Entry</button>
<button onclick="read()">Read</button>
<button onclick="update()">Update</button>
<button onclick="delet()">Delete</button>
</td>
</tr>
<tr>
<td colspan="2">Response:<b id="result"></b></td>
</tr>
</table>
</body>
</html>
entry.js.html
<script>
function submit(){
let tx1 = document.querySelector('#name').value;
let tx2 = document.querySelector('#eid').value;
let tx3 = document.querySelector('#selected').value;
let tx4 = document.querySelector('#date').value;
let tx5 = document.querySelector('#note').value;
var sd = [tx1,tx2,tx3,tx4,tx5]; //sending data in arry
let info = document.querySelector('#result');
info.innerHTML = "Making new entry...";
function onFailure(error){
info.innerHTML ="<span style='color:red'>"+error+"</span>";
};
function onSuccess(response){
info.innerHTML = "<span style='color:green'>"+response+"</span>";
};
google.script.run.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.newEntry(sd);
};
</script>
read.js.html
<script>
function read(){
let txt = document.querySelector('#id').value;
let info = document.querySelector('#result');
if (txt === ""){
info.innerHTML = "<span style='color:red'>Please enter id to read!</span>";
}else{
function onFailure(error){
info.innerHTML = "<span style='color:red'>"+error+"</span>";;
};
function onSuccess(response){
info.innerHTML = "<span style='color:green'>"+response[0]+"</span>";;
document.getElementById('name').value = response[1];
document.getElementById('eid').value = response[2];
document.getElementById('selected').value = response[3];
document.getElementById('date').value = response[4];
document.getElementById('note').value = response[5];
};
info.innerHTML = "Reading detail...";
google.script.run.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.readId(txt);}
};
</script>
update.js.html
<script>
function update(){
let txt = document.querySelector('#id').value;
let tx1 = document.querySelector('#name').value;
let tx2 = document.querySelector('#eid').value;
let tx3 = document.querySelector('#selected').value;
let tx4 = document.querySelector('#date').value;
let tx5 = document.querySelector('#note').value;
var sd = [txt,tx1,tx2,tx3,tx4,tx5]; //sending data in arry
let info = document.querySelector('#result');
if (txt === ""){
info.innerHTML = "<span style='color:red'>Please enter id to update!</span>";
}else{
function onFailure(error){
info.innerHTML = "<span style='color:red'>"+error+"</span>";;
};
function onSuccess(response){
info.innerHTML = "<span style='color:green'>"+response+"</span>";;
};
info.innerHTML = "Updating detail...";
google.script.run.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.updateId(sd);}
};
</script>
delete.js.html
<script>
function delet(){
let txt = document.querySelector('#id').value;
let info = document.querySelector('#result');
if (txt === ""){
info.innerHTML = "<span style='color:red'>Please enter id to delete!</span>";
}else{
function onFailure(error){
info.innerHTML ="<span style='color:red'>"+error+"</span>";
};
function onSuccess(response){
info.innerHTML = "<span style='color:green'>"+response+"</span>";
};
info.innerHTML = "Deleting detail...";
google.script.run.withFailureHandler(onFailure)
.withSuccessHandler(onSuccess)
.deleteId(txt);
}
};
</script>
All above code has different function according to Code.gs function.
See this diffrences and arrange it as your need.
>>>TRY TO CHECK OUT , IF ANY ERROR FOUND. PLEASE LET ME KNOW BY COMMENT.
I'LL TRY MY LEVEL BEST TO FIX THE PROBLEM.
THANKS FOR VISITING CodyLab
Have a nice day!
-------------------------- -------------------------