#JP10 Pencarian Data App Script dan Hyperlink

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

Pencarian Data App Script dan Hyperlink

1. Copy Spreadsheet (Klik Disini)

2. Setelah dicopy akan tampil seperti pada gambar dibawah ini

3. Buka lembar kerja Apps Script dengan cara klik Ekstensi - Pilih Apps Script

4. Di menu sebelah kiri ada 2 file Kode.gs dan Index.php

5. Silahkan copy script dibawah ini dan paste di Kode.gs

Masukkan Password Untuk Melihat Script (Password ada di dalam video)

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}


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

//SEARCH FOR MATCHED CONTENTS 
function search(searchtext){
  var spreadsheetId   = '17KDVzhN_cbDkNLqXblfcdd553kyXcIV5kIqh2OEuhYs'; //** CHANGE !!!
  var dataRage        = 'Data!A2:Y';                                    //** CHANGE !!!
  var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRage).values;
  var ar = [];
  
  data.forEach(function(f) {
    if (~f.indexOf(searchtext)) {
       ar.push([f[0],f[1],f[2],f[3],'<a target="_blank"  href='  + f[5] + '>' + f[4] + '</a>']);
    }
  });
  return ar;
}

/* Script by www.javabitpro.com */

PENJELASAN !

Ganti SpreadsheetId dengan ID Spreadsheet kalian

6. Silahkan copy script dibawah ini dan paste di Index.html

Masukkan Password Untuk Melihat Script (Password sama dengan di atas)

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <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>
        
        <!--##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) {
            google.script.run.withSuccessHandler(createTable).processForm(formObject);
            document.getElementById("search-form").reset();
          }
        
          //CREATE THE DATA TABLE
          function createTable(dataArray) {
            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 witht he Google Sheet
                              "<th scope='col'>Nama</th>"+
                              "<th scope='col'>Umur</th>"+
                              "<th scope='col'>Provinsi</th>"+
                              "<th scope='col'>Kota</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>
        <!--##JAVASCRIPT FUNCTIONS ~ END ---------------------------------------------------- -->
        
    </head>
    <body>
      
        <div class="container">
            <br>
            <div class="row">
              <div class="col">
           
                  <!-- ## SEARCH FORM ------------------------------------------------ -->
                  <form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
                    <div class="form-group mb-2">
                      <label for="searchtext">Search Text</label>
                    </div>
                    <div class="form-group mx-sm-3 mb-2">
                      <input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Search Text">
                    </div>
                    <button type="submit" class="btn btn-primary mb-2">Search</button>
                  </form>
                  <!-- ## 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>
    </body>
</html>
<!-- Script by www.javabitpro.com ------------------------------------------------ -->
<style>.footer,.generic-footer{margin-bottom:98px}@media (min-width:374px){.footer,.generic-footer{margin-bottom:78px}}@media (min-width:546px){.footer,.generic-footer{margin-bottom:56px}}@media (min-width:1055px){.footer,.generic-footer{margin-bottom:0}}.disclaimer{position:fixed;z-index:9999999;bottom:0;right:0;border-top:2px solid #ff5c62;text-align:center;font-size:14px;font-weight:400;background-color:#fff;padding:5px 10px 5px 10px}.disclaimer a:hover{text-decoration:underline}@media (min-width:1052px){.disclaimer{text-align:right;border-left:2px solid red;border-top-left-radius:10px}}@media (min-width:1920px){.disclaimer{width:60%}}</style><div class="disclaimer">@Copyright <a title="https://www.javabitpro.com/" target="_blank" href="https://www.javabitpro.com/" style="color: black;"><b>www.javabitpro.com</b></a></div>