Google Sheet Data update by web app Script

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

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

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

How to Google Sheet Data Update by Web App Script

Follow Step to update google sheet data

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 Google Sheeet ID'; const ss = SpreadsheetApp.openById(spreadsheetId); function doGet(e) { var tmp = HtmlService.createTemplateFromFile("index"); return tmp.evaluate() .setTitle("Only Data Update") .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 < 9); } 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, 8).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.s_name); dataSheet.getRange(i+2, 4).setValue(dataInfo.stu_fname); dataSheet.getRange(i+2, 5).setValue(dataInfo.stu_class); dataSheet.getRange(i+2, 6).setValue(dataInfo.stu_mobile); dataSheet.getRange(i+2, 7).setValue(dataInfo.stu_remarks); dataSheet.getRange(i+2, 8).setValue(new Date()); } } return 'SUCCESS'; } function getDataForSearch() { const dataSheet = ss.getSheetByName("data"); var getLastRow = dataSheet.getLastRow(); var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 7).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

<!---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><!---form row---> 

   <div id="rowdata"></div>
  
  </div><!---add rec form--->
  </div><!---padding 10 px--->

   <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>

<!-- Include Date Range Picker -->
<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 += "<td><div class=\"form-row\"><div class=\"form-group col-md-8\"><label for=\"up_s_name\">Student Name</label> ";          
          displayTable += "<input type=\"text\" id=\"up_s_name"+ row_number +"\" value=\""+r[2]+"\" class=\"form-control\" /></div></td> ";         
          
displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-8\"><label for=\"up_stu_fname\">Student Father Name</label> ";          
          displayTable += "<input type=\"text\" id=\"up_stu_fname"+ row_number +"\" value=\""+r[3]+"\" class=\"form-control\" /></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[4]+"\" class=\"form-control\" /><option>"+r[4]+"</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[5]+"\" class=\"form-control\" /></div></td> ";         
          

           

          displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-8\"><label for=\"up_stu_remarks\">Remarks</label> ";          
          displayTable += "<input type=\"text\" id=\"up_stu_remarks"+ row_number +"\" value=\""+r[6]+"\" 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 += "</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.s_name = document.getElementById("up_s_name"+row_number).value;
      dataInfo.stu_fname = document.getElementById("up_stu_fname"+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; 
      dataInfo.stu_remarks = document.getElementById("up_stu_remarks"+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 by web app Script

How to Google Sheet Data Update by Web App Script

Follow Step to update google sheet data

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

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

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

Step 8 :- More Detail See Video

2 Comments

Donation and Support

Contact Form

Google Sheet Data update by web app Script

How to Google Sheet Data Update by Web App Script

Follow Step to update google sheet data

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

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

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

Step 8 :- More Detail See Video

2 Comments

Contact Form

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