CRUD Operation with Search Data Table

नमस्‍कार दोस्‍तो,

इस ब्‍लॉग में हम आपको बहुत ही सरल भाषा में बतायेगें की किस प्रकार गुगल शीट के डेटा को वेब एप स्‍क्रीप्‍ट की मदद से CRUD ऑपरेशन Perform किया जाता है ा

CRUD Operation Means - Google Sheet के डेटा को वेब एप स्‍क्रीप्‍ट की मदद से रिड करना, गुगल शीट में नया डेटा ऐड करना, गुगल शीट के डेटा को अपडेट करना एवं गुगल शीट के डेटा को डिलीट करना  इत्‍यादि ा

 Learn to CRUD Operation With Search Data Table by Web app Script

Practical

   

 Please Follow following Step 

Step 1 :- Click to Google Sheet Copy With Source Code

 

Step 2 :-Copy Custom Online Form With Signature Google Sheet

Step 3 :- Click to Google Sheet>> Extensions Click >> App Script>> 

 Step 4 :-then Create  three file in App Script File

code.gs

index.html

form.html

DataTable.html

Step 5 :- Copy of code.gs File below file and Paste to Web App Script Editor

code.gs

/** * Tutorail: https://www.bpwebs.com/crud-operations-on-google-sheets-with-online-forms/ * Download code: https://github.com/AishwaryT/Google-app-script-crud */ function doGet(request) { return HtmlService.createTemplateFromFile('index').evaluate() .addMetaTag('viewport','width=device-width , initial-scale=1') .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL) } /** INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES */ function include(filename) { return HtmlService.createHtmlOutputFromFile(filename) .getContent(); } function globalVariables(){ var varArray = { spreadsheetId : '1za0cJQrNuNfBTr4s61P_OolGmehIt_Zibe3JTK0_ljE', //** CHANGE !!! dataRage : 'Data!A2:G', //** CHANGE !!! idRange : 'Data!A2:A', //** CHANGE !!! lastCol : 'H', //** CHANGE !!! insertRange : 'Data!A1:G1', //** CHANGE !!! sheetID : '0' //** CHANGE !!! }; return varArray; } /** PROCESS FORM */ function processForm(formObject){ /**--Execute if form passes an ID and if is an existing ID */ if(formObject.RecId && checkID(formObject.RecId)){ /**--Update Data */ updateData(getFormValues(formObject),globalVariables().spreadsheetId,getRangeByID(formObject.RecId)); }else{ /**--Execute if form does not pass an ID **--Append Form Data */ appendData(getFormValues(formObject),globalVariables().spreadsheetId,globalVariables().insertRange); } return getAllData(); } /** GET FORM VALUES AS AN ARRAY */ function getFormValues(formObject){ /** ADD OR REMOVE VARIABLES ACCORDING TO YOUR FORM */ if(formObject.RecId && checkID(formObject.RecId)){ var values = [[formObject.RecId.toString(), formObject.name, formObject.State, formObject.date, formObject.data1, formObject.data2, formObject.data3]]; }else{ /** Reference https://webapps.stackexchange.com/a/51012/244121 */ var values = [[new Date().getTime().toString(), formObject.name, formObject.State, formObject.date, formObject.data1, formObject.data2, formObject.data3]]; } return values; } /** ## CURD FUNCTIONS ---------------------------------------------------------------------------------------- */ /** CREATE/ APPEND DATA */ function appendData(values, spreadsheetId,range){ var valueRange = Sheets.newRowData(); valueRange.values = values; var appendRequest = Sheets.newAppendCellsRequest(); appendRequest.sheetID = spreadsheetId; appendRequest.rows = valueRange; var results = Sheets.Spreadsheets.Values.append(valueRange, spreadsheetId, range,{valueInputOption: "RAW"}); } /** READ DATA */ function readData(spreadsheetId,range){ var result = Sheets.Spreadsheets.Values.get(spreadsheetId, range); return result.values; } /** UPDATE DATA */ function updateData(values,spreadsheetId,range){ var valueRange = Sheets.newValueRange(); valueRange.values = values; var result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, { valueInputOption: "RAW"}); } /** DELETE DATA */ function deleteData(ID){ var startIndex = getRowIndexByID(ID); var deleteRange = { "sheetId" : globalVariables().sheetID, "dimension" : "ROWS", "startIndex" : startIndex, "endIndex" : startIndex+1 } var deleteRequest= [{"deleteDimension":{"range":deleteRange}}]; Sheets.Spreadsheets.batchUpdate({"requests": deleteRequest}, globalVariables().spreadsheetId); return getAllData(); } /** ## HELPER FUNCTIONS FOR CRUD OPERATIONS -------------------------------------------------------------- */ /** CHECK FOR EXISTING ID, RETURN BOOLEAN */ function checkID(ID){ var idList = readData(globalVariables() .spreadsheetId,globalVariables().idRange,) .reduce(function(a,b){ return a.concat(b); }); return idList.includes(ID); } /** GET DATA RANGE A1 NOTATION FOR GIVEN ID */ function getRangeByID(id){ if(id){ var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange); for(var i=0;i<idList.length;i++){ if(id==idList[i][0]){ return 'Data!A'+(i+2)+':'+globalVariables().lastCol+(i+2); } } } } /** GET RECORD BY ID */ function getRecordById(id){ if(id && checkID(id)){ var result = readData(globalVariables().spreadsheetId,getRangeByID(id)); return result; } } /** GET ROW NUMBER FOR GIVEN ID */ function getRowIndexByID(id){ if(id){ var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange); for(var i=0;i<idList.length;i++){ if(id==idList[i][0]){ var rowIndex = parseInt(i+1); return rowIndex; } } } } /** GET ALL RECORDS */ // function getAllData(){ // var data = readData(globalVariables().spreadsheetId,globalVariables().dataRage); // return data; // } /**DataTable */ function getAllData() { var ss = SpreadsheetApp.getActiveSpreadsheet() var sheet = ss.getSheets()[0] var range = sheet.getDataRange() var values = range.getDisplayValues() Logger.log(values) return values } /** ## OTHER HELPERS FUNCTIONS ------------------------------------------------------------------------ */ /** GET DROPDOWN LIST */ function getDropdownList(range){ var list = readData(globalVariables().spreadsheetId,range); return list; }

Step 6 :- Copy of index.html File below file and Paste to Web App Script Editor

index.html

  <!DOCTYPE html> <html> <head> <base target="_top"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css" integrity="sha512-Fo3rlrZj/k7ujTnHg4CGR2D7kSs0v4LLanw2qksYuRlEzO+tcaEPQogQ0KaoGN26/zrn20ImR1DfuLWnOo7aBA==" crossorigin="anonymous" referrerpolicy="no-referrer" /> <!-- Popup Modal --> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script> <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"> <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous"> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"> </script> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js" integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" crossorigin="anonymous"> </script> </head> <body onload="createStateDropdown()"> <div class="container"> <!-- The Modal Form --> <div class="modal" id="myModal"> <div class="modal-dialog"> <div class="modal-content"> <!-- Modal Header --> <div class="modal-header"> <h2>Data</h2> <button type="button" id="btn-close" class="close" data-dismiss="modal" onclick="clearForm();">×</button> </div> <!-- Modal body --> <div class="modal-body"> <!-- See Form.html file --> <?!= include('form'); ?> </div> </div> </div> </div> </div> <!-- DataTable --> <div class="container"> <div class="row"> <div class="col"> <!-- See DataTable.html File --> <?!= include('DataTable'); ?> </div> </div> </div> <center> <footer class="footer"> <section class="container"> <p style="font-size:10px;"> <a style="color:orange;" target="_blank" href="https://www.youtube.com/channel/UCFiqQsZYzRlqfds5YhlGMWA/playlists" title="Contact "><i class="far fa-copyright"></i> Sunrise IT Education </a> </p> </section> </footer> </center> <script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script> </body> </html>

Step 7 :- Copy of form.html File below file and Paste to Web App Script Editor

form.html

  <form id="myForm" class="p-2 border border-light rounded bg-light" onsubmit="handleFormSubmit(this)"> <div id="message"></div> <input type="text" id="RecId" name="RecId" value="" style="display: none"> <div class="form-row"> <div class="form-group col-md-12"> <label for="name" >Name</label> <input type="text" class="form-control" id="name" name="name" placeholder="Name" required> </div> </div> <div class="form-row"> <div class="form-group col-md-6"> <label for="exampleFormControlSelect1">State</label> <select class="form-control" id="State" name="State" required> <option selected disabled value="">select</option> </select> </div> <div class="form-group col-md-6" > <label for="date">Date</label> <input type="date" class="form-control date" id="date" name="date" required> </div> </div> <div class="form-row"> <div class="form-group col-md-6"> <label for="data1" >Mobile No</label> <input type="text" class="form-control" id="data1" name="data1" placeholder="Mobile No" required> </div> <div class="form-group col-md-6"> <label for="data2" >Address</label> <input type="text" class="form-control" id="data2" name="data2" placeholder="Address" required> </div> <div class="form-group col-md-6"> <label for="data3" >Remarks</label> <input type="text" class="form-control" id="data3" name="data3" placeholder="Remarks" required> </div> </div> <button id="btnSubmit" type="submit" class="btn bd-blue-500" >Submit</button> <input class="btn btn-secondary" type="reset" value="Reset"> </form>

Step 8 :- Copy of DataTable.html  File below file and Paste to Web App Script Editor

DataTable.html 

 

<!DOCTYPE html> <html> <head> <base target="_top"> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous"> <link href="https://fonts.googleapis.com/css2?family=Noto+Sans+Thai&family=Niramit&display=swap" rel="stylesheet"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/css/bootstrap.min.css" /> <link rel="stylesheet" href="https://cdn.datatables.net/1.11.3/css/dataTables.bootstrap5.min.css" /> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/4.1.1/animate.min.css" /> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css" integrity="sha512-Fo3rlrZj/k7ujTnHg4CGR2D7kSs0v4LLanw2qksYuRlEzO+tcaEPQogQ0KaoGN26/zrn20ImR1DfuLWnOo7aBA==" crossorigin="anonymous" referrerpolicy="no-referrer" /> <link href="https://ltschool.web.app/css/Bootstrapcolor.css" rel="stylesheet" type="text/css"> <style> body { font-family: "Niramit", sans-serif; font-size: 16px; } .btn-group-xs>.btn, .btn-xs { padding: .25rem .4rem; font-size: .875rem; line-height: .5; border-radius: .2rem; } .btn-head-table { padding-bottom: 10px; } .editBtn:hover { color: white; } </style> </head> <body> <div class="container-fluid"> <div class="row"> <div class="col-md-12 mx-auto"> <nav class="navbar navbar-expand-lg navbar-light bd-red-500 my-3"> <div class="container-fluid"> <a class="navbar-brand text-white"><i class="fas fa-user"></i> Data</a> <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="navbarNav"> <ul class="navbar-nav ms-auto"> <li class="nav-item"> <button type='button' class='btn bd-yellow-500'data-toggle='modal' data-target='#myModal'><i class="fas fa-user-plus"></i> New Entry</button> </li> </ul> </div> </div> </nav> <center> <span id="resp-spinner5" class="spinner-grow spinner-grow-sm text-danger d-none" role="status" aria-hidden="true"></span> <span id="resp-spinner6" class="spinner-grow spinner-grow-sm text-warning d-none" role="status" aria-hidden="true"></span> <span id="resp-spinner7" class="spinner-grow spinner-grow-sm text-ligth d-none" role="status" aria-hidden="true"></span> <span id="resp-spinner8" class="spinner-grow spinner-grow-sm text-info d-none" role="status" aria-hidden="true"></span> <span id="resp-spinner9" class="spinner-grow spinner-grow-sm text-primary d-none" role="status" aria-hidden="true"></span> <span id="resp-spinner10" class="spinner-grow spinner-grow-sm text-dark d-none" role="status" aria-hidden="true"></span> </center> <table id="dataTable" class="table dt-responsive nowrap" style="width:100%"> <thead class="bd-pink-500"></thead> </table> </div> </div> </div> <script> /** * Prevent forms from submitting. * */ function preventFormSubmit() { var forms = document.querySelectorAll('form'); for (var i = 0; i < forms.length; i++) { forms[i].addEventListener('submit', function(event) { event.preventDefault(); }); } } window.addEventListener("load", functionInit, true); /** * INITIALIZE FUNCTIONS ONLOAD * */ function functionInit(){ preventFormSubmit(); getLastTenRows(); }; /** * HANDLE FORM SUBMISSION * */ function handleFormSubmit(formObject) { google.script.run.withSuccessHandler(createTable).processForm(formObject); // setTimeout(function() {$('#myModal').modal('hide');}, 10000); document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Added the record successfully.</div>"; document.getElementById("myForm").reset(); } /** * Clear form when pop-up is closed. * */ function clearForm() { document.getElementById("message").innerHTML = ""; document.getElementById("myForm").reset(); } google.script.run.withSuccessHandler(createTable).getAllData(); document.getElementById('resp-spinner5').classList.remove("d-none"); document.getElementById('resp-spinner6').classList.remove("d-none"); document.getElementById('resp-spinner7').classList.remove("d-none"); document.getElementById('resp-spinner8').classList.remove("d-none"); document.getElementById('resp-spinner9').classList.remove("d-none"); document.getElementById('resp-spinner10').classList.remove("d-none"); function createTable(dataArray) { $('#btn-close').click() $(document).ready(function(){ document.getElementById('resp-spinner5').classList.add("d-none"); document.getElementById('resp-spinner6').classList.add("d-none"); document.getElementById('resp-spinner7').classList.add("d-none"); document.getElementById('resp-spinner8').classList.add("d-none"); document.getElementById('resp-spinner9').classList.add("d-none"); document.getElementById('resp-spinner10').classList.add("d-none"); $('#dataTable').DataTable({ //data: dataArray, data: dataArray.slice(1), columns: [ { title: "ID" }, { title: "Name" }, { title: "State" }, { title: "Date" }, { title: "Mobile No" }, { title: "Address" }, { title: "Remarks" }, { title: "Edit", "render": function (data,type){ return "<button type='button' class='btn btn-outline-warning btn-xs editBtn' data-toggle='modal' data-target='#myModal' onclick='editData(this);'><i class='fa fa-edit'></i></button>"; } }, { title: "Delete", "render": function (data,type){ return "<button type='button' class='btn btn-outline-danger btn-xs deleteBtn' onclick='deleteData(this);'><i class='fa fa-trash''></i></button>"; } }, ], // "ordering": false, destroy:true, responsive:true, lengthMenu: [ [5, 10, 25, 50, 100, -1 ], ['5', '10', '25', '50','100', 'All' ] ], order: [[2, "asc"], [2, "asc"], ], }); }); } /** * DELETE DATA * */ function deleteData(el) { Swal.fire({ position:'top', title: 'Are you sure?', text: "You won't be able to revert this!", icon: 'warning', showCancelButton: true, confirmButtonColor: '#3085d6', cancelButtonColor: '#d33', cancelButtonText: 'Cencel', confirmButtonText: 'Ok' }).then((result) => { if (result.isConfirmed) { Swal.fire( 'Deleted!', 'Your file has been deleted.', ) var recordId = el.parentNode.parentNode.cells[0].innerHTML; google.script.run.withSuccessHandler(createTable).deleteData(recordId); } }); } /** * EDIT DATA * https://stackoverflow.com/a/32377357/2391195 * */ function editData(el){ var recordId = el.parentNode.parentNode.cells[0].innerHTML; google.script.run.withSuccessHandler(populateForm).getRecordById(recordId); } /** * POPULATE FORM * */ function populateForm(records){ document.getElementById('RecId').value = records[0][0]; document.getElementById('name').value = records[0][1]; document.getElementById('State').value = records[0][2]; document.getElementById('date').value = records[0][3]; document.getElementById('data1').value = records[0][4]; document.getElementById('data2').value = records[0][5]; document.getElementById('data3').value = records[0][6]; document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Update Record [ID: "+records[0][0]+"]</div>"; } /** * RETRIVE DATA FROM GOOGLE SHEET FOR State DROPDOWN * */ function createStateDropdown() { //SUBMIT YOUR DATA RANGE FOR DROPDOWN AS THE PARAMETER //Change to your sheet name and data range. google.script.run.withSuccessHandler(StateDropDown).getDropdownList("State!A1:A66"); } /** * POPULATE State DROPDOWNS * Ref: https://stackoverflow.com/a/53771955/2391195 * */ function StateDropDown(values) { var list = document.getElementById('State'); for (var i = 0; i < values.length; i++) { var option = document.createElement("option"); option.value = values[i]; option.text = values[i]; list.appendChild(option); } } function loading(){ window.addEventListener("load", functionInit, false); window.addEventListener("load", preventFormSubmitSearch, true); } </script> <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.10.2/dist/umd/popper.min.js" integrity="sha384-7+zCNj/IqJ95wo16oMtfsKbZ9ccEh31eOz1HGyDuCQ6wgnyJNSYdrPa03rtR1zdB" crossorigin="anonymous"> </script> <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.min.js" integrity="sha384-QJHtvGhmr9XOIpI6YVutG+2QOK9T+ZnN4kzFN1RtK3zEFEIsxhlmWl5/YESvpZ13" crossorigin="anonymous"> </script> <script src="https://code.jquery.com/jquery-3.5.1.js"></script> <script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script> <script src="https://cdn.datatables.net/1.11.3/js/dataTables.bootstrap5.min.js"></script> </body> </html>

Step 9:- Click to Deploy >>New Deployment>>

Step 10:-  for More Details See Video