PROMPT
Buatkan aplikasi perpustakaan berbasis Google Apps Script dengan Google Sheets sebagai database dan HTML frontend. Sebelum login berikan landing page yang menarik responsif. Struktur spreadsheet tambahkan di code.gs dengan function setupdatabase
Fitur:
Login multi role (admin, pegawai, anggota, guest)
CRUD buku & anggota
Peminjaman & pengembalian buku
Hitung denda otomatis
Cetak kartu anggota PDF + QR Code
Dashboard statistik
Laporan buku keluar masuk
Gunakan:
Struktur modular GAS
HTML + Bootstrap
google.script.run
Output:
Kode lengkap backend & frontend
Struktur spreadsheet
Cara deploy
Code.gs
/**
* Aplikasi Perpustakaan Digital - Backend
* Mengelola komunikasi antara Frontend dan Google Sheets
*/
const SS = SpreadsheetApp.getActiveSpreadsheet();
const APP_TITLE = "Pustaka Digital";
function doGet() {
return HtmlService.createTemplateFromFile('index')
.evaluate()
.setTitle(APP_TITLE)
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
/**
* Inisialisasi struktur spreadsheet secara otomatis
*/
function setupDatabase() {
const sheets = {
'users': ['id', 'username', 'password', 'role', 'name', 'email'],
'books': ['id', 'title', 'author', 'category', 'stock', 'isbn', 'image_url'],
'members': ['id', 'name', 'phone', 'address', 'joined_date', 'status'],
'transactions': ['id', 'book_id', 'member_id', 'borrow_date', 'due_date', 'return_date', 'fine', 'status'],
'settings': ['key', 'value']
};
for (let name in sheets) {
let sheet = SS.getSheetByName(name);
if (!sheet) {
sheet = SS.insertSheet(name);
sheet.appendRow(sheets[name]);
// Format header agar tebal
sheet.getRange(1, 1, 1, sheets[name].length).setFontWeight("bold");
}
}
// Tambahkan data awal jika sheet users baru dibuat (hanya ada header)
const userSheet = SS.getSheetByName('users');
if (userSheet.getLastRow() === 1) {
userSheet.appendRow(['1', 'admin', 'admin123', 'admin', 'Super Admin', 'admin@mail.com']);
}
// Tambahkan setting awal jika sheet settings baru dibuat
const settingsSheet = SS.getSheetByName('settings');
if (settingsSheet.getLastRow() === 1) {
settingsSheet.appendRow(['fine_per_day', '1000']);
}
return "Database berhasil disiapkan! Silakan refresh halaman.";
}
// --- AUTHENTICATION ---
function login(username, password) {
const sheet = SS.getSheetByName('users');
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][1] === username && data[i][2] === password) {
return {
success: true,
user: {
id: data[i][0],
username: data[i][1],
role: data[i][3],
name: data[i][4]
}
};
}
}
return { success: false, message: "Username atau password salah." };
}
// --- DATA FETCHING ---
function getData(sheetName) {
const sheet = SS.getSheetByName(sheetName);
const data = sheet.getDataRange().getValues();
const headers = data[0];
const rows = data.slice(1);
return rows.map(row => {
let obj = {};
headers.forEach((header, index) => {
obj[header] = row[index];
});
return obj;
});
}
// --- CRUD OPERATIONS ---
function saveBook(bookData) {
const sheet = SS.getSheetByName('books');
if (bookData.id) {
// Update
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][0] == bookData.id) {
sheet.getRange(i + 1, 2, 1, 6).setValues([[
bookData.title, bookData.author, bookData.category,
bookData.stock, bookData.isbn, bookData.image_url
]]);
return "Buku berhasil diperbarui";
}
}
} else {
// Create
const newId = new Date().getTime();
sheet.appendRow([
newId, bookData.title, bookData.author, bookData.category,
bookData.stock, bookData.isbn, bookData.image_url
]);
return "Buku berhasil ditambah";
}
}
// --- TRANSAKSI (Peminjaman & Pengembalian) ---
function processBorrow(memberId, bookId, days) {
const sheet = SS.getSheetByName('transactions');
const bookSheet = SS.getSheetByName('books');
const today = new Date();
const dueDate = new Date();
dueDate.setDate(today.getDate() + parseInt(days));
// Cek Stok
const books = getData('books');
const book = books.find(b => b.id == bookId);
if (book.stock <= 0) return { success: false, message: "Stok buku habis" };
// Catat Transaksi
sheet.appendRow([
"TRX-" + new Date().getTime(),
bookId, memberId, today, dueDate, "", 0, "Borrowed"
]);
// Kurangi Stok
const bookData = bookSheet.getDataRange().getValues();
for (let i = 1; i < bookData.length; i++) {
if (bookData[i][0] == bookId) {
bookSheet.getRange(i + 1, 5).setValue(bookData[i][4] - 1);
break;
}
}
return { success: true, message: "Peminjaman berhasil" };
}
function processReturn(trxId) {
const sheet = SS.getSheetByName('transactions');
const bookSheet = SS.getSheetByName('books');
const data = sheet.getDataRange().getValues();
const today = new Date();
const settings = getData('settings');
const finePerDay = settings.find(s => s.key === 'fine_per_day')?.value || 1000;
for (let i = 1; i < data.length; i++) {
if (data[i][0] === trxId && data[i][7] === "Borrowed") {
const dueDate = new Date(data[i][4]);
let fine = 0;
if (today > dueDate) {
const diffTime = Math.abs(today - dueDate);
const diffDays = Math.ceil(diffTime / (1000 * 60 * 60 * 24));
fine = diffDays * finePerDay;
}
// Update Transaksi
sheet.getRange(i + 1, 6).setValue(today);
sheet.getRange(i + 1, 7).setValue(fine);
sheet.getRange(i + 1, 8).setValue("Returned");
// Tambah Stok Buku
const bookId = data[i][1];
const bookData = bookSheet.getDataRange().getValues();
for (let j = 1; j < bookData.length; j++) {
if (bookData[j][0] == bookId) {
bookSheet.getRange(j + 1, 5).setValue(bookData[j][4] + 1);
break;
}
}
return { success: true, fine: fine };
}
}
}
// --- DASHBOARD STATS ---
function getDashboardStats() {
const books = getData('books');
const members = getData('members');
const trans = getData('transactions');
return {
totalBooks: books.length,
totalMembers: members.length,
activeLoans: trans.filter(t => t.status === 'Borrowed').length,
totalFines: trans.reduce((sum, t) => sum + (parseFloat(t.fine) || 0), 0)
};
}
// --- GENERATE PDF CARD (MEMBER) ---
function generateMemberCard(memberId) {
const members = getData('members');
const m = members.find(item => item.id == memberId);
if (!m) return "Member not found";
const qrUrl = `https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=${m.id}`;
const html = `
<div style="width: 350px; border: 2px solid #333; padding: 20px; font-family: sans-serif; border-radius: 10px; background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);">
<h2 style="text-align: center; margin-top: 0; color: #2c3e50;">KARTU ANGGOTA</h2>
<hr>
<div style="display: flex; gap: 15px; align-items: center;">
<img src="${qrUrl}" width="100" height="100" style="border: 1px solid #ccc;">
<div>
<p><strong>Nama:</strong> ${m.name}</p>
<p><strong>ID:</strong> ${m.id}</p>
<p><strong>Status:</strong> ${m.status}</p>
</div>
</div>
<p style="font-size: 10px; text-align: center; margin-top: 20px;">Berlaku di Seluruh Cabang Pustaka Digital</p>
</div>
`;
const blob = Utilities.newBlob(html, "text/html", "kartu.html");
const pdf = blob.getAs("application/pdf").setName(`Kartu_${m.name}.pdf`);
return "data:application/pdf;base64," + Utilities.base64Encode(pdf.getBytes());
}
index.html
<!DOCTYPE html>
<html lang="id">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Pustaka Digital - Modern Library System</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css">
<style>
:root {
--primary-color: #2c3e50;
--secondary-color: #34495e;
--accent-color: #3498db;
}
body { background-color: #f8f9fa; font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; }
.navbar { background-color: var(--primary-color) !important; }
.hero-section {
background: linear-gradient(rgba(0,0,0,0.6), rgba(0,0,0,0.6)), url('https://images.unsplash.com/photo-1507842217343-583bb7270b66?ixlib=rb-1.2.1&auto=format&fit=crop&w=1350&q=80');
background-size: cover;
background-position: center;
height: 80vh;
display: flex;
align-items: center;
color: white;
}
.sidebar { min-height: calc(100vh - 56px); background: #fff; box-shadow: 2px 0 5px rgba(0,0,0,0.05); }
.nav-link { color: #333; padding: 12px 20px; transition: 0.3s; }
.nav-link:hover, .nav-link.active { background: var(--accent-color); color: white; }
.stat-card { border-radius: 15px; border: none; box-shadow: 0 4px 6px rgba(0,0,0,0.1); transition: transform 0.3s; }
.stat-card:hover { transform: translateY(-5px); }
#loadingOverlay {
position: fixed; top: 0; left: 0; width: 100%; height: 100%;
background: rgba(255,255,255,0.8); display: none; justify-content: center;
align-items: center; z-index: 9999;
}
</style>
</head>
<body>
<div id="loadingOverlay">
<div class="spinner-border text-primary" role="status"></div>
</div>
<!-- LANDING PAGE -->
<div id="landingPage">
<nav class="navbar navbar-expand-lg navbar-dark fixed-top">
<div class="container">
<a class="navbar-brand font-weight-bold" href="#"><i class="fas fa-book-open me-2"></i>Pustaka Digital</a>
<button class="btn btn-outline-light ms-auto" onclick="showLogin()">Login</button>
</div>
</nav>
<section class="hero-section text-center">
<div class="container">
<h1 class="display-3 fw-bold mb-4">Jendela Dunia di Ujung Jari Anda</h1>
<p class="lead mb-5">Akses ribuan koleksi buku secara mudah, cepat, dan modern.</p>
<div class="d-flex justify-content-center gap-3">
<button class="btn btn-primary btn-lg px-5 rounded-pill" onclick="showCatalog()">Lihat Katalog</button>
<button class="btn btn-outline-light btn-lg px-5 rounded-pill" onclick="showLogin()">Mulai Sekarang</button>
</div>
</div>
</section>
</div>
<!-- LOGIN PAGE -->
<div id="loginPage" class="d-none">
<div class="container mt-5 pt-5">
<div class="row justify-content-center">
<div class="col-md-4">
<div class="card shadow">
<div class="card-body p-5">
<h3 class="text-center mb-4">Login Perpustakaan</h3>
<form id="loginForm">
<div class="mb-3">
<label>Username</label>
<input type="text" id="username" class="form-control" required>
</div>
<div class="mb-3">
<label>Password</label>
<input type="password" id="password" class="form-control" required>
</div>
<button type="submit" class="btn btn-primary w-100 py-2">Masuk</button>
</form>
<button class="btn btn-link w-100 mt-2" onclick="showLanding()">Kembali</button>
</div>
</div>
</div>
</div>
</div>
</div>
<!-- DASHBOARD MAIN -->
<div id="appShell" class="d-none">
<nav class="navbar navbar-expand-lg navbar-dark">
<div class="container-fluid">
<span class="navbar-brand"><i class="fas fa-book-open me-2"></i>Admin Panel</span>
<div class="ms-auto d-flex align-items-center">
<span class="text-white me-3" id="userDisplayName"></span>
<button class="btn btn-danger btn-sm" onclick="logout()">Logout</button>
</div>
</div>
</nav>
<div class="container-fluid">
<div class="row">
<nav id="sidebarMenu" class="col-md-3 col-lg-2 sidebar pt-3">
<div class="position-sticky">
<ul class="nav flex-column">
<li class="nav-item">
<a class="nav-link active" href="#" onclick="showSection('dashboard')">
<i class="fas fa-home me-2"></i> Dashboard
</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#" onclick="showSection('books')">
<i class="fas fa-book me-2"></i> Data Buku
</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#" onclick="showSection('members')">
<i class="fas fa-users me-2"></i> Anggota
</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#" onclick="showSection('loans')">
<i class="fas fa-exchange-alt me-2"></i> Peminjaman
</a>
</li>
</ul>
</div>
</nav>
<main class="col-md-9 ms-sm-auto col-lg-10 px-md-4 pt-4">
<!-- Section: Dashboard -->
<div id="section-dashboard" class="content-section">
<h2 class="mb-4">Statistik Perpustakaan</h2>
<div class="row g-4">
<div class="col-md-3">
<div class="card stat-card bg-primary text-white p-3">
<div class="d-flex justify-content-between">
<div><h5>Total Buku</h5><h2 id="stat-books">0</h2></div>
<i class="fas fa-book fa-3x opacity-50"></i>
</div>
</div>
</div>
<div class="col-md-3">
<div class="card stat-card bg-success text-white p-3">
<div class="d-flex justify-content-between">
<div><h5>Anggota</h5><h2 id="stat-members">0</h2></div>
<i class="fas fa-users fa-3x opacity-50"></i>
</div>
</div>
</div>
<div class="col-md-3">
<div class="card stat-card bg-warning text-white p-3">
<div class="d-flex justify-content-between">
<div><h5>Peminjaman</h5><h2 id="stat-loans">0</h2></div>
<i class="fas fa-clock fa-3x opacity-50"></i>
</div>
</div>
</div>
<div class="col-md-3">
<div class="card stat-card bg-danger text-white p-3">
<div class="d-flex justify-content-between">
<div><h5>Denda Terkumpul</h5><h2 id="stat-fines">0</h2></div>
<i class="fas fa-money-bill fa-3x opacity-50"></i>
</div>
</div>
</div>
</div>
</div>
<!-- Section: Books -->
<div id="section-books" class="content-section d-none">
<div class="d-flex justify-content-between align-items-center mb-3">
<h2>Koleksi Buku</h2>
<button class="btn btn-primary" onclick="openBookModal()">Tambah Buku</button>
</div>
<div class="table-responsive">
<table class="table table-hover bg-white rounded shadow-sm">
<thead class="table-light">
<tr>
<th>ID</th>
<th>Judul</th>
<th>Pengarang</th>
<th>Kategori</th>
<th>Stok</th>
<th>Aksi</th>
</tr>
</thead>
<tbody id="bookTableBody"></tbody>
</table>
</div>
</div>
<!-- Section: Members -->
<div id="section-members" class="content-section d-none">
<div class="d-flex justify-content-between align-items-center mb-3">
<h2>Daftar Anggota</h2>
<button class="btn btn-success">Registrasi Anggota Baru</button>
</div>
<div class="table-responsive">
<table class="table table-hover bg-white rounded shadow-sm">
<thead class="table-light">
<tr>
<th>ID</th>
<th>Nama</th>
<th>Telepon</th>
<th>Status</th>
<th>Aksi</th>
</tr>
</thead>
<tbody id="memberTableBody"></tbody>
</table>
</div>
</div>
<!-- Section: Loans -->
<div id="section-loans" class="content-section d-none">
<h2>Peminjaman & Pengembalian</h2>
<div class="card mt-3">
<div class="card-body">
<form id="loanForm" class="row g-3">
<div class="col-md-4">
<label>ID Member</label>
<input type="text" id="loanMemberId" class="form-control" placeholder="Masukkan ID Anggota">
</div>
<div class="col-md-4">
<label>ID Buku</label>
<input type="text" id="loanBookId" class="form-control" placeholder="ID Buku">
</div>
<div class="col-md-2">
<label>Durasi (Hari)</label>
<input type="number" id="loanDays" class="form-control" value="7">
</div>
<div class="col-md-2 d-flex align-items-end">
<button type="button" class="btn btn-primary w-100" onclick="handleBorrow()">Proses Pinjam</button>
</div>
</form>
</div>
</div>
<h5 class="mt-4">Transaksi Aktif</h5>
<table class="table mt-2 bg-white rounded">
<thead>
<tr>
<th>Trx ID</th>
<th>Member</th>
<th>Buku</th>
<th>Tgl Kembali</th>
<th>Aksi</th>
</tr>
</thead>
<tbody id="activeLoansTable"></tbody>
</table>
</div>
</main>
</div>
</div>
</div>
<!-- Modal Buku -->
<div class="modal fade" id="bookModal" tabindex="-1">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Data Buku</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal"></button>
</div>
<div class="modal-body">
<form id="bookForm">
<input type="hidden" id="bookId">
<div class="mb-2"><label>Judul</label><input type="text" id="bookTitle" class="form-control" required></div>
<div class="mb-2"><label>Pengarang</label><input type="text" id="bookAuthor" class="form-control"></div>
<div class="mb-2"><label>Kategori</label><input type="text" id="bookCategory" class="form-control"></div>
<div class="mb-2"><label>Stok</label><input type="number" id="bookStock" class="form-control"></div>
<div class="mb-2"><label>ISBN</label><input type="text" id="bookIsbn" class="form-control"></div>
</form>
</div>
<div class="modal-footer">
<button class="btn btn-secondary" data-bs-dismiss="modal">Batal</button>
<button class="btn btn-primary" onclick="handleSaveBook()">Simpan</button>
</div>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<script>
let currentUser = null;
const loader = document.getElementById('loadingOverlay');
function toggleLoader(show) { loader.style.display = show ? 'flex' : 'none'; }
// --- NAVIGATION ---
function showLogin() {
document.getElementById('landingPage').classList.add('d-none');
document.getElementById('loginPage').classList.remove('d-none');
}
function showLanding() {
document.getElementById('loginPage').classList.add('d-none');
document.getElementById('landingPage').classList.remove('d-none');
}
function showSection(sectionId) {
document.querySelectorAll('.content-section').forEach(el => el.classList.add('d-none'));
document.getElementById('section-' + sectionId).classList.remove('d-none');
document.querySelectorAll('.nav-link').forEach(el => el.classList.remove('active'));
event?.currentTarget?.classList?.add('active');
if (sectionId === 'dashboard') refreshStats();
if (sectionId === 'books') loadBooks();
if (sectionId === 'members') loadMembers();
if (sectionId === 'loans') loadActiveLoans();
}
// --- AUTH ---
document.getElementById('loginForm').onsubmit = function(e) {
e.preventDefault();
toggleLoader(true);
const u = document.getElementById('username').value;
const p = document.getElementById('password').value;
google.script.run.withSuccessHandler(res => {
toggleLoader(false);
if (res.success) {
currentUser = res.user;
document.getElementById('loginPage').classList.add('d-none');
document.getElementById('appShell').classList.remove('d-none');
document.getElementById('userDisplayName').innerText = `Halo, ${res.user.name} (${res.user.role})`;
showSection('dashboard');
} else {
alert(res.message);
}
}).login(u, p);
};
function logout() {
currentUser = null;
document.getElementById('appShell').classList.add('d-none');
showLanding();
}
// --- LOGIC DATA ---
function refreshStats() {
google.script.run.withSuccessHandler(stats => {
document.getElementById('stat-books').innerText = stats.totalBooks;
document.getElementById('stat-members').innerText = stats.totalMembers;
document.getElementById('stat-loans').innerText = stats.activeLoans;
document.getElementById('stat-fines').innerText = 'Rp ' + stats.totalFines.toLocaleString();
}).getDashboardStats();
}
function loadBooks() {
toggleLoader(true);
google.script.run.withSuccessHandler(books => {
toggleLoader(false);
const tbody = document.getElementById('bookTableBody');
tbody.innerHTML = books.map(b => `
<tr>
<td>${b.id}</td>
<td>${b.title}</td>
<td>${b.author}</td>
<td>${b.category}</td>
<td>${b.stock}</td>
<td>
<button class="btn btn-sm btn-info" onclick="editBook(${b.id})"><i class="fas fa-edit"></i></button>
</td>
</tr>
`).join('');
}).getData('books');
}
function loadMembers() {
google.script.run.withSuccessHandler(members => {
const tbody = document.getElementById('memberTableBody');
tbody.innerHTML = members.map(m => `
<tr>
<td>${m.id}</td>
<td>${m.name}</td>
<td>${m.phone}</td>
<td><span class="badge bg-success">${m.status}</span></td>
<td>
<button class="btn btn-sm btn-dark" onclick="printCard(${m.id})">Cetak Kartu</button>
</td>
</tr>
`).join('');
}).getData('members');
}
function loadActiveLoans() {
google.script.run.withSuccessHandler(trans => {
const active = trans.filter(t => t.status === 'Borrowed');
document.getElementById('activeLoansTable').innerHTML = active.map(t => `
<tr>
<td>${t.id}</td>
<td>${t.member_id}</td>
<td>${t.book_id}</td>
<td>${new Date(t.due_date).toLocaleDateString()}</td>
<td>
<button class="btn btn-sm btn-success" onclick="handleReturn('${t.id}')">Kembalikan</button>
</td>
</tr>
`).join('');
}).getData('transactions');
}
function handleBorrow() {
const mid = document.getElementById('loanMemberId').value;
const bid = document.getElementById('loanBookId').value;
const days = document.getElementById('loanDays').value;
if(!mid || !bid) return alert("Isi ID Member dan Buku");
toggleLoader(true);
google.script.run.withSuccessHandler(res => {
toggleLoader(false);
alert(res.message);
loadActiveLoans();
}).processBorrow(mid, bid, days);
}
function handleReturn(trxId) {
if(!confirm("Proses pengembalian buku?")) return;
toggleLoader(true);
google.script.run.withSuccessHandler(res => {
toggleLoader(false);
alert(`Buku Berhasil Dikembalikan! Denda: Rp ${res.fine}`);
loadActiveLoans();
}).processReturn(trxId);
}
function printCard(mid) {
toggleLoader(true);
google.script.run.withSuccessHandler(base64 => {
toggleLoader(false);
const link = document.createElement('a');
link.href = base64;
link.download = `Kartu_Anggota_${mid}.pdf`;
link.click();
}).generateMemberCard(mid);
}
// Modal helpers
const bookModal = new bootstrap.Modal(document.getElementById('bookModal'));
function openBookModal() {
document.getElementById('bookForm').reset();
document.getElementById('bookId').value = "";
bookModal.show();
}
function handleSaveBook() {
const data = {
id: document.getElementById('bookId').value,
title: document.getElementById('bookTitle').value,
author: document.getElementById('bookAuthor').value,
category: document.getElementById('bookCategory').value,
stock: document.getElementById('bookStock').value,
isbn: document.getElementById('bookIsbn').value,
image_url: ""
};
toggleLoader(true);
google.script.run.withSuccessHandler(msg => {
toggleLoader(false);
alert(msg);
bookModal.hide();
loadBooks();
}).saveBook(data);
}
</script>
</body>
</html>
Tidak ada komentar:
Posting Komentar