Cara Membuat Aplikasi E-Rapor Menggunakan Google App Script dan Database ke Spreadsheet

Di era digitalisasi pendidikan saat ini, pengelolaan nilai siswa tidak lagi harus bergantung pada aplikasi berbayar yang mahal atau sistem manual yang rumit. Dengan memanfaatkan Google Apps Script (GAS) dan Google Sheets, Anda bisa membuat aplikasi E-Rapor sendiri yang canggih, responsif, dan tentunya 100% Gratis.

Pada artikel kali ini, saya akan membagikan panduan lengkap dan Source Code E-Rapor Versi 34 (Final Stabil) yang sudah mendukung tampilan mobile friendly, cetak PDF otomatis, dan pengelolaan database yang aman.

1. Persiapan Awal

Sebelum menyalin kode, pastikan Anda telah menyiapkan hal berikut:

Buat Google Spreadsheet Baru di Google Drive Anda.
Beri nama spreadsheet tersebut (misal: Database Rapor Kelas 7).
Buka menu Ekstensi (Extensions) > Apps Script.
Hapus semua kode bawaan yang ada di file Code.gs.

2. Rahasia Pembuatan (Prompt AI)

Bagi Anda yang ingin mengembangkan sendiri menggunakan AI, berikut adalah contoh Prompt Detail yang saya gunakan untuk menghasilkan struktur kode ini:

CONTOH PROMPT UNTUK AI:

Bertindaklah sebagai Senior Google Apps Script Developer dan UI/UX Designer. Saya ingin Anda membuat kode lengkap untuk Aplikasi Web "E-Rapor SMP Versi 34" yang Modern, Responsif (Mobile-Friendly), dan siap pakai.

Buatkan aplikasi web E-Rapor menggunakan Google Apps Script dan HTML.

Aplikasi ini harus terdiri dari 2 file utama:
1. Code.gs (Backend)
2. index.html (Frontend: HTML + CSS + JS)

Fitur Wajib:
1. Backend (Code.gs) terhubung ke Google Sheets untuk CRUD (Create, Read, Update, Delete) data Siswa, Nilai, dan Mapel.
2. Frontend (HTML) menggunakan Bootstrap, memiliki Sidebar Menu yang responsif (bisa di-hide di HP).
3. Fitur Cetak Rapor yang memunculkan preview (Print Preview) sebelum dicetak ke kertas A4.
4. Tampilan Dashboard profesional dengan widget statistik berwarna.
5. Mobile Friendly: Sidebar otomatis menutup saat menu diklik di HP.

Berikut adalah Spesifikasi Detail Menu dan Fungsinya:

A. SPESIFIKASI TEKNIS & UI
1. Tech Stack: Google Apps Script (Server-side) dan HTML5/CSS3/Vanilla JS (Client-side).
2. Desain: Gunakan gaya "Premium Dashboard". Sidebar di kiri (warna gelap elegan), Konten di kanan (background abu-abu muda).
3. Responsif (Wajib):
- Pada Desktop: Sidebar terlihat penuh.
- Pada Mobile (HP): Sidebar tersembunyi (hidden) secara default. Ada tombol "Hamburger Menu" di pojok kiri atas untuk membuka sidebar. Saat menu diklik, sidebar otomatis menutup kembali. Ada overlay hitam transparan saat sidebar terbuka.
4. Notifikasi: Gunakan SweetAlert2 (CDN) untuk pesan sukses/gagal yang cantik.
5. Single Page Application (SPA): Perpindahan menu tidak boleh reload halaman, gunakan manipulasi DOM (Hide/Show Div).

B. STRUKTUR DATABASE (SPREADSHEET)
Backend harus otomatis mengecek dan membuat sheet jika belum ada dengan header berikut:
- DB_Siswa: Waktu, NISN (Key), NIS, Nama, TTL, JK, Agama, Alamat, Info Ayah (Nama, Pekerjaan), Info Ibu (Nama, Pekerjaan), Wali, Kelas, Fase, Semester.
- DB_Sekolah, DB_Mapel, DB_TP, DB_Nilai, DB_Ekstra, DB_Kokuri, DB_Lengkap (Absensi).

C. RINCIAN MENU & FITUR (FRONTEND)

1. MENU DASHBOARD
- Hero Section: Ucapan "Selamat Datang Wali Kelas".
- Statistik Cards (Gradient Color): Menampilkan jumlah Siswa, Mapel, Rombel, dan Status Online.

2. MENU DATA SEKOLAH
- Form input: Nama Sekolah, NPSN, Alamat Lengkap, Website, Email.
- Tombol Simpan untuk update database.

3. MENU DATA SISWA
- Form Identitas: NISN (Wajib/Key), NIS, Nama, TTL, JK, Agama.
- Form Orang Tua (PENTING): Kolom Pekerjaan Ayah dan Pekerjaan Ibu harus TERPISAH (input berbeda), jangan digabung.
- Form Akademik: Kelas (Dropdown), Fase, Semester, Tahun Ajaran.

4. MENU MAPEL & TP (Tujuan Pembelajaran)
- Panel Kiri: Form tambah Nama Mapel.
- Panel Kanan: Form input TP (Pilih Mapel dari dropdown, Kode TP, Deskripsi TP).

5. MENU INPUT NILAI (Sistem Cerdas)
- Dropdown 1: Pilih Mapel.
- Dropdown 2: Pilih Siswa (Format: NISN - Nama).
- Logika: Saat Mapel dipilih, script otomatis memuat daftar TP (Checkbox).
- Fitur Generator Deskripsi: Guru mencentang TP yang dikuasai siswa -> Sistem otomatis menyusun kalimat deskripsi capaian (Contoh: "Kompeten dalam [TP1], perlu bimbingan dalam [TP2]").
- Input Nilai Akhir (Angka 0-100).

6. MENU EKSTRA & ABSEN
- Dropdown Pilih Siswa (Global untuk halaman ini).
- Form Ekstrakurikuler: Input Nama Kegiatan & Keterangan.
- Form Kokurikuler: Input Kegiatan & Keterangan.
- Form Absensi: Input Sakit (S), Izin (I), Alpa (A), dan Catatan Wali Kelas.

7. MENU CETAK (FITUR UTAMA)
- Form Setting Tanda Tangan: Input Tanggal Rapor, Nama Wali Kelas, NIP Wali, Nama Kepsek, NIP Kepsek.
- Opsi 1: LIHAT RAPOR (Preview & Print)
- Saat diklik, muncul layar penuh (Overlay Viewer).
- Menampilkan Rapor Format 4 Halaman (Page Break per halaman):
* Hal 1: Cover (Logo & Judul).
* Hal 2: Data Sekolah.
* Hal 3: Identitas Siswa (Pekerjaan Ortu terpisah).
* Hal 4: Laporan Nilai, Tabel Ekstra, Absen, Catatan Wali.
- Ada tombol "Print" di pojok kanan atas viewer.
- Opsi 2: LIHAT LEGER
- Menampilkan tabel rekap nilai seluruh siswa dalam satu kelas (landscape).

Berikan kode lengkap dalam 2 file: Code.gs dan index.html. Pastikan kode JavaScript menangani navigasi mobile dengan sempurna."

3. Kode Backend (Code.gs)

Salin seluruh kode di bawah ini dan tempelkan ke file Code.gs di editor Apps Script Anda. Kode ini menangani koneksi database dan logika penyimpanan.

File: Code.gs

/* ==============================================
   E-RAPOR SMPN 3 KERINCI - VERSI 34.0 (STABIL)
   ============================================== */

function doGet() {
  return HtmlService.createTemplateFromFile('index')
    .evaluate()
    .setTitle('E-Rapor SMPN 3 Kerinci')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

// --- DATABASE SETUP ---
function cekDanBuatDatabase() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ["DB_Siswa", "DB_Sekolah", "DB_Mapel", "DB_TP", "DB_Nilai", "DB_Ekstra", "DB_Kokuri", "DB_Lengkap"];
  sheets.forEach(name => {
    var sheet = ss.getSheetByName(name);
    if (!sheet) {
      sheet = ss.insertSheet(name);
      if(name === "DB_Siswa") sheet.getRange("B:B").setNumberFormat("@");
    }
  });
}

function getDashboardStats() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sSiswa = ss.getSheetByName("DB_Siswa");
  var sMapel = ss.getSheetByName("DB_Mapel");
  return { 
    siswa: sSiswa ? Math.max(0, sSiswa.getLastRow() - 1) : 0, 
    mapel: sMapel ? Math.max(0, sMapel.getLastRow() - 1) : 0, 
    rombel: 1 
  };
}

function clean(val) {
  if (val == null || val === "") return "";
  return String(val).replace(/['"]/g, "").trim();
}

// --- CRUD ---
function simpanSiswa(d) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_Siswa");
  var rows = sheet.getDataRange().getValues();
  var target = clean(d.nisn);
  for(var i=rows.length-1; i>=1; i--) { if(clean(rows[i][1]) === target) sheet.deleteRow(i+1); }
  sheet.appendRow([new Date(), "'"+target, "'"+d.nis, d.nama.toUpperCase(), d.tmpLahir, d.tglLahir, d.jk, d.agama, d.pendidikan, d.alamatSiswa, d.ayah, d.pekAyah, d.ibu, d.pekIbu, d.alamatOrtu, d.wali, d.pekWali, d.alamatWali, d.kelas, d.fase, d.semester, d.tahun]);
  return "Data Siswa Tersimpan!";
}
function simpanSekolah(d) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_Sekolah");
  sheet.clearContents(); sheet.appendRow(["Nama", "NPSN", "Alamat", "Kode", "Desa", "Kec", "Kab", "Prov", "Web", "Email"]);
  sheet.appendRow([d.nama, d.npsn, d.alamat, d.kodepos, d.desa, d.kec, d.kab, d.prov, d.web, d.email]); return "Sekolah Update!";
}
function simpanMapel(d) { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_Mapel").appendRow(["MP-"+Math.floor(Math.random()*999), d.namaMapel]); return "Mapel OK"; }
function simpanTP(d) { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_TP").appendRow([d.namaMapel, d.kodeTp, d.deskripsiTp]); return "TP OK"; }
function simpanNilai(d) { 
  var s=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_Nilai"); var r=s.getDataRange().getValues(); var t=clean(d.nisn);
  for(var i=r.length-1; i>=1; i--) if(clean(r[i][1])===t && r[i][2]==d.mapel) s.deleteRow(i+1);
  s.appendRow([new Date(), "'"+t, d.mapel, d.nilai, d.deskripsi]); return "Nilai OK";
}
function simpanEkstra(d) { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_Ekstra").appendRow([new Date(), "'"+clean(d.nisn), d.kegiatan, d.predikat, d.ket]); return "Ekstra OK"; }
function simpanKokuri(d) { SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_Kokuri").appendRow([new Date(), "'"+clean(d.nisn), d.kegiatan, d.predikat, d.ket]); return "Kokuri OK"; }
function simpanDataLengkap(d) {
  var s=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_Lengkap"); var r=s.getDataRange().getValues(); var t=clean(d.nisn);
  for(var i=r.length-1; i>=1; i--) if(clean(r[i][1])===t) s.deleteRow(i+1);
  s.appendRow([new Date(), "'"+t, d.sakit, d.izin, d.alpa, d.catatan]); return "Absen OK";
}
function simpanCatatanKhusus(nisn, catatan) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_Lengkap"); var rows = sheet.getDataRange().getValues(); var t=clean(nisn); var found=false;
  for(var i=1; i ({nisn: clean(r[1]), nama: r[3]}));
}
function getListMapel() { var r=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_Mapel").getDataRange().getValues(); return r.length>1 ? r.slice(1).map(x=>x[1]) : []; }
function getTPByMapel(m) { var r=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DB_TP").getDataRange().getValues(); return r.filter(x=>x[0]==m).map(x=>({kode:x[1], deskripsi:x[2]})); }

// --- LEGER ---
function getLegerData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sSiswa = ss.getSheetByName("DB_Siswa");
  var sNilai = ss.getSheetByName("DB_Nilai");
  var sMapel = ss.getSheetByName("DB_Mapel");

  if (!sSiswa || !sNilai || !sMapel) return { mapel: [], data: [] };

  var dSiswa = sSiswa.getDataRange().getValues().slice(1);
  var dNilai = sNilai.getDataRange().getValues().slice(1);
  var mapels = sMapel.getDataRange().getValues().slice(1).map(r => r[1]); 
  
  var leger = {};

  dSiswa.forEach(s => { 
    var n = clean(s[1]); 
    if(n){ 
      leger[n] = { nama: s[3], nilai: {}, total: 0, avg: 0 }; 
      mapels.forEach(m => leger[n].nilai[m] = 0);
    } 
  });

  dNilai.forEach(n => { 
    var nisn = clean(n[1]); 
    var map = n[2]; 
    var val = parseFloat(n[3])||0; 
    if(leger[nisn]) leger[nisn].nilai[map] = val; 
  });

  var list = [];
  for (var key in leger) {
    var tot = 0; 
    mapels.forEach(m => { var v = leger[key].nilai[m] || 0; tot += v; });
    var avg = mapels.length > 0 ? (tot / mapels.length).toFixed(1) : 0;
    list.push({ nisn: key, nama: leger[key].nama, nilai: leger[key].nilai, total: tot, avg: avg });
  }

  list.sort((a, b) => b.total - a.total);
  list = list.map((item, index) => { item.rank = index + 1; return item; });
  
  return { mapel: mapels, data: list };
}

// --- SEARCH RAPOR ---
function cariDataRapor(nisnInput) {
  try {
    var target = clean(nisnInput);
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var res = { success: false, message: "NISN Tidak Ditemukan" };

    var sSek = ss.getSheetByName("DB_Sekolah");
    res.sekolah = {nama:"SMP NEGERI 3 KERINCI", npsn:"-", alamat:"-"};
    if(sSek && sSek.getLastRow()>1) { var d=sSek.getRange(2,1,1,10).getValues()[0]; res.sekolah={nama:d[0], npsn:d[1], alamat:d[2], desa:d[4], kec:d[5], kab:d[6], prov:d[7], email:d[9]}; }

    var sSiswa = ss.getSheetByName("DB_Siswa"); var dSiswa = sSiswa.getDataRange().getValues();
    for(var i=1; i<dSiswa.length; i++) {
      if(clean(dSiswa[i][1]) === target) {
        var r = dSiswa[i];
        res.siswa = { nisn:r[1], nis:r[2], nama:r[3], ttl:r[4]+", "+r[5], jk:r[6], agama:r[7], alamat:r[9], ayah:r[10], ibu:r[12], pekayah:r[11], pekibu:r[13], alortu:r[14], kelas:r[18], fase:r[19], sem:r[20], thn:r[21] };
        res.success = true; break;
      }
    }
    if(!res.success) return { success: false, message: "Siswa tidak ditemukan." };

    res.nilai=[]; var sNil=ss.getSheetByName("DB_Nilai"); if(sNil){ var d=sNil.getDataRange().getValues(); for(var i=1;i<d.length;i++) if(clean(d[i][1])===target) res.nilai.push({mapel:d[i][2], angka:d[i][3], desk:d[i][4]}); }
    res.ekstra=[]; var sEks=ss.getSheetByName("DB_Ekstra"); if(sEks){ var d=sEks.getDataRange().getValues(); for(var i=1;i<d.length;i++) if(clean(d[i][1])===target) res.ekstra.push({keg:d[i][2], ket:d[i][4]}); }
    res.kokuri=[]; var sKok=ss.getSheetByName("DB_Kokuri"); if(sKok){ var d=sKok.getDataRange().getValues(); for(var i=1;i<d.length;i++) if(clean(d[i][1])===target) res.kokuri.push({keg:d[i][2], ket:d[i][4]}); }
    res.lengkap={s:'-', i:'-', a:'-', cat:'-'}; var sAbs=ss.getSheetByName("DB_Lengkap"); if(sAbs){ var d=sAbs.getDataRange().getValues(); for(var i=1;i<d.length;i++) if(clean(d[i][1])===target) res.lengkap={s:d[i][2], i:d[i][3], a:d[i][4], cat:d[i][5]}; }

    return res;
  } catch(e) { return {success:false, message:e.toString()}; }
}

4. Kode Frontend (index.html)

Buat file baru di Apps Script dengan nama index (tanpa .html), lalu tempelkan kode berikut. Kode ini sudah dimodifikasi agar Sidebar bisa diakses melalui tombol di HP dan menutup otomatis saat menu diklik.

File: index.html

<!DOCTYPE html>
<html lang="id">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>E-Rapor SMPN 3 Kerinci</title>
  <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">
  <style>
    :root { 
      --sidebar-w: 260px; 
      --bg-dark: #1e293b; 
      --primary: #3b82f6;
    }
    body { background: #f1f5f9; font-family: 'Segoe UI', sans-serif; overflow-x: hidden; }
    
    /* SIDEBAR */
    .sidebar { width: var(--sidebar-w); height: 100vh; position: fixed; top:0; left:0; background: var(--bg-dark); color: white; display: flex; flex-direction: column; z-index: 1002; box-shadow: 4px 0 10px rgba(0,0,0,0.1); transition: transform 0.3s ease; }
    .sidebar-brand { padding: 25px; text-align: center; font-weight: 800; font-size: 1.2rem; background: rgba(0,0,0,0.2); border-bottom: 1px solid rgba(255,255,255,0.1); }
    .nav-container { flex-grow: 1; padding: 15px; overflow-y: auto; }
    
    /* NAV ITEM */
    .nav-item { padding: 12px 15px; margin-bottom: 5px; cursor: pointer; color: #94a3b8; display: flex; align-items: center; border-radius: 8px; transition: all 0.3s ease; text-decoration: none; font-weight: 500; }
    .nav-item:hover, .nav-item.active { background: var(--primary); color: white; transform: translateX(5px); box-shadow: 0 4px 6px rgba(59, 130, 246, 0.3); }
    .nav-item i { width: 30px; text-align: center; margin-right: 10px; }
    
    /* CONTENT */
    .content { margin-left: var(--sidebar-w); padding: 30px; transition: margin-left 0.3s ease; }
    .card { border: none; border-radius: 15px; box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1); background: white; margin-bottom: 25px; overflow: hidden; }
    .card-header { background: white; border-bottom: 1px solid #f1f5f9; font-weight: 700; padding: 20px 25px; font-size: 1.1rem; color: #334155; }
    .card-body { padding: 25px; }
    .hidden { display: none !important; }

    /* STATS & WIDGETS */
    .stat-card { color: white; padding: 30px; border-radius: 20px; position: relative; overflow: hidden; box-shadow: 0 10px 15px -3px rgba(0, 0, 0, 0.1); transition: transform 0.3s; }
    .stat-card:hover { transform: translateY(-5px); }
    .stat-card h2 { font-size: 3rem; font-weight: 800; margin: 0; line-height: 1; }
    .stat-card i { font-size: 5rem; opacity: 0.15; position: absolute; right: -10px; bottom: -10px; transform: rotate(-15deg); }
    .bg-g-1 { background: linear-gradient(135deg, #6366f1 0%, #a855f7 100%); } 
    .bg-g-2 { background: linear-gradient(135deg, #3b82f6 0%, #2dd4bf 100%); } 
    .bg-g-3 { background: linear-gradient(135deg, #f59e0b 0%, #ef4444 100%); } 
    .bg-g-4 { background: linear-gradient(135deg, #10b981 0%, #059669 100%); }
    .hero-welcome { background: linear-gradient(to right, #ffffff, #f8fafc); border-left: 5px solid var(--primary); padding: 30px; border-radius: 12px; margin-bottom: 30px; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.05); }
    
    /* FORM */
    input, select, textarea { width: 100%; padding: 12px; margin-bottom: 15px; border: 1px solid #cbd5e1; border-radius: 8px; }
    .btn-action { width: 100%; padding: 12px; border-radius: 8px; font-weight: bold; border: none; cursor: pointer; text-transform: uppercase; color:white; }
    .btn-save { background: var(--primary); }
    .btn-add { background: #10b981; }

    /* MOBILE SPECIFIC CSS */
    .mobile-toggle { display: none; }
    .sidebar-overlay { display: none; position: fixed; top: 0; left: 0; width: 100%; height: 100%; background: rgba(0,0,0,0.5); z-index: 1001; backdrop-filter: blur(2px); }

    @media (max-width: 768px) {
      .sidebar { transform: translateX(-100%); width: 260px; }
      .sidebar.active { transform: translateX(0); }
      .sidebar-overlay.active { display: block; }
      .content { margin-left: 0; padding: 15px; padding-top: 70px; } 
      .mobile-toggle { display: block; position: fixed; top: 15px; left: 15px; z-index: 1003; background: var(--primary); color: white; border: none; padding: 10px 15px; border-radius: 8px; box-shadow: 0 4px 10px rgba(0,0,0,0.2); cursor: pointer; font-size: 1.2rem; }
      .stat-card h2 { font-size: 2rem; }
      .hero-welcome { padding: 20px; }
      .card-body { padding: 15px; }
      .viewer-content { width: 95% !important; margin: 60px auto !important; padding: 10px !important; overflow-x: auto; }
    }

    /* VIEWER & PRINT */
    #viewerArea { position: fixed; top:0; left:0; width:100%; height:100%; background:rgba(0,0,0,0.85); z-index:9999; display:none; }
    .viewer-content { width: 210mm; min-height: 297mm; background: white; margin: 30px auto; padding: 20mm; overflow-x: auto; }
    .viewer-controls { position: fixed; top: 0; left: 0; width: 100%; background: #1e293b; padding: 15px; text-align: center; box-shadow: 0 4px 10px rgba(0,0,0,0.3); z-index: 10000; }
    
    @media print {
      .sidebar, .content, .viewer-controls, .mobile-toggle { display: none !important; }
      #viewerArea { position: static; display: block !important; background: white; }
      .viewer-content { margin: 0; padding: 0; width: 100%; }
      @page { margin: 10mm; }
      .page-break { page-break-after: always; }
      table { width: 100%; border-collapse: collapse; }
      th, td { border: 1px solid black; padding: 5px; font-size: 10pt; }
      .no-border td { border: none; }
      .header-grey { background: #ddd !important; -webkit-print-color-adjust: exact; font-weight: bold; text-align: center; }
      .box-border { border: 2px solid black; padding: 10px; font-weight: bold; font-size: 14pt; text-align: center; }
    }
  </style>
</head>
<body>

  <button class="mobile-toggle" onclick="toggleSidebar()"><i class="fas fa-bars"></i></button>
  <div class="sidebar-overlay" onclick="toggleSidebar()"></div>

  <div class="sidebar">
    <div class="sidebar-brand"><i class="fas fa-book-reader me-2"></i>E-RAPOR V.34</div>
    <div class="nav-container">
      <div onclick="nav('dashboard', this)" class="nav-item active"><i class="fas fa-chart-pie"></i> Dashboard</div>
      <div onclick="nav('sekolah', this)" class="nav-item"><i class="fas fa-school"></i> Data Sekolah</div>
      <div onclick="nav('siswa', this)" class="nav-item"><i class="fas fa-user-graduate"></i> Data Siswa</div>
      <div onclick="nav('mapel', this); loadMapel()" class="nav-item"><i class="fas fa-book"></i> Mapel & TP</div>
      <div onclick="nav('nilai', this); loadMapel(); loadDD('inputNisn')" class="nav-item"><i class="fas fa-edit"></i> Input Nilai</div>
      <div onclick="nav('ekstra', this); loadDD('extraNisn')" class="nav-item"><i class="fas fa-star"></i> Ekstra & Absen</div>
      <div onclick="nav('cetak', this); loadDD('cetakNisn')" class="nav-item mt-3" style="background: rgba(255,255,255,0.1); border: 1px solid rgba(255,255,255,0.1);"><i class="fas fa-print"></i> Cetak Rapor</div>
    </div>
    <div style="padding: 20px; border-top: 1px solid #334155; text-align: center;">
      <a href="https://docs.google.com/spreadsheets/d/17M29h-qRgv9lZWtaqM_8f-iqUcaf8BO8_0JdpJtV4cw/edit?usp=sharing" target="_blank" style="color: #4ade80; text-decoration: none; font-size: 0.85rem; font-weight: bold;">
        <i class="fas fa-external-link-alt me-1"></i> Data Spreadsheet
      </a>
    </div>
  </div>

  <div class="content">
    <!-- DASHBOARD DAN KONTEN LAINNYA SAMA SEPERTI SEBELUMNYA -->
    <div id="p-dashboard">
      <div class="hero-welcome">
        <h2 class="fw-bold text-dark mb-2">Selamat Datang, Wali Kelas!</h2>
        <p class="text-secondary mb-0">Silakan kelola data nilai siswa Kelas VII SMP Negeri 3 Kerinci.</p>
      </div>
      <div class="row g-4 mb-4">
        <div class="col-md-3"><div class="stat-card bg-g-1"><div><h2 id="st-siswa">0</h2><p>Total Siswa</p></div><i class="fas fa-users"></i></div></div>
        <div class="col-md-3"><div class="stat-card bg-g-2"><div><h2 id="st-mapel">0</h2><p>Mata Pelajaran</p></div><i class="fas fa-book-open"></i></div></div>
        <div class="col-md-3"><div class="stat-card bg-g-3"><div><h2 id="st-rombel">1</h2><p>Rombel (VII)</p></div><i class="fas fa-chalkboard-teacher"></i></div></div>
        <div class="col-md-3"><div class="stat-card bg-g-4"><div><h2>ONLINE</h2><p>System Status</p></div><i class="fas fa-wifi"></i></div></div>
      </div>
    </div>

    <!-- FORM SECTIONS (SEKOLAH, SISWA, MAPEL, NILAI, EKSTRA, CETAK) -->
    <div id="p-sekolah" class="hidden"><div class="card"><div class="card-header">Profil Sekolah</div><div class="card-body"><form onsubmit="save(event,'simpanSekolah')"><div class="row"><div class="col-md-6"><label>Nama Sekolah</label><input name="nama" class="form-control" value="SMP NEGERI 3 KERINCI"></div><div class="col-md-6"><label>NPSN</label><input name="npsn" class="form-control"></div><div class="col-12"><label>Alamat</label><input name="alamat" class="form-control"></div><div class="col-md-3"><input name="desa" placeholder="Desa"></div><div class="col-md-3"><input name="kec" placeholder="Kecamatan"></div><div class="col-md-3"><input name="kab" placeholder="Kabupaten"></div><div class="col-md-3"><input name="prov" placeholder="Provinsi"></div><div class="col-md-6"><input name="email" placeholder="Email"></div><div class="col-md-6"><input name="web" placeholder="Website"></div></div><button class="btn-action btn-save">SIMPAN DATA SEKOLAH</button></form></div></div></div>

    <div id="p-siswa" class="hidden"><div class="card"><div class="card-header">Data Siswa</div><div class="card-body"><form onsubmit="save(event,'simpanSiswa')"><h6 class="text-primary fw-bold">Identitas Pribadi</h6><div class="row"><div class="col-md-4"><input name="nisn" placeholder="NISN (Wajib)" required></div><div class="col-md-4"><input name="nis" placeholder="NIS"></div><div class="col-md-4"><input name="nama" placeholder="Nama Lengkap" required></div><div class="col-md-6"><input name="tmpLahir" placeholder="Tempat Lahir"></div><div class="col-md-6"><input name="tglLahir" placeholder="Tgl Lahir"></div><div class="col-md-6"><select name="jk"><option>Laki-Laki</option><option>Perempuan</option></select></div><div class="col-md-6"><input name="agama" value="Islam"></div><div class="col-12"><input name="alamatSiswa" placeholder="Alamat Siswa"></div></div><h6 class="text-primary fw-bold mt-4">Data Orang Tua (Terpisah)</h6><div class="row"><div class="col-md-6"><input name="ayah" placeholder="Nama Ayah"></div><div class="col-md-6"><input name="pekAyah" placeholder="Pekerjaan Ayah"></div><div class="col-md-6"><input name="ibu" placeholder="Nama Ibu"></div><div class="col-md-6"><input name="pekIbu" placeholder="Pekerjaan Ibu"></div><div class="col-12"><input name="alamatOrtu" placeholder="Alamat Orang Tua"></div></div><h6 class="text-primary fw-bold mt-4">Akademik</h6><div class="row"><div class="col-md-3"><select name="kelas"><option>VII</option></select></div><div class="col-md-3"><input name="fase" value="D"></div><div class="col-md-3"><select name="semester"><option>I (Ganjil)</option><option>II (Genap)</option></select></div><div class="col-md-3"><input name="tahun" value="2025/2026"></div></div><button class="btn-action btn-save mt-3">SIMPAN DATA SISWA</button></form></div></div></div>

    <div id="p-mapel" class="hidden"><div class="row"><div class="col-md-6"><div class="card"><div class="card-header">Tambah Mapel</div><div class="card-body"><form onsubmit="save(event,'simpanMapel')"><input name="namaMapel" placeholder="Nama Mapel"><button class="btn-action btn-add">TAMBAH</button></form></div></div></div><div class="col-md-6"><div class="card"><div class="card-header">Input TP</div><div class="card-body"><form onsubmit="save(event,'simpanTP')"><select name="namaMapel" class="dd-mapel"></select><input name="kodeTp" placeholder="Kode TP"><input name="deskripsiTp" placeholder="Deskripsi"><button class="btn-action btn-save">SIMPAN TP</button></form></div></div></div></div></div>

    <div id="p-nilai" class="hidden"><div class="card"><div class="card-header">Input Nilai</div><div class="card-body"><form id="f-nilai"><div class="row"><div class="col-md-6"><select name="mapel" class="dd-mapel" onchange="loadTPList()"></select></div><div class="col-md-6"><select name="nisn" id="inputNisn"></select></div></div><input type="number" name="nilai" placeholder="Nilai Akhir (0-100)"><textarea name="deskripsi" id="descResult" rows="3" placeholder="Deskripsi Capaian..."></textarea><div id="tpBox" class="p-3 mb-3 bg-light border rounded"></div><button type="button" class="btn-action btn-save" onclick="saveNilai()">SIMPAN NILAI</button></form></div></div></div>

    <div id="p-ekstra" class="hidden"><div class="card"><div class="card-body"><label>Pilih Siswa:</label><select id="extraNisn" onchange="document.querySelectorAll('.nisn-hide').forEach(x=>x.value=this.value)"></select><hr><div class="row"><div class="col-md-4"><h6>Ekstra</h6><form onsubmit="save(event,'simpanEkstra')"><input type="hidden" name="nisn" class="nisn-hide"><input name="kegiatan" placeholder="Kegiatan"><input name="ket" placeholder="Ket"><button class="btn-action btn-add">Save</button></form></div><div class="col-md-4"><h6>Kokurikuler</h6><form onsubmit="save(event,'simpanKokuri')"><input type="hidden" name="nisn" class="nisn-hide"><input name="kegiatan" placeholder="Kegiatan"><input name="ket" placeholder="Ket"><button class="btn-action btn-add">Save</button></form></div><div class="col-md-4"><h6>Absensi</h6><form onsubmit="save(event,'simpanDataLengkap')"><input type="hidden" name="nisn" class="nisn-hide"><div class="d-flex gap-2"><input name="sakit" placeholder="S"><input name="izin" placeholder="I"><input name="alpa" placeholder="A"></div><input name="catatan" placeholder="Catatan Wali"><button class="btn-action btn-add">Save</button></form></div></div></div></div></div>

    <div id="p-cetak" class="hidden">
      <div class="card">
        <div class="card-header bg-success text-white">Menu Cetak</div>
        <div class="card-body">
          <div class="row g-2 mb-3">
             <div class="col-md-3"><input id="t-tgl" placeholder="Tgl Rapor"></div>
             <div class="col-md-3"><input id="t-wali" placeholder="Wali Kelas"></div>
             <div class="col-md-2"><input id="t-nipw" placeholder="NIP Wali"></div>
             <div class="col-md-3"><input id="t-kep" placeholder="Kepsek"></div>
             <div class="col-md-1"><input id="t-nipk" placeholder="NIP Kepsek"></div>
          </div>
          <hr>
          <div class="row">
            <div class="col-md-6">
              <label>Cetak Rapor Siswa:</label>
              <div class="d-flex gap-2">
                <select id="cetakNisn"></select>
                <button class="btn btn-success" onclick="renderRapor()"><i class="fas fa-print"></i> LIHAT RAPOR</button>
              </div>
            </div>
            <div class="col-md-6 text-end">
              <label class="d-block"> </label>
              <button class="btn btn-primary" onclick="renderLeger()"><i class="fas fa-list"></i> LIHAT LEGER</button>
            </div>
          </div>
        </div>
      </div>
    </div>

  </div>

  <!-- VIEWER AREA -->
  <div id="viewerArea">
    <div class="viewer-controls">
      <button onclick="window.print()" style="background:#22c55e; color:white; border:none; padding:10px 20px; font-weight:bold; cursor:pointer; border-radius:30px; margin-right:10px;">🖨️ CETAK</button>
      <button onclick="document.getElementById('viewerArea').style.display='none'" style="background:#ef4444; color:white; border:none; padding:10px 20px; font-weight:bold; cursor:pointer; border-radius:30px;">❌ TUTUP</button>
    </div>
    <div id="printCanvas" class="viewer-content"></div>
  </div>

<script>
  function toggleSidebar() {
    document.querySelector('.sidebar').classList.toggle('active');
    document.querySelector('.sidebar-overlay').classList.toggle('active');
  }

  function nav(id, el) {
    document.querySelectorAll('.content > div').forEach(d => d.classList.add('hidden'));
    document.getElementById('p-'+id).classList.remove('hidden');
    document.querySelectorAll('.nav-item').forEach(m => m.classList.remove('active'));
    
    if(el) el.classList.add('active'); 
    else if(id==='dashboard') document.querySelector('.nav-item').classList.add('active');
    
    if(window.innerWidth <= 768) {
      document.querySelector('.sidebar').classList.remove('active');
      document.querySelector('.sidebar-overlay').classList.remove('active');
    }

    if(id==='dashboard') google.script.run.withSuccessHandler(s=>{document.getElementById('st-siswa').innerText=s.siswa;document.getElementById('st-mapel').innerText=s.mapel;}).getDashboardStats();
  }

  function save(e, func) { e.preventDefault(); google.script.run.withSuccessHandler(r=>alert(r))[func](e.target); }
  function loadMapel() { google.script.run.withSuccessHandler(l=>{document.querySelectorAll('.dd-mapel').forEach(s=>{s.innerHTML='<option>Pilih...</option>';l.forEach(m=>s.innerHTML+=`<option>${m}</option>`)})}).getListMapel(); }
  function loadDD(id) { google.script.run.withSuccessHandler(l=>{const s=document.getElementById(id);s.innerHTML='<option>Pilih Siswa...</option>';l.forEach(i=>s.innerHTML+=`<option value="${i.nisn}">${i.nisn} - ${i.nama}</option>`) }).getListSiswa(); }
  function loadTPList(){ const m=document.querySelector('#f-nilai select[name="mapel"]').value; google.script.run.withSuccessHandler(l=>{let h='';l.forEach(t=>h+=`<div class="form-check"><input class="form-check-input" type="checkbox" value="${t.deskripsi}" onchange="updateDesc()"> <label class="form-check-label">${t.kode}</label></div>`);document.getElementById('tpBox').innerHTML=h}).getTPByMapel(m); }
  function updateDesc(){ document.getElementById('descResult').value = "Kompeten dalam " + Array.from(document.querySelectorAll('#tpBox input:checked')).map(c=>c.value).join(', '); }
  function saveNilai(){ const f=document.getElementById('f-nilai'); google.script.run.withSuccessHandler(r=>alert(r)).simpanNilai({mapel:f.mapel.value, nisn:f.nisn.value, nilai:f.nilai.value, deskripsi:f.deskripsi.value}); }

  function renderRapor() {
    const nisn = document.getElementById('cetakNisn').value;
    if(!nisn || nisn.includes("Pilih")) return alert("Pilih siswa dulu!");
    document.getElementById('printCanvas').innerHTML = "<h3 style='text-align:center'>Memuat data...</h3>";
    document.getElementById('viewerArea').style.display = 'block';
    const tgl=document.getElementById('t-tgl').value; const wali=document.getElementById('t-wali').value; const nipw=document.getElementById('t-nipw').value; const kep=document.getElementById('t-kep').value; const nipk=document.getElementById('t-nipk').value;

    google.script.run.withSuccessHandler(res => {
      if(!res.success) { document.getElementById('printCanvas').innerHTML="Error: "+res.message; return; }
      const s=res.siswa; const sc=res.sekolah; const n=res.nilai; const e=res.ekstra; const k=res.kokuri; const a=res.lengkap;
      let h = `<center style="margin-top:50px;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguA-QoEv-kpfpK_kCLNFJtiW2-73AR_eTw-wHTYX-QciY_4g2AuqxDoeQIWA3fXav2gNfQ3BQD7YkhQb_ieHHulqnsRIm8zYw2WIOJdHK6-DKGZpDfshZmuRKDPbP1WB9DTxXgsr1scY9o2fOuDTXP6j5S8Ccs3fTCjnI_HmnY0QicaCYOH_DP1R0lEHk/s200/download.jpeg" width="150"><br><br><h2>RAPOR<br>SEKOLAH MENENGAH PERTAMA (SMP)</h2><br><br><div>Nama Murid:</div><div class="box-border">${s.nama}</div><div>NISN:</div><div class="box-border">${s.nisn}</div><br><br><br><br><b>KEMENTERIAN PENDIDIKAN DASAR DAN MENENGAH</b></center><div class="page-break"></div><center><h3>DATA SEKOLAH</h3></center><table class="no-border"><tr><td width="30%">Nama Sekolah</td><td>: ${sc.nama}</td></tr><tr><td>NPSN</td><td>: ${sc.npsn}</td></tr><tr><td>Alamat</td><td>: ${sc.alamat}</td></tr></table><div class="page-break"></div><center><h3>IDENTITAS MURID</h3></center><table class="no-border"><tr><td width="30%">Nama Lengkap</td><td>: ${s.nama}</td></tr><tr><td>NISN</td><td>: ${s.nisn}</td></tr></table><div style="text-align:right; margin-top:50px; margin-right:50px;">Kepala Sekolah,<br><br><br><br><u>${kep}</u><br>NIP. ${nipk}</div><div class="page-break"></div><center><h3>LAPORAN HASIL BELAJAR</h3></center><table class="no-border" style="width:100%; margin-bottom:10px;"><tr><td>Nama: ${s.nama}<br>NISN: ${s.nisn}</td><td align="right">Kelas: ${s.kelas}<br>Semester: ${s.sem}</td></tr></table><table border="1" style="width:100%"><tr class="header-grey"><th>No</th><th>Mapel</th><th>Nilai</th><th>Capaian</th></tr>`;
      if(n.length==0) h+=`<tr><td colspan="4" align="center">-</td></tr>`;
      n.forEach((r,i) => { h+=`<tr><td align="center">${i+1}</td><td>${r.mapel}</td><td align="center"><b>${r.angka}</b></td><td>${r.desk}</td></tr>`; });
      h+=`</table><br><b>Kokurikuler</b><div style="border:1px solid black; padding:5px;">${k.length>0?k.map(x=>x.ket).join('<br>'):'-'}</div><br><b>Ekstrakurikuler</b><table border="1" style="width:100%"><tr class="header-grey"><th>Kegiatan</th><th>Keterangan</th></tr>`;
      e.forEach((r,i)=>h+=`<tr><td>${r.keg}</td><td>${r.ket}</td></tr>`);
      h+=`</table><br><table border="1" style="width:100%"><tr><td width="40%" valign="top"><b>Absensi</b><br>Sakit: ${a.s}<br>Izin: ${a.i}<br>Alpa: ${a.a}</td><td width="60%" valign="top"><b>Catatan Wali</b><br>${a.cat}</td></tr></table><br><div style="display:flex; justify-content:space-between; text-align:center;"><div>Orang Tua<br><br><br><br>.....</div><div>Kerinci, ${tgl}<br>Wali Kelas<br><br><br><br><b>${wali}</b><br>NIP. ${nipw}</div></div><div style="text-align:center;">Mengetahui,<br>Kepala Sekolah<br><br><br><br><b>${kep}</b><br>NIP. ${nipk}</div>`;
      document.getElementById('printCanvas').innerHTML = h;
    }).cariDataRapor(nisn);
  }

  function renderLeger() {
    const tgl=document.getElementById('t-tgl').value; const wali=document.getElementById('t-wali').value; const nipw=document.getElementById('t-nipw').value; const kep=document.getElementById('t-kep').value; const nipk=document.getElementById('t-nipk').value;
    document.getElementById('printCanvas').innerHTML = "<h3>Memuat Leger...</h3>";
    document.getElementById('viewerArea').style.display = 'block';
    google.script.run.withSuccessHandler(res => {
      if(res.data.length === 0) { document.getElementById('printCanvas').innerHTML="Data Kosong"; return; }
      let h = `<center><h3>LEGER NILAI KELAS VII</h3></center><style>@page {size: landscape; margin: 1cm;} table {font-size: 10px;}</style><table border="1" style="width:100%"><tr class="header-grey"><th>No</th><th>Nama</th>`;
      res.mapel.forEach(m => h+=`<th>${m}</th>`); h+=`<th>Total</th><th>Rank</th></tr>`;
      res.data.forEach((d,i) => {
        h+=`<tr><td align="center">${i+1}</td><td>${d.nama}</td>`;
        res.mapel.forEach(m => h+=`<td align="center">${d.nilai[m]||''}</td>`);
        h+=`<td align="center"><b>${d.total}</b></td><td align="center">${d.rank}</td></tr>`;
      });
      h+=`</table><br><div style="display:flex; justify-content:space-between; text-align:center;"><div>Mengetahui,<br>Kepala Sekolah<br><br><br><br><b>${kep}</b><br>NIP. ${nipk}</div><div>Kerinci, ${tgl}<br>Wali Kelas<br><br><br><br><b>${wali}</b><br>NIP. ${nipw}</div></div>`;
      document.getElementById('printCanvas').innerHTML = h;
    }).getLegerData();
  }
</script>