Google Sheet Data Update & Delete

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

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

निचें किये दिशा निर्देशों को ध्‍यान से पढकर अपनी गुगल शीट में कार्य करे ा

How to Google Sheet Data Update and Delete by Web App Script

Follow Step to update and Delete Data of Google Sheet

Practical :-

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

Step 2 :- Click to >> Extension >> App Script >>

Step 3 :- Create two file

code.gs

index.html

Step 4 :- Copy of Code.gs file in Given Below Code and Paste to Web App Script Editor

code.gs

//code.gs const spreadsheetId = 'Paste your Google Sheet ID'; const ss = SpreadsheetApp.openById(spreadsheetId); function doGet(e) { var tmp = HtmlService.createTemplateFromFile("index"); return tmp.evaluate() .setTitle("Only Updat and Delete") .addMetaTag('viewport', 'width=device-width, initial-scale=1') .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); } function uniqueId() { var uuid_array = []; var dataSheet = ss.getSheetByName("data"); var getLastRow = dataSheet.getLastRow(); if(getLastRow > 1) { var uuid_values = dataSheet.getRange(2, 1, getLastRow - 1, 1).getValues(); for(i = 0; i < uuid_values.length; i++){ uuid_array.push(uuid_values[i][0]); } var x_count = 0; do { var y = 'false'; var uuid_value = Utilities.getUuid(); if(uuid_array.indexOf(uuid_value) == -1.0){ y = 'true'; //Logger.log(uuid_value); return uuid_value; } x_count++; } while (y == 'false' && x_count < 7); } else { return Utilities.getUuid(); } } function updateRecord(record_id, dataInfo) { var dataSheet = ss.getSheetByName("data"); var getLastRow = dataSheet.getLastRow(); var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 7).getValues(); for(i = 0; i < table_values.length; i++){ if(table_values[i][0] == record_id){ dataSheet.getRange(i+2, 2).setValue(dataInfo.Roll); dataSheet.getRange(i+2, 3).setValue(dataInfo.name); dataSheet.getRange(i+2, 4).setValue(dataInfo.stu_class); dataSheet.getRange(i+2, 5).setValue(dataInfo.stu_mobile); dataSheet.getRange(i+2, 6).setValue(new Date()); } } return 'SUCCESS'; } function deleteRecord(record_id){ var dataSheet = ss.getSheetByName("data"); var getLastRow = dataSheet.getLastRow(); var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 5).getValues(); for(i = 0; i < table_values.length; i++){ if(table_values[i][0] == record_id){ var rowNumber = i+2; dataSheet.deleteRow(rowNumber); } } return 'SUCCESS'; } function getDataForSearch() { const dataSheet = ss.getSheetByName("data"); var getLastRow = dataSheet.getLastRow(); var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 5).getValues(); Logger.log(table_values); return table_values; }

Step 5 :- code.gs file line no 2 Change google sheet ID

Step 6 :- Copy of index.html file in Given Below Code and Paste to Web App Script Editor

index.html  <!DOCTYPE html> <html> <head> <base target="_top"> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous"> </head> <body> <div style="padding: 10px;" > <div id="addrecform" > <div id = "loading" style="font-size:18px;color:green;"> </div> <div class="form-row"> <div class="form-group col-md-12"> <label for="searchInput">Search Data</label> <input type="text" id="searchInput" class="form-control" oninput="search()"/> </div> </div> <div id="rowdata"></div> </div> </div> <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script> <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>  <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.4.1/js/bootstrap-datepicker.min.js"></script> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.4.1/css/bootstrap-datepicker3.css"/> <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.min.js" integrity="sha384-w1Q4orYjBQndcko6MimVbzY0tgp4pWB4lZ7lr30WKz0vr/aWKhXdBNmNb5D92v7s" crossorigin="anonymous"></script> <script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script> <script> var data; function setDataForSearch(){ loadingStart(); google.script.run.withSuccessHandler(function(dataReturned){ data = dataReturned.slice(); //set from global var name is data loadingEnd(); }).getDataForSearch(); } function search(){ var searchInput = document.getElementById("searchInput").value.toString().toLowerCase().trim(); var searchWords = searchInput.split(/\s+/); var searchColumns =[1,2]; var row_number = 0; var resultsArray = searchInput === "" ? [] :data.filter(function(r){ //search from global var name is data return searchWords.every(function(word){ return searchColumns.some(function(colIndex){ return r[colIndex].toString().toLowerCase().indexOf(word) !== -1 }); }); }); var displayTable = '<form>'; displayTable += '<table class=\"table\" id=\"mainTable\" >'; displayTable += "<tr>"; displayTable += "<th></th>"; displayTable += "<th></th>"; displayTable += "</tr>"; resultsArray.forEach(function(r){ displayTable += "<tr id=\"ROWNUMBER:"+ row_number +"\" >"; displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-8\" ><label for=\"up_Roll\">Student Roll Number</label> "; displayTable += "<input type=\"number\" id=\"up_Roll"+ row_number +"\" value=\""+r[1]+"\" class=\"form-control\" /> "; displayTable += "<input type=\"hidden\" id=\"up_record_id"+ row_number +"\" value=\""+r[0]+"\" /></div></div> "; displayTable += "<div class=\"form-row\"><div class=\"form-group col-md-8\"><label for=\"up_name\">Student Name</label> "; displayTable += "<input type=\"text\" id=\"up_name"+ row_number +"\" value=\""+r[2]+"\" class=\"form-control\" /></div></div></td> "; displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-8\"><label for=\"up_stu_class\">Student Class</label> "; displayTable += "<select type=\"text\" id=\"up_stu_class"+ row_number +"\" value=\""+r[3]+"\" class=\"form-control\" /><option>"+r[3]+"</option><option>5</option><option>6</option><option>7</option></select></div></td> "; displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-8\"><label for=\"up_stu_mobile\">Student Mobile</label> "; displayTable += "<input type=\"text\" id=\"up_stu_mobile"+ row_number +"\" value=\""+r[4]+"\" class=\"form-control\" /></div></td> "; displayTable += "<td><div style=\"padding: 5px\"> "; displayTable += "<input type=\"button\" id=\"update_button"+ row_number +"\" value=\"Update Record\" class=\"btn btn-primary\" "; displayTable += "onclick=\"updateRecordWapp("+row_number+")\" /></div> "; displayTable += "<div style=\"padding: 5px\" > "; displayTable += "<input type=\"button\" value=\"Delete Record\" id=\"delete_button"+ row_number +"\" class=\"btn btn-danger\" "; displayTable += "onclick=\"deleteRecordWapp("+row_number+")\" /></div> "; displayTable += "</td>"; displayTable += "</tr>"; row_number++; }); displayTable += '</table></form>'; document.getElementById("rowdata").innerHTML = displayTable; }//end func search function updateRecordWapp(row_number){ document.getElementById("update_button"+row_number).disabled = true; var dataInfo = {}; var record_id = document.getElementById("up_record_id"+row_number).value; dataInfo.Roll = document.getElementById("up_Roll"+row_number).value; dataInfo.name = document.getElementById("up_name"+row_number).value; dataInfo.stu_class = document.getElementById("up_stu_class"+row_number).value; dataInfo.stu_mobile = document.getElementById("up_stu_mobile"+row_number).value; google.script.run.withSuccessHandler(function(return_string){ document.getElementById("update_button"+row_number).disabled = false; Swal.fire({ position: 'center', icon: 'success', title: 'update Successfully', showConfirmButton: true, timer: 1500 }) setDataForSearch(); }).updateRecord(record_id, dataInfo); } function deleteRecordWapp(row_number){ var record_id = document.getElementById("up_record_id"+row_number).value; Swal.fire({ position: 'center', title: 'Are You Delete Record?', icon: 'warning', showCancelButton: true, confirmButtonColor: '#3085d6', cancelButtonColor: '#d33', confirmButtonText: 'Yes', cancelButtonText: 'NO' }).then((result) => { if(result.isConfirmed){ google.script.run.withSuccessHandler(function(return_string){ if(return_string == 'SUCCESS'){ document.getElementById('mainTable').deleteRow(document.getElementById('ROWNUMBER:'+row_number).rowIndex); setDataForSearch(); } }).deleteRecord(record_id); Swal.fire( 'Delete Record', 'Delete Record Successfully', 'success' ) }//if result.isConfirmed })//then } function loadingStart(){ document.getElementById("loading").classList.remove("invisible"); } function loadingEnd(){ document.getElementById("loading").classList.add("invisible"); } document.addEventListener("DOMContentLoaded",setDataForSearch); </script> </body> </html>

Google Sheet Data Update & Delete

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

Step 2 :- Click to >> Extension >> App Script >>

Step 3 :- Create two file

code.gs

index.html

Step 4 :- Copy of Code.gs file in Given Below Code and Paste to Web App Script Editor

code.gs

Step 5 :- code.gs file line no 2 Change google sheet ID

Step 6 :- Copy of index.html file in Given Below Code and Paste to Web App Script Editor

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

Step 8 :- More Detail See Video

1 Comments

Donation and Support

Contact Form