Google Sheet Data Search on Web page by ID & Password

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

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

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

How to Google Sheet Data Search to Web page By ID & Password

Follow Step

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

Step 2 :- Click to >> 123 >> Pain Text >>

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

Step 4 :- Create Two File
code.gs
index.html

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

and Change to Google Sheet ID and Google Sheet Name in Code.gs File

Code.gs

function doGet(e) {
  
  return HtmlService.createTemplateFromFile("Index").evaluate()
  .setTitle("WebApp: Search By Password")
  .addMetaTag('viewport', 'width=device-width, initial-scale=1')
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}


/* PROCESS FORM */
function processForm(formObject){ 
  var concat = formObject.searchtext+formObject.searchtext2;
  var result = "";
  if(concat){//Execute if form passes search text
      result = search(concat);
  }
  return result;
}

//SEARCH FOR MATCHED CONTENTS ;
function search(searchtext){
  var spreadsheetId   = 'SPREADSHEET ID'; //** CHANGE !!!!
  var sheetName = "PARTICULAR SHEET"
  var range = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName).getDataRange();
  var data = range.getValues();
  var ar = [];
  
  data.forEach(function(f) {
    if (~[f[26]].indexOf(searchtext)) {
      ar.push([ f[1],f[2],f[3],f[4],f[5],f[6],f[7],f[8],f[9],f[10],f[11],f[12],f[13],f[14],f[15],f[16],f[17],f[18],f[19],f[20],f[21],f[22],f[23],f[24],f[25] ]);
    }
  });
                                           
  return ar;
};

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

index.html

<!DOCTYPE html>
<html>
    <head>
        <base target="_self">
        <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
       
   <style>
   
   /* h5 {background: red;} */
   
   </style>

        
    </head>
    <body>
        <div class="container">
            <br>
            <div class="row">
              <div class="col">
            
                  <!-- ## SEARCH FORM ------------------------------------------------ -->
                  <center><form id="search-form" onsubmit="handleFormSubmit(this)">
                    <div class="form-group mb-2">
                      <h5 for="searchtext">Welcome to Sunrise IT Educaion</h5>
                    </div><p>
                    <div class="form-group mx-sm-3 mb-3">
                      <input type="text" class="form-control col-sm-6" id="searchtext" name="searchtext" placeholder="Full Name"><br>
                      <input type="password" class="form-control col-sm-6" id="searchtext2" name="searchtext2" placeholder="Password">
                    </div><p>
                    <button type="submit" class="btn btn-primary mb-2">Search</button>
                    <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-info d-none" role="status" aria-hidden="true"></span>
                  </form></center>
                  <!-- ## SEARCH FORM ~ END ------------------------------------------- -->
              
              </div>    
            </div>
            <div class="row">
              <div class="col">
            
                <!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
                <div id="search-results" class="table-responsive">
                  <!-- The Data Table is inserted here by JavaScript -->
                </div>
                <!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
                  
              </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 src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.min.js" integrity="sha384-w1Q4orYjBQndcko6MimVbzY0tgp4pWB4lZ7lr30WKz0vr/aWKhXdBNmNb5D92v7s" crossorigin="anonymous"></script>
        <!--##JAVASCRIPT FUNCTIONS ---------------------------------------------------- -->
        <script>
          //PREVENT FORMS FROM SUBMITTING / PREVENT DEFAULT BEHAVIOUR
          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", preventFormSubmit, true); 
             
          
          //HANDLE FORM SUBMISSION
          function handleFormSubmit(formObject) {
           if(document.getElementById('searchtext').value == "" || document.getElementById('searchtext2').value == ""){
              alert("Fill in Name and Password");
           }else{
             document.getElementById('resp-spinner5').classList.remove("d-none");
             document.getElementById('resp-spinner6').classList.remove("d-none");
             document.getElementById('resp-spinner7').classList.remove("d-none");
            google.script.run.withSuccessHandler(createTable).processForm(formObject);
            document.getElementById("search-form").reset();
           };
          };
        
          //CREATE THE DATA TABLE
          function createTable(dataArray) {
             document.getElementById('resp-spinner5').classList.add("d-none");
             document.getElementById('resp-spinner6').classList.add("d-none");
             document.getElementById('resp-spinner7').classList.add("d-none");
            if(dataArray && dataArray !== undefined && dataArray.length != 0){
              var result = "<table class='table table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
                           "<thead style='white-space: nowrap'>"+
                             "<tr>"+                               //Change table headings to match with the Google Sheet
                              "<th scope='col'>A</th>"+
                              "<th scope='col'>B</th>"+
                              "<th scope='col'>C</th>"+
                              "<th scope='col'>D</th>"+
                              "<th scope='col'>E</th>"+
                              "<th scope='col'>F</th>"+
                              "<th scope='col'>G</th>"+
                              "<th scope='col'>H</th>"+
                              "<th scope='col'>I</th>"+
                              "<th scope='col'>J</th>"+
                              "<th scope='col'>K</th>"+
                              "<th scope='col'>L</th>"+
                              "<th scope='col'>M</th>"+
                              "<th scope='col'>N</th>"+
                              "<th scope='col'>O</th>"+
                              "<th scope='col'>P</th>"+
                              "<th scope='col'>Q</th>"+
                              "<th scope='col'>R</th>"+
                              "<th scope='col'>S</th>"+
                              "<th scope='col'>T</th>"+
                              "<th scope='col'>U</th>"+
                              "<th scope='col'>V</th>"+
                              "<th scope='col'>W</th>"+
                              "<th scope='col'>X</th>"+
                              "<th scope='col'>Y</th>"+
                              "<th scope='col'>Z</th>"+
                            "</tr>"+
                          "</thead>";
              for(var i=0; i<dataArray.length; i++) {
                  result += "<tr>";
                  for(var j=0; j<dataArray[i].length; j++){
                      result += "<td>"+dataArray[i][j]+"</td>";
                  }
                  result += "</tr>";
              }
              result += "</table>";
              var div = document.getElementById('search-results');
              div.innerHTML = result;
            }else{
              var div = document.getElementById('search-results');
              //div.empty()
              div.innerHTML = "Data not found!";
            }
          }
        </script>
        <!--##JAVASCRIPT FUNCTIONS ~ END ---------------------------------------------------- -->    
    
    </body>
</html>

Google Sheet Data Search on Web page by ID & Password

How to Google Sheet Data Search to Web page By ID & Password

Follow Step

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

Step 2 :- Click to >> 123 >> Pain Text >>

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

Step 4 :- Create Two File
code.gs
index.html

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

and Change to Google Sheet ID and Google Sheet Name in Code.gs File

Code.gs

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

index.html

Step 7 :- Some Change to code and index file see video

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

Step 9 :- More Detail See Video

Post a Comment

Donation and Support

Contact Form

Google Sheet Data Search on Web page by ID & Password

How to Google Sheet Data Search to Web page By ID & Password

Follow Step

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

Step 2 :- Click to >> 123 >> Pain Text >>

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

Step 4 :- Create Two File code.gsindex.html

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

and Change to Google Sheet ID and Google Sheet Name in Code.gs File

Code.gs

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

index.html

Step 7 :- Some Change to code and index file see video

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

Step 9 :- More Detail See Video

Post a Comment

Contact Form

Step 4 :- Create Two File
code.gs
index.html