Source Code untuk Kode GS Aplikasi Guru BK

Berikut Kode GS untuk Aplikasi Guru BK:

/* BACKEND - SIP BK SMPN 3 KERINCI (FINAL UPDATE) */

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

/* --- API UTAMA --- */

function getInitialData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  setupDatabase(ss); 
  return {
    students: getSheetData(ss, "Data_Siswa"),
    violations: getSheetData(ss, "Data_Pelanggaran"),
    counselings: getSheetData(ss, "Data_Konseling"),
    achievements: getSheetData(ss, "Data_Prestasi"),
    homeVisits: getSheetData(ss, "Data_HomeVisit")
  };
}

/* Single Insert */
function addDataToSheet(sheetName, dataObj) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  const rowData = headers.map(header => {
    if (header === 'id') return Date.now();
    let val = dataObj[header];
    return (val === undefined || val === null) ? "" : val;
  });
  
  sheet.appendRow(rowData);
  return getSheetData(ss, sheetName);
}

/* BULK INSERT (Untuk Import Excel) */
function importBulkStudents(dataArray) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Data_Siswa");
  
  if (!dataArray || dataArray.length === 0) return getSheetData(ss, "Data_Siswa");

  // Format data sesuai urutan kolom: id, name, class, nis, status, points
  // Kita generate ID unik untuk setiap baris
  const rows = dataArray.map((row, index) => {
    return [
      Date.now() + index, // ID Unik
      row.name || "",
      row.class || "",
      row.nis || "",
      "Aktif", // Default Status
      0        // Default Poin
    ];
  });

  // Tulis ke sheet (mulai dari baris terakhir + 1)
  const lastRow = sheet.getLastRow();
  sheet.getRange(lastRow + 1, 1, rows.length, rows[0].length).setValues(rows);
  
  return getSheetData(ss, "Data_Siswa");
}

function deleteDataFromSheet(sheetName, id) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const idIndex = data[0].indexOf('id');
  
  if (idIndex === -1) return;

  for (let i = 1; i < data.length; i++) {
    // Pakai '==' biar aman bandingkan string/number
    if (data[i][idIndex] == id) { 
      sheet.deleteRow(i + 1);
      break;
    }
  }
  return getSheetData(ss, sheetName);
}

/* HELPER */
function getSheetData(ss, sheetName) {
  const sheet = ss.getSheetByName(sheetName);
  if (!sheet || sheet.getLastRow() < 2) return [];
  
  const rawData = sheet.getDataRange().getValues();
  const headers = rawData.shift();
  
  return rawData.map(row => {
    let obj = {};
    headers.forEach((header, index) => {
      let value = row[index];
      if (Object.prototype.toString.call(value) === '[object Date]') {
        value = Utilities.formatDate(value, Session.getScriptTimeZone(), "yyyy-MM-dd");
      }
      obj[header] = value;
    });
    return obj;
  });
}

function setupDatabase(ss) {
  const schema = {
    "Data_Siswa": ["id", "name", "class", "nis", "status", "points"],
    "Data_Pelanggaran": ["id", "studentId", "studentName", "date", "type", "points", "note"],
    "Data_Konseling": ["id", "studentId", "studentName", "date", "time", "status", "topic"],
    "Data_Prestasi": ["id", "studentId", "studentName", "date", "title", "level", "note"],
    "Data_HomeVisit": ["id", "studentId", "studentName", "date", "parentName", "result", "petugas"]
  };

  for (let sheetName in schema) {
    if (!ss.getSheetByName(sheetName)) {
      let s = ss.insertSheet(sheetName);
      s.appendRow(schema[sheetName]);
    }
  }
}