Membuat Database Siswa Lengkap Dengan Foto Menggunakan Web Apps Script (CRUD)

1. Copy Spreadsheet (Klik Disini)

2. Buatlah folder pada Google Drive untuk menyimpan foto siswa (wajib share)

3. Kembali pada spreadsheet yang telah di copy di atas, terdapat 2 sheet yaitu sheet menu dan sheet data

4. Pada sheet menu terdapat beberapa kolom yang yang berisi tentang menu-menu yang akan tampil di web apps script.

5. Pada sheet data terdapat beberapa kolom yang akan terisi saat menambahkan data di web apps script.

6. Buatlah lembar kerja apps script dengan cara klik menu Ektensi/Extensions lalu pilih Apps Script.

7. Pada lembar kerja Apps Script terdapat beberapa file default yaitu :

Code.gs
index.html
css.html
script.html
modalSTD.html

8. 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)

//---------------------
//Youtube : Javabitpro
//Website : www.javabitpro.com
//---------------------  

var folder = DriveApp.getFolderById('.....') //change ID FOLDER
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data') //change NAMA SHEET
var data= ss.getDataRange().getValues().slice(1)

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate()
  .addMetaTag('viewport', 'width=device-width, initial-scale=1')
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}

function include(fh){
  return HtmlService.createHtmlOutputFromFile(fh).getContent()
}

function getMenu(){
 return ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('menu').getDataRange().getDisplayValues().slice(1)
}

function getData(id){
  if(id == undefined){id = "KELAS 1"}
  var rowData = data.filter(r=>{ return r[4]==id })
  return rowData
}

function serachSTD(id){
  var row = data.find(r=>{ return r[0]==id  })
  return row
}

function saveData(obj){
  let myPic
  let file
  let rowID = data.findIndex (r=> r[0]== obj.idstd)+2
    if(rowID > 1){
        if(obj.myFile.length == 0){
              myPic = ss.getRange(rowID,7).getValue()
        }else{
              file = folder.createFile(obj.myFile).getId()
              myPic = "https://lh3.googleusercontent.com/d/"+file
        }     
        ss.getRange(rowID,2).setValue(obj.data1);      
        ss.getRange(rowID,3).setValue(obj.data2);  
        ss.getRange(rowID,4).setValue(obj.data3);  
        ss.getRange(rowID,5).setValue(obj.data4);  
        ss.getRange(rowID,6).setValue(obj.data5);  
        ss.getRange(rowID,7).setValue(myPic); 
    }else{
      if(obj.myFile.length == 0){
         myPic = "https://sv1.picz.in.th/images/2023/05/04/yANhPV.png"
      }else{
        file = folder.createFile(obj.myFile).getId()
        myPic = "https://lh3.googleusercontent.com/d/"+file
      }
        ss.appendRow([
        obj.idstd, 
        obj.data1, 
        obj.data2, 
        obj.data3, 
        obj.data4, 
        obj.data5, 
        myPic
        ])
    }
  }

function deleteData(id) {
  let rowID = data.findIndex(r => r[0] == id) + 2
  if (rowID > 0) {
    ss.deleteRow(rowID);
  }
  return true;
}

//---------------------
//Youtube : Javabitpro
//Website : www.javabitpro.com
//---------------------

PENJELASAN :

Pada line nomor 7, gantilah ID Folder dengan ID Folder yang telah dibuat tadi.

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

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

<!DOCTYPE html>
<html lang="en">

<head>
  <link href="https://fonts.googleapis.com/css2?family=Prompt:ital@0;1&display=swap" rel="stylesheet">
  <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
  <link rel="stylesheet"
    href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-icons/1.9.1/font/bootstrap-icons.min.css">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.12.1/css/all.min.css">
  <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
  <script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script>
  <?!=include('css')?>
</head>

<body>
  <?!=include('modalSTD')?>
  <nav class="navbar fixed-top bg-light">
    <div class="container-fluid">
      <a class="navbar-brand">Database Siswa Sekolah Dasar Merdeka</a>
      <form class="d-flex" role="search">
        <span id="showadmin"></span>
        <button class="btn btn-outline-primary me-1" type="button" id="btn_add" data-bs-toggle="modal" data-bs-target="#exampleModal" onclick="addData()" style="display:none">TAMBAH</button>
        <button class="btn btn-outline-success me-1" type="button" id="btn_login" onclick="login()">LOGIN</button>
        <button class="btn btn-outline-danger" type="button" id="btn_logout" onclick="logout()" style="display:none">LOGOUT</button>
      </form>
    </div>
  </nav>

  <div class="offcanvas offcanvas-start sidebar-nav" tabindex="-1" id="sidebar">
    <div class="offcanvas-body p-0">
      <nav class="navbar-dark">
        <ul class="navbar-nav p-1">
          <li>
            <div class="p-2 bg-light">
              <center>
                <i class="fas fa-th-large"></i> Pilih Kelas <i class="fas fa-th-large"> </i>
              </center>
            </div>
          </li>
          <li>
            <div id="menu"></div>
          </li>
        </ul>
      </nav>
    </div>
  </div>
  <!-- offcanvas -->
  <main class="mt-4 pt-3">
    <div class="container-fluid p-2">
      <div id="std"></div>
    </div>
  </main>
  <?!=include('script')?>

  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js">
  </script>
</body>

</html>

10. Copy dan pastekan script di bawah ini ke css.html

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

<style>
  body,
  button {
    font-family: 'Prompt', sans-serif;
  }
  :root {
    --offcanvas-width: 220px;
    --topNavbarHeight: 56px;
  }
  .sidebar-nav {
    width: var(--offcanvas-width);
  }
  .sidebar-link {
    display: flex;
    align-items: center;
  }
  .sidebar-link .right-icon {
    display: inline-flex;
  }
  .sidebar-link[aria-expanded="true"] .right-icon {
    transform: rotate(180deg);
  }
  .sidebar-link .right-icon2 {
    display: inline-flex;
  }

  .sidebar-link[aria-expanded="true"] .right-icon2 {
    transform: rotate(180deg);
  }
  @media (min-width: 992px) {
    body {
      overflow: auto !important;
    }
    main {
      margin-left: var(--offcanvas-width);
    }
    /* this is to remove the backdrop */
    .offcanvas-backdrop::before {
      display: none;
    }
    .sidebar-nav {
      -webkit-transform: none;
      transform: none;
      visibility: visible !important;
      height: calc(100% - var(--topNavbarHeight));
      top: var(--topNavbarHeight);
    }
  }
  .offcanvas {
    background: #199ad1; 
    box-shadow: rgba(0, 0, 0, 0.35) 0px 5px 15px;
  }
  .nav-link:hover {
    background: #03a9f5;
    color: #000;
  }
  span {
    color: #fff;
  }
  .navbar {
    background: #fff;
    box-shadow: rgba(0, 0, 0, 0.35) 0px 5px 15px;
  }
  
.grid{
  display:grid;
  grid-template-columns:repeat(auto-fit,minmax(140px,1fr));
  cursor:pointer;
}
  .grid-items{
    border-radius: 15px;
    border: 1px solid #cccccc;
    align-items: center;
    justify-content: center;
    padding: 10px;
    margin: 10px;
    box-sizing: border-box;
    transition: all linear 200ms;
    font-size: 12px;
    width:140px;
    display:grid;
  }
.imgStd{
  width:98%;
}
        </style>

11. Copy dan pastekan script di bawah ini ke script.html

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

<script>
  var user ='desta'   // USER ADMIN
  var pass =12345   // PASSWORD ADMIN

//---------------------
//Youtube : Javabitpro
//Website : www.javabitpro.com
//---------------------  


 google.script.run.withSuccessHandler(createTable).getMenu()
   function createTable(menu) {
      if(menu){
      var html ="<div>"+
                  "<table>"
        for (let i = 0; i < menu.length; i++) {
         html += `<a href="${menu[i][2]}" class="nav-link px-1 text-white"  onclick="showroom('${menu[i][2]}')">
         <span class="me-2">${menu[i][0]}</span> ${menu[i][1]}
          </a> `
    }
         html += "</table></div>";
                $("#menu").html(html)
}
   }

   function showroom(room){
     event.preventDefault()
     google.script.run.withSuccessHandler(createTable2).getData(room);
   }
</script>

<script>

  google.script.run.withSuccessHandler(createTable2).getData()
   function createTable2(menu) {
      if(menu){
      var html ="<div class='grid' >"
        for (let i = 0; i < menu.length; i++) {
         html += ` <div class="grid-items" onclick="editSTD('${menu[i][0]}')" data-bs-toggle="modal" data-bs-target="#exampleModal">
            <img class="imgStd" src="${menu[i][6]}">
            <center><p>${menu[i][1]} ${menu[i][2]}</p></center>
            <center><p>TEMPAT: ${menu[i][4]}</p></center>
          </div> ` 
    }
         html += "</div>";
                $("#std").html(html)
}
   }

// Add Data
function addData(){
      event.preventDefault()
      document.querySelector('#myForm').reset()
        $("#idstd").attr("readonly", false);
        $("#data1").attr("readonly", false);
        $("#imagePreview").text("");
        $("#btn_del").hide()
}

// Login
  function login(){
    Swal.fire({
  title: 'Login Form',
  html: `<input type="text" id="login" class="swal2-input" placeholder="Username">
  <input type="password" id="password" class="swal2-input" placeholder="Password">`,
  confirmButtonText: 'Sign in',
  focusConfirm: false,
  preConfirm: () => {
    const login = Swal.getPopup().querySelector('#login').value
    const password = Swal.getPopup().querySelector('#password').value
    if (!login || !password) {
      Swal.showValidationMessage(`Please enter login and password`)
    }
  if(login == user && password == pass){
          Swal.fire(
            'Berhasil Masuk',
          )
      $('#admin_login').show()
      $('#admin_logout').show()
      $('#btn_logout').show()
      $('#btn_login').hide()
      $('#showadmin').val('ADMIN')
      $('#btn_add').show()
      $("#data1").attr("readonly", false);
      $("#data2").attr("readonly", false);
      $("#data3").attr("readonly", false);
      $("#data4").attr("readonly", false);
      $("#data5").attr("readonly", false);
      
     
       
      
      
  }else{
         Swal.fire(
         'User atau Katasandi Salah!',
          )
  }
  }
})
  }


// Logout
function logout(){
      $('#admin_login').hide()
      $('#admin_logout').hide()
      $('#btn_logout').hide()
      $('#btn_login').show()
      $('#showadmin').val('')
      $('#btn_add').hide()
      $("#data1").attr("readonly", true);
      $("#data2").attr("readonly", true);
      $("#data3").attr("readonly", true);
      $("#data4").attr("readonly", true);
      $("#data5").attr("readonly", true);
      
}


// Edit
function editSTD(id){
    event.preventDefault()
    google.script.run.withSuccessHandler((result)=>{
     if(result !=null){
       $("#btn_del").show()
      
      
      document.querySelector('#idstd').value= result[0]
      document.getElementById('imagePreview').innerHTML = '<img width="120px" src="'+result[6]+'"/>';
       $("#idstd").attr("readonly", true);

    for (let i = 1; i < result.length-1; i++) {
      document.querySelector(`#data${i}`).value = result[i];
  } 

     }
   }).serachSTD(id)
   }


// Delete
function delForm(){
let idDel = document.querySelector('#idstd').value
Swal.fire({
  title: 'Apakah yakin akan di hapus?',
  text: "Data Yang Dihapus Tidak Bisa Dikembalikan!",
  icon: 'warning',
  showCancelButton: true,
  confirmButtonColor: '#3085d6',
  cancelButtonColor: '#d33',
  confirmButtonText: 'Delete!'
}).then((result) => {
  if (result.isConfirmed) {
      google.script.run.withSuccessHandler(()=>{
        alert('Data Berhasil Dihapus')
      google.script.run.withSuccessHandler(createTable2).getData()
        }).deleteData(idDel)
   }
})
       }


// Upload File
function fileValidation(){
    var fileInput = document.getElementById('myFile');
    var filePath = fileInput.value;
    var allowedExtensions = /(\.jpg|\.jpeg|\.png|\.gif)$/i;
    if(!allowedExtensions.exec(filePath)){
        // alert('Please upload file having extensions .jpeg/.jpg/.png/.gif only.');
        Swal.fire({
          position: 'top',
          icon: 'error',
          title: 'Harap Upload Gambar!',
          showConfirmButton: false,
          timer: 1500
        })
        fileInput.value = '';
        return false;
    }else{
        //Image preview
        if (fileInput.files && fileInput.files[0]) {
            var reader = new FileReader();
            reader.onload = function(e) {
                document.getElementById('imagePreview').innerHTML = '<img width="120px" src="'+e.target.result+'"/>';
            };
            reader.readAsDataURL(fileInput.files[0]);
        }
    }
}

//---------------------
//Youtube : Javabitpro
//Website : www.javabitpro.com
//---------------------  
</script>

12. Copy dan pastekan script di bawah ini ke modalSTD.html

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

<!-- Modal -->
<div class="modal fade" id="exampleModal" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h1 class="modal-title fs-5" id="exampleModalLabel">Informasi Siswa</h1>
        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
      </div>
      <div class="modal-body">
    <form onsubmit="saveForm(this)" id="myForm">
  <div class="row mb-1" id="form_idStd">
    <label for="inputtext3" class="col-sm-4 col-form-label">NIS</label>
    <div class="col-sm-8">
      <input type="text" class="form-control" id="idstd" name="idstd" readonly>
    </div>
  </div>
  <div class="row mb-1">
    <label for="inputtext3" class="col-sm-4 col-form-label">Foto Siswa</label>
    <div class="col-sm-8">
      <div id="imagePreview"></div>
    </div>
  </div>
  <div class="row mb-1">
    <label for="inputtext3" class="col-sm-4 col-form-label">Nama Depan</label>
    <div class="col-sm-8">
      <input type="text" class="form-control" id="data1" name="data1" required readonly >
    </div>
  </div>
  <div class="row mb-1">
    <label for="inputtext3" class="col-sm-4 col-form-label">Nama Belakang</label>
    <div class="col-sm-8">
      <input type="text" class="form-control" id="data2" name="data2" required readonly>
    </div>
  </div>
  <div class="row mb-1">
    <label for="inputtext3" class="col-sm-4 col-form-label">Nomor Absen</label>
    <div class="col-sm-8">
      <input type="text" class="form-control" id="data3" name="data3" readonly>
    </div>
  </div>
  <div class="row mb-1">
    <label for="inputtext3" class="col-sm-4 col-form-label">Kelas</label>
    <div class="col-sm-8">
          <select id="data4" name="data4"  class="form-select" required readonly>
      <option selected>Pilih Kelas...</option>
      <option>KELAS 1</option>
      <option>KELAS 2</option>
      <option>KELAS 3</option>
      <option>KELAS 4 A</option>
      <option>KELAS 4 B</option>
      <option>KELAS 5 A</option>
      <option>KELAS 5 B</option>
      <option>KELAS 6 A</option>
      <option>KELAS 6 B</option>
    </select>
    </div>
  </div>
  <div class="row mb-1">
    <label for="inputtext3" class="col-sm-4 col-form-label">Alamat</label>
    <div class="col-sm-8">
      <textarea type="text" rows="2" class="form-control" id="data5" name="data5" readonly></textarea>
    </div>
  </div>
  <div class="row mb-1">
    <label for="inputtext3" class="col-sm-4 col-form-label">Upload Foto</label>
    <div class="col-sm-8">
      <input type="file" class="form-control" id="myFile" name="myFile" onchange="return fileValidation()">
    </div>
  </div>

      <div class="modal-footer" id="admin_login" style="display:none">
        <button type="submit" class="btn btn-primary">Simpan
          <div class="spinner-border spinner-border-sm" id="btn_spin2" role="status" style="display:none">
  <span class="visually-hidden">Loading...</span>
</div>
        </button>
        <button type="button" class="btn btn-danger" id="btn_del" onclick="delForm()">Delete</button>
        
      </div>
</form>
      </div>
    </div>
  </div>
</div>

<script>
    function saveForm(obj) { 
        event.preventDefault();
        $('#btn_spin2').show()
        google.script.run.withSuccessHandler(()=> {
         document.querySelector('#myForm').reset()
        document.getElementById('btn_spin2').style.display="none"
          Swal.fire(
            'Save Data Success',
            "Data Tersimpan",
            'success'
          )  
          $('#exampleModal').modal('hide')
            google.script.run.withSuccessHandler(createTable2).getData()
           }).saveData(obj);
      }
</script>