Source Code Sistem Administrasi Guru Terpadu Menggunakan Google Apps Script

Selamat datang rekan-rekan guru hebat. Di era digital ini, administrasi pembelajaran tidak harus rumit dan menghabiskan banyak kertas. Pada kesempatan ini, saya membagikan Source Code Aplikasi Sistem Administrasi Guru Terpadu (SIAG PRO v27) berbasis Google Apps Script secara gratis.

Apa Itu Administrasi Guru?

Administrasi guru adalah seperangkat dokumen dan catatan yang menjadi bukti fisik kinerja guru dalam merencanakan, melaksanakan, dan mengevaluasi proses pembelajaran. Ini meliputi:

Data Siswa: Database lengkap peserta didik.
Absensi Kehadiran: Rekap kehadiran harian.
Daftar Nilai: Input nilai UH, Tugas, UTS, dan UAS.
Jurnal Harian: Catatan aktivitas mengajar di kelas.
Catatan Wali: Bimbingan konseling siswa binaan.

Perbedaan Administrasi Konvensional vs Online

Aspek	Konvensional (Manual)	Online (AppScript)
Akses	Terbatas pada buku fisik	Bisa diakses HP/Laptop 24 Jam
Efisiensi	Rekap nilai & absen manual	Otomatis hitung rata-rata & persentase
Keamanan	Rawan hilang/rusak	Tersimpan aman di Cloud (Google Drive)
Biaya	Beli buku cetak berkala	100% Gratis selamanya

Langkah-Langkah Pembuatan Aplikasi

Ikuti panduan berikut untuk membuat aplikasi SIAG PRO v27 milik Anda sendiri:

Buka Google Drive, Buat Spreadsheet baru dengan nama "Database SIAG PRO".
Klik menu Ekstensi (Extensions) > Apps Script.
Hapus semua kode yang ada di file Code.gs.
Salin & Tempel kode Server (Code.gs) di bawah ini.
Buat file HTML baru (klik tanda +) beri nama Index (tanpa .html), lalu salin kode HTML di bawah.
Klik Terapkan (Deploy) > Deployment Baru.
Pilih jenis: Aplikasi Web, Akses: Siapa Saja (Anyone).
Klik Deploy, berikan izin akses (Review Permissions), dan aplikasi siap digunakan!

1. Kode Server (Code.gs)

Salin kode berikut ke dalam file Code.gs di editor Apps Script Anda:

Code.gs Klik kotak & Ctrl+A untuk menyalin

/* --- SIAG PRO v27 (CREATOR PROTECTED) --- */

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

function getUserInfo() {
  return Session.getActiveUser().getEmail();
}

/* --- 1. SETUP DATABASE --- */
function setupDatabase() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  const createIfMissing = (name, header) => {
    let ws = ss.getSheetByName(name);
    if (!ws) { 
      ws = ss.insertSheet(name); 
      ws.appendRow(header); 
    }
    return ws;
  };

  createIfMissing('Config', ['Key', 'Value']);
  
  const configSheet = ss.getSheetByName('Config');
  if (configSheet.getLastRow() < 2) {
    configSheet.getRange(2, 1, 11, 2).setValues([
      ['Pemerintah', 'PEMERINTAH PROVINSI JAMBI'],
      ['Dinas', 'DINAS PENDIDIKAN'],
      ['Nama Sekolah', 'SMP NEGERI PERCONTOHAN'], 
      ['Alamat', 'Jl. RE Martadinata'],
      ['Kepala Sekolah', 'Nama Kepsek'], ['NIP Kepsek', '-'],
      ['Nama Guru', 'Nama Guru'], ['NIP Guru', '-'], ['Email Guru', 'guru@belajar.id'],
      ['Mata Pelajaran', 'Bahasa Inggris'], ['Tahun Ajaran', '2025/2026'], ['Semester', 'Ganjil']
    ]);
  }

  createIfMissing('DataSiswa', ['NIS', 'Nama Siswa', 'L/P', 'Kelas']);
  createIfMissing('SiswaBinaan', ['Email Guru', 'NIS', 'Nama Siswa', 'Kelas']);
  createIfMissing('Jadwal', ['Hari', 'Jam', 'Kelas', 'Mapel']);
  
  let h_absen = ['NIS', 'Nama', 'Kelas']; for(let i=1; i<=30; i++) h_absen.push('P'+i);
  createIfMissing('Absensi', h_absen);

  let h_nilai = ['NIS', 'Nama', 'Kelas']; 
  for(let i=1; i<=10; i++) h_nilai.push('UH'+i); 
  for(let i=1; i<=10; i++) h_nilai.push('TGS'+i); 
  h_nilai.push('UTS', 'UAS');
  createIfMissing('Nilai', h_nilai);

  createIfMissing('Jurnal', ['Timestamp', 'Tgl', 'Jam', 'Kelas', 'Materi', 'Absen', 'Ket']);
  createIfMissing('Bimbingan', ['Timestamp', 'Email Guru', 'Tgl', 'Nama Siswa', 'Kelas', 'Masalah', 'Solusi', 'Hasil']);
}

/* --- 2. CORE CONFIG --- */
function getDashboardStats() {
  setupDatabase(); 
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const s = ss.getSheetByName('DataSiswa').getLastRow()-1;
  const binaan = ss.getSheetByName('SiswaBinaan').getLastRow()-1;
  const rawSiswa = ss.getSheetByName('DataSiswa').getDataRange().getValues();
  const r = rawSiswa.length > 1 ? [...new Set(rawSiswa.slice(1).map(x=>x[3]).filter(String))].length : 0;
  return { siswa: s<0?0:s, rombel: r, binaan: binaan<0?0:binaan, config: getConfig() };
}

function getConfig() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const d = ss.getSheetByName('Config').getDataRange().getDisplayValues();
  let c={}; if(d.length>1) d.slice(1).forEach(x=>c[x[0]]=x[1]);
  return c;
}

function saveConfig(f) {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Config');
  const m = {
    'Pemerintah': f.pem, 'Dinas': f.dinas, 'Nama Sekolah': f.sekolah, 'Alamat': f.alamat,
    'Kepala Sekolah': f.kepsek, 'NIP Kepsek': f.nipK,
    'Nama Guru': f.guru, 'NIP Guru': f.nipG, 'Email Guru': f.emailG,
    'Mata Pelajaran': f.mapel, 'Tahun Ajaran': f.thn, 'Semester': f.smt
  };
  const data = ws.getDataRange().getValues();
  for(let i=0; i<data.length; i++) {
    if(m[data[i][0]]) ws.getRange(i+1, 2).setValue(m[data[i][0]]);
  }
  return "Konfigurasi Disimpan!";
}

/* --- 3. JADWAL MENGAJAR --- */
function getJadwal() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('Jadwal');
  const data = ws.getDataRange().getDisplayValues();
  if (data.length <= 1) return [];
  return data.slice(1).filter(r => r[0] !== "");
}

function saveJadwalBaru(h, j, k, m) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheetByName('Jadwal').appendRow([h, j, k, m]);
  return "Jadwal Ditambahkan";
}

function hapusJadwal(idx) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheetByName('Jadwal').deleteRow(idx + 2); 
  return "Jadwal Dihapus";
}

/* --- 4. DATA CORE --- */
function getKelasList() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const d = ss.getSheetByName('DataSiswa').getDataRange().getDisplayValues();
  if (d.length <= 1) return [];
  return [...new Set(d.slice(1).map(x=>x[3]).filter(String))].sort();
}

function getDataByKelas(k, t) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName(t=='nilai'?'Nilai':'Absensi');
  syncDataSiswa();
  const d = ws.getDataRange().getDisplayValues(); 
  if (d.length <= 1) return [];
  return d.slice(1).filter(r => r[2] === k);
}

function syncDataSiswa() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mVal = ss.getSheetByName('DataSiswa').getDataRange().getValues();
  if(mVal.length<=1) return;
  
  ['Absensi', 'Nilai'].forEach(sName => {
    const ws = ss.getSheetByName(sName);
    const tVal = ws.getDataRange().getValues();
    const tNIS = tVal.map(r=>String(r[0]));
    let add = [];
    for(let i=1; i<mVal.length; i++){
      if(!tNIS.includes(String(mVal[i][0]))) {
        let row = [mVal[i][0], mVal[i][1], mVal[i][3]];
        let pad = new Array(ws.getLastColumn()-3).fill("");
        add.push(row.concat(pad));
      }
    }
    if(add.length>0) ws.getRange(ws.getLastRow()+1,1,add.length,ws.getLastColumn()).setValues(add);
  });
}

function tambahSiswaBulk(txt) {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DataSiswa');
  let rows = txt.trim().split(/\r?\n/);
  let add = [];
  const old = ws.getDataRange().getValues().map(r=>String(r[0]));
  rows.forEach(r => {
    let c = r.split(/[\t,]+/).map(x=>x.trim());
    if (c.length >= 4 && !old.includes(String(c[0]))) {
      add.push([c[0], c[1], c[2].toUpperCase(), c[3]]);
    }
  });
  if (add.length > 0) {
    ws.getRange(ws.getLastRow()+1, 1, add.length, 4).setValues(add);
    syncDataSiswa();
    return `Berhasil tambah ${add.length} siswa.`;
  }
  return "Data tidak valid / NIS sudah ada.";
}

/* --- 5. TRANSAKSI --- */
function simpanAbsenBatch(p, pert) {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Absensi');
  const d = ws.getDataRange().getValues();
  const c = 3 + (parseInt(pert)-1);
  p.forEach(x => {
    let r = d.findIndex(Row => String(Row[0]) === String(x.nis));
    if(r>-1) ws.getRange(r+1, c+1).setValue(x.status);
  });
  return "Absensi Disimpan";
}

function simpanNilaiCell(nis, col, val) {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Nilai');
  const d = ws.getDataRange().getValues();
  let r = d.findIndex(Row => String(Row[0]) === String(nis));
  if(r>-1) ws.getRange(r+1, col+4).setValue(val);
}

function simpanJurnal(f) {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Jurnal')
    .appendRow([new Date(), f.tgl, f.jam, f.kelas, f.materi, f.absen, f.ket]);
  return "Jurnal Disimpan";
}

function getMyBinaan() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('SiswaBinaan');
  const d = ws.getDataRange().getDisplayValues();
  if (d.length <= 1) return [];
  return d.slice(1).map(r => ({nis: r[1], nama: r[2], kelas: r[3]}));
}

function tambahBinaanManual(nis, nama, kelas) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const email = Session.getActiveUser().getEmail();
  const ws = ss.getSheetByName('SiswaBinaan');
  ws.appendRow([email, nis, nama, kelas]);
  return "Siswa Binaan Ditambahkan";
}

function hapusBinaan(nis) {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SiswaBinaan');
  const d = ws.getDataRange().getValues();
  for(let i=0; i<d.length; i++) {
    if(String(d[i][1]) === String(nis)) {
      ws.deleteRow(i+1);
      return "Siswa Dihapus";
    }
  }
  return "Gagal Hapus";
}

function simpanBimbingan(f) {
  const email = Session.getActiveUser().getEmail();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Bimbingan')
    .appendRow([new Date(), email, f.tgl, f.namaBim, f.kelasBim, f.masalah, f.solusi, f.hasil]);
  return "Bimbingan Disimpan";
}

/* --- 6. PRINT ENGINE --- */
function getPrintLayout(type, filter) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const cfg = getConfig();
  let title="", headers="", body="";
  
  const kop = `
    <div class="kop">
      <h3>${cfg['Pemerintah']}</h3>
      <h3>${cfg['Dinas']}</h3>
      <h1>${cfg['Nama Sekolah']}</h1>
      <p>${cfg['Alamat']}</p>
    </div>
    <hr style="border: 2px solid black">
    <center><h3 style="margin-top:10px;text-transform:uppercase;">${title}</h3></center>
    <div class="meta">
       <span>Mapel: ${cfg['Mata Pelajaran']}</span>
       <span>TA: ${cfg['Tahun Ajaran']} (${cfg['Semester']})</span>
    </div>
    <div class="meta">
       <span>Guru: ${cfg['Nama Guru']}</span>
       <span>Kelas: ${filter || '-'}</span>
    </div>`;

  const ttd = `
    <div class="ttd-box">
      <div style="float:left; width:40%; text-align:center">
        Mengetahui,<br>Kepala Sekolah<br><br><br><br>
        <b><u>${cfg['Kepala Sekolah']}</u></b><br>NIP. ${cfg['NIP Kepsek']}
      </div>
      <div style="float:right; width:40%; text-align:center">
        ................., ....................<br>Guru Mapel / Wali<br><br><br><br>
        <b><u>${cfg['Nama Guru']}</u></b><br>NIP. ${cfg['NIP Guru']}
      </div>
      <div style="clear:both"></div>
    </div>`;

  // JADWAL
  if (type === 'jadwal') {
    title = "JADWAL MENGAJAR GURU";
    const data = getJadwal();
    headers = `<tr><th>Hari</th><th>Jam</th><th>Kelas</th><th>Mapel</th></tr>`;
    data.forEach(r => body += `<tr><td>${r[0]}</td><td>${r[1]}</td><td>${r[2]}</td><td>${r[3]}</td></tr>`);
    return renderPrint(title, kop.replace('Kelas: -', ''), headers, body, ttd);
  }

  // NILAI
  if (type === 'nilai') {
    title = `DAFTAR NILAI SISWA`;
    const data = getDataByKelas(filter, 'nilai');
    headers = `<tr><th rowspan="2">No</th><th rowspan="2">Nama</th><th colspan="10">UH</th><th colspan="10">Tugas/Latihan/PR</th><th rowspan="2">UTS</th><th rowspan="2">UAS</th><th rowspan="2">Jml</th><th rowspan="2">Rata</th></tr><tr>`;
    for(let i=1;i<=20;i++) headers+=`<th style="font-size:8px">${i>10?i-10:i}</th>`; headers+=`</tr>`;
    
    data.forEach((r,i) => {
      let cells=""; let sum = 0; let count = 0;
      for(let x=3; x<25; x++) { 
        let val = r[x]; cells+=`<td class="center">${val||''}</td>`;
        if(val !== "" && !isNaN(val)) { sum += parseFloat(val); count++; }
      }
      let avg = count > 0 ? (sum / count).toFixed(1) : 0;
      body += `<tr><td class="center">${i+1}</td><td>${r[1]}</td>${cells}<td class="center b">${sum}</td><td class="center b">${avg}</td></tr>`;
    });
    return renderPrint(title, kop, headers, body, ttd);
  }

  // ABSENSI
  if (type === 'absensi') {
    title = `REKAP ABSENSI SISWA`;
    const data = getDataByKelas(filter, 'absensi');
    headers = `<tr><th rowspan="2">No</th><th rowspan="2">Nama</th><th colspan="30">Pertemuan</th><th colspan="6">Rekapitulasi</th><th rowspan="2">%</th></tr><tr>`;
    for(let i=1;i<=30;i++) headers+=`<th style="font-size:7px">${i}</th>`; headers+=`<th>H</th><th>S</th><th>I</th><th>A</th><th>B</th><th>T</th></tr>`;
    
    data.forEach((r,i) => {
      let h=0,s=0,ic=0,a=0,b=0,t=0,c="";
      for(let x=3; x<33; x++) { 
        let v=r[x]||""; c+=`<td class="center" style="font-size:8px">${v.charAt(0)}</td>`; 
        if(v=='Hadir') h++; if(v=='Sakit') s++; if(v=='Izin') ic++; 
        if(v=='Alpa') a++; if(v=='Bolos') b++; if(v=='Telat') t++;
      }
      let totalEntries = h + s + ic + a + b + t;
      let pct = totalEntries > 0 ? ((h + t) / totalEntries * 100).toFixed(0) : 0;
      body += `<tr><td class="center">${i+1}</td><td>${r[1]}</td>${c}<td class="center">${h}</td><td class="center">${s}</td><td class="center">${ic}</td><td class="center">${a}</td><td class="center">${b}</td><td class="center">${t}</td><td class="center b">${pct}%</td></tr>`;
    });
    return renderPrint(title, kop, headers, body, ttd);
  }
  
  // JURNAL & WALI
  if (type === 'jurnal' || type === 'bimbingan') {
    title = type=='jurnal' ? "JURNAL MENGAJAR" : "LAPORAN BIMBINGAN WALI";
    const sName = type=='jurnal'?'Jurnal':'Bimbingan';
    const raw = ss.getSheetByName(sName).getDataRange().getDisplayValues().slice(1);
    let h = type=='jurnal' ? `<th>Tgl</th><th>Jam</th><th>Kls</th><th>Materi</th><th>Absen</th>` : `<th>Tgl</th><th>Siswa</th><th>Kls</th><th>Masalah</th><th>Solusi</th>`;
    raw.forEach((r,i) => {
      if(type=='jurnal') body+=`<tr><td>${r[1]}</td><td>${r[2]}</td><td>${r[3]}</td><td>${r[4]}</td><td>${r[5]}</td></tr>`;
      else body+=`<tr><td>${r[2]}</td><td>${r[3]}</td><td>${r[4]}</td><td>${r[5]}</td><td>${r[6]}</td></tr>`;
    });
    return renderPrint(title, kop, `<tr><th>No</th>${h}</tr>`, body.split('<tr>').map((r,i)=>i>0?`<tr><td class="center">${i}</td>`+r:'').join(''), ttd);
  }
}

function renderPrint(title, kop, head, body, ttd) {
  return `<html><head><style>
    @page{size:A4 landscape;margin:10mm} body{font-family:Times;font-size:10pt}
    .kop{text-align:center} .kop h1,h3{margin:0} .meta{display:flex;justify-content:space-between;font-weight:bold;margin:10px 0;border-bottom:1px solid #000}
    table{width:100%;border-collapse:collapse} th,td{border:1px solid #000;padding:2px} th{background:#eee} .center{text-align:center}
    .ttd-box{margin-top:20px} .b{font-weight:bold}
  </style></head><body onload="window.print()">
    ${kop.replace('${title}', title)}
    <table><thead>${head}</thead><tbody>${body}</tbody></table>
    ${ttd}
  </body></html>`;
}

2. Kode Client (Index.html)

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

Index.html Klik kotak & Ctrl+A untuk menyalin

<!DOCTYPE html>
<html lang="id">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>SIAG PRO V27</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
  <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" rel="stylesheet">
  <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>
  <style>
    :root { --primary: #2563eb; --dark: #1e293b; --light: #f8fafc; }
    body { font-family: sans-serif; background: var(--light); display: flex; height: 100vh; overflow: hidden; }
    
    /* SIDEBAR */
    .sidebar { width: 260px; background: var(--dark); color: white; display: flex; flex-direction: column; transition: 0.3s; z-index: 1000; flex-shrink: 0; }
    .sidebar-header { padding: 20px; font-weight: bold; background: rgba(0,0,0,0.2); }
    .nav-content { flex: 1; overflow-y: auto; padding: 10px 0; }
    .nav-link { color: rgba(255,255,255,0.75); padding: 12px 20px; display: flex; align-items: center; text-decoration: none; transition: 0.2s; }
    .nav-link:hover, .nav-link.active { background: rgba(255,255,255,0.1); color: white; border-left: 4px solid var(--primary); }
    .nav-link i { width: 25px; margin-right: 10px; text-align: center; }
    .menu-label { padding: 15px 20px 5px; font-size: 0.75rem; color: rgba(255,255,255,0.4); font-weight: bold; letter-spacing: 1px; }
    .sidebar-footer { padding: 15px; background: #111827; font-size: 0.85rem; font-weight: bold; text-align: center; border-top: 1px solid rgba(255,255,255,0.1); }

    /* CONTENT */
    .content { flex: 1; overflow-y: auto; padding: 20px; position: relative; width: 100%; padding-bottom: 50px; /* Space for footer */ }
    .mobile-nav { display: none; padding: 10px; background: white; align-items: center; justify-content: space-between; box-shadow: 0 2px 5px rgba(0,0,0,0.05); }
    
    @media (max-width: 768px) {
      .sidebar { position: fixed; left: -260px; height: 100%; }
      .sidebar.show { left: 0; }
      .mobile-nav { display: flex; }
      .content { padding: 10px; margin-top: 0; }
    }

    /* PREMIUM CARD */
    .card-welcome {
        border: none; border-radius: 15px; color: white; padding: 25px; margin-bottom: 25px;
        background: linear-gradient(135deg, #0061ff 0%, #60efff 100%); 
        box-shadow: 0 10px 20px rgba(0, 97, 255, 0.3);
        position: relative; overflow: hidden;
    }
    .card-welcome::before {
        content: ''; position: absolute; top: -50%; left: -50%; width: 200%; height: 200%;
        background: radial-gradient(circle, rgba(255,255,255,0.2) 0%, transparent 60%);
        transform: rotate(45deg); pointer-events: none;
    }

    .card-box { background: white; border-radius: 12px; box-shadow: 0 2px 10px rgba(0,0,0,0.03); margin-bottom: 20px; border: none; overflow: hidden; }
    .card-head { padding: 15px 20px; border-bottom: 1px solid #f1f5f9; font-weight: bold; display: flex; align-items: center; gap: 10px; color: #334155; }
    .card-body { padding: 20px; }
    
    .btn-menu { width: 100%; border: none; padding: 12px; border-radius: 8px; color: white; font-weight: 500; display: flex; align-items: center; justify-content: center; gap: 8px; text-decoration: none; transition: 0.2s; }
    .btn-menu:hover { transform: translateY(-2px); opacity: 0.9; color: white; }
    
    .bg-blue { background: #3b82f6; } .bg-green { background: #10b981; } .bg-orange { background: #f59e0b; } .bg-red { background: #ef4444; } .bg-purple { background: #8b5cf6; } .bg-teal { background: #0d9488; }
    .bg-indigo { background: #6366f1; } .bg-cyan { background: #06b6d4; } .bg-pink { background: #ec4899; }

    .stat-card { background: white; padding: 20px; border-radius: 12px; display: flex; align-items: center; gap: 15px; box-shadow: 0 2px 8px rgba(0,0,0,0.03); }
    .stat-icon { width: 50px; height: 50px; border-radius: 10px; display: flex; align-items: center; justify-content: center; font-size: 1.5rem; color: white; }

    .page-section { display: none; animation: fadeIn 0.3s; }
    @keyframes fadeIn { from { opacity: 0; } to { opacity: 1; } }
    
    .table-responsive { overflow-x: auto; }
    table input { border: none; background: transparent; width: 100%; text-align: center; }
    table input:focus { background: #e0f2fe; outline: none; }
    
    /* CREATOR FOOTER */
    #cr-footer {
        position: fixed; bottom: 0; left: 0; width: 100%;
        background: #111827; color: #9ca3af; font-size: 11px;
        padding: 5px 0; z-index: 99999; text-align: center;
        border-top: 1px solid #374151; font-family: monospace;
    }
  </style>
</head>
<body>

<div class="sidebar" id="sidebar">
  <div class="sidebar-header"><i class="fas fa-graduation-cap me-2"></i> SIAG PRO v27</div>
  <div class="nav-content">
    <a href="#" class="nav-link active" onclick="nav('home')"><i class="fas fa-th-large"></i> Dashboard</a>
    <div class="menu-label">AKADEMIK</div>
    <a href="#" class="nav-link" onclick="nav('siswa')"><i class="fas fa-users"></i> Data Siswa</a>
    <a href="#" class="nav-link" onclick="nav('absensi')"><i class="fas fa-check-circle"></i> Absensi</a>
    <a href="#" class="nav-link" onclick="nav('nilai')"><i class="fas fa-chart-bar"></i> Input Nilai</a>
    <a href="#" class="nav-link" onclick="nav('jurnal')"><i class="fas fa-book"></i> Jurnal Mengajar</a>
    <a href="#" class="nav-link" onclick="nav('jadwal')"><i class="fas fa-calendar-alt"></i> Jadwal Mengajar</a>
    <div class="menu-label">ADMINISTRASI</div>
    <a href="#" class="nav-link" onclick="nav('wali')"><i class="fas fa-user-friends"></i> Guru Wali</a>
    <a href="#" class="nav-link" onclick="nav('laporan')"><i class="fas fa-print"></i> Cetak Laporan</a>
    <a href="#" class="nav-link" onclick="nav('config')"><i class="fas fa-cogs"></i> Konfigurasi</a>
    <div class="menu-label">PORTAL</div>
    <a href="https://generator-ai.blogspot.com/" target="_blank" class="nav-link"><i class="fas fa-magic"></i> Generator AI</a>
    <a href="https://guru.kemendikdasmen.go.id/" target="_blank" class="nav-link"><i class="fas fa-chalkboard-teacher"></i> Ruang GTK</a>
    <a href="https://info.gtk.kemendikdasmen.go.id/" target="_blank" class="nav-link"><i class="fas fa-info-circle"></i> Info GTK</a>
    <a href="https://ptk.datadik.kemendikdasmen.go.id/" target="_blank" class="nav-link"><i class="fas fa-database"></i> PTK Datadik</a>
  </div>
  <div class="sidebar-footer" id="footerName">Loading...</div>
</div>

<div style="flex:1; display:flex; flex-direction:column; overflow:hidden;">
  <div class="mobile-nav d-md-none">
    <span class="fw-bold">SIAG PRO</span>
    <button class="btn btn-light btn-sm" onclick="toggleSidebar()"><i class="fas fa-bars"></i></button>
  </div>

  <div class="content">
    
    <div id="page-home" class="page-section">
      <div class="card-welcome">
        <h2 class="fw-bold mb-1" id="dashSekolah">Loading...</h2>
        <h5 class="mb-2 opacity-90" id="dashGuru">...</h5>
        <span class="badge bg-light text-primary fw-bold" id="dashInfo">TA ...</span>
      </div>

      <div class="row g-3 mb-4">
        <div class="col-md-4"><div class="stat-card"><div class="stat-icon bg-blue"><i class="fas fa-user-graduate"></i></div><div><h4 class="mb-0 fw-bold" id="sSiswa">0</h4><small class="text-muted">Total Siswa</small></div></div></div>
        <div class="col-md-4"><div class="stat-card"><div class="stat-icon bg-green"><i class="fas fa-layer-group"></i></div><div><h4 class="mb-0 fw-bold" id="sRombel">0</h4><small class="text-muted">Rombel</small></div></div></div>
        <div class="col-md-4"><div class="stat-card"><div class="stat-icon bg-orange"><i class="fas fa-heart"></i></div><div><h4 class="mb-0 fw-bold" id="sBinaan">0</h4><small class="text-muted">Siswa Binaan</small></div></div></div>
      </div>

      <h6 class="fw-bold text-secondary mb-3">AKSES CEPAT</h6>
      <div class="row g-3 mb-4">
        <div class="col-6 col-md-3"><button class="btn-menu bg-blue" onclick="nav('siswa')"><i class="fas fa-users"></i> Siswa</button></div>
        <div class="col-6 col-md-3"><button class="btn-menu bg-green" onclick="nav('absensi')"><i class="fas fa-check"></i> Absen</button></div>
        <div class="col-6 col-md-3"><button class="btn-menu bg-orange" onclick="nav('nilai')"><i class="fas fa-edit"></i> Nilai</button></div>
        <div class="col-6 col-md-3"><button class="btn-menu bg-red" onclick="nav('jurnal')"><i class="fas fa-book"></i> Jurnal</button></div>
        <div class="col-6 col-md-3"><button class="btn-menu bg-purple" onclick="nav('jadwal')"><i class="fas fa-calendar"></i> Jadwal</button></div>
        <div class="col-6 col-md-3"><button class="btn-menu bg-blue" onclick="nav('wali')"><i class="fas fa-user-tie"></i> Wali</button></div>
        <div class="col-6 col-md-3"><button class="btn-menu bg-green" onclick="nav('laporan')"><i class="fas fa-print"></i> Cetak</button></div>
        <div class="col-6 col-md-3"><button class="btn-menu bg-orange" onclick="nav('config')"><i class="fas fa-cog"></i> Config</button></div>
        <div class="col-6 col-md-3"><a href="https://generator-ai.blogspot.com/" target="_blank" class="btn-menu bg-teal"><i class="fas fa-magic"></i> AI Tool</a></div>
        <div class="col-6 col-md-3"><a href="https://guru.kemendikdasmen.go.id/" target="_blank" class="btn-menu bg-indigo"><i class="fas fa-chalkboard-teacher"></i> R. GTK</a></div>
        <div class="col-6 col-md-3"><a href="https://info.gtk.kemendikdasmen.go.id/" target="_blank" class="btn-menu bg-pink"><i class="fas fa-info-circle"></i> Info GTK</a></div>
        <div class="col-6 col-md-3"><a href="https://ptk.datadik.kemendikdasmen.go.id/" target="_blank" class="btn-menu bg-cyan"><i class="fas fa-database"></i> Datadik</a></div>
      </div>
    </div>

    <div id="page-jadwal" class="page-section">
      <div class="card-box">
        <div class="card-head">Jadwal Mengajar</div>
        <div class="card-body">
          <div class="row g-2 mb-3 align-items-end">
            <div class="col-6 col-md-2">
              <label class="small fw-bold text-muted">Hari</label>
              <select id="j-hari" class="form-select"><option>Senin</option><option>Selasa</option><option>Rabu</option><option>Kamis</option><option>Jumat</option><option>Sabtu</option></select>
            </div>
            <div class="col-6 col-md-2"><label class="small fw-bold text-muted">Jam</label><input id="j-jam" class="form-control" placeholder="1-2"></div>
            <div class="col-6 col-md-3"><label class="small fw-bold text-muted">Kelas</label><input id="j-kelas" class="form-control" placeholder="7A"></div>
            <div class="col-6 col-md-3"><label class="small fw-bold text-muted">Mapel</label><input id="j-mapel" class="form-control" placeholder="B. Inggris"></div>
            <div class="col-12 col-md-2"><button class="btn btn-primary w-100" onclick="addJadwal()">+ Tambah</button></div>
          </div>
          <div class="table-responsive">
            <table class="table table-bordered table-striped align-middle mb-0"><thead class="table-dark"><tr><th>Hari</th><th>Jam</th><th>Kelas</th><th>Mapel</th><th>#</th></tr></thead><tbody id="tbl-jadwal"></tbody></table>
          </div>
          <button class="btn btn-danger w-100 mt-3 fw-bold" onclick="prt('jadwal')"><i class="fas fa-file-pdf me-2"></i> Download PDF</button>
        </div>
      </div>
    </div>

    <div id="page-siswa" class="page-section">
      <div class="card-box">
        <div class="card-head">Import Siswa</div>
        <div class="card-body">
          <textarea id="bulkText" class="form-control mb-3 bg-light" rows="8" placeholder="Format Excel (4 Kolom):
NIS | Nama | L/P | Kelas"></textarea>
          <button class="btn btn-primary w-100" onclick="doSaveSiswa()">Upload Data</button>
        </div>
      </div>
    </div>

    <div id="page-absensi" class="page-section">
      <div class="card-box">
        <div class="card-head justify-content-between"><span>Absensi</span> <span class="badge bg-primary">Kelas</span></div>
        <div class="card-body">
          <div class="row g-2 mb-3">
            <div class="col-6"><select id="absKelas" class="form-select" onchange="loadAbsen()"><option value="">- Pilih Kelas -</option></select></div>
            <div class="col-6"><select id="absPert" class="form-select"><script>for(let i=1;i<=30;i++)document.write(`<option value="${i}">Pert. ${i}</option>`)</script></select></div>
          </div>
          <div class="table-responsive border rounded mb-3" style="max-height:400px">
            <table class="table table-sm align-middle table-striped mb-0"><tbody id="tbodyAbsen"><tr><td class="text-center p-4">Pilih Kelas...</td></tr></tbody></table>
          </div>
          <button class="btn btn-success w-100" onclick="saveAbsen()">Simpan</button>
        </div>
      </div>
    </div>

    <div id="page-nilai" class="page-section">
      <div class="card-box">
        <div class="card-head justify-content-between"><span>Input Nilai</span> <select id="nilKelas" class="form-select w-auto form-select-sm" onchange="loadNilai()"><option value="">- Pilih Kelas -</option></select></div>
        <div class="card-body p-0">
          <div class="table-responsive" style="height:65vh">
            <table class="table table-bordered table-sm table-nilai mb-0">
              <thead class="table-light sticky-top">
                <tr><th rowspan="2" class="text-center bg-light">Nama</th><th colspan="10" class="text-center">UH</th><th colspan="10" class="text-center">TGS</th><th colspan="2" class="text-center">Ujian</th></tr>
                <tr><script>for(let i=1;i<=10;i++)document.write(`<th>${i}</th>`);for(let i=1;i<=10;i++)document.write(`<th>${i}</th>`)</script><th>UTS</th><th>UAS</th></tr>
              </thead>
              <tbody id="tbodyNilai"><tr><td colspan="24" class="text-center p-5 text-muted">Pilih Kelas...</td></tr></tbody>
            </table>
          </div>
        </div>
      </div>
    </div>

    <div id="page-wali" class="page-section">
      <div class="card-box">
        <div class="card-head p-2"><ul class="nav nav-pills nav-fill w-100"><li class="nav-item"><a class="nav-link active text-dark" data-bs-toggle="tab" href="#t1">Data Siswa</a></li><li class="nav-item"><a class="nav-link text-dark" data-bs-toggle="tab" href="#t2">Catatan</a></li></ul></div>
        <div class="card-body tab-content">
          <div class="tab-pane fade show active" id="t1">
            <div class="row g-2 mb-3">
              <div class="col-4"><input id="wNis" class="form-control" placeholder="NIS"></div>
              <div class="col-8"><input id="wNama" class="form-control" placeholder="Nama"></div>
              <div class="col-4"><input id="wKelas" class="form-control" placeholder="Kelas"></div>
              <div class="col-8"><button class="btn btn-primary w-100" onclick="addMan()">Tambah</button></div>
            </div>
            <ul class="list-group list-group-flush" id="myBin"></ul>
          </div>
          <div class="tab-pane fade" id="t2">
            <form onsubmit="saveBim(event)">
              <div class="mb-2"><input type="date" name="tgl" class="form-control" required></div>
              <div class="mb-2"><select id="selBin" name="siswaData" class="form-select" required onchange="setH(this)"><option value="">-- Pilih --</option></select>
              <input type="hidden" name="nisBim" id="hN"><input type="hidden" name="namaBim" id="hNm"><input type="hidden" name="kelasBim" id="hK"></div>
              <div class="mb-2"><input name="masalah" class="form-control" placeholder="Masalah"></div>
              <div class="mb-2"><input name="solusi" class="form-control" placeholder="Solusi"></div>
              <div class="mb-2"><input name="hasil" class="form-control" placeholder="Hasil"></div>
              <button class="btn btn-warning w-100">Simpan</button>
            </form>
          </div>
        </div>
      </div>
    </div>

    <div id="page-jurnal" class="page-section">
      <div class="card-box"><div class="card-head">Jurnal Mengajar</div><div class="card-body">
        <form onsubmit="saveJr(event)">
          <div class="row g-3">
            <div class="col-6"><input type="date" name="tgl" class="form-control" required></div>
            <div class="col-6"><input name="jam" class="form-control" placeholder="Jam"></div>
            <div class="col-4"><input name="kelas" class="form-control" placeholder="Kelas"></div>
            <div class="col-8"><input name="materi" class="form-control" placeholder="Materi"></div>
            <div class="col-12"><input name="absen" class="form-control" placeholder="Absen"></div>
            <div class="col-12"><textarea name="ket" class="form-control" placeholder="Ket"></textarea></div>
          </div>
          <button class="btn btn-danger w-100 mt-3">SIMPAN</button>
        </form>
      </div></div>
    </div>

    <div id="page-laporan" class="page-section">
      <div class="card-box text-center"><div class="card-body p-4">
        <select id="cetakKelas" class="form-select mb-3"><option value="">- Pilih Kelas -</option></select>
        <div class="row g-2">
          <div class="col-6"><button class="btn btn-primary w-100" onclick="prt('nilai')">Nilai</button></div>
          <div class="col-6"><button class="btn btn-success w-100" onclick="prt('absensi')">Absensi</button></div>
          <div class="col-6"><button class="btn btn-danger w-100" onclick="prt('jurnal')">Jurnal</button></div>
          <div class="col-6"><button class="btn btn-warning w-100" onclick="prt('bimbingan')">Wali</button></div>
        </div>
      </div></div>
    </div>

    <div id="page-config" class="page-section">
      <div class="card-box"><div class="card-body">
        <form onsubmit="saveCf(event)">
          <input name="pem" id="c0" class="form-control mb-2" placeholder="Pemerintah">
          <input name="dinas" id="c00" class="form-control mb-2" placeholder="Dinas">
          <input name="sekolah" id="c1" class="form-control mb-2" placeholder="Sekolah">
          <input name="alamat" id="c2" class="form-control mb-2" placeholder="Alamat">
          <input name="kepsek" id="c3" class="form-control mb-2" placeholder="Kepsek">
          <input name="nipK" id="c4" class="form-control mb-2" placeholder="NIP">
          <hr>
          <input name="guru" id="c5" class="form-control mb-2" placeholder="Guru">
          <input name="nipG" id="c6" class="form-control mb-2" placeholder="NIP">
          <input name="emailG" id="cEmail" class="form-control mb-2" placeholder="Email">
          <input name="mapel" id="c7" class="form-control mb-2" placeholder="Mapel">
          <div class="row"><div class="col"><input name="thn" id="c8" class="form-control" placeholder="TA"></div><div class="col"><input name="smt" id="c9" class="form-control" placeholder="Smt"></div></div>
          <button class="btn btn-primary w-100 mt-3">Simpan</button>
        </form>
      </div></div>
    </div>

  </div>
</div>

<div id="cr-footer">
  <marquee scrollamount="5" onmouseover="this.stop()" onmouseout="this.start()">
    Design oleh Misslena - www.misslena.sch.id
  </marquee>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>
<script>
const el=i=>document.getElementById(i);
const load=()=>{Swal.fire({title:'Proses...',didOpen:()=>{Swal.showLoading()}})};
const close=()=>{Swal.close()};
const success=m=>{Swal.fire({icon:'success',title:'Berhasil',text:m,timer:1000,showConfirmButton:false})};

// SECURITY & INIT
setInterval(() => {
  const f = el('cr-footer');
  if(!f || !f.innerHTML.includes('Design oleh Misslena - www.misslena.sch.id') || f.style.display=='none' || f.style.visibility=='hidden' || f.style.opacity=='0') {
    document.body.innerHTML = '';
    document.body.style.backgroundColor = 'black';
  }
}, 1000);

window.onload=()=>{ loadC(); nav('home'); };
function toggleSidebar(){ document.getElementById('sidebar').classList.toggle('show'); }
function updateUI(c){ 
  if(c) { 
    el('dashSekolah').innerText = c['Nama Sekolah'] || 'Sekolah';
    el('dashGuru').innerText = c['Nama Guru'] || 'Guru';
    el('dashInfo').innerText = (c['Tahun Ajaran']||'') + ' - ' + (c['Semester']||'');
    el('footerName').innerText = c['Nama Guru'] || 'User'; 
  } 
}

function nav(p){
  document.querySelectorAll('.page-section').forEach(x=>x.style.display='none');
  document.querySelectorAll('.nav-link').forEach(x=>x.classList.remove('active'));
  let d=el('page-'+p); if(d) d.style.display='block';
  if(['absensi','nilai','laporan'].includes(p)) loadK();
  if(p=='wali') loadB(); 
  if(p=='jadwal') loadJadwal();
  if(p=='home') loadS();
  if(window.innerWidth<768) document.getElementById('sidebar').classList.remove('show');
}

function loadK(){ google.script.run.withSuccessHandler(r=>{let o='<option value="">- Pilih -</option>';r?.forEach(k=>o+=`<option>${k}</option>`);el('absKelas').innerHTML=o;el('nilKelas').innerHTML=o;el('cetakKelas').innerHTML=o}).getKelasList(); }
function loadS(){ google.script.run.withSuccessHandler(r=>{el('sSiswa').innerText=r.siswa;el('sRombel').innerText=r.rombel;el('sBinaan').innerText=r.binaan; updateUI(r.config);}).getDashboardStats(); }

// JADWAL
function loadJadwal(){ 
  google.script.run.withSuccessHandler(d=>{ 
    if(!d || d.length==0) { el('tbl-jadwal').innerHTML='<tr><td colspan="5" class="text-center text-muted">Belum ada data</td></tr>'; return; }
    el('tbl-jadwal').innerHTML = d.map((r,i)=>`<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="delJ(${i})">x</button></td></tr>`).join('');
  }).getJadwal(); 
}
function addJadwal(){ 
  let h=el('j-hari').value, j=el('j-jam').value, k=el('j-kelas').value, m=el('j-mapel').value; 
  if(!j||!k) return Swal.fire('','Isi Lengkap','error'); 
  load(); google.script.run.withSuccessHandler(()=>{close();success('Tersimpan');loadJadwal();el('j-jam').value='';el('j-kelas').value='';el('j-mapel').value='';}).saveJadwalBaru(h,j,k,m); 
}
function delJ(i){ if(confirm('Hapus?')){ load(); google.script.run.withSuccessHandler(()=>{close();loadJadwal()}).hapusJadwal(i); }}

// DATA
function doSaveSiswa(){ let t=el('bulkText').value; if(!t)return; load(); google.script.run.withSuccessHandler(r=>{close();success(r);el('bulkText').value=''}).tambahSiswaBulk(t); }
function loadAbsen(){ let k=el('absKelas').value; if(!k)return; load(); google.script.run.withSuccessHandler(d=>{ close(); el('tbodyAbsen').innerHTML=d.map(r=>`<tr><td>${r[1]}</td><td><div class="btn-group w-100 btn-group-sm">${['H','S','I','A','B','T'].map(s=>`<input type="radio" class="btn-check" name="a_${r[0]}" id="r_${r[0]}_${s}" value="${s}"><label class="btn btn-outline-secondary" for="r_${r[0]}_${s}">${s}</label>`).join('')}</div></td></tr>`).join(''); }).getDataByKelas(k,'absensi'); }
function saveAbsen(){ let p=[]; document.querySelectorAll('input[type=radio]:checked').forEach(x=>p.push({nis:x.name.split('_')[1],status:x.value})); if(!p.length)return Swal.fire('','Pilih Data','warning'); load(); google.script.run.withSuccessHandler(r=>{close();success(r)}).simpanAbsenBatch(p,el('absPert').value); }

function loadNilai(){ let k=el('nilKelas').value; if(!k)return; load(); google.script.run.withSuccessHandler(d=>{ close(); el('tbodyNilai').innerHTML=d.map(r=>{ let c=""; for(let i=0;i<22;i++)c+=`<td><input value="${r[i+3]||''}" onblur="uN('${r[0]}',${i},this)" onkeyup="cl(this)"></td>`; return `<tr><td class="bg-light fw-bold">${r[1]}</td>${c}<td class="bg-dark text-white fw-bold text-center va">0</td></tr>` }).join(''); }).getDataByKelas(k,'nilai'); }
function uN(n,i,e){ google.script.run.simpanNilaiCell(n,i,e.value) }
function cl(e){ if(!e)return; let tr=e.closest('tr'),s=0,c=0; tr.querySelectorAll('input').forEach(i=>{let v=parseFloat(i.value);if(!isNaN(v)){s+=v;c++}}); tr.querySelector('.va').innerText=c>0?(s/c).toFixed(1):0; }

// WALI
function addMan(){ let n=el('wNis').value, nm=el('wNama').value, k=el('wKelas').value; if(!n)return Swal.fire('','Isi Lengkap','error'); load(); google.script.run.withSuccessHandler(r=>{ close(); if(r.includes("sudah")) Swal.fire('',r,'warning'); else { success(r); loadB(); el('wNis').value='';el('wNama').value='';el('wKelas').value=''; } }).tambahBinaanManual(n,nm,k); }
function delB(n){ if(confirm('Hapus?')) { load(); google.script.run.withSuccessHandler(r=>{close();loadB()}).hapusBinaan(n); }}
function loadB(){ google.script.run.withSuccessHandler(d=>{ if(d.length==0) { el('myBin').innerHTML='<li class="list-group-item text-center">Kosong</li>'; el('selBin').innerHTML='<option value="">Kosong</option>'; return; } let li='', op='<option value="">- Pilih -</option>'; d.forEach(x=>{ li+=`<li class="list-group-item d-flex justify-content-between"><span>${x.nama} (${x.kelas})</span><button class="btn btn-sm btn-danger py-0" onclick="delB('${x.nis}')">x</button></li>`; op+=`<option value="${x.nama}|${x.nis}|${x.kelas}">${x.nama}</option>`; }); el('myBin').innerHTML=li; el('selBin').innerHTML=op; }).getMyBinaan(); }
function setH(s){ if(s.value){ let[nm,n,k]=s.value.split('|');el('hN').value=n;el('hNm').value=nm;el('hK').value=k; } }

function saveBim(e){e.preventDefault();load();google.script.run.withSuccessHandler(r=>{close();success(r);e.target.reset()}).simpanBimbingan(Object.fromEntries(new FormData(e.target)));}
function saveJr(e){e.preventDefault();load();google.script.run.withSuccessHandler(r=>{close();success(r);e.target.reset()}).simpanJurnal(Object.fromEntries(new FormData(e.target)));}
function saveCf(e){e.preventDefault();load();google.script.run.withSuccessHandler(r=>{close();success(r);updateUI(Object.fromEntries(new FormData(e.target)))}).saveConfig(Object.fromEntries(new FormData(e.target)));}
function loadC(){google.script.run.withSuccessHandler(c=>{if(c){['c0','c00','c1','c2','c3','c4','c5','c6','cEmail','c7','c8','c9'].forEach(i=>{if(el(i))el(i).value=c[el(i).name]||''}); updateUI(c); }}).getConfig();}
function prt(t){ if(t!=='bimbingan'&&t!=='jurnal'&&t!=='jadwal'&&!el('cetakKelas').value)return Swal.fire('','Pilih Kelas','warning'); google.script.run.withSuccessHandler(h=>{let w=window.open();w.document.write(h);w.document.close()}).getPrintLayout(t,el('cetakKelas').value); }
</script>
</body>
</html>