How to CRUD operation on Google sheet using Google Apps Script?
Creating top CRUD in Google AppsScript to manage data in Google sheet.
Dear friend, In this article, we are going to describe about CRUD that is built on Google apps script platfrm to manage Google sheet data directly through the webapp. In the givnen example CRUD has a great function and look that impress you to build a like. Each function has been given in dropdown options. Only one form has been taken to operate all the functions. When user select a function from dropdown menu, form Attribute automacally change according t the selected function. And button also got renamed.
The Webapp has basically four function:
- To create or insert data into Google sheet.
- To read data from Google sheet according to provided ID.
- To Update data in Google sheet( Before update user need to read data to check which field need to update)
- To Delete data from Google sheet.
Form has a great look. The form designed by the support of w3.css materials and fieldset, legend elements.
- To insert data into Google sheet user need to select Add New function from dropdown list. When user select this function button also renamed with Add New text. User has to fill out all the field of the form to continue. after fill out as soon as user click on Add Naw button and user will get processing response and success or failer response just right to the button.
- To read data user need to select Read function from dropdown list and put the id number of the particular entry that user has filled while inserting the data.
This is the Code.gs. You have to paste following code into Code.gs fields.
function doGet(e){
return HtmlService.createTemplateFromFile("index")
.evaluate()
.setTitle("CodyLab CRUD");
}
var ss = SpreadsheetApp.openById("your spreadsheet id"); var sn = ss.getSheetByName("Sheet2");
var ss = SpreadsheetApp.openById("your spreadsheet id"); var sn = ss.getSheetByName("Sheet2");
function addCondidate(lr){
var z = (num, places) => String(num).padStart(places, '0');
var l = sn.getLastRow();
var id = "1"+z(l,4);
var flag = 1 ;
for(var i = 1;i <= l;i++){
var vid = sn.getRange(i, 4).getValue();
if(vid == lr.idn){// checking if condidate already exist.
flag = 0;
var data =["exist","Condidate already exist"];
return data;
}
}
if(flag==1){
var dt = Utilities.formatDate(new Date(),Intl.DateTimeFormat().resolvedOptions().timeZone, 'yyyy-MM-dd HH:mm:ss');
sn.appendRow([dt,id,lr.fullname,lr.idn,lr.dob,lr.jname,lr.mob,lr.email]);
sn.getRange(i,1).setNumberFormat('@STRING@');
sn.getRange(i,5).setNumberFormat('@STRING@');
var data = 'Entry successfully made.-'+id;
return data;
}
};
function readF(req){
var flag = 1 ;
var l = sn.getLastRow();
for(var i = 1;i <= l;i++){
var vid = sn.getRange(i, 2).getValue();
if(vid == req){
flag = 0;
var b1 = sn.getRange(i, 3).getValue();
var b2 = sn.getRange(i, 4).getValue();
var b3 = sn.getRange(i, 5).getValue();
var b4 = sn.getRange(i, 6).getValue();
var b5 = sn.getRange(i, 7).getValue();
var b6 = sn.getRange(i, 8).getValue();
var data =["Data Fetched",b1,b2,b3,b4,b5,b6];
return data;
}
}
if(flag==1){
var data ="ID not exist.";
return data;
}
};
function updateF(lr){
var flag = 1 ;
var l = sn.getLastRow();
for(var i = 1;i <= l;i++){
var vid = sn.getRange(i, 2).getValue();
if(vid == lr.enro){
flag = 0;
sn.getRange(i, 3).setValue(lr.fullname);
sn.getRange(i, 4).setValue(lr.idn);
sn.getRange(i, 5).setValue(lr.dob).setNumberFormat('@STRING@');
sn.getRange(i, 6).setValue(lr.jname);
sn.getRange(i, 7).setValue(lr.mob);
sn.getRange(i, 8).setValue(lr.email);
var data =["pass","Successfully updated."]
return data;
}
}
if(flag==1){
var data ="ID not exist.";
return data;
}
};
function deleteF(req){
var flag = 1 ;
var l = sn.getLastRow();
for(var i = 1;i <= l;i++){
var vid = sn.getRange(i, 2).getValue();
if(vid == req){
flag = 0;
sn.deleteRow(i);
var data = "ID "+req+" successfully deleted.";
return data;
}
}
if(flag==1){
var data ="ID not exist.";
return data;
}
};
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<style>
input{text-transform:uppercase;}
html,body,h1,h2,h3,h4,h5{font-family:times;}
.w3-bar-block .w3-bar-item {padding: 12px;}
fieldset{border-radius:10px;border:1px solid teal;width:70%;}
legend{color:teal;}
</style>
</head>
<body style="margin-left:20%" onload="setFun(this);">
<script>
function setFun(){
var svx = document.getElementById('fc1');
var vfx = svx.options[svx.selectedIndex].value;
var vtx = svx.options[svx.selectedIndex].text;
var fm = document.getElementById('Condidate').setAttribute("onsubmit","event.preventDefault();"+vfx);
var fv = document.getElementById('btf1').setAttribute("onclick", vfx);
if(vtx == "Add New"){
document.getElementById("enro").disabled = true;
document.getElementById('idn').setAttribute("required", "true");
document.getElementById('enro').setAttribute("placeholder", "");
document.getElementById('btf1').innerHTML = vtx+' <i class="fa fa-refresh fa-0"id="rinfo"></i>';
}else{
document.getElementById('enro').setAttribute("required", "true");
document.getElementById('enro').setAttribute("placeholder", "Enter ID");
document.getElementById('idn').removeAttribute("required", "");
document.getElementById("btf1").disabled = false;
document.getElementById("enro").disabled = false;
document.getElementById('btf1').innerHTML = vtx+' <i class="fa fa-refresh fa-0"id="rinfo"></i>';}}
</script>
<form id="Condidate" onsubmit="event.preventDefault();addCon('this')">
<fieldset class="w3-border-teal">
<legend><i>function:</i></legend>
<div class="w3-container w3-light-grey">
<select name="fc1"id="fc1" onchange="setFun()"class="w3-select w3-col s2">
<option value="addCon(this)">Add New</option>
<option value="readFun(this)">READ</option>
<option value="updateFun(this)">UPDATE</option>
<option value="deleteFun(this)">DELETE</option></select>
<input type="number"name="enro"id="enro"placeholder="Enter ID"class="w3-input w3-col s2">
<button type="submit"id="btf1" onclick="readFun1(this)"class="w3-btn w3-teal w3-hover-red w3-col s2"></button>
<center class="w3-col s4" id="info"style="margin-top:8px;"></center>
</div>
</fieldset>
<fieldset class="w3-border-teal">
<legend><i>Condidate Information:</i></legend>
<div class="w3-row-padding w3-light-grey w3-light-grey">
<div class="w3-col s6">
<lavel>Condidate's name:*</lavel>
<input type="text"name="fullname"id="fullname"class="w3-input w3-border"onkeyup="lowerCase(this)">
</div>
<div class="w3-col s3">
<lavel>Document number:*</lavel>
<input type="number"name="idn"id="idn"class="w3-input w3-border">
</div>
<div class="w3-col s3">
<lavel>Date of Birth:*</lavel>
<input type="date"name="dob"id="dob"class="w3-input w3-border">
</div>
<div class="w3-col s6">
<lavel>Job Title:*</lavel>
<input type="text"name="jname"id="jname"class="w3-input w3-border"onkeyup="lowerCase(this)">
</div>
<div class="w3-col s3">
<lavel>Mobile Number:*</lavel>
<input type="tel"name="mob"id="mob"class="w3-input w3-border">
</div>
<div class="w3-col s3"style="padding-bottom:10px">
<lavel>Email ID:</lavel>
<input type="email"name="email"id="email"class="w3-input w3-border"onkeyup="lowerCase(this)">
</div>
</div>
</fieldset>
</form>
<script>
function addCon(lr) {
var info = document.getElementById('info');
var rinfo = document.getElementById("rinfo");
var req = document.getElementById('idn').value;
if(req === ""){
info.innerHTML = "<i style='color:red;'>Document number is required.</i>";
}else{
info.innerHTML = "Working on...";
rinfo.classList.add('fa-spin');
google.script.run.withFailureHandler(failure)
.withSuccessHandler(infoF)
.addCondidate(lr);}}
function infoF(fdata) {
info.innerHTML = "";
rinfo.classList.remove('fa-spin');
if(fdata[0] == "exist"){
info.innerHTML = fdata[1];
}else{
info.innerHTML = "<b>"+fdata;
document.getElementById("Condidate").reset();
}}
function updateFun(lr){
var info = document.getElementById('info');
var rinfo = document.getElementById("rinfo");
var rek = document.getElementById('idn').value;
var req = document.getElementById('enro').value;
if(req ===""||rek===""){
info.innerHTML = "<i style='color:red;'>First read ID to Update</i>";
}else{
info.innerHTML = "Updating.....";
rinfo.classList.add('fa-spin');
google.script.run.withFailureHandler(failure)
.withSuccessHandler(updateinfo)
.updateF(lr);}}
function updateinfo(sdata) {
if(sdata[0] == "pass"){
rinfo.classList.remove('fa-spin');
info.innerHTML = sdata[1];
document.getElementById("Condidate").reset();
setFun();
}else{
rinfo.classList.remove('fa-spin');
info.innerHTML = sdata;
}};
function deleteFun() {
var req = document.getElementById('enro').value;
if(req === ""){
info.innerHTML = "<i style='color:red;'>ID Number is required.</i>";
}else{
info.innerHTML = "Deleting.....";
rinfo.classList.add('fa-spin');
google.script.run.withFailureHandler(failure)
.withSuccessHandler(deleteinfo)
.deleteF(req);}}
function deleteinfo(sdata) {
rinfo.classList.remove('fa-spin');
info.innerHTML = sdata;
document.getElementById("Condidate").reset();
setFun();
};
function readFun() {
var req = document.getElementById('enro').value;
if(req === ""){
info.innerHTML = "<i style='color:red;'>ID Number is required.</i>";
}else{
info.innerHTML = "Fetching detail.....";
rinfo.classList.add('fa-spin');
google.script.run.withFailureHandler(failure)
.withSuccessHandler(readinfo)
.readF(req);}}
function readinfo(pdata){
if(pdata != ""){
rinfo.classList.remove('fa-spin');
info.innerHTML = pdata[0];
document.getElementById('fullname').value = pdata[1];
document.getElementById('idn').value = pdata[2];
document.getElementById('dob').value = pdata[3];
document.getElementById('jname').value = pdata[4];
document.getElementById('mob').value = pdata[5];
document.getElementById('email').value = pdata[6];
}else{
info.innerHTML = pdata;
rinfo.classList.remove('fa-spin');
}};
function failure(error){
info.innerHTML = "<i style='color:red;'>"+error+"</i>";
rinfo.classList.remove('fa-spin');}
//to change nto lowercase
function lowerCase(elem) {
var temp = elem.value;
elem.value = temp.toLowerCase();}
</script>
</body>
</html>
>>>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!
-------------------------- -------------------------