#JP50 Mencari Data Autofill (Dropdown) dan Memisahkan Data Yang Sudah Di Edit

Mencari data Autofill (Dropdown) dan Memisahkan Data Yang Sudah Di Edit Ke Sheet Lain.

1. Copy Spreadsheet (Klik disini)

2. Terdapat 2 sheet Data 1 dan Data 2 (Sudah Di Edit), masing-masing sheet memiliki field/kolom yang sama.

3. Buatlah kembar kerja Apps Script dengan cara klik menu Ekstensi/Extensions lalu pilih Apps Script.

4. Terdapat 5 file yang sudah tersedia yang siap di isi script, di antaranya :

1. Code.gs

2. js.html

3. index.html

4. popup.html

5. AutoCompletestyle.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)

function doGet(e){
  return HtmlService.createTemplateFromFile('index').evaluate();
}
var z = (num, places) => String(num).padStart(places, '0');
var ws = SpreadsheetApp.openById("1W27-4rqaZmSNkYIaHEdL8TrLR94CPDly7eC0JhmfEZA") //Ganti ID Spreadsheet
var sn = ws.getSheetByName("Data 1"); //Sesuaikan dengan sheet 1
var sv = ws.getSheetByName("Data 2 (Sudah Di Edit)"); //Sesuaikan Sheet 2
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function auto(){
  var desa = sn.getRange('b2:b').getValues()
  var kecamatan = sn.getRange('c2:c').getValues()
  var kabupaten = sn.getRange('d2:d').getValues()
  var provinsi = sn.getRange('e2:e').getValues()
  var v5 = sn.getRange('f2:f').getValues()
  var v6 = sn.getRange('g2:g').getValues()
  var v7 = sn.getRange('h2:h').getValues()
  return [desa,kecamatan,kabupaten,provinsi,v5,v6,v7];
}

function fin(x){
var l = sn.getLastRow()+1;
var  flag  =  1 ;
  for(var i = 1;i <= l;i++){
  if(sn.getRange(i,2).getValue()==x){
      flag = 0;
  var a = sn.getRange(i,3).getValue();
  var b = sn.getRange(i,4).getValue();
  var c = sn.getRange(i,5).getValue();
  var d = sn.getRange(i,6).getValue();
  var e = sn.getRange(i,7).getValue();
  var f = sn.getRange(i,8).getValue();
  return [a,b,c,d,e,f];
  }}if(flag==1){
  return[];
  }}
  function subf(v){
  var l = sn.getLastRow();
  var j = sv.getLastRow();
  var dt = Utilities.formatDate(new Date(),Intl.DateTimeFormat().resolvedOptions().timeZone, 'yyyy-MM-dd HH:mm:ss');
  var id = "a"+z(l,3)
  var  flag  =  1 ;
  for(var i = 1;i <= l;i++){
  if(v.id==sn.getRange(i,2).getValue()){
      flag = 0;
  sv.appendRow([dt,v.id,v.name,v.alamat,v.desa,v.kecamatan,v.kabupaten,v.provinsi])
  sv.getRange(j+1,1).setNumberFormat('@STRING@');
  var data = 'Berhasil di tambahkan ke Sheet Data 2';
  return data;}}
  if(flag==1){
  sn.appendRow([dt,id,v.name,v.alamat,v.desa,v.kecamatan,v.kabupaten,v.provinsi])
  sn.getRange(l+1,1).setNumberFormat('@STRING@');
  var data = 'Berhasil di tambahkan ke Sheet Data 1';
  return data;
    }};
function getStates()
{
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var statesSheet = ss.getSheetByName("Data 1");
  var statesRange = statesSheet.getRange("B2:B51");
  var statesValues = statesRange.getValues();  
  return statesValues;

}

PENJELASAN

Line 5 : Ganti ID Spreadsheet dengan ID Spreadsheet anda.

Line 6 : Sesuaikan dengan nama sheet Data 1

Line 7 : Sesuaikan dengan nama sheet Data 2

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

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

<script src="https://code.jquery.com/jquery-3.6.0.js"></script>
<script src="https://code.jquery.com/ui/1.13.2/jquery-ui.js"></script>
  <script>
$(document).ready(function() {autoComplete();});
function autoComplete(){
  google.script.run.withSuccessHandler(function(ar){
        listA = [];
        ar[0].forEach(function(item, index){
        listA.push(item[0]);
        });
        $("#id").autocomplete({
          source: listA
        });
        listB = [];
        ar[1].forEach(function(item, index){
        listB.push(item[0]);
        });
        $("#name").autocomplete({
          source: listB
        });
        listC = [];
        ar[2].forEach(function(item, index){
        listC.push(item[0]);
        });
        $("#alamat").autocomplete({
          source: listC
        });
        listD = [];
        ar[3].forEach(function(item, index){
        listD.push(item[0]);
        });
        $("#desa").autocomplete({
          source: listD
        });
        listE = [];
        ar[4].forEach(function(item, index){
        listE.push(item[0]);
        });
        $("#kecamatan").autocomplete({
          source: listE
        });
        listF = [];
        ar[5].forEach(function(item, index){
        listF.push(item[0]);
        });
        $("#provinsi").autocomplete({
          source: listF
        });
        listG = [];
        ar[6].forEach(function(item, index){
        listG.push(item[0]);
        });
        $("#provinsi").autocomplete({
          source: listG
        });
      }).auto();
    }
</script>
<script>
function myFun(){
  let x = document.getElementById("id").value;
  if(x.length==4){
  document.getElementById("id").disabled = true;
  document.getElementById('lod').style.display='block';
  google.script.run.withFailureHandler(re)
                     .withSuccessHandler(re)
                     .fin(x);}else{
  document.getElementById("id").disabled = false;
  document.getElementById('lod').style.display='none'                     
  }
}
function re(v){
document.getElementById('lod').style.display='none'
document.getElementById("id").disabled = false;
if(v!=""){
var a = document.getElementById("name").value=v[0];
var b = document.getElementById("alamat").value=v[1];
var c = document.getElementById("desa").value=v[2];
var d = document.getElementById("kecamatan").value=v[3];
var e = document.getElementById("kabupaten").value=v[4];
var f = document.getElementById("provinsi").value=v[5];
}else{
document.getElementById("lab").reset();
}
}
</script>
<script>
function subm(v){
  document.getElementById('lod').style.display='block'
  google.script.run.withFailureHandler(fail)
                     .withSuccessHandler(pass)
                     .subf(v);}                    
  
function fail(v){
document.getElementById('lod').style.display='none'
document.getElementById('failp').style.display='block'
document.getElementById("rsf").innerHTML = v;}
function pass(v){
document.getElementById('lod').style.display='none'
document.getElementById('succ').style.display='block'
document.getElementById("rsp").innerHTML = v;
document.getElementById("lab").reset();}
</script>

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

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

<html class="no-js" lang="en">
<head>
    <title>Mencari Data Autofill dan memisahkan data yang sudah Di Edit</title>
    <base target="_top">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<link rel="stylesheet" href="//code.jquery.com/ui/1.13.2/themes/base/jquery-ui.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js" ></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js" ></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.6.1/css/bootstrap.min.css" />
    <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet"></link>
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/1000hz-bootstrap-validator/0.11.9/validator.min.js"></script>
    
   <?!= include('js'); ?>
 <?!= include('popup'); ?>
  <?!= include('AutoCompleteStyle'); ?>
 <script>
  {
      google.script.run.withSuccessHandler(function(ar) 
      {
        console.log(ar);
        statesArray = [];
        ar.forEach(function(item, index) 
        {
            statesArray.push(item[0]);
        });


        $("#id").autocomplete({
          source: statesArray
        });

      }).getStates();
    }
    </script>
    <style>
              body {
            background: #EECDA3;
            background: -webkit-linear-gradient(to top, #EF629F, #EECDA3);
            background: linear-gradient(to top, #EF629F, #EECDA3);
        }
        .container {
            max-width: 550px;
        }
        .has-error label,
        .has-error input,
        .has-error textarea {
            color: red;
            border-color: red;
        }
        .list-unstyled li {
            font-size: 13px;
            padding: 4px 0 0;
            color: red;
        }
    </style>
</head>
<body>
    <div class="container mt-5">
        <div class="card">
            <h5 class="card-header text-center">Mencari Data Autofill</h5>
            <h5 class="card-header text-center">Memisahkan Data yang sudah Di Edit</h5>
            <div class="card-body">
                <form id="lab" onsubmit="event.preventDefault();subm(this)">

                    <div class="form-group">
                         <lavel for="id">ID</lavel>
                        <input type="text" name="id"id="id" class="form-control" placeholder="Cari ID"  onchange="myFun()" >
                        <!-- Error -->
                        <div class="help-block with-errors"></div>
                        <label style="color:blue;"><b><i><sup>Jika Input Data Baru, ID Tidak Usah Diisi</sup></i></b></label>
                    </div>
                    <div class="form-group">
                        <lavel for="name">Nama</lavel>
                        <input type="text" name="name" id="name" class="form-control" placeholder="Masukkan Nama" required>
                        <!-- Error -->
                        <div class="help-block with-errors"></div>
                    </div>
                    <div class="form-group">
                        <lavel for="alamat">Alamat</lavel>
                        <input type="text" name="alamat" id="alamat" class="form-control" placeholder="Masukkan Alamat" required>
                        <!-- Error -->
                        <div class="help-block with-errors"></div>
                    </div>
                    <div class="form-group">
                         <lavel for="desa">Desa</lavel>
                        <input type="text" name="desa" id="desa" class="form-control" placeholder="Masukkan Desa" required>
                        <!-- Error -->
                        <div class="help-block with-errors"></div>
                    </div>

                    <div class="form-group">
                        <lavel for="kecamatan">Kecamatan</lavel>
                        <div class="form-group">
                            <input type="text" name="kecamatan" id="kecamatan" class="form-control" placeholder="Masukkan Kecamatan" required />
                            <!-- Error -->
                            <div class="help-block with-errors"></div>
                        </div>
                    </div>
                    <div class="form-group">
                         <lavel for="kabupaten">Kabupaten</lavel>
                        <div class="form-group">
                            <input type="text" name="kabupaten" id="kabupaten" class="form-control" placeholder="Masukkan Kabupaten" required />
                            <!-- Error -->
                            <div class="help-block with-errors"></div>
                        </div>
                    </div>

                    <div class="form-group">
                        <lavel for="provinsi">Provinsi</lavel>
                        <input type="text" name="provinsi"id="provinsi" class="form-control" placeholder="Masukkan Provinsi" required />
                        <!-- Error -->
                        <div class="help-block with-errors"></div>
                    </div>

                    <div class="form-group">
                         <input type="submit"class="btn btn-primary btn-block">
                        <input type="reset"class="btn btn-primary btn-block">
                    </div>
                    <script>GetStates(); </script>
                </form>
               
            </div>
        </div>
    </div>
</body>
 <br>
  <br>
   <br>
    <br>
</html>
<!--Footer -->
  <style>.footer,.generic-footer{margin-bottom:98px}@media (min-width:52px){.footer,.generic-footer{margin-bottom:78px}}@media (min-width:52px){.footer,.generic-footer{margin-bottom:56px}}@media (min-width:52px){.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:52px){.disclaimer{text-align:right;border-left:2px solid red;border-top-left-radius:10px}}@media (min-width:1920px){.disclaimer{width:20%}}</style><div class="disclaimer">Version.02.25.23 @Copyright <a title="https://www.javabitpro.com/" target="_blank" href="https://www.javabitpro.com/" style="color: black;"><b>www.javabitpro.com</b></a></div>

8. Copy dan pastekan script di bawah ini ke popup.html.

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

<style>
.loader {margin-left:45%;
  border: 2px solid #f3f3f3;
  border-radius: 50%;
  border-top: 2px solid #3498db;
  width: 120px;
  height: 120px;
  -webkit-animation: spin 2s linear infinite; /* Safari */
  animation: spin 2s linear infinite;
}

/* Safari */
@-webkit-keyframes spin {
  0% { -webkit-transform: rotate(0deg); }
  100% { -webkit-transform: rotate(360deg); }
}

@keyframes spin {
  0% { transform: rotate(0deg); }
  100% { transform: rotate(360deg); }
}
</style>


<div id="lod" class="w3-modal">
<div class="w3-center">
<div class="loader"></div>
</div>
</div>
<div id="succ" class="w3-modal">
<div class="w3-modal-content w3-white w3-round-large"style="width:300px;">
<div class="w3-center w3-margin-bottom">
<i class="w3-xxxlarge w3-round-xxlarge w3-text-green 3-bordered">&#10003;</i>
<h4 class="w3-text-green">Berhasil!</h4>	
      <div class="w3-container">
        <p id="rsp"></p>
      </div>
      <button onclick="document.getElementById('succ').style.display='none'" class="w3-button w3-center w3-text-green">OK</button><br><br>
  </div>
</div>
   </div> 
   <div id="failp" class="w3-modal">
<div class="w3-modal-content w3-white w3-round-large"style="width:300px;">
<div class="w3-center w3-margin-bottom">
<i class="w3-xxxlarge w3-round-xxlarge w3-text-red 3-bordered">&#x2717;</i>
<h4 class="w3-text-red">Gagal!</h4>	
      <div class="w3-container">
        <p id="rsf"></p>
      </div>
      <button onclick="document.getElementById('failp').style.display='none'" class="w3-button w3-center w3-text-red">OK</button><br><br>
  </div>
</div>
   </div>  

9. Copy dan pastekan script di bawah ini ke AutoCompleteStyle.html

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

<style>
    .ui-autocomplete {
      position: absolute;
      top: 100%;
      left: 0;
      z-index: 1000;
      display: none;
      float: left;
      min-width: 160px;
      padding: 5px 0;
      margin: 2px 0 0;
      list-style: none;
      font-size: 14px;
      text-align: left;
      background-color: #ffffff;
      border: 1px solid #cccccc;
      border: 1px solid rgba(0, 0, 0, 0.15);
      border-radius: 4px;
      -webkit-box-shadow: 0 6px 12px rgba(0, 0, 0, 0.175);
      box-shadow: 0 6px 12px rgba(0, 0, 0, 0.175);
      background-clip: padding-box;
    }

    .ui-autocomplete > li > div {
      display: block;
      padding: 3px 20px;
      clear: both;
      font-weight: normal;
      line-height: 1.42857143;
      color: #333333;
      white-space: nowrap;
    }

    .ui-state-hover,
    .ui-state-active,
    .ui-state-focus {
      text-decoration: none;
      color: #262626;
      background-color: #f5f5f5;
      cursor: pointer;
    }

    .ui-helper-hidden-accessible {
      border: 0;
      clip: rect(0 0 0 0);
      height: 1px;
      margin: -1px;
      overflow: hidden;
      padding: 0;
      position: absolute;
      width: 1px;
    }
</style>

10. Klik ikon Save.

11. Klik tombol Terapkan/Deploy lalu pilih Deployment baru/New Deployment.

12. Pastikan jenisnya adalah Aplikasi web dan hak akses adalah Siapa saja/Anyone

13. Klik Url atau salin Url yang sudah di Deploy.

SELESAI !!!

`