Membangun Aplikasi CLI Pencatatan Kehadiran Terintegrasi Google Sheets dengan Python

August 15, 2025
oleh Edusoft Center

Daftar Isi

Pendahuluan
1.1 Latar Belakang
1.2 Tujuan Proyek
Persiapan Lingkungan
2.1 Instalasi Python dan Library
2.2 Mengaktifkan Google Sheets API
2.3 Membuat Service Account & Credential JSON
2.4 Memberi Akses ke Spreadsheet
Struktur Proyek
Source Code Python
Demo Penggunaan
5.1 Tampilan Menu
5.2 Ekspor CSV
Kesimpulan
6.1 Kelebihan Aplikasi
6.2 Potensi Pengembangan

1. Pendahuluan

1.1 Latar Belakang

Pencatatan kehadiran secara manual di buku atau Excel sering memakan waktu dan rawan kehilangan data. Dengan mengintegrasikan Google Sheets dan Python, kita bisa membuat sistem pencatatan kehadiran yang:

Aman di cloud
Mudah diakses dari mana saja
Otomatis menghitung persentase kehadiran

1.2 Tujuan Proyek

Membuat aplikasi berbasis CLI untuk:

Input data kehadiran
Menampilkan rekap presensi
Mengedit atau menghapus data
Mengekspor laporan ke CSV

2. Persiapan Lingkungan

2.1 Instalasi Python dan Library

Pastikan Python sudah terinstal. Install library berikut di terminal vscode :

pip install gspread oauth2client

2.2 Mengaktifkan Google Sheets API

Buka Google Cloud Console
Login dengan akun Google-mu
Atau langsung buka link berikut https://console.cloud.google.com/projectcreate

Isi Project name → Misalnya: Project Pencatatan Kehadiran
Location/Organization → Pilih default (kalau tidak ada organisasi).
Klik Create.
Tunggu beberapa detik sampai project selesai dibuat.

Pastikan project yang baru dibuat terpilih di pojok kiri atas seperti ini :

Buka halaman Google Sheets API :

https://console.cloud.google.com/apis/library/sheets.googleapis.com

Klik Enable dan tunggu sampai API aktif, lalu kamu akan diarahkan ke dashboard API.

2.3 Membuat Service Account & Credential JSON

Sekarang kita perlu membuat credentials supaya aplikasi bisa mengakses Google Sheets.

Pilihan yang disarankan (untuk CLI Python): Service Account

Pergi ke halaman Credentials:
https://console.cloud.google.com/apis/credentials
Klik Create Credentials → Service account

Setelah itu isi :
- Service account name → cli-sheets-service
- Service account ID otomatis terisi
Klik Create and Continue
Role → pilih Editor (supaya bisa baca & tulis data)

Setelah itu klik Done

Masih di halaman Service Accounts, klik nama service account yang baru dibuat

Pergi ke tab Keys
Klik Add Key → Create new key → Pilih JSON → Create

Setelah itu file.json akan terdownload dan kalian akan mendapatkan email service account kalian. Simpan baik-baik file dan url email kalian!

Contoh format file akan seperti ini : credentials-kalian.json

2.4 Memberi Akses ke Spreadsheet

Agar service account bisa membaca/menulis Google Sheets:

Buat Google Sheet baru dengan nama Sheet1
Tambahkan header NIP/NIS, Nama, Tanggal, Status, Persentase Kehadiran

Klik Share
Copy email dari service account (bisa dilihat di halaman service account di Google Cloud)
Paste di kolom share, pilih akses Editor, klik Send

3. Stuktur Proyek

Buat struktur proyek seperti ini :

PRESENSI_CLI/
│-- main.py
│-- presensi-xxxx.json
│-- README.md

4. Source Code Python

Masukkan kode berikut ke dalam file main.py :

import re
import csv
import os
from datetime import datetime
from typing import List, Dict, Any, Tuple, Optional

import gspread
from oauth2client.service_account import ServiceAccountCredentials
from gspread.exceptions import APIError

# ----------------- KONFIGURASI -----------------
SPREADSHEET_ID = "MASUKKAN_SPREADSHEET_ID"
CREDENTIALS_FILE = "credentialkalian.json"
SHEET_NAME = "Sheet1/namasheetkalian"

DEFAULT_HEADERS = ["NIP/NIS", "Nama", "Tanggal", "Status", "Persentase Kehadiran"]
STATUS_VALID = {"hadir", "izin", "alpha"}  # Status yang valid
DATE_FORMATS = ("%Y-%m-%d", "%d-%m-%Y", "%d/%m/%Y")  # Format tanggal yang didukung

# ----------------- KONEKSI GOOGLE SHEETS -----------------
def connect_to_sheet() -> gspread.Worksheet:
    scope = [
        "https://spreadsheets.google.com/feeds",
        "https://www.googleapis.com/auth/drive"
    ]
    try:
        creds = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, scope)
        client = gspread.authorize(creds)
        spreadsheet = client.open_by_key(SPREADSHEET_ID)
        return spreadsheet.worksheet(SHEET_NAME)
    except Exception as e:
        raise ConnectionError(f"Gagal terhubung ke Google Sheets: {str(e)}")

# ----------------- FUNGSI UTILITAS -----------------
def get_nip_from_record(record: Dict[str, Any]) -> str:
    return (record.get("nip_nis") or record.get("nip") or "").strip()

def get_nip_col(column_map: Dict[str, int]) -> Optional[int]:
    return column_map.get("nip_nis") or column_map.get("nip")

def canonical(s: Optional[str]) -> str:
    if s is None:
        return ""
    s = s.strip().lower()
    s = re.sub(r'[^0-9a-z]+', '_', s)
    return re.sub(r'^_+|_+$', '', s)

def ensure_header(sheet: gspread.Worksheet) -> None:
    try:
        first_row = sheet.row_values(1)
        if not first_row:
            sheet.append_row(DEFAULT_HEADERS)
            print("✅ Header default ditambahkan:", DEFAULT_HEADERS)
    except APIError as e:
        raise RuntimeError(f"Gagal memastikan header: {str(e)}")

def read_sheet_data(sheet: gspread.Worksheet) -> Tuple[List[Dict[str, Any]], Dict[str, int]]:
    try:
        values = sheet.get_all_values()
        if not values:
            return [], {}

        headers = values[0]
        column_map = {canonical(h): i + 1 for i, h in enumerate(headers)}
        
        records = []
        for row_idx, row in enumerate(values[1:], start=2):
            record = {
                canonical(headers[i]): (row[i] if i < len(row) else "")
                for i in range(len(headers))
            }
            record["__row"] = row_idx
            records.append(record)
            
        return records, column_map
    except APIError as e:
        raise RuntimeError(f"Gagal membaca data: {str(e)}")

def parse_date(date_str: str) -> str:
    date_str = date_str.strip()
    for fmt in DATE_FORMATS:
        try:
            return datetime.strptime(date_str, fmt).strftime("%Y-%m-%d")
        except ValueError:
            continue
    raise ValueError(
        f"Format tanggal tidak valid. Gunakan salah satu dari: {', '.join(DATE_FORMATS)}"
    )

def calculate_attendance_percentage(records: List[Dict[str, Any]]) -> Dict[str, Dict[str, Any]]:
    attendance_data = {}
    for record in records:
        nip = record.get("nip_nis", "").strip()
        status = record.get("status", "").strip().lower()
        if nip not in attendance_data:
            attendance_data[nip] = {
                "hadir": 0,
                "izin": 0,
                "alpha": 0,
                "nama": record.get("nama", ""),
                "total": 0
            }
        if status in attendance_data[nip]:
            attendance_data[nip][status] += 1
            attendance_data[nip]["total"] += 1
    return attendance_data

# ----------------- FUNGSI UTAMA -----------------
def update_attendance_percentages(sheet: gspread.Worksheet) -> None:
    try:
        records, column_map = read_sheet_data(sheet)
        if not records or not column_map:
            print("⚠ Tidak ada data untuk menghitung persentase.")
            return

        attendance_data = calculate_attendance_percentage(records)
        percentage_column = column_map.get("persentase_kehadiran")
        
        if not percentage_column:
            print("⚠ Kolom 'Persentase Kehadiran' tidak ditemukan.")
            return

        updates = []
        for record in records:
            nip = record.get("nip_nis", "").strip()
            if nip in attendance_data:
                total = attendance_data[nip]["total"]
                present = attendance_data[nip]["hadir"]
                percentage = round((present / total) * 100, 2) if total else 0.0
                updates.append((record["__row"], percentage_column, percentage))

        if updates:
            sheet.batch_update([{
                'range': gspread.utils.rowcol_to_a1(row, col),
                'values': [[value]]
            } for row, col, value in updates])
            
    except Exception as e:
        raise RuntimeError(f"Gagal memperbarui persentase kehadiran: {str(e)}")

def add_attendance_record(sheet: gspread.Worksheet) -> None:
    print("\n=== TAMBAH KEHADIRAN ===")
    try:
        nip = input("NIP/NIS          : ").strip()
        if not nip:
            raise ValueError("NIP/NIS tidak boleh kosong.")
        nama = input("Nama             : ").strip()
        if not nama:
            raise ValueError("Nama tidak boleh kosong.")
        tgl = input("Tanggal (YYYY-MM-DD): ").strip()
        tgl = parse_date(tgl)
        status = input("Status (Hadir/Izin/Alpha): ").strip().lower()
        if status not in STATUS_VALID:
            raise ValueError("Status harus salah satu dari: Hadir, Izin, Alpha.")
        new_row = [nip, nama, tgl, status.capitalize(), ""]
        sheet.append_row(new_row, value_input_option="USER_ENTERED")
        update_attendance_percentages(sheet)
        print("\n✅ Data berhasil dimasukkan.")
    except ValueError as e:
        print(f"\n❌ Kesalahan: {str(e)}")
    except APIError as e:
        print(f"\n❌ Gagal menambah data: {str(e)}")

def view_attendance_summary(sheet: gspread.Worksheet) -> None:
    """Display complete attendance records from the sheet."""
    print("\n=== REKAP KEHADIRAN LENGKAP ===")
    
    try:
        records, column_map = read_sheet_data(sheet)
        if not records:
            print("No attendance records found.")
            return
        
        # Ambil header sesuai sheet
        headers = sheet.row_values(1)
        print(" | ".join(f"{h:<20}" for h in headers))
        print("-" * (len(headers) * 23))  # garis pembatas
        
        # Tampilkan semua baris data
        for r in records:
            row_values = sheet.row_values(r["__row"])
            print(" | ".join(f"{val:<20}" for val in row_values))
            
    except Exception as e:
        print(f"\n❌ Failed to display summary: {str(e)}")


def edit_attendance_record(sheet: gspread.Worksheet) -> None:
    """Edit data kehadiran dengan memilih baris dari daftar hasil filter."""
    print("\n=== EDIT DATA KEHADIRAN ===")
    try:
        nip_input = input("Masukkan NIP/NIS (kosongkan untuk tampilkan semua): ").strip()
        tgl_input = input("Filter Tanggal (opsional, contoh 2025-11-17 / 17-11-2025 / 17/11/2025): ").strip()

        # Normalisasi tanggal input (jika diisi)
        tgl_filter = None
        if tgl_input:
            tgl_filter = parse_date(tgl_input)  # YYYY-MM-DD

        records, column_map = read_sheet_data(sheet)

        # Kumpulkan baris yang cocok
        matches = []
        for r in records:
            # Ambil NIP dari record (mendukung NIP/NIS atau NIP)
            nip_val = get_nip_from_record(r)

            if nip_input and nip_val != nip_input:
                continue

            if tgl_filter:
                # Normalisasi tanggal dari sheet juga supaya perbandingan adil
                rec_date_raw = (r.get("tanggal", "") or "").strip()
                try:
                    rec_date_norm = parse_date(rec_date_raw) if rec_date_raw else ""
                except ValueError:
                    rec_date_norm = rec_date_raw  # jika format aneh, bandingkan apa adanya
                if rec_date_norm != tgl_filter:
                    continue

            matches.append(r)

        if not matches:
            print("Tidak ada data yang cocok.")
            return

        # Tampilkan daftar pilihan baris
        headers = ["No."] + sheet.row_values(1)
        rows_to_show = []
        for i, r in enumerate(matches, start=1):
            row_vals = sheet.row_values(r["__row"])
            rows_to_show.append([str(i)] + row_vals)

        # Printer tabel rapi (pakai helper format_table bila sudah Anda tambah)
        try:
            format_table(headers, rows_to_show)
        except NameError:
            # fallback sederhana bila Anda belum menempel helper format_table
            print(" | ".join(headers))
            print("-" * 80)
            for row in rows_to_show:
                print(" | ".join(row))

        pilih = input("Pilih nomor baris yang akan diubah: ").strip()
        if not pilih.isdigit() or not (1 <= int(pilih) <= len(matches)):
            print("Pilihan tidak valid.")
            return

        target = matches[int(pilih) - 1]

        print("\nKolom yang bisa diubah:")
        print("1) Status   2) Tanggal   3) Nama   4) NIP/NIS")
        which = input("Pilih (1-4): ").strip()

        col = None
        new_val = None

        if which == "1":
            new_status = input("Status baru (Hadir/Izin/Alpha): ").strip().lower()
            if new_status not in STATUS_VALID:
                raise ValueError("Status harus salah satu dari: Hadir, Izin, Alpha.")
            new_val = new_status.capitalize()
            col = column_map.get("status")

        elif which == "2":
            new_val = parse_date(input("Tanggal baru: ").strip())  # normalisasi YYYY-MM-DD
            col = column_map.get("tanggal")

        elif which == "3":
            new_val = input("Nama baru: ").strip()
            if not new_val:
                raise ValueError("Nama tidak boleh kosong.")
            col = column_map.get("nama")

        elif which == "4":
            new_val = input("NIP/NIS baru: ").strip()
            if not new_val:
                raise ValueError("NIP/NIS tidak boleh kosong.")
            col = get_nip_col(column_map)

        else:
            print("Pilihan tidak dikenali.")
            return

        if not col:
            raise RuntimeError("Kolom yang dipilih tidak ditemukan di sheet.")

        # Lakukan update
        sheet.update_cell(target["__row"], col, new_val)

        # Rehitung persen jika perubahan memengaruhi agregasi
        if which in ("1", "4"):
            update_attendance_percentages(sheet)

        print("\n✅ Data berhasil diubah.")
    except ValueError as e:
        print(f"\n❌ Error: {str(e)}")
    except APIError as e:
        print(f"\n❌ Gagal mengubah data: {str(e)}")
    except Exception as e:
        print(f"\n❌ Terjadi kesalahan: {str(e)}")

def delete_attendance_records(sheet: gspread.Worksheet) -> None:
    """Hapus data kehadiran berdasarkan NIP/NIS dan (opsional) tanggal."""
    print("\n=== HAPUS DATA KEHADIRAN ===")
    try:
        nip_input = input("NIP/NIS yang dihapus: ").strip()
        if not nip_input:
            raise ValueError("NIP/NIS tidak boleh kosong.")

        tgl_input = input("Tanggal spesifik (kosongkan untuk hapus semua): ").strip()
        tgl_filter = None
        if tgl_input:
            tgl_filter = parse_date(tgl_input)  # normalisasi ke YYYY-MM-DD

        records, column_map = read_sheet_data(sheet)

        # Cari baris yang cocok
        matches = []
        for r in records:
            nip_val = get_nip_from_record(r)  # dukung 'NIP' atau 'NIP/NIS'
            if nip_val != nip_input:
                continue

            if tgl_filter:
                rec_date_raw = (r.get("tanggal", "") or "").strip()
                try:
                    rec_date_norm = parse_date(rec_date_raw) if rec_date_raw else ""
                except ValueError:
                    # Kalau format aneh di sheet, bandingkan apa adanya
                    rec_date_norm = rec_date_raw
                if rec_date_norm != tgl_filter:
                    continue

            matches.append(r)

        if not matches:
            print("Tidak ada baris yang cocok untuk dihapus.")
            return

        # Tampilkan baris-baris yang akan dipertimbangkan untuk dihapus
        headers = ["No."] + sheet.row_values(1)
        rows_to_show = []
        for i, r in enumerate(matches, start=1):
            rows_to_show.append([str(i)] + sheet.row_values(r["__row"]))

        # Cetak tabel rapi (pakai helper format_table jika ada)
        try:
            format_table(headers, rows_to_show)
        except NameError:
            print(" | ".join(headers))
            print("-" * 80)
            for row in rows_to_show:
                print(" | ".join(row))

        # Pilihan hapus
        to_delete_rows = []
        if len(matches) == 1:
            confirm = input("Hapus baris ini? (y/n): ").strip().lower()
            if confirm == "y":
                to_delete_rows = [matches[0]["__row"]]
        else:
            sel = input("Ketik 'semua' untuk hapus semua yang cocok, atau masukkan nomor (pisah koma): ").strip().lower()
            if sel == "semua":
                to_delete_rows = [m["__row"] for m in matches]
            else:
                idxs = []
                for token in re.split(r"[,\s]+", sel):
                    if token.isdigit():
                        k = int(token)
                        if 1 <= k <= len(matches):
                            idxs.append(k)
                to_delete_rows = [matches[i - 1]["__row"] for i in idxs]

        if not to_delete_rows:
            print("Dibatalkan.")
            return

        # Hapus dari bawah ke atas agar index aman
        for row_num in sorted(to_delete_rows, reverse=True):
            sheet.delete_rows(row_num)

        update_attendance_percentages(sheet)
        print(f"\n🗑 Data {len(to_delete_rows)} berhasil dihapus.")

    except ValueError as e:
        print(f"\n❌ Error: {str(e)}")
    except APIError as e:
        print(f"\n❌ Gagal menghapus data: {str(e)}")
    except Exception as e:
        print(f"\n❌ Terjadi kesalahan: {str(e)}")


def export_to_csv(sheet: gspread.Worksheet) -> None:
    """Ekspor data ke CSV berdasarkan NIP/NIS dan (opsional) tanggal."""
    print("\n=== EKSPOR KE CSV ===")
    try:
        nip_input = input("NIP/NIS yang diekspor: ").strip()
        if not nip_input:
            raise ValueError("NIP/NIS tidak boleh kosong.")

        tgl_input = input("Tanggal spesifik (opsional): ").strip()
        tgl_filter = None
        if tgl_input:
            tgl_filter = parse_date(tgl_input)

        records, column_map = read_sheet_data(sheet)

        matches = []
        for r in records:
            nip_val = get_nip_from_record(r)
            if nip_val != nip_input:
                continue

            if tgl_filter:
                rec_date_raw = (r.get("tanggal", "") or "").strip()
                try:
                    rec_date_norm = parse_date(rec_date_raw) if rec_date_raw else ""
                except ValueError:
                    rec_date_norm = rec_date_raw
                if rec_date_norm != tgl_filter:
                    continue

            matches.append(r)

        if not matches:
            print(f"Tidak ada catatan yang ditemukan untuk NIP/NIS {nip_input}.")
            return

        # Ambil header persis dari sheet
        headers = sheet.row_values(1)

        # Buat nama file
        if tgl_filter:
            filename = f"export_{nip_input}_{tgl_filter}.csv"
        else:
            filename = f"export_{nip_input}.csv"

        filepath = os.path.join(os.getcwd(), filename)

        # Simpan ke CSV
        with open(filepath, mode="w", newline="", encoding="utf-8") as f:
            writer = csv.writer(f)
            writer.writerow(headers)
            for r in matches:
                row_values = sheet.row_values(r["__row"])
                writer.writerow(row_values)

        print(f"\n✅ Data berhasil diekspor ke {filepath}")

    except ValueError as e:
        print(f"\n❌ Error: {str(e)}")
    except Exception as e:
        print(f"\n❌ Terjadi kesalahan: {str(e)}")


# ----------------- MAIN MENU -----------------
def display_menu() -> None:
    """Display the main menu options."""
    print("""
==== MENU PRESENSI ====
1. Tambahkan Kehadiran
2. Lihat Rekap Kehadiran
3. Edit Data Kehadiran
4. Hapus Data Kehadiran
5. Hitung Ulang Persentase Kehadiran
6. Ekspor ke CSV
7. Keluar
""")

def main() -> None:
    """Main program loop."""
    print("Koneksi ke Google Sheets...")
    try:
        sheet = connect_to_sheet()
        ensure_header(sheet)
    except Exception as e:
        print(f"Gagal menginisialisasi: {str(e)}")
        return

    while True:
        display_menu()
        choice = input("Pilih menu (1-7): ").strip()
        
        try:
            if choice == "1":
                add_attendance_record(sheet)
            elif choice == "2":
                view_attendance_summary(sheet)
            elif choice == "3":
                edit_attendance_record(sheet)
            elif choice == "4":
                delete_attendance_records(sheet)
            elif choice == "5":
                update_attendance_percentages(sheet)
                print("\n✅ Persentase kehadiran dihitung ulang.")
            elif choice == "6":
                export_to_csv(sheet)
            elif choice == "7":
                print("\nTerima kasih, program selesai.")
                break
            else:
                print("\n❌ Pilihan tidak valid. Silakan pilih 1-7.")
                
            input("\nTekan ENTER untuk kembali ke menu...")
        except Exception as e:
            print(f"\n❌ Terjadi kesalahan: {str(e)}")
            input("Tekan ENTER untuk melanjutkan...")

if __name__ == "__main__":
    main()

5. Demo Penggunaan

Jalankan aplikasi dengan ketikan python main.py di terminal vscode

5.1 Tampilan Menu Aplikasi

Tampilan menu akan tampil seperti ini secara keseluruhan :

5.2 Ekspor CSV

Kalian bisa ekspor data yang kalian inginkan dengan format CSV

Data yang di ekspor akan masuk ke folder yang sama dengan aplikasi ini, kalian juga bisa melihat nya di struktur file projek vscode kalian.