Wujudkan Masa Depan Bersama SMK NUKLIR (SMK NU 03 Kaliwungu)

Panduan Menghubungkan Formulir, Logo & Flyer ke Google Sheets:

1. Buka Google Sheets Baru (sheets.new) dan beri nama Database SPMB SMK NU 03 Kaliwungu 2027/2028.
2. Klik menu Extensions (Ekstensi) → Apps Script.
3. Hapus seluruh kode di dalamnya, lalu tempelkan kode Google Apps Script di bawah ini (Data Siswa di Sheet 1, File Dokumen di Berkas_Siswa, dan Pengaturan di Pengaturan_SPMB).
4. Klik Deploy → New deployment (atau Manage deployments → Edit → New version) → pilih jenis Web app.
5. Pada Execute as pilih Me, dan pada Who has access pilih Anyone (Siapa saja), lalu klik Deploy.
6. Salin Web App URL yang muncul, lalu tempelkan pada kolom di atas dan klik Simpan URL Koneksi.

function doPost(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var p = e.parameter || {};

  // 1. SIMPAN FILE DOKUMEN SISWA (AKTA, KK, KTP, NISN) DI SHEET KHUSUS "Berkas_Siswa"
  if (p.action === "save_student_doc" || (p.action === "save_assets" && (p.target_sheet === "Berkas_Siswa" || String(p.asset_key || "").indexOf("berkas_") === 0))) {
    var docSheet = ss.getSheetByName("Berkas_Siswa");
    if (!docSheet) {
      docSheet = ss.insertSheet("Berkas_Siswa");
      docSheet.appendRow([
        "Key Berkas", "Waktu Upload", "No. Registrasi", "Nama Siswa",
        "Jenis Dokumen", "Nama File", "Chunk_1", "Chunk_2", "Chunk_3", "Chunk_4", "Chunk_5"
      ]);
    }
    var docKey = p.asset_key;
    var docVal = p.asset_value || "";
    var totalDocChunks = Math.max(5, Math.ceil(docVal.length / 45000));
    var docChunks = [];
    for (var d = 0; d < totalDocChunks; d++) {
      docChunks.push(docVal.substring(d * 45000, (d + 1) * 45000));
    }
    var docData = docSheet.getDataRange().getValues();
    var foundDocRow = -1;
    for (var dr = 1; dr < docData.length; dr++) {
      if (docData[dr][0] === docKey) { foundDocRow = dr + 1; break; }
    }
    var docRowVals = [
      docKey,
      new Date().toLocaleString("id-ID"),
      p.no_registrasi || "-",
      p.nama_siswa || "-",
      p.jenis_dokumen || "-",
      p.nama_file || "-"
    ].concat(docChunks);

    if (foundDocRow > 0) {
      docSheet.getRange(foundDocRow, 1, 1, docRowVals.length).setValues([docRowVals]);
    } else {
      docSheet.getRange(docSheet.getLastRow() + 1, 1, 1, docRowVals.length).setValues([docRowVals]);
    }
    return ContentService.createTextOutput(JSON.stringify({ status: "student_doc_saved", key: docKey }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  // 2. SIMPAN LOGO, FLYER, & NO WA DI SHEET "Pengaturan_SPMB"
  if (p.action === "save_assets") {
    var cfgSheet = ss.getSheetByName("Pengaturan_SPMB");
    if (!cfgSheet) {
      cfgSheet = ss.insertSheet("Pengaturan_SPMB");
      cfgSheet.appendRow(["Key", "Waktu Update", "Chunk_1", "Chunk_2", "Chunk_3", "Chunk_4", "Chunk_5"]);
    }
    var key = p.asset_key;
    var val = p.asset_value || "";
    var totalChunks = Math.max(5, Math.ceil(val.length / 45000));
    var chunks = [];
    for (var i = 0; i < totalChunks; i++) {
      chunks.push(val.substring(i * 45000, (i + 1) * 45000));
    }
    var data = cfgSheet.getDataRange().getValues();
    var foundRow = -1;
    for (var r = 1; r < data.length; r++) {
      if (data[r][0] === key) { foundRow = r + 1; break; }
    }
    var rowVals = [key, new Date().toLocaleString("id-ID")].concat(chunks);
    if (foundRow > 0) {
      cfgSheet.getRange(foundRow, 1, 1, rowVals.length).setValues([rowVals]);
    } else {
      cfgSheet.getRange(cfgSheet.getLastRow() + 1, 1, 1, rowVals.length).setValues([rowVals]);
    }
    return ContentService.createTextOutput(JSON.stringify({ status: "asset_saved", key: key }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  // 3. SIMPAN BARIS PENDAFTAR BARU + INFO BERKAS DOKUMEN KE SHEET UTAMA (SHEET 1)
  var sheet = ss.getSheets()[0];
  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      "No. Registrasi", "Waktu Daftar", "Tahap Pendaftaran", "Jurusan",
      "Nama Lengkap", "NISN", "Jenis Kelamin", "Asal Sekolah",
      "TTL", "No. WhatsApp", "Nama Orang Tua", "Status Alumni & Diskon",
      "Keterangan Alumni", "Biaya SPP", "Alamat Lengkap", "Dokumen Siap",
      "File Akta", "File KK", "File KTP Ortu", "File NISN"
    ]);
  }

  sheet.appendRow([
    p.no_registrasi, p.waktu_daftar, p.tahap_pendaftaran, p.jurusan,
    p.nama_lengkap, "'" + p.nisn, p.jenis_kelamin, p.asal_sekolah,
    p.ttl, "'" + p.no_whatsapp, p.nama_ortu, p.status_alumni,
    p.keterangan_alumni, p.estimasi_spp, p.alamat, p.dokumen_siap,
    p.file_akta_info || "-", p.file_kk_info || "-", p.file_ktp_info || "-", p.file_nisn_info || "-"
  ]);

  return ContentService.createTextOutput(JSON.stringify({ status: "success" }))
    .setMimeType(ContentService.MimeType.JSON);
}

// 4. MUAT DATA SISWA (SHEET 1), FILE BERKAS SISWA ("Berkas_Siswa"), & PENGATURAN ("Pengaturan_SPMB")
function doGet(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var queryKey = (e && e.parameter && e.parameter.asset_key) ? e.parameter.asset_key : "";
  var result = { status: "ok", assets: {}, registrants: [] };

  // Jika Admin meminta melihat/mengunduh file berkas siswa tertentu, cari di sheet "Berkas_Siswa"
  if (queryKey && queryKey.indexOf("berkas_") === 0) {
    var docSheet = ss.getSheetByName("Berkas_Siswa");
    if (docSheet && docSheet.getLastRow() > 1) {
      var docRows = docSheet.getDataRange().getValues();
      for (var dr = 1; dr < docRows.length; dr++) {
        if (String(docRows[dr][0] || "") === queryKey) {
          var combinedDoc = "";
          for (var dc = 6; dc < docRows[dr].length; dc++) {
            if (docRows[dr][dc]) combinedDoc += String(docRows[dr][dc]);
          }
          result.assets[queryKey] = combinedDoc;
          return ContentService.createTextOutput(JSON.stringify(result))
            .setMimeType(ContentService.MimeType.JSON);
        }
      }
    }
  }

  var cfgSheet = ss.getSheetByName("Pengaturan_SPMB");
  if (cfgSheet && cfgSheet.getLastRow() > 1) {
    var data = cfgSheet.getDataRange().getValues();
    for (var r = 1; r < data.length; r++) {
      var k = String(data[r][0] || "");
      if (!k) continue;
      if (queryKey && k !== queryKey) continue;
      if (!queryKey && k.indexOf("berkas_") === 0) continue;
      var combined = "";
      for (var c = 2; c < data[r].length; c++) {
        if (data[r][c]) combined += String(data[r][c]);
      }
      result.assets[k] = combined;
    }
  }

  if (!queryKey) {
    var sheet = ss.getSheets()[0];
    if (sheet && sheet.getLastRow() > 1) {
      var rows = sheet.getDataRange().getValues();
      for (var i = rows.length - 1; i >= 1; i--) {
        var row = rows[i];
        if (!row[0] && !row[4]) continue;
        result.registrants.push({
          no_registrasi: String(row[0] || ""),
          waktu_daftar: String(row[1] || ""),
          tahap_pendaftaran: String(row[2] || ""),
          jurusan: String(row[3] || ""),
          nama_lengkap: String(row[4] || ""),
          nisn: String(row[5] || "").replace(/^'/, ""),
          jenis_kelamin: String(row[6] || ""),
          asal_sekolah: String(row[7] || ""),
          ttl: String(row[8] || ""),
          no_whatsapp: String(row[9] || "").replace(/^'/, ""),
          nama_ortu: String(row[10] || ""),
          status_alumni: String(row[11] || ""),
          keterangan_alumni: String(row[12] || ""),
          estimasi_spp: String(row[13] || ""),
          alamat: String(row[14] || ""),
          dokumen_siap: String(row[15] || ""),
          file_akta_info: String(row[16] || ""),
          file_kk_info: String(row[17] || ""),
          file_ktp_info: String(row[18] || ""),
          file_nisn_info: String(row[19] || ""),
          synced_to_sheet: true
        });
      }
    }
  }

  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}