Source Code Aplikasi Administrasi Guru Lengkap dengan Guru Wali Menggunakan Appscript

Salam Pendidikan!

Pada postingan kali ini, saya membagikan Source Code lengkap Aplikasi Administrasi Guru & Bimbingan Wali Kelas (Versi Final Update) berbasis Google Apps Script.

Aplikasi ini dirancang khusus untuk mempermudah administrasi guru dengan fitur unggulan:

✅ Database Otomatis: Tidak perlu setting ID Spreadsheet manual.
✅ Filter Cetak PDF: Cetak Laporan Nilai & Absensi bisa per kelas.
✅ Smart Import: Support import data siswa dari Excel/CSV.
✅ UI Premium: Tampilan modern dengan efek timbul (Embossed).

Langkah Instalasi

Buka Google Spreadsheet Baru.
Klik menu Ekstensi > Apps Script.
Hapus semua kode bawaan, lalu salin kode di bawah ini.

1. Kode Backend (Code.gs)

Code.gs

/**
 * SISTEM ADMINISTRASI GURU (FINAL UPDATE FILTER CETAK)
 * Lokasi: SMP Negeri 3 Kerinci
 * Guru: Yefri Haryanto
 */

const GURU = "Yefri Haryanto";

function doGet() {
  setupDatabase(); 
  return HtmlService.createTemplateFromFile('Index').evaluate()
      .setTitle('Sistem Administrasi Guru')
      .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
      .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

function getGuruName() { return GURU; }

// --- DATABASE AUTO SETUP ---
function setupDatabase() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = [
    {name: "DataSiswa", head: ["No", "Nama Siswa", "Kelas", "NIS"]},
    {name: "Absensi",   head: ["Waktu", "Kelas", "Nama Siswa", "Status", "Keterangan"]},
    {name: "Nilai",     head: ["Waktu", "Kelas", "Jenis Ujian", "Nama Siswa", "Nilai", "Keterangan"]},
    {name: "Agenda",    head: ["Tanggal", "Jam", "Kelas", "Materi", "Status", "Catatan"]},
    {name: "Bimbingan", head: ["Tanggal", "Nama Siswa", "Kelas", "Masalah", "Solusi", "Status"]},
    {name: "SiswaBinaan", head: ["No", "Nama Siswa", "Kelas", "Nama Ortu", "No HP"]}
  ];

  sheets.forEach(s => {
    let sheet = ss.getSheetByName(s.name);
    if (!sheet) {
      sheet = ss.insertSheet(s.name);
      sheet.appendRow(s.head);
      sheet.getRange(1, 1, 1, s.head.length).setFontWeight("bold").setBackground("#0d47a1").setFontColor("#ffffff");
    }
  });
}

function getSheet(name) { return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name); }

// --- MODULE SISWA ---
function getSiswa() {
  const sh = getSheet("DataSiswa");
  if (sh.getLastRow() < 2) return [];
  return sh.getRange(2, 1, sh.getLastRow()-1, 4).getDisplayValues();
}
function simpanSiswa(nama, kelas, nis) {
  const sh = getSheet("DataSiswa");
  sh.appendRow([sh.getLastRow(), nama, kelas, nis]);
  return "Siswa Tersimpan";
}
function importSiswa(data) {
  const sh = getSheet("DataSiswa");
  const lastNo = sh.getLastRow();
  const rows = data.map((d, i) => [lastNo + i, d.nama, d.kelas, d.nis]);
  if (rows.length > 0) sh.getRange(sh.getLastRow()+1, 1, rows.length, 4).setValues(rows);
  return "Import Berhasil (" + rows.length + " Siswa)";
}
function hapusData(sheetName, rowIndex) {
  getSheet(sheetName).deleteRow(rowIndex + 2);
  return "Data Terhapus";
}

// --- MODULE ABSENSI ---
function simpanAbsen(kelas, listAbsen) {
  const sh = getSheet("Absensi");
  const time = Utilities.formatDate(new Date(), "GMT+7", "dd/MM/yyyy HH:mm");
  const rows = listAbsen.map(d => [time, kelas, d.nama, d.status, ""]);
  if(rows.length > 0) sh.getRange(sh.getLastRow()+1, 1, rows.length, 5).setValues(rows);
  return "Absensi Tersimpan";
}
function getRiwayatAbsen() {
  const sh = getSheet("Absensi");
  if (sh.getLastRow() < 2) return [];
  return sh.getRange(2, 1, sh.getLastRow()-1, 5).getDisplayValues().reverse();
}

// --- MODULE NILAI ---
function simpanNilai(kelas, jenis, ket, listNilai) {
  const sh = getSheet("Nilai");
  const time = Utilities.formatDate(new Date(), "GMT+7", "dd/MM/yyyy");
  const rows = listNilai.map(d => [time, kelas, jenis, d.nama, d.nilai, ket]);
  if(rows.length > 0) sh.getRange(sh.getLastRow()+1, 1, rows.length, 6).setValues(rows);
  return "Nilai Tersimpan";
}
function getRiwayatNilai() {
  const sh = getSheet("Nilai");
  if (sh.getLastRow() < 2) return [];
  return sh.getRange(2, 1, sh.getLastRow()-1, 6).getDisplayValues().reverse();
}
function importNilai(data) {
  const sh = getSheet("Nilai");
  const time = Utilities.formatDate(new Date(), "GMT+7", "dd/MM/yyyy");
  const rows = data.map(d => [time, d.kelas, d.jenis, d.nama, d.nilai, d.ket]);
  if (rows.length > 0) sh.getRange(sh.getLastRow()+1, 1, rows.length, 6).setValues(rows);
  return "Import Nilai Sukses (" + rows.length + " Data)";
}

// --- MODULE AGENDA ---
function simpanAgenda(data) { getSheet("Agenda").appendRow(data); return "Jurnal Tersimpan"; }
function getAgenda() {
  const sh = getSheet("Agenda");
  if (sh.getLastRow() < 2) return [];
  return sh.getRange(2, 1, sh.getLastRow()-1, 6).getDisplayValues().reverse();
}

// --- MODULE WALI ---
function getSiswaBinaan() {
  const sh = getSheet("SiswaBinaan");
  if (sh.getLastRow() < 2) return [];
  return sh.getRange(2, 1, sh.getLastRow()-1, 5).getDisplayValues();
}
function simpanSiswaBinaan(nama, kelas, ortu, hp) {
  getSheet("SiswaBinaan").appendRow([getSheet("SiswaBinaan").getLastRow(), nama, kelas, ortu, hp]);
  return "Siswa Binaan Tersimpan";
}
function simpanBimbingan(data) { getSheet("Bimbingan").appendRow(data); return "Bimbingan Tersimpan"; }
function getBimbingan() {
  const sh = getSheet("Bimbingan");
  if (sh.getLastRow() < 2) return [];
  return sh.getRange(2, 1, sh.getLastRow()-1, 6).getDisplayValues().reverse();
}

2. Kode Frontend (Index.html)

Buat file baru di Apps Script, beri nama Index, lalu salin kode ini:

Index.html

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
  <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf-autotable/3.5.28/jspdf.plugin.autotable.min.js"></script>

  <style>
    @import url('https://fonts.googleapis.com/css2?family=Cinzel:wght@700&family=Lato:wght@400;700&display=swap');
    
    body { background-color: #f0f2f5; font-family: 'Lato', sans-serif; }
    
    .header-premium {
      background: linear-gradient(135deg, #0d47a1 0%, #1565c0 100%);
      padding: 30px 20px;
      text-align: center;
      border-bottom: 6px solid #ffd700;
      box-shadow: 0 10px 20px rgba(0,0,0,0.3);
    }
    
    .title-emboss {
      font-family: 'Cinzel', serif;
      font-size: 1.8rem;
      font-weight: 900;
      color: #ffffff;
      text-transform: uppercase;
      letter-spacing: 2px;
      text-shadow: 0px 4px 3px rgba(0,0,0,0.4), 0px 8px 13px rgba(0,0,0,0.1), 0px 18px 23px rgba(0,0,0,0.1);
      margin-bottom: 5px;
    }
    
    .subtitle-emboss {
      font-size: 1rem;
      color: #ffd700;
      font-weight: bold;
      text-shadow: 1px 1px 2px rgba(0,0,0,0.8);
      letter-spacing: 1px;
    }

    .nav-pills { background: #fff; padding: 10px; border-radius: 50px; box-shadow: 0 5px 15px rgba(0,0,0,0.05); margin-top: -25px; justify-content: center; position: relative; z-index: 10; max-width: 900px; margin-left: auto; margin-right: auto; }
    .nav-pills .nav-link { color: #333; font-weight: bold; border-radius: 30px; padding: 10px 20px; transition: 0.3s; }
    .nav-pills .nav-link.active { background: #0d47a1; color: #ffd700; box-shadow: 0 4px 10px rgba(13, 71, 161, 0.4); transform: scale(1.05); }

    .card-box { background: #fff; border-radius: 15px; border: none; box-shadow: 0 5px 15px rgba(0,0,0,0.05); margin-bottom: 25px; overflow: hidden; }
    .card-head { background: linear-gradient(to right, #0d47a1, #1976d2); padding: 15px 20px; color: white; font-weight: bold; display: flex; justify-content: space-between; align-items: center; }
    
    .alert-instruction { border-left: 5px solid #0d47a1; background-color: #e3f2fd; color: #0d47a1; font-size: 0.9rem; margin-bottom: 20px; }
    .hidden { display: none !important; }
    .table-responsive { max-height: 400px; overflow-y: auto; }
    thead { position: sticky; top: 0; background: #fff; z-index: 5; }
    
    .btn-simpan-absen { width: 80%; border-radius: 25px; padding: 10px; font-weight: 900; letter-spacing: 1px; box-shadow: 0 4px 6px rgba(0,0,0,0.2); }
  </style>
</head>
<body>

<div class="header-premium">
  <div class="title-emboss">SISTEM ADMINISTRASI GURU</div>
  <div class="subtitle-emboss">SMP NEGERI 3 KERINCI</div>
  <div class="text-white mt-2 small" style="opacity:0.8">GURU: <span id="guru-name">Yefri Haryanto</span></div>
</div>

<div class="container" style="max-width: 1000px;">

  <ul class="nav nav-pills mb-4">
    <li class="nav-item"><a class="nav-link active" onclick="nav('siswa', this)"><i class="fas fa-users"></i> Data Siswa</a></li>
    <li class="nav-item"><a class="nav-link" onclick="nav('absen', this)"><i class="fas fa-check-circle"></i> Absensi</a></li>
    <li class="nav-item"><a class="nav-link" onclick="nav('nilai', this)"><i class="fas fa-star"></i> Nilai</a></li>
    <li class="nav-item"><a class="nav-link" onclick="nav('agenda', this)"><i class="fas fa-book"></i> Agenda</a></li>
    <li class="nav-item"><a class="nav-link" onclick="nav('wali', this)"><i class="fas fa-user-tie"></i> Guru Wali</a></li>
  </ul>

  <!-- 1. SISWA -->
  <div id="v-siswa" class="view-sec">
    <div class="alert alert-instruction p-3 rounded shadow-sm">
      <i class="fas fa-info-circle me-2"></i> <strong>Petunjuk:</strong> Input manual atau Import CSV. Format CSV: Nama,Kelas,NIS.
    </div>
    <div class="card-box">
      <div class="card-head">Input Siswa Baru</div>
      <div class="p-3">
        <div class="row g-2">
           <div class="col-md-5"><input id="s-nama" class="form-control" placeholder="Nama Lengkap"></div>
           <div class="col-md-3"><input id="s-kelas" class="form-control" placeholder="Kelas (Cth: 7A)"></div>
           <div class="col-md-4"><div class="input-group"><input id="s-nis" class="form-control" placeholder="NIS"><button class="btn btn-primary" onclick="addSiswa()">Simpan</button></div></div>
        </div>
      </div>
    </div>
    <div class="card-box">
       <div class="card-head">
          Database Siswa
          <div>
             <button onclick="dlTemplate('siswa')" class="btn btn-sm btn-light text-primary fw-bold me-1">Template</button>
             <button onclick="document.getElementById('f-siswa').click()" class="btn btn-sm btn-warning fw-bold text-dark">Import</button>
             <input type="file" id="f-siswa" class="hidden" onchange="procImpSiswa(this)">
          </div>
       </div>
       <div class="p-0 table-responsive">
          <table class="table table-striped table-hover mb-0" id="t-siswa">
             <thead class="table-dark"><tr><th>No</th><th>Nama</th><th>Kelas</th><th>NIS</th><th>Hapus</th></tr></thead>
             <tbody><tr><td colspan="5" class="text-center">Memuat...</td></tr></tbody>
          </table>
       </div>
    </div>
  </div>

  <!-- 2. ABSENSI -->
  <div id="v-absen" class="view-sec hidden">
     <div class="alert alert-instruction p-3 rounded shadow-sm">
        <i class="fas fa-info-circle me-2"></i> <strong>Petunjuk:</strong> Pilih Kelas, ceklis kehadiran, lalu klik tombol SIMPAN di bawah.
     </div>
     <div class="card-box">
        <div class="card-head">Input Kehadiran</div>
        <div class="p-3">
           <div class="row mb-3">
              <div class="col-md-4"><select id="a-kelas" class="form-select" onchange="loadInputAbsen()"><option value="">Pilih Kelas...</option></select></div>
           </div>
           
           <div class="border rounded p-2 bg-light mb-4" style="max-height:350px; overflow-y:auto">
              <table class="table table-sm mb-0 align-middle">
                <thead class="table-light sticky-top">
                  <tr><th>Nama Siswa</th><th>H</th><th>S</th><th>I</th><th>A</th></tr>
                </thead>
                <tbody id="list-absen">
                  <tr><td colspan="5" class="text-center text-muted py-3">Pilih Kelas Dahulu</td></tr>
                </tbody>
              </table>
           </div>

           <div class="text-center">
              <button onclick="kirimAbsen()" class="btn btn-primary btn-simpan-absen">SIMPAN ABSENSI</button>
           </div>
        </div>
     </div>
     <div class="card-box">
        <div class="card-head">
           <div class="d-flex align-items-center gap-2">
             <span>Riwayat Absensi</span>
             <select id="filter-absen-kls" class="form-select form-select-sm text-dark fw-bold" style="width:120px;" onchange="renderAbsenTable()">
                <option value="">Semua</option>
             </select>
           </div>
           <button class="btn btn-sm btn-warning text-dark fw-bold" onclick="cetakPDF('t-hist-absen', 'Laporan Absensi', false)">Cetak PDF</button>
        </div>
        <div class="table-responsive"><table class="table table-bordered table-sm mb-0" id="t-hist-absen"><thead><tr><th>Waktu</th><th>Kls</th><th>Nama</th><th>Sts</th><th>Act</th></tr></thead><tbody></tbody></table></div>
     </div>
  </div>

  <!-- 3. NILAI -->
  <div id="v-nilai" class="view-sec hidden">
     <div class="alert alert-instruction p-3 rounded shadow-sm">
        <i class="fas fa-info-circle me-2"></i> <strong>Petunjuk:</strong> Input Manual pilih kelas dari dropdown. Untuk Import, gunakan format: Kelas, Jenis, Nama, Nilai, Ket.
     </div>
     <div class="card-box">
        <div class="card-head">
           Input Nilai
           <div>
              <button class="btn btn-sm btn-outline-light active" onclick="modeNilai('man')">Manual</button>
              <button class="btn btn-sm btn-outline-light" onclick="modeNilai('imp')">Import</button>
           </div>
        </div>
        <div class="p-3">
           <div id="n-man">
              <div class="row g-2 mb-2">
                 <div class="col-3">
                    <select id="ni-kelas" class="form-select" onchange="loadFormNilai()">
                        <option value="">Pilih Kelas...</option>
                    </select>
                 </div>
                 <div class="col-3"><select id="ni-jns" class="form-select"><option>UH</option><option>UTS</option><option>UAS</option><option>Tugas</option></select></div>
                 <div class="col-6"><input id="ni-ket" class="form-control" placeholder="Keterangan"></div>
              </div>
              <div class="border p-2 mb-2 bg-light" style="max-height:250px; overflow-y:auto">
                 <table class="table table-sm mb-0"><tbody id="form-nilai"><tr><td class="text-center text-muted">Pilih Kelas di atas</td></tr></tbody></table>
              </div>
              <button onclick="saveNilaiMan()" class="btn btn-primary w-100 fw-bold">SIMPAN NILAI</button>
           </div>
           <div id="n-imp" class="hidden text-center p-4">
              <i class="fas fa-file-csv fa-3x text-success mb-2"></i>
              <p>Format CSV: <b>Kelas, Jenis, Nama, Nilai, Ket</b></p>
              <button onclick="dlTemplate('nilai')" class="btn btn-sm btn-outline-dark">Template CSV</button>
              <button onclick="document.getElementById('f-nilai').click()" class="btn btn-success ms-2">Upload File</button>
              <input type="file" id="f-nilai" class="hidden" onchange="procImpNilai(this)">
           </div>
        </div>
     </div>
     
     <div class="card-box">
        <div class="card-head">
           <div class="d-flex align-items-center gap-2">
             <span>Riwayat Nilai</span>
             <select id="filter-nilai-kls" class="form-select form-select-sm text-dark fw-bold" style="width:120px;" onchange="renderNilaiTable()">
                <option value="">Semua</option>
             </select>
           </div>
           <button class="btn btn-sm btn-warning text-dark fw-bold" onclick="cetakPDF('t-nilai', 'Laporan Nilai', false)">Cetak PDF</button>
        </div>
        <div class="table-responsive"><table class="table table-bordered table-sm mb-0" id="t-nilai"><thead><tr><th>Tgl</th><th>Kls</th><th>Jns</th><th>Nama</th><th>Nilai</th><th>Ket</th><th>Act</th></tr></thead><tbody></tbody></table></div>
     </div>
  </div>

  <!-- 4. AGENDA -->
  <div id="v-agenda" class="view-sec hidden">
     <div class="card-box">
        <div class="card-head">Jurnal Mengajar</div>
        <div class="p-3">
           <div class="row g-2">
              <div class="col-md-4"><input type="date" id="ag-tgl" class="form-control"></div>
              <div class="col-md-2"><input id="ag-jam" class="form-control" placeholder="Jam"></div>
              <div class="col-md-2"><input id="ag-kls" class="form-control" placeholder="Kelas"></div>
              <div class="col-md-4"><select id="ag-sts" class="form-select"><option>Tuntas</option><option>Belum</option></select></div>
              <div class="col-12"><input id="ag-mat" class="form-control" placeholder="Materi"></div>
              <div class="col-12"><textarea id="ag-cat" class="form-control" placeholder="Catatan" rows="2"></textarea></div>
              <div class="col-12"><button onclick="saveAgenda()" class="btn btn-primary w-100 fw-bold">SIMPAN JURNAL</button></div>
           </div>
        </div>
     </div>
     <div class="card-box">
        <div class="card-head">Riwayat <button class="btn btn-sm btn-warning text-dark fw-bold" onclick="cetakPDF('t-agenda', 'Jurnal Mengajar', false)">Cetak PDF</button></div>
        <div class="table-responsive"><table class="table table-bordered table-sm" id="t-agenda"><thead><tr><th>Tgl</th><th>Kls</th><th>Materi</th><th>Sts</th><th>Act</th></tr></thead><tbody></tbody></table></div>
     </div>
  </div>

  <!-- 5. WALI -->
  <div id="v-wali" class="view-sec hidden">
     <div class="alert alert-instruction p-3 rounded shadow-sm" style="background-color: #fff3e0; color: #e65100; border-left-color: #e65100;">
        <i class="fas fa-exclamation-circle me-2"></i> <strong>Petunjuk Wali:</strong> Input Siswa Binaan -> Input Bimbingan.
     </div>
     <div class="text-center mb-3">
        <div class="btn-group">
           <button class="btn btn-outline-primary active" id="btn-sub-binaan" onclick="subWali('binaan')">1. Data Binaan</button>
           <button class="btn btn-outline-primary" id="btn-sub-kasus" onclick="subWali('kasus')">2. Input Bimbingan</button>
        </div>
     </div>
     <div id="sub-binaan">
        <div class="card-box">
           <div class="card-head">Siswa Binaan (Manual)</div>
           <div class="p-3">
              <div class="row g-2 mb-3">
                 <div class="col-md-4"><input id="wb-nama" class="form-control" placeholder="Nama"></div>
                 <div class="col-md-2"><input id="wb-kelas" class="form-control" placeholder="Kelas"></div>
                 <div class="col-md-3"><input id="wb-ortu" class="form-control" placeholder="Ortu"></div>
                 <div class="col-md-3"><button onclick="addBinaan()" class="btn btn-primary w-100">Tambah</button></div>
              </div>
              <div class="table-responsive"><table class="table table-striped table-sm" id="t-binaan"><thead class="table-dark"><tr><th>No</th><th>Nama</th><th>Kelas</th><th>Ortu</th><th>Act</th></tr></thead><tbody></tbody></table></div>
           </div>
        </div>
     </div>
     <div id="sub-kasus" class="hidden">
        <div class="card-box">
           <div class="card-head">Form Bimbingan</div>
           <div class="p-3">
              <div class="row g-2">
                 <div class="col-md-6"><label>Siswa</label><select id="wb-pilih" class="form-select"></select></div>
                 <div class="col-md-6"><label>Masalah</label><input id="wb-masalah" class="form-control"></div>
                 <div class="col-12"><label>Solusi</label><textarea id="wb-solusi" class="form-control" rows="2"></textarea></div>
                 <div class="col-12"><button onclick="saveBimbingan()" class="btn btn-primary w-100 fw-bold">SIMPAN</button></div>
              </div>
           </div>
        </div>
        <div class="card-box">
           <div class="card-head">Riwayat <button class="btn btn-sm btn-warning text-dark fw-bold" onclick="cetakPDF('t-bimbingan', 'Laporan Bimbingan Wali', true)">Cetak PDF Wali</button></div>
           <div class="table-responsive"><table class="table table-bordered table-sm" id="t-bimbingan"><thead><tr><th>Tgl</th><th>Nama</th><th>Masalah</th><th>Solusi</th><th>Act</th></tr></thead><tbody></tbody></table></div>
        </div>
     </div>
  </div>

</div>

<script>
let DB_SISWA = [];
let DB_NILAI_FULL = []; 
let DB_ABSEN_FULL = []; 
const { jsPDF } = window.jspdf;

window.onload = function() {
   Swal.fire({title: 'Memuat Sistem...', didOpen:()=>{Swal.showLoading()}});
   google.script.run.withSuccessHandler(n=>{
      document.getElementById('guru-name').innerText = n;
      loadAllData();
   }).getGuruName();
};

function nav(v, el) {
   document.querySelectorAll('.view-sec').forEach(x=>x.classList.add('hidden'));
   document.getElementById('v-'+v).classList.remove('hidden');
   document.querySelectorAll('.nav-link').forEach(x=>x.classList.remove('active'));
   el.classList.add('active');
}

function loadAllData(){ loadSiswa(); loadAbsenHist(); loadNilaiHist(); loadAgenda(); loadWali(); }
function notif(msg, type='success'){ Swal.fire({ icon: type, title: msg, timer: 1500, showConfirmButton: false }); }

function loadSiswa(){
   google.script.run.withSuccessHandler(d=>{
      DB_SISWA=d; 
      let h='', kls=new Set(), op='<option value="">Pilih Kelas...</option>';
      d.forEach((r,i)=>{
         h+=`<tr><td>${r[0]}</td><td>${r[1]}</td><td>${r[2]}</td><td>${r[3]}</td><td><button class="btn btn-sm btn-danger py-0" onclick="delData('DataSiswa',${i},loadSiswa)">x</button></td></tr>`;
         kls.add(r[2]);
      });
      document.querySelector('#t-siswa tbody').innerHTML = h || '<tr><td colspan="5" class="text-center">Kosong</td></tr>';
      
      let sortedKls = Array.from(kls).sort();
      sortedKls.forEach(k=>op+=`<option value="${k}">${k}</option>`);
      document.getElementById('a-kelas').innerHTML = op;
      document.getElementById('ni-kelas').innerHTML = op;
      
      let filt = '<option value="">Semua Kelas</option>';
      sortedKls.forEach(k=>filt+=`<option value="${k}">${k}</option>`);
      document.getElementById('filter-nilai-kls').innerHTML = filt;
      document.getElementById('filter-absen-kls').innerHTML = filt;

      Swal.close();
   }).getSiswa();
}

function parseCSV(file, callback) {
   const reader = new FileReader();
   reader.onload = function(e) {
      const text = e.target.result;
      const pattern = /(".*?"|[^",\s]+)(?=\s*,|\s*;)|\s*,|\s*;/g; 
      const lines = text.split(/\r\n|\n/);
      const result = [];
      lines.forEach((line, index) => {
         if (index > 0 && line.trim()) {
             let cols = line.includes(';') ? line.split(';') : line.split(',');
             cols = cols.map(c => c.trim().replace(/^"|"$/g, ''));
             result.push(cols);
         }
      });
      callback(result);
   };
   reader.readAsText(file);
}

function procImpSiswa(e){
   let file = e.files[0];
   if(!file) return;
   Swal.fire({title: 'Importing...', didOpen:()=>{Swal.showLoading()}});
   parseCSV(file, d=>{
      let clean = d.map(r=>({nama:r[0], kelas:r[1], nis:r[2]}));
      google.script.run.withSuccessHandler(r=>{
         notif(r); 
         loadSiswa();
      }).importSiswa(clean);
      e.value = "";
   });
}
function addSiswa(){
   let n=document.getElementById('s-nama').value, k=document.getElementById('s-kelas').value, i=document.getElementById('s-nis').value;
   if(n) google.script.run.withSuccessHandler(r=>{notif(r); document.getElementById('s-nama').value=''; loadSiswa();}).simpanSiswa(n,k,i);
}

function loadInputAbsen(){
   let k = document.getElementById('a-kelas').value;
   let s = DB_SISWA.filter(r=>r[2]==k);
   let h='';
   s.forEach((r,i)=>{
      h+=`<tr><td class="ab-n fw-bold">${r[1]}</td>
      <td><input class="form-check-input" type="radio" name="ab${i}" value="H" checked></td>
      <td><input class="form-check-input" type="radio" name="ab${i}" value="S"></td>
      <td><input class="form-check-input" type="radio" name="ab${i}" value="I"></td>
      <td><input class="form-check-input" type="radio" name="ab${i}" value="A"></td></tr>`;
   });
   document.getElementById('list-absen').innerHTML = h || '<tr><td class="text-center text-danger py-3">Tidak ada siswa</td></tr>';
}
function kirimAbsen(){
   let k=document.getElementById('a-kelas').value, d=[];
   if(!k) return notif('Pilih Kelas!','warning');
   document.querySelectorAll('#list-absen tr').forEach(r=>{
      d.push({nama:r.querySelector('.ab-n').innerText, status:r.querySelector('input:checked').value});
   });
   google.script.run.withSuccessHandler(r=>{notif(r); loadAbsenHist();}).simpanAbsen(k,d);
}
function loadAbsenHist(){
   google.script.run.withSuccessHandler(d=>{
      DB_ABSEN_FULL = d;
      renderAbsenTable();
   }).getRiwayatAbsen();
}
function renderAbsenTable(){
    let filter = document.getElementById('filter-absen-kls').value;
    let data = DB_ABSEN_FULL;
    if(filter) data = data.filter(r => r[1] === filter);
    let h=''; 
    data.forEach((r,i)=>h+=`<tr><td>${r[0]}</td><td>${r[1]}</td><td>${r[2]}</td><td>${r[3]}</td><td><button class="btn btn-sm btn-danger py-0" onclick="delData('Absensi',${i},loadAbsenHist)">x</button></td></tr>`);
    document.querySelector('#t-hist-absen tbody').innerHTML = h || '<tr><td colspan="5" class="text-center">Tidak ada data</td></tr>';
}

function modeNilai(m){
   document.getElementById('n-man').classList.toggle('hidden', m!='man');
   document.getElementById('n-imp').classList.toggle('hidden', m!='imp');
}
function loadFormNilai(){
   let k = document.getElementById('ni-kelas').value;
   if(!k) return document.getElementById('form-nilai').innerHTML = '<tr><td class="text-center">Pilih Kelas</td></tr>';
   let s = DB_SISWA.filter(r=>r[2]==k);
   let h=''; s.forEach(r=>h+=`<tr><td>${r[1]}</td><td><input type="number" class="form-control form-control-sm val-n" data-n="${r[1]}"></td></tr>`);
   document.getElementById('form-nilai').innerHTML = h;
}
function saveNilaiMan(){
   let k=document.getElementById('ni-kelas').value, j=document.getElementById('ni-jns').value, t=document.getElementById('ni-ket').value, d=[];
   document.querySelectorAll('.val-n').forEach(e=>{if(e.value) d.push({nama:e.dataset.n, nilai:e.value})});
   if(!d.length) return notif('Kosong','warning');
   google.script.run.withSuccessHandler(r=>{notif(r); loadNilaiHist();}).simpanNilai(k,j,t,d);
}
function procImpNilai(e){ 
   let file = e.files[0];
   if(!file) return;
   Swal.fire({title: 'Importing...', didOpen:()=>{Swal.showLoading()}});
   parseCSV(file, d=>{ 
      let clean = d.map(r=>({kelas:r[0],jenis:r[1],nama:r[2],nilai:r[3],ket:r[4]}));
      google.script.run.withSuccessHandler(r=>{ notif(r, 'success'); loadNilaiHist(); }).importNilai(clean);
      e.value = "";
   }); 
}
function loadNilaiHist(){
   google.script.run.withSuccessHandler(d=>{
      DB_NILAI_FULL = d; 
      renderNilaiTable();
   }).getRiwayatNilai();
}
function renderNilaiTable(){
    let filter = document.getElementById('filter-nilai-kls').value;
    let data = DB_NILAI_FULL;
    if(filter) data = data.filter(r => r[1] === filter);
    let h=''; 
    data.forEach((r,i)=>h+=`<tr><td>${r[0]}</td><td>${r[1]}</td><td>${r[2]}</td><td>${r[3]}</td><td>${r[4]}</td><td>${r[5]}</td><td><button class="btn btn-sm btn-danger py-0" onclick="delData('Nilai',${i},loadNilaiHist)">x</button></td></tr>`);
    document.querySelector('#t-nilai tbody').innerHTML = h || '<tr><td colspan="7" class="text-center">Tidak ada data</td></tr>';
}

function saveAgenda(){
   let d=[document.getElementById('ag-tgl').value, document.getElementById('ag-jam').value, document.getElementById('ag-kls').value, document.getElementById('ag-mat').value, document.getElementById('ag-sts').value, document.getElementById('ag-cat').value];
   google.script.run.withSuccessHandler(r=>{notif(r); loadAgenda();}).simpanAgenda(d);
}
function loadAgenda(){
   google.script.run.withSuccessHandler(d=>{
      let h=''; d.forEach((r,i)=>h+=`<tr><td>${r[0]}</td><td>${r[2]}</td><td>${r[3]}</td><td>${r[4]}</td><td><button class="btn btn-sm btn-danger py-0" onclick="delData('Agenda',${i},loadAgenda)">x</button></td></tr>`);
      document.querySelector('#t-agenda tbody').innerHTML = h;
   }).getAgenda();
}
function subWali(m){
   document.getElementById('sub-binaan').classList.toggle('hidden', m!='binaan');
   document.getElementById('sub-kasus').classList.toggle('hidden', m!='kasus');
   document.getElementById('btn-sub-binaan').classList.toggle('active', m=='binaan');
   document.getElementById('btn-sub-kasus').classList.toggle('active', m=='kasus');
}
function loadWali(){
   google.script.run.withSuccessHandler(d=>{
      let h='', op='<option value="">Pilih...</option>';
      d.forEach((r,i)=>{
         h+=`<tr><td>${i+1}</td><td>${r[1]}</td><td>${r[2]}</td><td>${r[3]}</td><td><button class="btn btn-sm btn-danger py-0" onclick="delData('SiswaBinaan',${i},loadWali)">x</button></td></tr>`;
         op+=`<option value="${r[1]}">${r[1]}</option>`;
      });
      document.querySelector('#t-binaan tbody').innerHTML=h; document.getElementById('wb-pilih').innerHTML=op;
   }).getSiswaBinaan();
   google.script.run.withSuccessHandler(d=>{
      let h=''; d.forEach((r,i)=>h+=`<tr><td>${r[0]}</td><td>${r[1]}</td><td>${r[3]}</td><td>${r[4]}</td><td><button class="btn btn-sm btn-danger py-0" onclick="delData('Bimbingan',${i},loadWali)">x</button></td></tr>`);
      document.querySelector('#t-bimbingan tbody').innerHTML=h;
   }).getBimbingan();
}
function addBinaan(){
   let n=document.getElementById('wb-nama').value, k=document.getElementById('wb-kelas').value, o=document.getElementById('wb-ortu').value;
   if(n) google.script.run.withSuccessHandler(r=>{notif(r); loadWali();}).simpanSiswaBinaan(n,k,o,'-');
}
function saveBimbingan(){
   let s=document.getElementById('wb-pilih').value;
   if(s) google.script.run.withSuccessHandler(r=>{notif(r); loadWali();}).simpanBimbingan([new Date().toLocaleDateString('id-ID'), s, '-', document.getElementById('wb-masalah').value, document.getElementById('wb-solusi').value, 'Proses']);
}
function delData(s,i,cb){ if(confirm('Hapus?')) google.script.run.withSuccessHandler(()=>{notif('Terhapus'); cb();}).hapusData(s,i); }
function dlTemplate(t){ let c=t=='siswa'?"Nama,Kelas,NIS\nBudi,7A,123":"Kelas,Jenis,Nama,Nilai,Ket\n7A,UH1,Budi,80,Aljabar"; let b=new Blob([c],{type:'text/csv'}); let a=document.createElement('a'); a.href=URL.createObjectURL(b); a.download="Template_"+t+".csv"; a.click(); }

function cetakPDF(tblId, judul, isWali=false){
   const doc = new jsPDF();
   let g = document.getElementById('guru-name').innerText;
   let label = isWali ? "Guru Wali: " + g : "Guru: " + g;

   doc.setFontSize(14); doc.text(judul.toUpperCase(), 14, 15);
   doc.setFontSize(10); doc.text("SMP NEGERI 3 KERINCI", 14, 22); doc.text(label, 14, 28);
   
   let tbl = document.getElementById(tblId);
   let data = [], heads = [];
   
   let ths = tbl.querySelectorAll('thead th');
   let rowH = [];
   for(let i=0; i<ths.length-1; i++) rowH.push(ths[i].innerText);
   heads.push(rowH);
   
   let trs = tbl.querySelectorAll('tbody tr');
   trs.forEach(tr => {
       if(tr.style.display !== 'none' && tr.innerText !== 'Tidak ada data') {
           let tds = tr.querySelectorAll('td');
           let rowD = [];
           if(tds.length > 1) {
               for(let i=0; i<tds.length-1; i++) rowD.push(tds[i].innerText);
               data.push(rowD);
           }
       }
   });

   doc.autoTable({ head: heads, body: data, startY: 35, theme:'grid' });
   doc.save(judul+".pdf");
}
</script>
</body>
</html>

Panduan Mengubah Nama Guru & Sekolah

1. Ganti Nama Guru (Backend):
Buka file Code.gs, pada baris pertama, ubah teks di dalam tanda kutip:
const GURU = "Yefri Haryanto"; → Ganti dengan nama Anda.

2. Ganti Nama Sekolah (Frontend):
Buka file Index.html, cari bagian Header (sekitar baris 85):
<div class="subtitle-emboss">SMP NEGERI 3 KERINCI</div> → Ubah sesuai sekolah Anda.

3. Ganti Kop Surat (PDF):
Masih di Index.html, scroll ke paling bawah (fungsi cetakPDF):
doc.text("SMP NEGERI 3 KERINCI", 14, 22); → Ubah teks ini.

⚠ PENTING: Setiap kali melakukan perubahan, jangan lupa klik Deploy > New Deployment agar perubahan tersimpan.