Hyperlink Search From Google Sheet by ID & Password

March 02, 2022

1

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

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

How to Google Sheet Data Search to Website with Hyperlink

Follow Step

Step 1 :- Open Google Sheet

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

3- Create Code.gs and index.html file

4- Copy of Code.gs and index.html file and paste to App script

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 = '11H4v08AOXEF7O3r8mL_zVj6g2BZVGs5dqnim1mv57Fg'; //** CHANGE !!!! var sheetName = "Data" var range = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName).getDataRange(); var data = range.getValues(); var ar = []; data.forEach(function(f) { if (~[f[7]].indexOf(searchtext)) { ar.push([f[2],f[3],f[4],'<a target="_blank" href=' + f[5] + '>' + f[6] + '</a>']); } }); return ar; };

5- Change Google Sheet iD and Google Sheet Name

index.html

function doGet(e) { <!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">  <center><form id="search-form" onsubmit="handleFormSubmit(this)"> <div class="form-group mb-2"> <h5 for="searchtext">Welcome to Sunrise IT Education</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>  </div> </div> <div class="row"> <div class="col">  <div id="search-results" class="table-responsive">  </div>  </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>  <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'>ILR Name</th>"+ "<th scope='col'>Patwar Name</th>"+ "<th scope='col'>Village Name</th>"+ "<th scope='col'>Website</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>  </body> </html>

6- Deploment Web App Script to Anyone

More Deatils See Video

Tags Script