API tools faq
paste
bahar2509

Presensi/GoogleScript

bahar2509
Jan 19th, 2026 (edited)
98
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
JavaScript 19.58 KB | Source Code | 0 0
raw download clone embed print report
  1. //Code.GS
  2.  
  3. function doGet() {
  4.   return HtmlService.createTemplateFromFile('Index')
  5.       .evaluate()
  6.       .setTitle(CONFIG.APP_TITLE)
  7.       .addMetaTag('viewport', 'width=device-width, initial-scale=1')
  8.       .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  9. }
  10.  
  11. function columnToLetter(column) {
  12.   var temp, letter = '';
  13.   while (column > 0) {
  14.     temp = (column - 1) % 26;
  15.     letter = String.fromCharCode(temp + 65) + letter;
  16.     column = (column - temp - 1) / 26;
  17.   }
  18.   return letter;
  19. }
  20.  
  21. // --- BARU: AMBIL DAFTAR KELAS (NAMA SHEET) ---
  22. function getDaftarKelas() {
  23.   var ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
  24.   var sheets = ss.getSheets();
  25.   var daftarKelas = [];
  26.  
  27.   sheets.forEach(function(sheet) {
  28.     // Ambil nama sheet sebagai nama kelas
  29.     daftarKelas.push(sheet.getName());
  30.   });
  31.  
  32.   return {
  33.     kelas: daftarKelas,
  34.     config: CONFIG // Kirim config ke frontend juga
  35.   };
  36. }
  37.  
  38. // --- FUNGSI CEK TEMPLATE (DINAMIS PER KELAS) ---
  39. function cekDanBuatTemplate(namaKelas) {
  40.   var ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
  41.   var sheet = ss.getSheetByName(namaKelas);
  42.  
  43.   if (!sheet) return; // Safety check
  44.  
  45.   // Security Check 1
  46.   var headerNo = sheet.getRange(CONFIG.HEADER_ROW, 1).getValue();
  47.   if (headerNo === "No") return;
  48.  
  49.   // Security Check 2
  50.   var lastRow = sheet.getLastRow();
  51.   if (lastRow >= CONFIG.DATA_START_ROW) {
  52.     var dataCheck = sheet.getRange(CONFIG.DATA_START_ROW, 2, Math.min(lastRow, 20), 1).getValues();
  53.     for (var i = 0; i < dataCheck.length; i++) {
  54.       if (dataCheck[i][0] && dataCheck[i][0].toString().trim() !== "") return;
  55.     }
  56.   }
  57.  
  58.   // --- INSTALASI TEMPLATE ---
  59.   sheet.clear();
  60.  
  61.   var headerInfos = [
  62.     { row: 1, text: CONFIG.SCHOOL_NAME, size: 14 },
  63.     { row: 2, text: "KELAS: " + namaKelas, size: 12 }, // Dinamis dari Nama Sheet
  64.     { row: 3, text: CONFIG.SUBJECT_INFO, size: 12 },
  65.     { row: 4, text: CONFIG.SEMESTER_INFO, size: 12 }
  66.   ];
  67.  
  68.   headerInfos.forEach(function(info) {
  69.     sheet.getRange(info.row, 1, 1, 9).merge()
  70.          .setValue(info.text)
  71.          .setHorizontalAlignment("center").setVerticalAlignment("middle")
  72.          .setFontWeight("bold").setFontSize(info.size);
  73.   });
  74.  
  75.   var headerColor = "#0f515e"; var fontColor = "#ffffff";
  76.  
  77.   sheet.getRange(CONFIG.HEADER_ROW - 1, 1, 2, 1).merge().setValue("No");
  78.   sheet.getRange(CONFIG.HEADER_ROW - 1, 2, 2, 1).merge().setValue("Nama Siswa");
  79.   sheet.getRange(CONFIG.HEADER_ROW - 1, 1, 2, 2)
  80.        .setFontWeight("bold").setHorizontalAlignment("center").setVerticalAlignment("middle")
  81.        .setBackground(headerColor).setFontColor(fontColor)
  82.        .setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  83.  
  84.   sheet.getRange(CONFIG.HEADER_ROW - 1, 3).setValue("Pertemuan").setFontColor(headerColor);
  85.  
  86.   sheet.getRange(CONFIG.HEADER_ROW - 1, 4, 1, 6).merge().setValue("Total")
  87.        .setFontWeight("bold").setHorizontalAlignment("center").setVerticalAlignment("middle")
  88.        .setBackground(headerColor).setFontColor(fontColor)
  89.        .setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  90.  
  91.   var subHeaders = ["H", "A", "I", "S", "T", "B"];
  92.   sheet.getRange(CONFIG.HEADER_ROW, 4, 1, 6).setValues([subHeaders])
  93.        .setFontWeight("bold").setHorizontalAlignment("center").setVerticalAlignment("middle")
  94.        .setBackground(headerColor).setFontColor(fontColor)
  95.        .setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  96.  
  97.   sheet.setColumnWidth(1, 40); sheet.setColumnWidth(2, 250); sheet.setColumnWidth(3, 10);
  98.   for (var c = 4; c <= 9; c++) sheet.setColumnWidth(c, 40);
  99.   sheet.hideColumns(3);
  100.  
  101.   var startRow = CONFIG.DATA_START_ROW;
  102.   sheet.getRange(startRow, 1).setValue(1).setHorizontalAlignment("center");
  103.   sheet.getRange(startRow, 2).setValue("Siswa Contoh (Ganti Nama Ini)");
  104.  
  105.   var totalHLetter = columnToLetter(4);
  106.   var formulas = [[
  107.     '=COUNTIF($C7:OFFSET($'+totalHLetter+'7;0;-1); "H")',
  108.     '=COUNTIF($C7:OFFSET($'+totalHLetter+'7;0;-1); "A")',
  109.     '=COUNTIF($C7:OFFSET($'+totalHLetter+'7;0;-1); "I")',
  110.     '=COUNTIF($C7:OFFSET($'+totalHLetter+'7;0;-1); "S")',
  111.     '=COUNTIF($C7:OFFSET($'+totalHLetter+'7;0;-1); "T")',
  112.     '=COUNTIF($C7:OFFSET($'+totalHLetter+'7;0;-1); "B")'
  113.   ]];
  114.   sheet.getRange(startRow, 4, 1, 6).setFormulas(formulas).setHorizontalAlignment("center");
  115.   sheet.getRange(startRow, 1, 1, 9).setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID).setVerticalAlignment("middle");
  116. }
  117.  
  118. // --- AMBIL SISWA BERDASARKAN KELAS (REVISI FIX REKAP) ---
  119. function getDaftarSiswa(namaKelas) {
  120.   // Cek template dulu untuk kelas yg dipilih
  121.   cekDanBuatTemplate(namaKelas);
  122.  
  123.   var ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
  124.   var sheet = ss.getSheetByName(namaKelas);
  125.  
  126.   if (!sheet) return [];
  127.  
  128.   var lastRow = sheet.getLastRow();
  129.   if (lastRow < CONFIG.DATA_START_ROW) return [];
  130.  
  131.   // Ambil semua data dari baris pertama siswa sampai baris paling bawah sheet
  132.   var data = sheet.getRange(CONFIG.DATA_START_ROW, 2, lastRow - CONFIG.DATA_START_ROW + 1, 1).getValues();
  133.  
  134.   var siswa = [];
  135.  
  136.   // Daftar kata kunci yang menandakan batas akhir data siswa
  137.   var stopKeywords = [
  138.     "REKAPITULASI HARIAN",
  139.     "Total Hadir", "Total Hadir (H)",
  140.     "Total Sakit", "Total Sakit (S)",
  141.     "Total Izin", "Total Izin (I)",
  142.     "Total Alpa", "Total Alpa (A)",
  143.     "Total Terlambat", "Total Terlambat (T)",
  144.     "Total Bolos", "Total Bolos (B)"
  145.   ];
  146.  
  147.   for (var i = 0; i < data.length; i++) {
  148.     var cellValue = data[i][0];
  149.    
  150.     // Validasi: Pastikan cell tidak kosong
  151.     if (cellValue && cellValue.toString().trim() !== "") {
  152.       var namaStr = cellValue.toString().trim();
  153.       if (stopKeywords.indexOf(namaStr) > -1 || namaStr.indexOf("Total ") === 0) {
  154.         break; // BERHENTI loop (stop scan ke bawah)
  155.       }
  156.      
  157.       // Jika bukan rekap, masukkan ke daftar siswa
  158.       siswa.push(namaStr);
  159.     }
  160.   }
  161.  
  162.   return siswa;
  163. }
  164.  
  165. // --- FUNGSI UTAMA: SIMPAN PRESENSI (VERSI DINAMIS & RAPI) ---
  166. function simpanPresensi(dataPresensi, namaKelas) {
  167.   var ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
  168.   var sheet = ss.getSheetByName(namaKelas);
  169.  
  170.   // 1. BERSIHKAN AREA SISWA (Hapus baris kosong/siswa keluar)
  171.   bersihkanAreaSiswa(sheet);
  172.  
  173.   // 2. DETEKSI POSISI & STRUKTUR
  174.   var lastRowTotal = sheet.getLastRow();
  175.   var startRekapRow = -1;
  176.   var dataKolomB = sheet.getRange(1, 2, lastRowTotal, 1).getValues();
  177.  
  178.   for (var i = 0; i < lastRowTotal; i++) {
  179.     if (dataKolomB[i][0] === "REKAPITULASI HARIAN") {
  180.       startRekapRow = i + 1;
  181.       break;
  182.     }
  183.   }
  184.  
  185.   // Tentukan baris siswa terakhir
  186.   var lastStudentRow = CONFIG.DATA_START_ROW - 1;
  187.   if (startRekapRow !== -1) {
  188.     for (var r = startRekapRow - 1; r >= CONFIG.DATA_START_ROW; r--) {
  189.       var val = sheet.getRange(r, 2).getValue();
  190.       if (val !== "" && val !== null) {
  191.         lastStudentRow = r;
  192.         break;
  193.       }
  194.     }
  195.   } else {
  196.     lastStudentRow = sheet.getLastRow();
  197.   }
  198.  
  199.   // Jaga jarak 1 baris (Gap)
  200.   if (startRekapRow !== -1 && (startRekapRow - lastStudentRow) <= 1) {
  201.     sheet.insertRowAfter(lastStudentRow);
  202.     startRekapRow++;
  203.   }
  204.  
  205.   // Bersihkan Baris Gap
  206.   var gapRow = lastStudentRow + 1;
  207.   var maxCols = sheet.getLastColumn();
  208.   sheet.getRange(gapRow, 1, 1, maxCols).clearContent().clearFormat().setBackground("white");
  209.  
  210.   // 3. INSERT KOLOM TANGGAL BARU
  211.   var lastCol = sheet.getLastColumn();
  212.   var headers = sheet.getRange(CONFIG.HEADER_ROW, 1, 1, lastCol).getValues()[0];
  213.   var targetColIndex = -1;
  214.   for (var i = 0; i < headers.length; i++) {
  215.     if (headers[i] === CONFIG.HEADER_TOTAL_START || headers[i] === "Total") { targetColIndex = i + 1; break; }
  216.   }
  217.   if (targetColIndex === -1) targetColIndex = lastCol - 5;
  218.  
  219.   sheet.insertColumnBefore(targetColIndex);
  220.   sheet.setColumnWidth(targetColIndex, 35);
  221.  
  222.   // Header Pertemuan & Tanggal
  223.   var superHeaderRow = CONFIG.HEADER_ROW - 1;
  224.   var colsToMerge = targetColIndex - 3 + 1;
  225.   try { sheet.getRange(superHeaderRow, 3, 1, colsToMerge - 1).breakApart(); } catch(e){}
  226.   sheet.getRange(superHeaderRow, 3, 1, colsToMerge).merge().setValue("Pertemuan")
  227.        .setHorizontalAlignment("center").setVerticalAlignment("middle")
  228.        .setFontWeight("bold").setFontColor("white").setBackground("#0f515e")
  229.        .setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  230.  
  231.   var today = new Date();
  232.   var dateString = Utilities.formatDate(today, "GMT+8", "dd/MM");
  233.   sheet.getRange(CONFIG.HEADER_ROW, targetColIndex).setValue(dateString)
  234.        .setFontWeight("bold").setHorizontalAlignment("center")
  235.        .setBackground("#0f515e").setFontColor("white")
  236.        .setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  237.  
  238.   // 4. TULIS DATA ABSENSI
  239.   var numSiswa = lastStudentRow - CONFIG.DATA_START_ROW + 1;
  240.  
  241.   if (numSiswa > 0) {
  242.     var dataToWrite = [];
  243.     for (var j = 0; j < numSiswa; j++) {
  244.       dataToWrite.push([ (j < dataPresensi.length) ? dataPresensi[j] : "" ]);
  245.     }
  246.    
  247.     sheet.getRange(CONFIG.DATA_START_ROW, targetColIndex, numSiswa, 1)
  248.          .setValues(dataToWrite).setHorizontalAlignment("center");
  249.  
  250.     // --- BAGIAN INI DIMODIFIKASI UNTUK UPDATE FORMULA & WARNA ---
  251.    
  252.     var colTotalStart = targetColIndex + 1;
  253.     var totalHLetter = columnToLetter(colTotalStart);
  254.    
  255.     // A. UPDATE HEADER KECIL (Agar sesuai urutan H, A, I, S, T, B)
  256.     var subHeaders = [["H", "A", "I", "S", "T", "B"]];
  257.     sheet.getRange(CONFIG.HEADER_ROW, colTotalStart, 1, 6)
  258.          .setValues(subHeaders)
  259.          .setHorizontalAlignment("center").setFontWeight("bold")
  260.          .setBackground("#0f515e").setFontColor("white"); // Samakan style header
  261.  
  262.     var formulasTotal = [];
  263.     var nomorUrut = [];
  264.    
  265.     for(var k = 0; k < numSiswa; k++) {
  266.        var r = CONFIG.DATA_START_ROW + k;
  267.        // B. RUMUS AKURAT SESUAI URUTAN HEADER (H, A, I, S, T, B)
  268.        formulasTotal.push([
  269.          '=COUNTIF($C'+r+':OFFSET($'+totalHLetter+r+';0;-1); "H")', // Kolom 1: H
  270.          '=COUNTIF($C'+r+':OFFSET($'+totalHLetter+r+';0;-1); "A")', // Kolom 2: A (Alpa)
  271.          '=COUNTIF($C'+r+':OFFSET($'+totalHLetter+r+';0;-1); "I")', // Kolom 3: I
  272.          '=COUNTIF($C'+r+':OFFSET($'+totalHLetter+r+';0;-1); "S")', // Kolom 4: S
  273.          '=COUNTIF($C'+r+':OFFSET($'+totalHLetter+r+';0;-1); "T")', // Kolom 5: T
  274.          '=COUNTIF($C'+r+':OFFSET($'+totalHLetter+r+';0;-1); "B")'  // Kolom 6: B
  275.        ]);
  276.        nomorUrut.push([k + 1]);
  277.     }
  278.    
  279.     // Terapkan Rumus
  280.     var rangeTotal = sheet.getRange(CONFIG.DATA_START_ROW, colTotalStart, numSiswa, 6);
  281.     rangeTotal.setFormulas(formulasTotal)
  282.               .setHorizontalAlignment("center") // C. AUTO CENTER
  283.               .setVerticalAlignment("middle");
  284.  
  285.     sheet.getRange(CONFIG.DATA_START_ROW, 1, numSiswa, 1).setValues(nomorUrut).setHorizontalAlignment("center");
  286.    
  287.     // Border Rapi
  288.     sheet.getRange(CONFIG.DATA_START_ROW, 1, numSiswa, sheet.getLastColumn())
  289.          .setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  290.  
  291.     // --- D. CONDITIONAL FORMATTING (WARNA ALPA OTOMATIS) ---
  292.     var colAlpaIndex = colTotalStart + 1;
  293.     var rangeAlpa = sheet.getRange(CONFIG.DATA_START_ROW, colAlpaIndex, numSiswa, 1);
  294.    
  295.     // Hapus aturan lama agar tidak menumpuk
  296.     rangeAlpa.clearFormat(); // Hapus warna manual
  297.     // Kembalikan border & align karena clearFormat menghapusnya
  298.     rangeAlpa.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID)
  299.              .setHorizontalAlignment("center").setVerticalAlignment("middle");
  300.  
  301.     var rules = sheet.getConditionalFormatRules();
  302.     var newRules = [];
  303.        
  304.     // Aturan 1: Merah (10-20 Alpa)
  305.     var ruleMerah = SpreadsheetApp.newConditionalFormatRule()
  306.       .whenNumberBetween(10, 20)
  307.       .setBackground("#ea9999") // Merah
  308.       .setRanges([rangeAlpa])
  309.       .build();
  310.      
  311.     // Aturan 2: Orange (5-9 Alpa)
  312.     var ruleOrange = SpreadsheetApp.newConditionalFormatRule()
  313.       .whenNumberBetween(5, 9)
  314.       .setBackground("#f9cb9c") // Orange
  315.       .setRanges([rangeAlpa])
  316.       .build();
  317.  
  318.     // Aturan 3: Kuning (3-4 Alpa)
  319.     var ruleKuning = SpreadsheetApp.newConditionalFormatRule()
  320.       .whenNumberBetween(3, 4)
  321.       .setBackground("#ffe599") // Kuning
  322.       .setRanges([rangeAlpa])
  323.       .build();
  324.  
  325.     sheet.clearConditionalFormatRules();
  326.    
  327.     newRules.push(ruleMerah);
  328.     newRules.push(ruleOrange);
  329.     newRules.push(ruleKuning);
  330.    
  331.     sheet.setConditionalFormatRules(newRules);
  332.   }
  333.  
  334.   // Update Header Info
  335.   var currentLastCol = sheet.getLastColumn();
  336.   var headerInfos = [
  337.     { row: 1, text: CONFIG.SCHOOL_NAME }, { row: 2, text: "KELAS: " + namaKelas },
  338.     { row: 3, text: CONFIG.SUBJECT_INFO }, { row: 4, text: CONFIG.SEMESTER_INFO }
  339.   ];
  340.   headerInfos.forEach(function(info) {
  341.     try { sheet.getRange(info.row, 1, 1, currentLastCol).breakApart(); } catch(e){}
  342.     sheet.getRange(info.row, 1, 1, currentLastCol).merge().setValue(info.text).setHorizontalAlignment("center");
  343.   });
  344.   updateRekapHarian(sheet, targetColIndex, lastStudentRow);
  345.   return CONFIG.SUCCESS_MSG;
  346. }
  347.  
  348.  
  349. function bersihkanBarisSiswaKosong(sheet) {
  350.   var lastRow = sheet.getLastRow();
  351.   var limitRow = lastRow;
  352.   var dataB = sheet.getRange(CONFIG.DATA_START_ROW, 2, lastRow - CONFIG.DATA_START_ROW + 1, 1).getValues();
  353.  
  354.   for(var i=0; i<dataB.length; i++) {
  355.     if (dataB[i][0] === "REKAPITULASI HARIAN") {
  356.       limitRow = CONFIG.DATA_START_ROW + i - 1;
  357.       break;
  358.     }
  359.   }
  360.   for (var r = limitRow; r >= CONFIG.DATA_START_ROW; r--) {
  361.     var nama = sheet.getRange(r, 2).getValue();
  362.     if (nama === "" || nama === null || nama.toString().trim() === "") {
  363.       sheet.deleteRow(r);
  364.     }
  365.   }
  366. }
  367.  
  368. function hapusRekapHarian(sheet) {
  369.   var lastRow = sheet.getLastRow();
  370.  
  371.   var keywords = [
  372.     "REKAPITULASI HARIAN",
  373.     "Total Hadir", "Total Hadir (H)",
  374.     "Total Sakit", "Total Sakit (S)",
  375.     "Total Izin", "Total Izin (I)",
  376.     "Total Alpa", "Total Alpa (A)",
  377.     "Total Terlambat", "Total Terlambat (T)",
  378.     "Total Bolos", "Total Bolos (B)"
  379.   ];
  380.  
  381.   // Loop mundur dari bawah ke atas
  382.   for (var i = lastRow; i >= CONFIG.DATA_START_ROW; i--) {
  383.     var cellValue = sheet.getRange(i, 2).getValue();
  384.     var stringValue = cellValue ? cellValue.toString().trim() : "";
  385.     if (keywords.indexOf(stringValue) > -1 || (stringValue.indexOf("Total ") === 0 && stringValue.length < 25)) {
  386.       // Hapus baris ini
  387.       sheet.deleteRow(i);
  388.     }
  389.   }
  390. }
  391.  
  392. // --- HELPER 2: BUAT REKAP HARIAN BARU ---
  393. function buatRekapHarian(sheet, colIndexDate) {
  394.   var lastRowSiswa = sheet.getLastRow();
  395.   var startRow = lastRowSiswa + 2; // Beri jarak 1 baris kosong dari data siswa terakhir
  396.  
  397.   // Label Rekap Lengkap (H, S, I, A, T, B)
  398.   var labels = [
  399.     ["REKAPITULASI HARIAN"],
  400.     ["Total Hadir (H)"],
  401.     ["Total Sakit (S)"],
  402.     ["Total Izin (I)"],
  403.     ["Total Alpa (A)"],
  404.     ["Total Terlambat (T)"], // Tambahan
  405.     ["Total Bolos (B)"]      // Tambahan
  406.   ];
  407.  
  408.   // Set Label di Kolom B (Nama Siswa)
  409.   sheet.getRange(startRow, 2, labels.length, 1).setValues(labels)
  410.        .setFontWeight("bold").setHorizontalAlignment("right");
  411.  
  412.   // Format Judul Rekap (agar lebih menonjol)
  413.   sheet.getRange(startRow, 2).setFontColor("#0f515e").setFontSize(11);
  414.  
  415.   // Buat Range Data untuk dihitung (Dari baris pertama data s.d. baris terakhir siswa)
  416.   var colLetter = columnToLetter(colIndexDate);
  417.   var rangeData = colLetter + CONFIG.DATA_START_ROW + ":" + colLetter + lastRowSiswa;
  418.  
  419.   // Rumus Lengkap
  420.   var formulas = [
  421.     [""], // Baris judul kosong
  422.     ["=COUNTIF(" + rangeData + "; \"H\")"],
  423.     ["=COUNTIF(" + rangeData + "; \"S\")"],
  424.     ["=COUNTIF(" + rangeData + "; \"I\")"],
  425.     ["=COUNTIF(" + rangeData + "; \"A\")"],
  426.     ["=COUNTIF(" + rangeData + "; \"T\")"], // Rumus T
  427.     ["=COUNTIF(" + rangeData + "; \"B\")"]  // Rumus B
  428.   ];
  429.  
  430.   // Tulis Formula ke Sheet
  431.   sheet.getRange(startRow, colIndexDate, formulas.length, 1).setFormulas(formulas)
  432.        .setFontWeight("bold").setHorizontalAlignment("center")
  433.        .setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID)
  434.        .setBackground("#f3f3f3"); // Background abu muda
  435. }
  436.  
  437.  
  438. function updateRekapHarian(sheet, colIndexDate, lastStudentRow) {
  439.   // --- KONFIGURASI TAMPILAN (DISESUAIKAN DENGAN URUTAN H, A, I, S, T, B) ---
  440.   var labels = [
  441.     "REKAPITULASI HARIAN",
  442.     "Total Hadir (H)",
  443.     "Total Alpa (A)",       // Alpa naik ke urutan kedua
  444.     "Total Izin (I)",
  445.     "Total Sakit (S)",
  446.     "Total Terlambat (T)",
  447.     "Total Bolos (B)"
  448.   ];
  449.  
  450.   // Kode Rumus (Harus urut sesuai labels di atas)
  451.   var codes = ["", "H", "A", "I", "S", "T", "B"];
  452.  
  453.   // Palette Warna
  454.   var styles = [
  455.     { bg: "#ffffff", text: "#0f515e" }, // Header
  456.     { bg: "#e6f4ea", text: "#137333" }, // Hadir (Hijau)
  457.     { bg: "#fce8e6", text: "#c5221f" }, // Alpa (Merah)
  458.     { bg: "#fef7e0", text: "#b06000" }, // Izin (Kuning)
  459.     { bg: "#e8f0fe", text: "#1967d2" }, // Sakit (Biru)
  460.     { bg: "#fff8e1", text: "#e37400" }, // Terlambat (Oranye)
  461.     { bg: "#fce8e6", text: "#c5221f" }  // Bolos (Merah)
  462.   ];
  463.  
  464.   // 1. CARI POSISI REKAP
  465.   var startRow = -1;
  466.   var lastRowSheet = sheet.getLastRow();
  467.  
  468.   for (var i = Math.max(CONFIG.DATA_START_ROW, lastRowSheet - 20); i <= lastRowSheet; i++) {
  469.     var val = sheet.getRange(i, 2).getValue();
  470.     if (val == "REKAPITULASI HARIAN") {
  471.       startRow = i;
  472.       break;
  473.     }
  474.   }
  475.  
  476.   if (startRow === -1) startRow = lastStudentRow + 2;
  477.  
  478.   // 2. UPDATE LABEL
  479.   for (var k = 0; k < labels.length; k++) {
  480.     var currentRow = startRow + k;
  481.     var cellLabel = sheet.getRange(currentRow, 2);
  482.     cellLabel.setValue(labels[k]);
  483.    
  484.     if (k === 0) {
  485.       cellLabel.setFontWeight("bold").setFontSize(11)
  486.                .setFontColor("#0f515e").setHorizontalAlignment("left")
  487.                .setBackground(null)
  488.                .setBorder(null, null, true, null, null, null, "#0f515e", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  489.     } else {
  490.       cellLabel.setFontWeight("bold").setFontSize(9)
  491.                .setFontColor("#5f6368").setHorizontalAlignment("right").setVerticalAlignment("middle");
  492.     }
  493.   }
  494.  
  495.   // 3. UPDATE DATA
  496.   var colLetter = columnToLetter(colIndexDate);
  497.   var rangeFormula = colLetter + CONFIG.DATA_START_ROW + ":" + colLetter + lastStudentRow;
  498.  
  499.   for (var k = 0; k < labels.length; k++) {
  500.     if (k === 0) continue;
  501.  
  502.     var currentRow = startRow + k;
  503.     var cellData = sheet.getRange(currentRow, colIndexDate);
  504.     var formula = "=COUNTIF(" + rangeFormula + "; \"" + codes[k] + "\")";
  505.    
  506.     cellData.setFormula(formula)
  507.             .setFontWeight("bold")
  508.             .setHorizontalAlignment("center").setVerticalAlignment("middle")
  509.             .setBackground(styles[k].bg)
  510.             .setFontColor(styles[k].text)
  511.             .setBorder(true, true, true, true, null, null, "#dadce0", SpreadsheetApp.BorderStyle.SOLID);
  512.   }
  513. }
  514.  
  515.  
  516. function bersihkanAreaSiswa(sheet) {
  517.   var lastRow = sheet.getLastRow();
  518.   var dataB = sheet.getRange(1, 2, lastRow, 1).getValues();
  519.   var limitRow = lastRow;
  520.  
  521.   for(var i=0; i<dataB.length; i++) {
  522.     if (dataB[i][0] === "REKAPITULASI HARIAN") {
  523.       limitRow = i + 1;
  524.       break;
  525.     }
  526.   }
  527.  
  528.   for (var r = limitRow - 1; r >= CONFIG.DATA_START_ROW; r--) {
  529.     var nama = sheet.getRange(r, 2).getValue();
  530.     var isGapRow = (r === limitRow - 1);
  531.     if (nama === "" || nama === null || nama.toString().trim() === "") {
  532.       sheet.deleteRow(r);
  533.       limitRow--;
  534.     }
  535.   }
  536. }
Tags: presensi
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!