Form Untuk Pencarian Data Dari Semua Sheet Yang Ada

1. Copy Spreadsheet (Klik Disini)

2. Pada contoh spreadsheet di atas terdapat beberapa sheet dengan nama bantuan yang berbeda-beda dan setiap sheet memilik kolom yang sama.

3. Untuk membuat Web Apps Script untuk mencari data sesuai NIK pada semua sheet yang ada maka buatlah lembar kerja Apps Script dengan cara klik menu Ekstensi/Extenstions lalu pilih Apps Script.

4. Pada lembar kerja Apps Script terdapat 3 file default yaitu :

Code.gs
index.html
visit.html

5. Copy dan pastekan script di bawah ini ke Code.gs

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

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

/**
 *  Website : www.javabitpro.com 
 *  Youtube : Javabitro
*/
function doGet(e) {
  var template = HtmlService.createTemplateFromFile('index')
  return  template.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME)
  .addMetaTag('viewport', 'width=device-width , initial-scale=1')
}

function include(filename) {
   return HtmlService.createHtmlOutputFromFile(filename)
  .getContent();
}

function getCode(code) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var allss =ss.getSheets();  
for (var i in allss){
  var ws =ss.getSheets()[i];
  var data=ws.getDataRange().getDisplayValues().filter(row=>{
    return row[1]==code //0  adalah pencarian dari kolom 0 atau pertama
    })
    Logger.log(data)
  if(data.length>0) break;
}

var stdCodesList = data.map (function(r) { return r[1]; }); //0  adalah pencarian dari kolom 0 atau pertama
var stdList = data.map(function(r) { 
return [`  
<center><span class="text-shadow:1px 1px 0 #444 w3-large w3-text-blue">Hasil Pencarian</span></center>
        <table class="table table-bordered">
        <thead class="thead-light">
         <tr>
          <th scope="col"><center>NIK</center></th>
          <th scope="col"><center>No KK</center></th>
          <th scope="col"><center>Nama</center></th>
          <th scope="col"><center>Alamat</center></th>
          <th scope="col"><center>Jenis Bantuan</center></th>
         </tr>
        </thead>
        <tbody>
         <tr>
          <td>${r[1]}</td> <td>${r[2]}</td> <td>${r[3]}</td>  <td>${r[4]} </td> <td>${r[5]} </td>
         </td>
         </tr>
        </tbody>
        </table>
        `];
});

var position = stdCodesList.indexOf(code); 
if(position > -1){
return stdList[position];
} else {
return '*Tidak Informasi yang ditemukan';

  }
    
}

function getURL(){
return ScriptApp.getService().getUrl()
}

6. Copy dan pastekan script di bawah ini ke index.html

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

<html>
<head>
<!-- Required meta tags -->
<base target="_top">
<!--font awesome-->
<script src="https://kit.fontawesome.com/ad42651166.js" crossorigin="anonymous"></script>
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.8.1/css/all.css" integrity="sha384-50oBUHEmvpQ+1lW4y57PTFmhCaXp0ML5d60M1M7uH2+nqUivzIebhndOJK28anvf" crossorigin="anonymous">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/css/bootstrap.min.css">
<script src="https://cdn.jsdelivr.net/npm/jquery@3.6.4/dist/jquery.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.6.2/dist/js/bootstrap.bundle.min.js"></script>

    <!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<link href='https://fonts.googleapis.com/css2?family=Crimson+Pro&family=Literata' rel='stylesheet' type='text/css'>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">

<!-- font-awesome@6.2.0 icon Visit -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css">
<?!= include('visit')?>
<style type='text/css'>

@font-face {
  font-family: 'Crimson Pro', serif;
}
 
body { font-family: 'Crimson Pro' !important; }
 
::placeholder {
  color: peachpuff;
  font-size: 16px;
  text-align: center;
}
 * {
  margin: 0;
  padding: 0;
  box-sizing: border-box;
  font-family: 'Lato', 'Arial', sans-serif;
}

/* HEADINGS */

h1, p {
  color: black;
  text-align: left;
  line-height: 1.4;
}

h1 {
  font-size: 2.2rem;
}

h2 {
  color: #000;
  font-size: 1.3rem;
  text-align: center;
  line-height: 1.4;
  margin-bottom: 10px;
}

/* BASIC SETUP */

.page-wrapper {
  width: 100%;
  height: auto;
}

.nav-wrapper {
  width: 100%;
  position: -webkit-sticky; /* Safari */
  position: sticky;
  top: 0;
  background: linear-gradient(-45deg, #EE7752, #E73C7E, #23A6D5, #23D5AB);
  background-size: 400% 400%;
  	-webkit-animation: gradbar 15s ease infinite;
	-moz-animation: gradbar 15s ease infinite;
	animation: gradbar 15s ease infinite;
}

.grad-bar {
  width: 100%;
  height: 5px;
  background: linear-gradient(-45deg, #23D5AB,#23A6D5,#E73C7E,#EE7752);
  background-size: 400% 400%;
  	-webkit-animation: gradbar 15s ease infinite;
	-moz-animation: gradbar 15s ease infinite;
	animation: gradbar 15s ease infinite;
}

/* NAVIGATION */

.navbar {
  display: grid;
  grid-template-columns: 1fr 3fr;
  align-items: center;
  height: 50px;
  overflow: hidden;
}

.navbar img {
  height: 16px;
  width: auto;
  justify-self: start;
  margin-left: 20px;
}

.navbar ul {
  list-style: none;
  display: grid;
  grid-template-columns: repeat(6,1fr);
  justify-self: end;
  
}

.nav-item a {
  color:  #fff;
  font-size: 0.9rem;
  font-weight: 400;
  text-decoration: none;
  transition: color 0.3s ease-out;
}

.nav-item a:hover {
  color: #3498db;
}

/* SECTIONS */



/* MOBILE MENU & ANIMATION */

.menu-toggle .bar{
  width: 25px;
  height: 3px;
  background-color: #3f3f3f;
  margin: 5px auto;
  -webkit-transition: all 0.3s ease-in-out;
  -o-transition: all 0.3s ease-in-out;
  transition: all 0.3s ease-in-out;
}

.menu-toggle {
  justify-self: end;
  margin-right: 25px;
  display: none;
}

.menu-toggle:hover{
  cursor: pointer;
}

#mobile-menu.is-active .bar:nth-child(2){
  opacity: 0;
}

#mobile-menu.is-active .bar:nth-child(1){
  -webkit-transform: translateY(8px) rotate(45deg);
  -ms-transform: translateY(8px) rotate(45deg);
  -o-transform: translateY(8px) rotate(45deg);
  transform: translateY(8px) rotate(45deg);
}

#mobile-menu.is-active .bar:nth-child(3){
  -webkit-transform: translateY(-8px) rotate(-45deg);
  -ms-transform: translateY(-8px) rotate(-45deg);
  -o-transform: translateY(-8px) rotate(-45deg);
  transform: translateY(-8px) rotate(-45deg);
}

/* KEYFRAME ANIMATIONS */

@-webkit-keyframes gradbar {
	0% {
		background-position: 0% 50%
	}
	50% {
		background-position: 100% 50%
	}
	100% {
		background-position: 0% 50%
	}
}

@-moz-keyframes gradbar {
	0% {
		background-position: 0% 50%
	}
	50% {
		background-position: 100% 50%
	}
	100% {
		background-position: 0% 50%
	}
}

@keyframes gradbar {
	0% {
		background-position: 0% 50%
	}
	50% {
		background-position: 100% 50%
	}
	100% {
		background-position: 0% 50%
	}
}

/* Media Queries */

  /* Mobile Devices - Phones/Tablets */

@media only screen and (max-width: 720px) { 
  .features {
    flex-direction: column;
    padding: 50px;
  }
  
  /* MOBILE HEADINGS */
  
  h1 {
    font-size: 1.9rem;
  }
  
  h2 {
    font-size: 1rem;
  }
  
  p {
    font-size: 0.8rem;
  }
  
  /* MOBILE NAVIGATION */
     
  .navbar ul {
    display: flex;
    flex-direction: column;
    position: fixed;
    justify-content: start;
    top: 55px;
    background-color: #fff;
    width: 100%;
    height: calc(100vh - 55px);
    transform: translate(-101%);
    text-align: center;
    overflow: hidden;
  }
  
  .navbar li {
    padding: 15px;
  }
  
  .navbar li:first-child {
    margin-top: 50px;
  }
  
  .navbar li a {
    font-size: 1rem;
  }
   
  .menu-toggle, .bar {
    display: block;
    cursor: pointer;
  }
  
  .mobile-nav {
  transform: translate(0%)!important;
}
  
  /* SECTIONS */
  
  .headline {
    height: 20vh;
  }
    
  .feature-container p {
    margin-bottom: 25px;
  }
  
  .feature-container {
    margin-top: 20px;
  }
  
  .feature-container:nth-child(2) {
    order: -1;
  }
  
  /* SEARCH DISABLED ON MOBILE */
  
  #search-icon {
    display: none;
  }
  
  .search-input {
  display: none;
 }
  
}
</style>
 
  </head>
  <body>
<div class="container">
<div class="page-wrapper">
 <div class="nav-wrapper">
  <div class="grad-bar"></div>
  <nav class="navbar">
    <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqdWTRu3bkEmwvYgic_5qivtpFT0OPTzjdv14X2X2QrK0BseSjyUAYlDzebAmMpG20NgR9Ac7pZpMtjtdz5i6RZR3ppukUG4JqHME9qzQsjWloKoDZV7j0_Tqp2sMwMnAP-fwJLp92GPsfhQTAVjBqa-MnSJSsF7_JzBv469y2uLnY639th1GBN7MhWAU/s320/log%20putih.png"style="width:150px; height:auto; ">
    <div class="menu-toggle" id="mobile-menu">
      <span class="bar"></span>
      <span class="bar"></span>
      <span class="bar"></span>
    </div>
    <ul class="nav no-search">
      <li class="nav-item"><a href="#">Beranda</a></li>
      <li class="nav-item"><a href="#">Tentang</a></li>
      <li class="nav-item"><a href="#">Informasi</a></li>
      <li class="nav-item"><a href="#">Kontak Person</a></li>


    </ul>
  </nav>
  </div>
  
 <div class="alert alert-warning" role="alert" background-color: #ffed00>
        <h6 class="font-weight-bold"><i class="fas fa-book-reader "></i>
         Cara mencari informasi</h6>
        <p>1. Masukkan 16 Digit Nomor KTP. 
       <br>2. Tekan Enter Pada Keyboard.</p>
 </div> 
<center>

    <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZ8B_nPqW3IXcEHCrI7xrh-sqJTMHP8IUzgXSIZhH6-y04PdmOsunTJUWXZGsiHVUDt1fBvhMdDltzihl6hq0rZT8SO1EgCu7LgzQXfxlP_7tbeuekfm0gZqvA8MLV0QpyFEc2e1VsIRt7SDhZaDFFTwYX_vdSWEjS15Z_kyKeC-mKyuWxfkQEPOP0UD8/s320/Untitled%20design.gif" style="width:200px; height:auto; padding-bottom: 10px; ">
  </center>


 <div class="container">

 <div class="row">
 <div class="col">
 <!-- 1 of 3 -->
 </div>
 <div class="col-md-auto">
     <div class="card border-info">     
                   <h5 class="card-header text-white bg-info"><i class='fa fa-search'></i> Ketikkan 16 digit nomor KTP.</h5>
                   <div class="card-body"><input class="form-control form-control-lg" type="text" id="idStudent" placeholder="No KTP 16 Digit" maxlength="16"></div>
               </div> 
 
 </div>
    
 <div class="col">
 </div>
 </div>
 </div>
 
 <div class="container">
 <div class="row">
 <div class="col-md">
 </div>
 <div class="col-md">
 
 <br>
   
 </div>
 <div class="col-md">
 </div>
 </div>
 </div>
 
 <div class="container">
 <div class="row">
 <div class="col">
 </div>
 <div class="col-5">
 
 <center> <input type="button" class = "w3-button w3-blue w3-round-xlarge" id="home-btn" value = "Clear" onclick ="Home()" ></center>
 </div>
 <!-- Result Header End -->
  <!-- www.javabitpro.com -->
 <br>
 
 <div class="col">
 </div>
 </div>
 

 <div class="row">
 <div class="col">
 </div>
 <div class="col-md-auto">
 
 <br>
 
 <div class="container">
 <div id="search">
 <textarea style="border:none;resize:none;" id="search" readonly></textarea>
 </div>
 </div>
 
 </div>
 <div class="col">
    </div>
 </div>
 </div>
 
</div>
<!-- Footer -->
<div class="container">
        <footer><br>
            <div class="nav-wrapper">
                <div class="container">
                    <br>
                    <div class="row">
                        <div class="clo-lg-3 col-md-auto">
                            <h6 class="prompt text-white"><i class="fa fa-user-circle-o mr-3"></i> © 2023 Copyright | javabitpro</h6>
                        </div>
                    </div>
                </div>
            </div>
        </footer>
        </div>
   
        <!-- End Footer -->

        <!-- www.javabitpro.com -->
<script>
 
document.getElementById("idStudent").addEventListener("input", findCode);
  

 function findCode() {
 var code = document.getElementById("idStudent").value;
 if(code.length === 16){
    google.script.run.withSuccessHandler(updateData).getCode(code);

 }
}

function updateData(data) {
  if(data == '*Tidak Informasi yang ditemukan'){
    Swal.fire({
  position: 'center',
  icon: 'error',
  title: 'Maaf..informasi Anda tidak ditemukan.',
  showConfirmButton: true,
  //timer: 1500
})
 document.getElementById("search").innerHTML = data;
 M.updateTexfields();
}else{
Swal.fire({
  position: 'center',
  icon: 'success',
  title: 'Selamat..menemukan informasi Anda.',
  showConfirmButton: false,
  timer: 2000
})
 document.getElementById("home-btn").style.visibility="visible"
 document.getElementById("search").innerHTML = data;
 M.updateTexfields();

  }
}
window.onload = function(){document.getElementById("home-btn").style.visibility="hidden"}

function Home(){
google.script.run.withSuccessHandler(function(url){
window.open(url,'_top')
}).getURL()

}

</script>
<script src="//cdn.jsdelivr.net/npm/sweetalert2@10"></script>
 
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
 
</body>
</html>

7. Copy dan pastekan script di bawah ini ke visit.html

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

 <style>


 .fab-container2 {
	position: fixed;
	bottom: 80px;
	right: 1px;
	z-index: 999;
	cursor: pointer;
}

.fab-label2 {
    padding: 2px 5px;
    align-self: center;
    user-select: none;
    white-space: nowrap;
    border-radius: 3px;
    font-size: 16px;
    background: #666666;
    color: #ffffff;
    box-shadow: 0 6px 20px rgba(0,0,0,0.2);
    /* margin-right: 10px; */
}

 .fab-container {
	position: fixed;
	bottom: 10px;
	right: 10px;
	z-index: 999;
	cursor: pointer;
}

.fab-icon-holder {
	width: 50px;
	height: 50px;
	border-radius: 100%;
	background: #6dd4fc;

	box-shadow: 0 6px 20px rgba(0,0,0,0.2);
}

.fab-icon-holder:hover{
	opacity: 0.8;
}

.fab-icon-holder i {
	display: flex;
	align-items: center;
	justify-content: center;

	height: 100%;
	font-size: 25px;
	color: #000000;
}

.fab {
	width: 56px;
	height: 56px;
	background: #6dd4fc;
}

.fab-options {
	list-style-type: none;
	margin: 0;

	position: absolute;
	bottom: 70px;
	right: 0;

	opacity: 0;

	transition: all 0.3s ease;
	transform: scale(0);
	transform-origin: 85% bottom;
}

.fab:hover + .fab-options, .fab-options:hover {
	opacity: 1;
	transform: scale(1);
}

.fab-options li {
	display: flex;
	justify-content: flex-end;
	padding: 5px;
}

.fab-label {
	padding: 2px 5px;
	align-self: center;
	user-select: none;
	white-space: nowrap;
	border-radius: 3px;
	font-size: 16px;
	background: #666666;
	color: #ffffff;
	box-shadow: 0 6px 20px rgba(0,0,0,0.2);
	margin-right: 10px;
} 

a {
  color: inherit;
  text-decoration: none;
}
</style>

  <!-- ------------------------------ Visit ------------------------------- -->
	<div class="fab-container2">
    <p class="fab-label2">Visit</p>
  </div>
	<div class="fab-container">
		<div class="fab fab-icon-holder">
			<i><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiC7hb7GJ4v4IwapVwZJJyvGsG5WF4RxsmEb0va1X0t-6urm1PGPDA2IEsETeLCuiyfJkUWhOOIUg9FN_Dr3gJJQmQUe2-AnYI3-R05TC6PbMRSnaljamNC9lQMOeE2q4Dr29jkfEaYwpadAjraVGgIgC_wGGp596Uh6tLX_ClrfbqK3wdhz4jE7i16/s1600/PNG%20JP.png" style="width: 75px;"></i>
		</div>

		<ul class="fab-options">
			<li>
				<span class="fab-label">Youtube</span>
				<div class="fab-icon-holder">
					<a target="_blank" href="https://www.youtube.com/@Javabitpro/videos"><i class="fa-brands fa-youtube"></i></i></a>
				</div>
			</li>
			<li style="margin-bottom: 60px;">
				<span class="fab-label">Website</span>
				<div class="fab-icon-holder">
					<a target="_blank" href="https://www.javabitpro.com/"><i class="fa-solid fa-globe"></i></a>
				</div>
			</li>
			
				
		</ul>
	</div>