https://docs.google.com/spreadsheets/d/1y1Vk8fnrq5fj0zQle4PPO2SA8Cc-6tuhwETuDRER9k4/edit?gid=0#gid=0
PROMPT
Saya akan membuat aplikasi dengan google apps script, tolong buatkan file code.gs dan file index.html. Saya akan membuat aplikasi buku tamu yang dapat menyimpan data pengunjung
Saya menggunakan spreadsheet untuk menyimpan databasenya. Buatkan pula tombol untuk menggenerate database di spreadsheet.
Code.gs
/**
* Fungsi utama untuk melayani tampilan web app (index.html)
*/
function doGet() {
return HtmlService.createHtmlOutputFromFile('index')
.setTitle('Buku Tamu Digital')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
.addMetaTag('viewport', 'width=device-width, initial-scale=1.0');
}
/**
* Mendapatkan objek Spreadsheet berdasarkan ID spesifik yang Anda berikan.
* Ini memastikan data dapat diakses meskipun skrip dibuat secara standalone.
*/
function getSpreadsheet() {
const SPREADSHEET_ID = "1y1Vk8fnrq5fj0zQle4PPO2SA8Cc-6tuhwETuDRER9k4";
try {
return SpreadsheetApp.openById(SPREADSHEET_ID);
} catch (e) {
try {
// Fallback ke active spreadsheet jika skrip terikat langsung (container-bound)
return SpreadsheetApp.getActiveSpreadsheet();
} catch (err) {
throw new Error("Gagal mengakses Spreadsheet dengan ID: " + SPREADSHEET_ID + ". Pastikan Anda telah memberikan izin akses (autorisasi) saat deploy skrip.");
}
}
}
/**
* Fungsi untuk menginisialisasi / membuat database sheet baru secara otomatis.
* Fungsi ini dipanggil dari tombol 'Setup Database' di antarmuka web.
*/
function setupDatabase() {
try {
const ss = getSpreadsheet();
let sheet = ss.getSheetByName("Buku Tamu");
// Jika sheet belum ada, buat sheet baru
if (!sheet) {
sheet = ss.insertSheet("Buku Tamu");
}
// Periksa apakah sheet masih kosong (belum ada baris data atau header)
if (sheet.getLastRow() === 0) {
const headers = ['No', 'Tanggal & Waktu', 'Nama Lengkap', 'Email', 'Instansi / Organisasi', 'Keperluan', 'Pesan & Kesan'];
sheet.appendRow(headers);
// Mengatur style header agar terlihat profesional
const range = sheet.getRange(1, 1, 1, headers.length);
range.setFontWeight("bold");
range.setBackground("#4F46E5"); // Warna Indigo 600
range.setFontColor("#FFFFFF"); // Warna teks putih
range.setHorizontalAlignment("center");
// Membekukan baris pertama (freeze row)
sheet.setFrozenRows(1);
// Auto-resize lebar kolom
sheet.autoResizeColumns(1, headers.length);
return { success: true, message: "Database 'Buku Tamu' berhasil dibuat dengan struktur kolom yang sesuai!" };
} else {
return { success: true, message: "Database 'Buku Tamu' sudah ada sebelumnya dan sudah siap digunakan." };
}
} catch (error) {
return { success: false, message: "Gagal membuat database: " + error.toString() };
}
}
/**
* Menyimpan data pengunjung baru ke Google Sheet.
* @param {Object} guest - Data pengunjung dari form input
*/
function submitGuestData(guest) {
try {
const ss = getSpreadsheet();
let sheet = ss.getSheetByName("Buku Tamu");
// Jika sheet database tidak sengaja terhapus, buat otomatis saat submit
if (!sheet) {
setupDatabase();
sheet = ss.getSheetByName("Buku Tamu");
}
const lastRow = sheet.getLastRow();
// Tentukan nomor urut berikutnya (baris header diabaikan)
const nextNo = lastRow <= 1 ? 1 : lastRow;
// Mendapatkan zona waktu lokal script dan waktu saat ini
const localTime = new Date();
const formattedDate = Utilities.formatDate(localTime, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
const rowData = [
nextNo,
formattedDate,
guest.nama,
guest.email,
guest.instansi,
guest.keperluan,
guest.pesan || "-"
];
sheet.appendRow(rowData);
// Atur kembali ukuran kolom secara otomatis agar rapi
sheet.autoResizeColumns(1, rowData.length);
return { success: true, message: "Terima kasih! Data kunjungan Anda berhasil disimpan." };
} catch (error) {
return { success: false, message: "Gagal menyimpan data: " + error.toString() };
}
}
/**
* Mengambil daftar pengunjung terbaru untuk ditampilkan di aplikasi
* Mengembalikan maksimal 10 data kunjungan terakhir.
*/
function getGuests() {
try {
const ss = getSpreadsheet();
const sheet = ss.getSheetByName("Buku Tamu");
if (!sheet) {
throw new Error("DATABASE_NOT_FOUND");
}
const lastRow = sheet.getLastRow();
if (lastRow <= 1) {
return []; // Database kosong (hanya berisi baris header)
}
const rows = sheet.getDataRange().getValues();
const guests = [];
// Ambil maksimal 10 data terakhir secara mundur
const startRow = Math.max(1, rows.length - 10);
for (let i = rows.length - 1; i >= startRow; i--) {
const row = rows[i];
if (i === 0) continue; // Abaikan baris header
// Amankan konversi data Tanggal (Date Object) menjadi String
let tanggalStr = "-";
if (row[1] instanceof Date) {
try {
tanggalStr = Utilities.formatDate(row[1], Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
} catch(err) {
tanggalStr = row[1].toString();
}
} else if (row[1]) {
tanggalStr = row[1].toString();
}
guests.push({
no: row[0] ? row[0].toString() : "",
tanggal: tanggalStr,
nama: row[2] ? row[2].toString() : "",
email: row[3] ? row[3].toString() : "",
instansi: row[4] ? row[4].toString() : "",
keperluan: row[5] ? row[5].toString() : "",
pesan: row[6] ? row[6].toString() : ""
});
}
return guests;
} catch (error) {
// Teruskan pesan error spesifik ke frontend agar bisa dibaca user
throw new Error(error.message || error.toString());
}
}
index.html
<!DOCTYPE html>
<html lang="id">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Buku Tamu Digital</title>
<!-- Tailwind CSS CDN untuk Styling Modern -->
<script src="https://cdn.tailwindcss.com"></script>
<!-- Google Fonts: Inter untuk Typografi Clean -->
<link href="https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&display=swap" rel="stylesheet">
<!-- Lucide Icons -->
<script src="https://unpkg.com/lucide@latest"></script>
<style>
body {
font-family: 'Inter', sans-serif;
}
/* Kustomisasi scrollbar untuk bagian daftar pengunjung */
::-webkit-scrollbar {
width: 6px;
}
::-webkit-scrollbar-track {
background: #f1f1f1;
}
::-webkit-scrollbar-thumb {
background: #cbd5e1;
border-radius: 4px;
}
::-webkit-scrollbar-thumb:hover {
background: #94a3b8;
}
</style>
</head>
<body class="bg-slate-50 min-h-screen text-slate-800 antialiased flex flex-col justify-between">
<!-- Container Notifikasi (Toast) Kustom -->
<div id="toast-container" class="fixed top-5 right-5 z-50 flex flex-col gap-3 max-w-sm w-full"></div>
<!-- Header & Navigasi -->
<header class="bg-white border-b border-slate-200 sticky top-0 z-40">
<div class="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8 h-16 flex items-center justify-between">
<div class="flex items-center gap-3">
<div class="bg-indigo-600 p-2 rounded-lg text-white">
<i data-lucide="book-open" class="w-6 h-6"></i>
</div>
<div>
<h1 class="text-lg font-bold text-slate-900 leading-tight">Buku Tamu Digital</h1>
<p class="text-xs text-slate-500">Sistem Pencatatan Pengunjung Instansi</p>
</div>
</div>
<!-- Tombol Generate Database Quick-Access -->
<div>
<button
onclick="triggerSetupDatabase()"
id="btn-quick-setup"
class="flex items-center gap-2 px-3 py-1.5 bg-emerald-50 text-emerald-700 border border-emerald-200 hover:bg-emerald-100 rounded-md text-xs font-semibold transition-all duration-200 focus:outline-none focus:ring-2 focus:ring-emerald-500 focus:ring-offset-2"
>
<i data-lucide="database-backup" class="w-4 h-4"></i>
<span>Setup Database</span>
</button>
</div>
</div>
</header>
<!-- Konten Utama -->
<main class="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8 py-8 flex-grow w-full">
<div class="grid grid-cols-1 lg:grid-cols-12 gap-8">
<!-- Kolom Kiri: Form Input Data -->
<section class="lg:col-span-5 bg-white p-6 rounded-2xl border border-slate-200 shadow-sm flex flex-col justify-between h-fit">
<div>
<div class="flex items-center gap-2 mb-6">
<i data-lucide="user-plus" class="w-5 h-5 text-indigo-600"></i>
<h2 class="text-lg font-semibold text-slate-900">Formulir Kunjungan</h2>
</div>
<form id="form-guest" onsubmit="handleFormSubmit(event)" class="space-y-4">
<!-- Nama Lengkap -->
<div>
<label for="input-nama" class="block text-sm font-medium text-slate-700 mb-1">Nama Lengkap <span class="text-red-500">*</span></label>
<div class="relative">
<span class="absolute inset-y-0 left-0 flex items-center pl-3 text-slate-400">
<i data-lucide="user" class="w-4 h-4"></i>
</span>
<input
type="text"
id="input-nama"
required
placeholder="Contoh: John Doe"
class="w-full pl-10 pr-4 py-2 border border-slate-300 rounded-lg focus:ring-2 focus:ring-indigo-500 focus:border-indigo-500 text-sm placeholder:text-slate-400 focus:outline-none transition-all"
>
</div>
</div>
<!-- Email -->
<div>
<label for="input-email" class="block text-sm font-medium text-slate-700 mb-1">Alamat Email <span class="text-red-500">*</span></label>
<div class="relative">
<span class="absolute inset-y-0 left-0 flex items-center pl-3 text-slate-400">
<i data-lucide="mail" class="w-4 h-4"></i>
</span>
<input
type="email"
id="input-email"
required
placeholder="Contoh: johndoe@email.com"
class="w-full pl-10 pr-4 py-2 border border-slate-300 rounded-lg focus:ring-2 focus:ring-indigo-500 focus:border-indigo-500 text-sm placeholder:text-slate-400 focus:outline-none transition-all"
>
</div>
</div>
<!-- Instansi -->
<div>
<label for="input-instansi" class="block text-sm font-medium text-slate-700 mb-1">Instansi / Organisasi <span class="text-red-500">*</span></label>
<div class="relative">
<span class="absolute inset-y-0 left-0 flex items-center pl-3 text-slate-400">
<i data-lucide="building" class="w-4 h-4"></i>
</span>
<input
type="text"
id="input-instansi"
required
placeholder="Nama instansi atau 'Pribadi'"
class="w-full pl-10 pr-4 py-2 border border-slate-300 rounded-lg focus:ring-2 focus:ring-indigo-500 focus:border-indigo-500 text-sm placeholder:text-slate-400 focus:outline-none transition-all"
>
</div>
</div>
<!-- Keperluan -->
<div>
<label for="input-keperluan" class="block text-sm font-medium text-slate-700 mb-1">Keperluan Kunjungan <span class="text-red-500">*</span></label>
<div class="relative">
<span class="absolute inset-y-0 left-0 flex items-center pl-3 text-slate-400">
<i data-lucide="clipboard-list" class="w-4 h-4"></i>
</span>
<select
id="input-keperluan"
required
class="w-full pl-10 pr-4 py-2 border border-slate-300 rounded-lg focus:ring-2 focus:ring-indigo-500 focus:border-indigo-500 text-sm focus:outline-none transition-all bg-white"
>
<option value="" disabled selected>Pilih Keperluan...</option>
<option value="Pertemuan Bisnis / Rapat">Pertemuan Bisnis / Rapat</option>
<option value="Kunjungan Dinas / Koordinasi">Kunjungan Dinas / Koordinasi</option>
<option value="Pengiriman / Kurir">Pengiriman / Kurir</option>
<option value="Wawancara / Lamaran Kerja">Wawancara / Lamaran Kerja</option>
<option value="Lainnya">Lainnya (Tulis di pesan)</option>
</select>
</div>
</div>
<!-- Pesan / Kesan -->
<div>
<label for="input-pesan" class="block text-sm font-medium text-slate-700 mb-1">Pesan / Kesan / Keterangan Tambahan</label>
<textarea
id="input-pesan"
rows="3"
placeholder="Tulis pesan atau keterangan tambahan Anda di sini..."
class="w-full px-4 py-2 border border-slate-300 rounded-lg focus:ring-2 focus:ring-indigo-500 focus:border-indigo-500 text-sm placeholder:text-slate-400 focus:outline-none transition-all"
></textarea>
</div>
<!-- Tombol Submit -->
<button
type="submit"
id="btn-submit"
class="w-full flex items-center justify-center gap-2 px-4 py-2.5 bg-indigo-600 hover:bg-indigo-700 text-white font-semibold rounded-lg text-sm shadow-sm hover:shadow transition-all duration-200 focus:outline-none focus:ring-2 focus:ring-indigo-500 focus:ring-offset-2"
>
<i data-lucide="send" class="w-4 h-4"></i>
<span id="btn-text-submit">Simpan Kehadiran</span>
</button>
</form>
</div>
</section>
<!-- Kolom Kanan: Live Log / Riwayat Kunjungan -->
<section class="lg:col-span-7 flex flex-col gap-6">
<!-- List Daftar Pengunjung Terbaru -->
<div class="bg-white p-6 rounded-2xl border border-slate-200 shadow-sm flex flex-col flex-grow">
<div class="flex items-center justify-between mb-6">
<div class="flex items-center gap-2">
<i data-lucide="history" class="w-5 h-5 text-indigo-600"></i>
<h2 class="text-lg font-semibold text-slate-900">Daftar Pengunjung Terbaru</h2>
</div>
<button
onclick="loadGuestList()"
id="btn-refresh"
class="p-2 text-slate-500 hover:text-indigo-600 hover:bg-indigo-50 rounded-lg transition-colors duration-200"
title="Perbarui Data"
>
<i data-lucide="refresh-cw" class="w-4 h-4"></i>
</button>
</div>
<!-- Bagian Tabel Responsif -->
<div class="overflow-x-auto border border-slate-100 rounded-xl max-h-[450px] overflow-y-auto">
<table class="min-w-full divide-y divide-slate-200">
<thead class="bg-slate-50 sticky top-0 z-10">
<tr>
<th scope="col" class="px-4 py-3 text-left text-xs font-semibold text-slate-600 uppercase tracking-wider">No</th>
<th scope="col" class="px-4 py-3 text-left text-xs font-semibold text-slate-600 uppercase tracking-wider">Waktu</th>
<th scope="col" class="px-4 py-3 text-left text-xs font-semibold text-slate-600 uppercase tracking-wider">Nama & Email</th>
<th scope="col" class="px-4 py-3 text-left text-xs font-semibold text-slate-600 uppercase tracking-wider">Instansi & Keperluan</th>
</tr>
</thead>
<tbody id="guest-table-body" class="bg-white divide-y divide-slate-100">
<!-- Data akan dimasukkan oleh Javascript -->
<tr>
<td colspan="4" class="px-6 py-10 text-center text-sm text-slate-400">
<div class="flex flex-col items-center justify-center gap-2">
<i data-lucide="loader-2" class="w-8 h-8 animate-spin text-indigo-500"></i>
<span>Memuat data pengunjung...</span>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<!-- Info / Tips Section -->
<div class="bg-blue-50 border border-blue-200 rounded-xl p-4 flex items-start gap-3">
<i data-lucide="info" class="w-5 h-5 text-blue-600 flex-shrink-0 mt-0.5"></i>
<div>
<h3 class="text-sm font-semibold text-blue-900">Petunjuk Penggunaan Database</h3>
<p class="text-xs text-blue-700 mt-1">
Data yang Anda input di sini akan secara real-time tersimpan pada Google Spreadsheet Anda. Jika ini pertama kali Anda menggunakan aplikasi ini, silakan tekan tombol <strong>Setup Database</strong> di bagian kanan atas halaman.
</p>
</div>
</div>
</section>
</div>
</main>
<!-- Footer -->
<footer class="bg-white border-t border-slate-200 mt-12 py-4">
<div class="max-w-7xl mx-auto px-4 text-center text-xs text-slate-500">
© 2026 Aplikasi Buku Tamu Digital • Terintegrasi Google Apps Script & Google Sheets
</div>
</footer>
<!-- Javascript Logic -->
<script>
// Inisialisasi ikon Lucide
lucide.createIcons();
// Jalankan pemuatan data saat halaman pertama kali dibuka
window.onload = function() {
loadGuestList();
};
/**
* Memunculkan notifikasi kustom (Toast)
*/
function showToast(message, type = 'success') {
const container = document.getElementById('toast-container');
const toast = document.createElement('div');
const bgColor = type === 'success' ? 'bg-emerald-500' : 'bg-rose-500';
const icon = type === 'success' ? 'check-circle' : 'alert-circle';
toast.className = `flex items-center gap-3 px-4 py-3 text-white rounded-xl shadow-lg transform translate-y-2 opacity-0 transition-all duration-300 ${bgColor}`;
toast.innerHTML = `
<i data-lucide="${icon}" class="w-5 h-5 flex-shrink-0"></i>
<div class="text-sm font-medium">${message}</div>
`;
container.appendChild(toast);
lucide.createIcons();
// Animasi masuk
setTimeout(() => {
toast.classList.remove('translate-y-2', 'opacity-0');
}, 50);
// Penghapusan otomatis setelah 4 detik
setTimeout(() => {
toast.classList.add('translate-y-2', 'opacity-0');
setTimeout(() => toast.remove(), 300);
}, 4000);
}
/**
* Menjalankan Inisialisasi Database Spreadsheet via Web
*/
function triggerSetupDatabase() {
const btn = document.getElementById('btn-quick-setup');
const originalText = btn.innerHTML;
btn.disabled = true;
btn.innerHTML = `<i data-lucide="loader-2" class="w-4 h-4 animate-spin"></i><span>Sedang Memproses...</span>`;
lucide.createIcons();
google.script.run
.withSuccessHandler(function(response) {
btn.disabled = false;
btn.innerHTML = originalText;
lucide.createIcons();
if (response.success) {
showToast(response.message, 'success');
loadGuestList(); // Muat ulang list setelah setup berhasil
} else {
showToast(response.message, 'error');
}
})
.withFailureHandler(function(error) {
btn.disabled = false;
btn.innerHTML = originalText;
lucide.createIcons();
showToast("Koneksi gagal: " + error.toString(), 'error');
})
.setupDatabase();
}
/**
* Memuat dan memperbarui daftar pengunjung dari Google Sheets
*/
function loadGuestList() {
const tableBody = document.getElementById('guest-table-body');
const btnRefresh = document.getElementById('btn-refresh');
if(btnRefresh) {
btnRefresh.classList.add('animate-spin');
}
google.script.run
.withSuccessHandler(function(data) {
if (btnRefresh) btnRefresh.classList.remove('animate-spin');
if (!data || data.length === 0) {
tableBody.innerHTML = `
<tr>
<td colspan="4" class="px-6 py-10 text-center text-sm text-slate-400">
<div class="flex flex-col items-center justify-center gap-1">
<i data-lucide="inbox" class="w-8 h-8 text-slate-300"></i>
<span>Belum ada data pengunjung yang terdaftar.</span>
<span class="text-xs text-slate-400">Cobalah untuk mengisi Formulir Kunjungan di sebelah kiri.</span>
</div>
</td>
</tr>
`;
lucide.createIcons();
return;
}
tableBody.innerHTML = '';
data.forEach(guest => {
const row = document.createElement('tr');
row.className = 'hover:bg-slate-50 transition-colors';
// Format tampilan tanggal
const dateStr = guest.tanggal ? guest.tanggal.toString() : '-';
row.innerHTML = `
<td class="px-4 py-3 whitespace-nowrap text-sm font-semibold text-slate-500">${guest.no}</td>
<td class="px-4 py-3 whitespace-nowrap text-xs text-slate-500">${dateStr}</td>
<td class="px-4 py-3 whitespace-nowrap">
<div class="text-sm font-semibold text-slate-900">${guest.nama}</div>
<div class="text-xs text-slate-400">${guest.email}</div>
</td>
<td class="px-4 py-3">
<div class="text-sm text-slate-800 font-medium">${guest.instansi}</div>
<div class="inline-flex mt-1 items-center px-2 py-0.5 rounded text-xs font-medium bg-indigo-50 text-indigo-700">
${guest.keperluan}
</div>
</td>
`;
tableBody.appendChild(row);
});
lucide.createIcons();
})
.withFailureHandler(function(error) {
if (btnRefresh) btnRefresh.classList.remove('animate-spin');
let errMsg = error.toString();
if (errMsg.includes("DATABASE_NOT_FOUND")) {
tableBody.innerHTML = `
<tr>
<td colspan="4" class="px-6 py-8 text-center text-sm text-amber-600 bg-amber-50 rounded-xl">
<div class="flex flex-col items-center justify-center gap-2">
<i data-lucide="database" class="w-8 h-8 text-amber-500"></i>
<span class="font-semibold">Database Belum Siap</span>
<span class="text-xs text-amber-700 max-w-sm mx-auto leading-relaxed">
Sheet "Buku Tamu" tidak ditemukan. Klik tombol <strong>Setup Database</strong> di kanan atas untuk menginisialisasi spreadsheet Anda.
</span>
</div>
</td>
</tr>
`;
} else {
tableBody.innerHTML = `
<tr>
<td colspan="4" class="px-6 py-8 text-center text-sm text-rose-500 bg-rose-50 rounded-xl">
<div class="flex flex-col items-center justify-center gap-2">
<i data-lucide="alert-triangle" class="w-8 h-8 text-rose-500"></i>
<span class="font-semibold">Gagal Memuat Data</span>
<span class="text-xs text-rose-700">${errMsg}</span>
</div>
</td>
</tr>
`;
}
lucide.createIcons();
})
.getGuests();
}
/**
* Mengatur pengiriman formulir pengunjung
*/
function handleFormSubmit(event) {
event.preventDefault();
const btnSubmit = document.getElementById('btn-submit');
const btnText = document.getElementById('btn-text-submit');
const form = document.getElementById('form-guest');
const guestData = {
nama: document.getElementById('input-nama').value,
email: document.getElementById('input-email').value,
instansi: document.getElementById('input-instansi').value,
keperluan: document.getElementById('input-keperluan').value,
pesan: document.getElementById('input-pesan').value
};
// Efek Loading pada tombol submit
btnSubmit.disabled = true;
const originalText = btnText.innerText;
btnText.innerText = "Mengirim...";
google.script.run
.withSuccessHandler(function(response) {
btnSubmit.disabled = false;
btnText.innerText = originalText;
if (response.success) {
showToast(response.message, 'success');
form.reset(); // Kosongkan form kembali setelah berhasil
loadGuestList(); // Update daftar list terbaru
} else {
showToast(response.message, 'error');
}
})
.withFailureHandler(function(error) {
btnSubmit.disabled = false;
btnText.innerText = originalText;
showToast("Pengiriman gagal: " + error.toString(), 'error');
})
.submitGuestData(guestData);
}
</script>
</body>
</html>
