Sistem Absensi Nasional

No	NIS	Nama Siswa	Hadir	Izin	Sakit	Alfa	Catatan
📅 Pilih tanggal untuk mulai mengisi absensi Gunakan input tanggal di atas untuk memilih tanggal absensi
Pengaturan Sistem

Google Apps Script URL
Sheet ID
Interval Sinkronisasi (detik)
Test Koneksi
📋 Code.gs untuk Google Apps Script

Copy code berikut ke Google Apps Script Anda:
// ========================================
// 🎯 SISTEM ABSENSI NASIONAL SMK FAJAR
// 📋 Google Apps Script - LENGKAP & SEMPURNA
// 🔧 Auto-Create Headers, Format, & Sync
// ========================================

const SHEET_ID = '1xA5zDo1nQfHR-MPWcI3p4FtkRSMrwHE_28ns1kJlF4Q';
const HEADERS = [
  'ID', 'Timestamp', 'Tanggal', 'Jam', 'NIS', 'Nama', 'Kelas', 
  'Status', 'Catatan', 'Sumber', 'Created By', 'Updated At'
];

// 📝 FUNGSI UTAMA - MENYIMPAN DATA ABSENSI
function doPost(e) {
  try {
    Logger.log('📥 POST Request received');
    
    // Parse data dari request
    const data = JSON.parse(e.postData.contents);
    Logger.log('📊 Data parsed: ' + JSON.stringify(data));
    
    // Buka spreadsheet
    const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
    Logger.log('📋 Spreadsheet opened successfully');
    
    // 🎨 AUTO-CREATE & FORMAT HEADERS jika sheet kosong
    if (sheet.getLastRow() === 0) {
      Logger.log('🔧 Creating headers - sheet is empty');
      createAndFormatHeaders(sheet);
    }
    
    // 💾 APPEND DATA (TIDAK PERNAH MENIMPA!)
    if (data.records && data.records.length > 0) {
      Logger.log('📝 Processing ' + data.records.length + ' records');
      
      // Konversi records ke format array
      const values = data.records.map(record => [
        record.id || generateUniqueId(),
        record.timestamp || new Date().toISOString(),
        record.tanggal || new Date().toISOString().split('T')[0],
        record.jam || new Date().toTimeString().split(' ')[0],
        record.nis || '',
        record.nama || '',
        record.kelas || '12 Pemasaran 1',
        record.status || 'HADIR',
        record.catatan || '',
        record.sumber || 'WEB',
        record.created_by || 'System',
        record.updated_at || new Date().toISOString()
      ]);
      
      // 🎯 SELALU APPEND ke baris terakhir + 1
      const lastRow = sheet.getLastRow();
      const startRow = lastRow + 1;
      
      Logger.log('📍 Appending to row: ' + startRow);
      sheet.getRange(startRow, 1, values.length, HEADERS.length).setValues(values);
      
      // 🎨 FORMAT DATA BARU
      formatNewData(sheet, startRow, values.length);
      
      Logger.log('✅ Data successfully appended and formatted');
      
      return ContentService.createTextOutput(JSON.stringify({
        success: true,
        message: `✅ ${values.length} data absensi berhasil disimpan ke spreadsheet`,
        recordsAdded: values.length,
        appendedToRow: startRow,
        timestamp: new Date().toISOString(),
        sheetUrl: `https://docs.google.com/spreadsheets/d/${SHEET_ID}/edit`
      })).setMimeType(ContentService.MimeType.JSON);
    }
    
    return ContentService.createTextOutput(JSON.stringify({
      success: false,
      message: '❌ Tidak ada data records untuk disimpan',
      error: 'No records provided'
    })).setMimeType(ContentService.MimeType.JSON);
      
  } catch (error) {
    Logger.log('❌ Error in doPost: ' + error.toString());
    
    return ContentService.createTextOutput(JSON.stringify({
      success: false,
      error: error.toString(),
      message: '❌ Gagal menyimpan data ke spreadsheet: ' + error.message,
      timestamp: new Date().toISOString()
    })).setMimeType(ContentService.MimeType.JSON);
  }
}

// 📊 FUNGSI GET - SYNC, TEST, STATS
function doGet(e) {
  try {
    Logger.log('📥 GET Request received with params: ' + JSON.stringify(e.parameter));
    
    const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
    const fn = e.parameter.fn || 'test';
    
    // 🔗 TEST CONNECTION
    if (fn === 'test') {
      Logger.log('🔗 Testing connection');
      
      return ContentService.createTextOutput(JSON.stringify({
        success: true,
        message: '✅ Connection OK - Google Apps Script berfungsi dengan baik!',
        timestamp: new Date().toISOString(),
        sheetName: sheet.getName(),
        totalRows: sheet.getLastRow(),
        totalColumns: sheet.getLastColumn(),
        sheetUrl: `https://docs.google.com/spreadsheets/d/${SHEET_ID}/edit`
      })).setMimeType(ContentService.MimeType.JSON);
    }
    
    // 🔄 SYNC DATA FROM SPREADSHEET
    if (fn === 'sync') {
      Logger.log('🔄 Syncing data from spreadsheet');
      
      const lastRow = sheet.getLastRow();
      
      if (lastRow <= 1) {
        return ContentService.createTextOutput(JSON.stringify({
          success: true,
          records: [],
          totalRecords: 0,
          message: '📋 Spreadsheet kosong atau hanya berisi header',
          timestamp: new Date().toISOString()
        })).setMimeType(ContentService.MimeType.JSON);
      }
      
      // Ambil semua data
      const data = sheet.getDataRange().getValues();
      const headers = data[0];
      
      // Konversi ke object format
      const records = data.slice(1).map((row, index) => {
        const record = {};
        headers.forEach((header, colIndex) => {
          const key = header.toLowerCase().replace(/\s+/g, '_');
          let value = row[colIndex] || '';
          
          // Format tanggal jika diperlukan
          if (key === 'tanggal' && value instanceof Date) {
            value = value.toISOString().split('T')[0];
          }
          if (key === 'timestamp' && value instanceof Date) {
            value = value.toISOString();
          }
          
          record[key] = value;
        });
        
        // Tambahkan row number untuk tracking
        record.rowNumber = index + 2; // +2 karena header di row 1
        
        return record;
      });
      
      // Filter records yang valid
      const validRecords = records.filter(record => 
        record.id && record.nis && record.tanggal && record.status &&
        record.status !== '' && record.nis !== ''
      );
      
      Logger.log('📊 Sync completed: ' + validRecords.length + ' valid records');
      
      return ContentService.createTextOutput(JSON.stringify({
        success: true,
        records: validRecords,
        totalRecords: validRecords.length,
        totalRowsInSheet: lastRow - 1,
        message: `✅ Berhasil mengambil ${validRecords.length} data valid dari spreadsheet`,
        timestamp: new Date().toISOString(),
        sheetUrl: `https://docs.google.com/spreadsheets/d/${SHEET_ID}/edit`
      })).setMimeType(ContentService.MimeType.JSON);
    }
    
    // 📈 GET STATISTICS
    if (fn === 'stats') {
      Logger.log('📈 Getting statistics');
      
      const lastRow = sheet.getLastRow();
      
      if (lastRow <= 1) {
        return ContentService.createTextOutput(JSON.stringify({
          success: true,
          stats: {
            totalRecords: 0,
            hadir: 0,
            izin: 0,
            sakit: 0,
            alfa: 0,
            uniqueStudents: 0,
            uniqueDates: 0
          },
          message: '📊 Belum ada data untuk statistik'
        })).setMimeType(ContentService.MimeType.JSON);
      }
      
      const data = sheet.getDataRange().getValues();
      const records = data.slice(1); // Skip header
      
      // Hitung statistik
      const stats = {
        totalRecords: records.length,
        hadir: records.filter(row => row[7] === 'HADIR').length,
        izin: records.filter(row => row[7] === 'IZIN').length,
        sakit: records.filter(row => row[7] === 'SAKIT').length,
        alfa: records.filter(row => row[7] === 'ALFA').length,
        uniqueStudents: [...new Set(records.map(row => row[4]).filter(nis => nis))].length,
        uniqueDates: [...new Set(records.map(row => {
          const date = row[2];
          return date instanceof Date ? date.toISOString().split('T')[0] : date;
        }).filter(date => date))].length,
        lastUpdate: new Date().toISOString()
      };
      
      // Tambahkan persentase
      if (stats.totalRecords > 0) {
        stats.hadirPercent = Math.round((stats.hadir / stats.totalRecords) * 100);
        stats.izinPercent = Math.round((stats.izin / stats.totalRecords) * 100);
        stats.sakitPercent = Math.round((stats.sakit / stats.totalRecords) * 100);
        stats.alfaPercent = Math.round((stats.alfa / stats.totalRecords) * 100);
      }
      
      Logger.log('📊 Stats calculated: ' + JSON.stringify(stats));
      
      return ContentService.createTextOutput(JSON.stringify({
        success: true,
        stats: stats,
        message: `📊 Statistik berhasil dihitung dari ${stats.totalRecords} records`,
        timestamp: new Date().toISOString()
      })).setMimeType(ContentService.MimeType.JSON);
    }
    
    // 🗑️ DELETE RECORD (ADMIN ONLY)
    if (fn === 'delete' && e.parameter.id) {
      Logger.log('🗑️ Deleting record with ID: ' + e.parameter.id);
      
      const data = sheet.getDataRange().getValues();
      const headers = data[0];
      const idColumnIndex = headers.indexOf('ID');
      
      if (idColumnIndex === -1) {
        throw new Error('ID column not found');
      }
      
      // Cari row yang akan dihapus
      let rowToDelete = -1;
      for (let i = 1; i < data.length; i++) {
        if (data[i][idColumnIndex] === e.parameter.id) {
          rowToDelete = i + 1; // +1 karena sheet index mulai dari 1
          break;
        }
      }
      
      if (rowToDelete > 0) {
        sheet.deleteRow(rowToDelete);
        Logger.log('✅ Row deleted: ' + rowToDelete);
        
        return ContentService.createTextOutput(JSON.stringify({
          success: true,
          message: '✅ Record berhasil dihapus',
          deletedId: e.parameter.id,
          timestamp: new Date().toISOString()
        })).setMimeType(ContentService.MimeType.JSON);
      } else {
        throw new Error('Record dengan ID tersebut tidak ditemukan');
      }
    }
    
    // 🔍 SEARCH RECORDS
    if (fn === 'search' && e.parameter.query) {
      Logger.log('🔍 Searching for: ' + e.parameter.query);
      
      const data = sheet.getDataRange().getValues();
      if (data.length <= 1) {
        return ContentService.createTextOutput(JSON.stringify({
          success: true,
          records: [],
          message: 'Tidak ada data untuk dicari'
        })).setMimeType(ContentService.MimeType.JSON);
      }
      
      const headers = data[0];
      const query = e.parameter.query.toLowerCase();
      
      const matchingRecords = data.slice(1)
        .filter(row => {
          return row.some(cell => 
            cell && cell.toString().toLowerCase().includes(query)
          );
        })
        .map(row => {
          const record = {};
          headers.forEach((header, index) => {
            const key = header.toLowerCase().replace(/\s+/g, '_');
            record[key] = row[index] || '';
          });
          return record;
        });
      
      return ContentService.createTextOutput(JSON.stringify({
        success: true,
        records: matchingRecords,
        totalFound: matchingRecords.length,
        query: e.parameter.query,
        message: `🔍 Ditemukan ${matchingRecords.length} records yang cocok`
      })).setMimeType(ContentService.MimeType.JSON);
    }
    
    // ❌ INVALID FUNCTION
    return ContentService.createTextOutput(JSON.stringify({
      success: false,
      error: 'Invalid function parameter: ' + fn,
      availableFunctions: ['test', 'sync', 'stats', 'delete', 'search'],
      message: '❌ Fungsi tidak valid. Gunakan: test, sync, stats, delete, atau search',
      timestamp: new Date().toISOString()
    })).setMimeType(ContentService.MimeType.JSON);
      
  } catch (error) {
    Logger.log('❌ Error in doGet: ' + error.toString());
    
    return ContentService.createTextOutput(JSON.stringify({
      success: false,
      error: error.toString(),
      message: '❌ Gagal mengakses spreadsheet: ' + error.message,
      timestamp: new Date().toISOString()
    })).setMimeType(ContentService.MimeType.JSON);
  }
}

// 🎨 FUNGSI HELPER - CREATE & FORMAT HEADERS
function createAndFormatHeaders(sheet) {
  Logger.log('🎨 Creating and formatting headers');
  
  // Set headers
  sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
  
  // Format header row
  const headerRange = sheet.getRange(1, 1, 1, HEADERS.length);
  headerRange.setBackground('#1a73e8'); // Google Blue
  headerRange.setFontColor('#ffffff');
  headerRange.setFontWeight('bold');
  headerRange.setHorizontalAlignment('center');
  headerRange.setVerticalAlignment('middle');
  headerRange.setFontSize(11);
  
  // Set column widths
  const columnWidths = [120, 150, 100, 80, 80, 150, 120, 80, 200, 80, 120, 150];
  columnWidths.forEach((width, index) => {
    sheet.setColumnWidth(index + 1, width);
  });
  
  // Freeze header row
  sheet.setFrozenRows(1);
  
  // Add borders
  headerRange.setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  
  Logger.log('✅ Headers created and formatted successfully');
}

// 🎨 FUNGSI HELPER - FORMAT NEW DATA
function formatNewData(sheet, startRow, numRows) {
  Logger.log('🎨 Formatting new data rows: ' + startRow + ' to ' + (startRow + numRows - 1));
  
  const dataRange = sheet.getRange(startRow, 1, numRows, HEADERS.length);
  
  // Add borders
  dataRange.setBorder(true, true, true, true, true, true, '#cccccc', SpreadsheetApp.BorderStyle.SOLID);
  
  // Alternate row colors
  for (let i = 0; i < numRows; i++) {
    const rowNum = startRow + i;
    const rowRange = sheet.getRange(rowNum, 1, 1, HEADERS.length);
    
    if (rowNum % 2 === 0) {
      rowRange.setBackground('#f8f9fa'); // Light gray for even rows
    } else {
      rowRange.setBackground('#ffffff'); // White for odd rows
    }
    
    // Format status column with colors
    const statusCell = sheet.getRange(rowNum, 8); // Status is column 8
    const statusValue = statusCell.getValue();
    
    switch (statusValue) {
      case 'HADIR':
        statusCell.setBackground('#d4edda').setFontColor('#155724');
        break;
      case 'IZIN':
        statusCell.setBackground('#fff3cd').setFontColor('#856404');
        break;
      case 'SAKIT':
        statusCell.setBackground('#f8d7da').setFontColor('#721c24');
        break;
      case 'ALFA':
        statusCell.setBackground('#e2e3e5').setFontColor('#383d41');
        break;
    }
  }
  
  // Center align certain columns
  const centerColumns = [1, 3, 4, 5, 8, 10]; // ID, Tanggal, Jam, NIS, Status, Sumber
  centerColumns.forEach(col => {
    sheet.getRange(startRow, col, numRows, 1).setHorizontalAlignment('center');
  });
  
  Logger.log('✅ Data formatting completed');
}

// 🔧 FUNGSI HELPER - GENERATE UNIQUE ID
function generateUniqueId() {
  const timestamp = new Date().getTime();
  const random = Math.random().toString(36).substr(2, 9);
  return `ID_${timestamp}_${random}`;
}

// 📋 FUNGSI HELPER - GET SHEET INFO
function getSheetInfo() {
  try {
    const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
    
    return {
      success: true,
      sheetName: sheet.getName(),
      totalRows: sheet.getLastRow(),
      totalColumns: sheet.getLastColumn(),
      sheetId: SHEET_ID,
      sheetUrl: `https://docs.google.com/spreadsheets/d/${SHEET_ID}/edit`,
      headers: HEADERS,
      timestamp: new Date().toISOString()
    };
  } catch (error) {
    return {
      success: false,
      error: error.toString(),
      message: 'Gagal mengakses informasi spreadsheet'
    };
  }
}

// 🧹 FUNGSI MAINTENANCE - CLEAN DUPLICATE DATA
function cleanDuplicateData() {
  try {
    const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
    const data = sheet.getDataRange().getValues();
    
    if (data.length <= 1) return { success: true, message: 'Tidak ada data untuk dibersihkan' };
    
    const headers = data[0];
    const records = data.slice(1);
    
    // Group by NIS + Tanggal, keep latest timestamp
    const uniqueRecords = {};
    
    records.forEach((row, index) => {
      const nis = row[4]; // NIS column
      const tanggal = row[2]; // Tanggal column
      const timestamp = row[1]; // Timestamp column
      const key = `${nis}_${tanggal}`;
      
      if (!uniqueRecords[key] || new Date(uniqueRecords[key].timestamp) < new Date(timestamp)) {
        uniqueRecords[key] = {
          row: row,
          originalIndex: index + 2, // +2 because header is row 1
          timestamp: timestamp
        };
      }
    });
    
    // Clear sheet and rewrite with unique data
    sheet.clear();
    
    // Recreate headers
    createAndFormatHeaders(sheet);
    
    // Add unique records
    const uniqueRows = Object.values(uniqueRecords).map(record => record.row);
    if (uniqueRows.length > 0) {
      sheet.getRange(2, 1, uniqueRows.length, HEADERS.length).setValues(uniqueRows);
      formatNewData(sheet, 2, uniqueRows.length);
    }
    
    return {
      success: true,
      message: `✅ Pembersihan selesai. ${records.length - uniqueRows.length} duplikat dihapus`,
      originalCount: records.length,
      uniqueCount: uniqueRows.length,
      duplicatesRemoved: records.length - uniqueRows.length
    };
    
  } catch (error) {
    return {
      success: false,
      error: error.toString(),
      message: 'Gagal membersihkan data duplikat'
    };
  }
}

// 📊 FUNGSI ADVANCED - GET DETAILED ANALYTICS
function getDetailedAnalytics() {
  try {
    const sheet = SpreadsheetApp.openById(SHEET_ID).getActiveSheet();
    const data = sheet.getDataRange().getValues();
    
    if (data.length <= 1) {
      return {
        success: true,
        analytics: {},
        message: 'Belum ada data untuk analisis'
      };
    }
    
    const records = data.slice(1);
    
    // Analisis per siswa
    const studentAnalytics = {};
    const dateAnalytics = {};
    
    records.forEach(row => {
      const nis = row[4];
      const nama = row[5];
      const tanggal = row[2];
      const status = row[7];
      
      // Per student
      if (!studentAnalytics[nis]) {
        studentAnalytics[nis] = {
          nama: nama,
          hadir: 0,
          izin: 0,
          sakit: 0,
          alfa: 0,
          total: 0
        };
      }
      
      studentAnalytics[nis][status.toLowerCase()]++;
      studentAnalytics[nis].total++;
      
      // Per date
      const dateKey = tanggal instanceof Date ? tanggal.toISOString().split('T')[0] : tanggal;
      if (!dateAnalytics[dateKey]) {
        dateAnalytics[dateKey] = {
          hadir: 0,
          izin: 0,
          sakit: 0,
          alfa: 0,
          total: 0
        };
      }
      
      dateAnalytics[dateKey][status.toLowerCase()]++;
      dateAnalytics[dateKey].total++;
    });
    
    // Calculate percentages for students
    Object.keys(studentAnalytics).forEach(nis => {
      const student = studentAnalytics[nis];
      if (student.total > 0) {
        student.hadirPercent = Math.round((student.hadir / student.total) * 100);
        student.performanceLevel = student.hadirPercent >= 90 ? 'Excellent' : 
                                  student.hadirPercent >= 80 ? 'Good' : 
                                  student.hadirPercent >= 70 ? 'Fair' : 'Poor';
      }
    });
    
    return {
      success: true,
      analytics: {
        studentAnalytics: studentAnalytics,
        dateAnalytics: dateAnalytics,
        totalStudents: Object.keys(studentAnalytics).length,
        totalDays: Object.keys(dateAnalytics).length,
        totalRecords: records.length
      },
      message: `✅ Analisis detail berhasil untuk ${Object.keys(studentAnalytics).length} siswa`
    };
    
  } catch (error) {
    return {
      success: false,
      error: error.toString(),
      message: 'Gagal melakukan analisis detail'
    };
  }
}
Dashboard Absensi

Absensi Harian - 12 Pemasaran 1

Rekap Detail Kehadiran

0

0

0

0

Rekap Jumlah Kehadiran

Absensi Tanggal Mundur

📅 Absensi untuk:

Laporan & Analisis

0

0

0%

0

📊 Analisis Kehadiran

Data Siswa - 12 Pemasaran 1

Pengaturan Sistem

📋 Code.gs untuk Google Apps Script

Sistem Absensi Nasional

Dashboard Absensi

Absensi Harian - 12 Pemasaran 1

Rekap Detail Kehadiran

0

0

0

0

Rekap Jumlah Kehadiran

Absensi Tanggal Mundur

📅 Absensi untuk:

Laporan & Analisis

0

0

0%

0

📊 Analisis Kehadiran

Data Siswa - 12 Pemasaran 1

Pengaturan Sistem

📋 Code.gs untuk Google Apps Script

Menyinkronkan Data

Konfirmasi Logout
