Website Pencarian Data Dari Banyak Spreadsheet Menggunakan Apps Script V2
1. Copy Spreadsheet utama (Klik disini)
- Copy Spreadsheet 1 (Klik Disini)
- Copy Spreadsheet 2 (Klik Disini)
2. Pada Spreadsheet utama terdapat sheet Data, dengan kolom :
- Kolom URL Spreadsheet (isikan dengan URL dari spreadsheet data yang akan dicari)
- Kolom Nama Spreadsheet (isikan dengan nama Spreadsheet dari URL diatas)
3. Buatlah lembar kerja Apps Script dengan cara klik menu Extensions/Ektensi lalu pilih Apps Script.
4. Pada lembar kerja Apps Script terdapat 3 file default yaitu :
- Code.gs
- Index.html
- Visit.html
5. Copy dan pastekan script di bawah ini ke Code.gs
Masukkan Password Untuk Melihat Script (Password ada di dalam video)
//Source Code by www.javabitpro.com
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate()
.setTitle('Form Pencarian Data | Javabitpro')
.addMetaTag('viewport','width=device-width , initial-scale=1')
.setFaviconUrl('https://i.imgur.com/thmO7Xv.png');
}
// Fungsi untuk mendapatkan data dari URL Spreadsheet eksternal
function getExternalData(searchQuery) {
Logger.log(`Search Query: ${searchQuery}`);
// Pastikan searchQuery tidak kosong
if (!searchQuery) {
throw new Error("Search query is required.");
}
const mainSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
const data = mainSheet.getDataRange().getValues();
const results = [];
for (let i = 1; i < data.length; i++) {
const externalUrl = data[i][0];
const spreadsheetName = data[i][1];
if (!externalUrl) {
Logger.log(`Skipping row ${i} due to empty URL.`);
continue;
}
try {
Logger.log(`Accessing URL: ${externalUrl}`);
const externalSpreadsheet = SpreadsheetApp.openByUrl(externalUrl);
const externalSheet = externalSpreadsheet.getSheets()[0];
const externalData = externalSheet.getDataRange().getValues();
Logger.log(`Data retrieved from ${spreadsheetName}: ${externalData.length} rows`);
// Pastikan ada data di spreadsheet eksternal
if (externalData.length <= 1) {
Logger.log(`No data in spreadsheet: ${spreadsheetName}`);
continue;
}
// Ambil header dari baris pertama
const header = externalData[0];
// Filter data berdasarkan kolom A
const filteredData = externalData.filter((row, index) => {
if (!row[0]) {
return false; // Skip jika kolom pertama kosong
}
return index > 0 && row[0].toString().toLowerCase().includes(searchQuery.toLowerCase());
});
// Proses kolom tertentu yang berformat tanggal dan URL
const processedData = filteredData.map((row) => {
return row.map((cell, colIndex) => {
// Jika kolom berisi tanggal (misalnya kolom kolom C maka row ke 2) (Kolom A adalah row 0)
if (colIndex === 2 && cell instanceof Date) {
return Utilities.formatDate(cell, Session.getScriptTimeZone(), "dd-MM-yyyy");
}
// Jika kolom berisi URL (misalnya kolom E maka row ke 4)(Kolom A adalah row 0)
if (colIndex === 4 && typeof cell === 'string' && isValidUrl(cell)) {
return `<a href="${cell}" target="_blank">${cell}</a>`; // Membuat link yang dapat diklik
}
return cell; // Biarkan kolom lainnya tetap seperti semula
});
});
Logger.log(`Filtered and formatted data count for ${spreadsheetName}: ${processedData.length}`);
if (processedData.length > 0) {
results.push({
spreadsheetName: spreadsheetName,
header: header, // Menyimpan header dari spreadsheet eksternal
data: processedData,
});
}
} catch (e) {
Logger.log(`Error accessing ${externalUrl}: ${e.message}`);
}
}
return results;
}
// Fungsi untuk memvalidasi apakah sebuah string adalah URL
function isValidUrl(url) {
const pattern = new RegExp('^(https?:\\/\\/)'); // Regex untuk validasi URL yang dimulai dengan http:// atau https://
return pattern.test(url);
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
//Source Code by www.javabitpro.com
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate()
.setTitle('Form Pencarian Data | Javabitpro')
.addMetaTag('viewport','width=device-width , initial-scale=1')
.setFaviconUrl('https://i.imgur.com/thmO7Xv.png');
}
// Fungsi untuk mendapatkan data dari URL Spreadsheet eksternal
function getExternalData(searchQuery) {
Logger.log(`Search Query: ${searchQuery}`);
// Pastikan searchQuery tidak kosong
if (!searchQuery) {
throw new Error("Search query is required.");
}
const mainSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
const data = mainSheet.getDataRange().getValues();
const results = [];
for (let i = 1; i < data.length; i++) {
const externalUrl = data[i][0];
const spreadsheetName = data[i][1];
if (!externalUrl) {
Logger.log(`Skipping row ${i} due to empty URL.`);
continue;
}
try {
Logger.log(`Accessing URL: ${externalUrl}`);
const externalSpreadsheet = SpreadsheetApp.openByUrl(externalUrl);
const externalSheet = externalSpreadsheet.getSheets()[0];
const externalData = externalSheet.getDataRange().getValues();
Logger.log(`Data retrieved from ${spreadsheetName}: ${externalData.length} rows`);
// Pastikan ada data di spreadsheet eksternal
if (externalData.length <= 1) {
Logger.log(`No data in spreadsheet: ${spreadsheetName}`);
continue;
}
// Ambil header dari baris pertama
const header = externalData[0];
// Filter data berdasarkan kolom A
const filteredData = externalData.filter((row, index) => {
if (!row[0]) {
return false; // Skip jika kolom pertama kosong
}
return index > 0 && row[0].toString().toLowerCase().includes(searchQuery.toLowerCase());
});
// Proses kolom tertentu yang berformat tanggal dan URL
const processedData = filteredData.map((row) => {
return row.map((cell, colIndex) => {
// Jika kolom berisi tanggal (misalnya kolom kolom C maka row ke 2) (Kolom A adalah row 0)
if (colIndex === 2 && cell instanceof Date) {
return Utilities.formatDate(cell, Session.getScriptTimeZone(), "dd-MM-yyyy");
}
// Jika kolom berisi URL (misalnya kolom E maka row ke 4)(Kolom A adalah row 0)
if (colIndex === 4 && typeof cell === 'string' && isValidUrl(cell)) {
return `<a href="${cell}" target="_blank">${cell}</a>`; // Membuat link yang dapat diklik
}
return cell; // Biarkan kolom lainnya tetap seperti semula
});
});
Logger.log(`Filtered and formatted data count for ${spreadsheetName}: ${processedData.length}`);
if (processedData.length > 0) {
results.push({
spreadsheetName: spreadsheetName,
header: header, // Menyimpan header dari spreadsheet eksternal
data: processedData,
});
}
} catch (e) {
Logger.log(`Error accessing ${externalUrl}: ${e.message}`);
}
}
return results;
}
// Fungsi untuk memvalidasi apakah sebuah string adalah URL
function isValidUrl(url) {
const pattern = new RegExp('^(https?:\\/\\/)'); // Regex untuk validasi URL yang dimulai dengan http:// atau https://
return pattern.test(url);
}
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
6. Copy dan pastekan script di bawah ini ke Index.html
Masukkan Password Untuk Melihat Script (Password sama dengan di atas)
<!DOCTYPE html>
<html>
<head>
<title>Form Pencarian</title>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.0.3/css/font-awesome.css" rel="stylesheet">
<!-- font-awesome@6.2.0 icon Visit -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<!-- Source Code by www.javabitpro.com -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css">
<?!= include('Visit')?>
<style>
body, html {
height: 100%;
margin: 0;
padding: 0;
background: #351c75 !important;
}
/* Container utama */
.main-container {
display: flex;
flex-direction: column;
align-items: center;
justify-content: center;
height: 100vh;
transition: all 0.5s ease; /* Animasi */
}
.main-container.moved {
justify-content: flex-start; /* Geser ke atas jika data ditemukan */
height: auto; /* Tinggi dinamis */
padding-top: 20px;
}
/* Gambar di atas form */
.logo-container img {
width: 200px; /* Ukuran gambar */
margin-bottom: 20px; /* Jarak antara gambar dan form */
border-radius: 10px;
}
.search {
width: 600px;
height: 50px;
background-color: #fff;
border-radius: 40px;
padding: 8px;
display: flex;
align-items: center;
transition: all 0.5s ease;
}
.search_input {
color: black;
border: 0;
outline: 0;
background: none;
width: 100%;
padding: 0 10px;
}
.search_icon {
height: 30px;
width: 30px;
display: flex;
justify-content: center;
align-items: center;
border-radius: 50%;
color: white;
background-color: black;
cursor: pointer;
}
/* Hasil pencarian */
#results {
margin-top: 20px;
width: 100%;
color: red;
overflow-y: auto; /* Tambahkan scrollbar jika data melebihi tinggi maksimal */
border: 2px solid #ddd; /* Tambahkan border untuk membedakan area hasil */
border-radius: 10px;
background-color: white;
}
</style>
<script>
function search() {
const searchQuery = document.getElementById("searchInput").value;
if (!searchQuery) {
alert("Please enter a search query.");
return;
}
// Modal Loading
const loadingModal = new bootstrap.Modal(document.getElementById('loadingModal'));
loadingModal.show();
document.getElementById("loadingMessage").innerText = "Loading...";
google.script.run
.withSuccessHandler(displayResults)
.withFailureHandler((error) => {
document.getElementById("loadingMessage").innerText = "Error: " + error.message;
console.error("Error:", error.message);
setTimeout(() => {
loadingModal.hide();
}, 1500);
})
.getExternalData(searchQuery);
}
function displayResults(results) {
let output = "";
const loadingModal = bootstrap.Modal.getInstance(document.getElementById('loadingModal'));
const successModal = new bootstrap.Modal(document.getElementById('successModal'));
if (results.length === 0) {
output = "<p>No results found.</p>";
document.getElementById("loadingMessage").innerText = "No results found.";
} else {
results.forEach((result) => {
output += `<br><h5>Lokasi Data Pada Spreadsheet : ${result.spreadsheetName}</h5>`;
output += "<table class='table table-bordered'><thead><tr>";
const headers = result.header;
headers.forEach((header) => {
output += `<th>${header}</th>`;
});
output += "</tr></thead><tbody>";
result.data.forEach((row) => {
output += "<tr>";
row.forEach((cell) => {
output += `<td>${cell}</td>`;
});
output += "</tr>";
});
output += "</tbody></table>";
});
document.getElementById("loadingMessage").innerText = "Search completed!";
successModal.show();
}
document.getElementById("results").innerHTML = output;
// Geser form pencarian ke atas
document.querySelector('.main-container').classList.add('moved');
setTimeout(() => {
loadingModal.hide();
}, 1500);
}
function handleEnterKey(event) {
if (event.key === "Enter") {
search();
}
}
document.addEventListener("DOMContentLoaded", function () {
const searchInput = document.getElementById("searchInput");
searchInput.addEventListener("keydown", handleEnterKey);
});
</script>
</head>
<body>
<!-- Container Utama -->
<div class="container main-container">
<!-- Gambar Logo -->
<div class="logo-container">
<img src="https://cdn.jsdelivr.net/gh/javabitpro/javabitproimage@main/Untitled%20design.gif" alt="Logo">
</div>
<!-- Form Pencarian -->
<div class="search">
<input type="text" id="searchInput" class="search_input" placeholder="Masukkan kata kunci..." />
<button class="search_icon" onclick="search()">
<i class="fa fa-search"></i>
</button>
</div>
</div>
<!-- Tempat Menampilkan Hasil -->
<div id="results" class="container"></div>
<br>
<!-- Modal Loading -->
<div class="modal fade" id="loadingModal" tabindex="-1" aria-labelledby="loadingModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="loadingModalLabel">Searching...</h5>
</div>
<div class="modal-body">
<div id="loadingMessage">Loading...</div>
</div>
</div>
</div>
</div>
<!-- Modal Success -->
<div class="modal fade" id="successModal" tabindex="-1" aria-labelledby="successModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="successModalLabel">Success</h5>
</div>
<div class="modal-body">
<span class="text-success">✔️ Search completed successfully!</span>
</div>
</div>
</div>
</div>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<title>Form Pencarian</title>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.0.3/css/font-awesome.css" rel="stylesheet">
<!-- font-awesome@6.2.0 icon Visit -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<!-- Source Code by www.javabitpro.com -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css">
<?!= include('Visit')?>
<style>
body, html {
height: 100%;
margin: 0;
padding: 0;
background: #351c75 !important;
}
/* Container utama */
.main-container {
display: flex;
flex-direction: column;
align-items: center;
justify-content: center;
height: 100vh;
transition: all 0.5s ease; /* Animasi */
}
.main-container.moved {
justify-content: flex-start; /* Geser ke atas jika data ditemukan */
height: auto; /* Tinggi dinamis */
padding-top: 20px;
}
/* Gambar di atas form */
.logo-container img {
width: 200px; /* Ukuran gambar */
margin-bottom: 20px; /* Jarak antara gambar dan form */
border-radius: 10px;
}
.search {
width: 600px;
height: 50px;
background-color: #fff;
border-radius: 40px;
padding: 8px;
display: flex;
align-items: center;
transition: all 0.5s ease;
}
.search_input {
color: black;
border: 0;
outline: 0;
background: none;
width: 100%;
padding: 0 10px;
}
.search_icon {
height: 30px;
width: 30px;
display: flex;
justify-content: center;
align-items: center;
border-radius: 50%;
color: white;
background-color: black;
cursor: pointer;
}
/* Hasil pencarian */
#results {
margin-top: 20px;
width: 100%;
color: red;
overflow-y: auto; /* Tambahkan scrollbar jika data melebihi tinggi maksimal */
border: 2px solid #ddd; /* Tambahkan border untuk membedakan area hasil */
border-radius: 10px;
background-color: white;
}
</style>
<script>
function search() {
const searchQuery = document.getElementById("searchInput").value;
if (!searchQuery) {
alert("Please enter a search query.");
return;
}
// Modal Loading
const loadingModal = new bootstrap.Modal(document.getElementById('loadingModal'));
loadingModal.show();
document.getElementById("loadingMessage").innerText = "Loading...";
google.script.run
.withSuccessHandler(displayResults)
.withFailureHandler((error) => {
document.getElementById("loadingMessage").innerText = "Error: " + error.message;
console.error("Error:", error.message);
setTimeout(() => {
loadingModal.hide();
}, 1500);
})
.getExternalData(searchQuery);
}
function displayResults(results) {
let output = "";
const loadingModal = bootstrap.Modal.getInstance(document.getElementById('loadingModal'));
const successModal = new bootstrap.Modal(document.getElementById('successModal'));
if (results.length === 0) {
output = "<p>No results found.</p>";
document.getElementById("loadingMessage").innerText = "No results found.";
} else {
results.forEach((result) => {
output += `<br><h5>Lokasi Data Pada Spreadsheet : ${result.spreadsheetName}</h5>`;
output += "<table class='table table-bordered'><thead><tr>";
const headers = result.header;
headers.forEach((header) => {
output += `<th>${header}</th>`;
});
output += "</tr></thead><tbody>";
result.data.forEach((row) => {
output += "<tr>";
row.forEach((cell) => {
output += `<td>${cell}</td>`;
});
output += "</tr>";
});
output += "</tbody></table>";
});
document.getElementById("loadingMessage").innerText = "Search completed!";
successModal.show();
}
document.getElementById("results").innerHTML = output;
// Geser form pencarian ke atas
document.querySelector('.main-container').classList.add('moved');
setTimeout(() => {
loadingModal.hide();
}, 1500);
}
function handleEnterKey(event) {
if (event.key === "Enter") {
search();
}
}
document.addEventListener("DOMContentLoaded", function () {
const searchInput = document.getElementById("searchInput");
searchInput.addEventListener("keydown", handleEnterKey);
});
</script>
</head>
<body>
<!-- Container Utama -->
<div class="container main-container">
<!-- Gambar Logo -->
<div class="logo-container">
<img src="https://cdn.jsdelivr.net/gh/javabitpro/javabitproimage@main/Untitled%20design.gif" alt="Logo">
</div>
<!-- Form Pencarian -->
<div class="search">
<input type="text" id="searchInput" class="search_input" placeholder="Masukkan kata kunci..." />
<button class="search_icon" onclick="search()">
<i class="fa fa-search"></i>
</button>
</div>
</div>
<!-- Tempat Menampilkan Hasil -->
<div id="results" class="container"></div>
<br>
<!-- Modal Loading -->
<div class="modal fade" id="loadingModal" tabindex="-1" aria-labelledby="loadingModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="loadingModalLabel">Searching...</h5>
</div>
<div class="modal-body">
<div id="loadingMessage">Loading...</div>
</div>
</div>
</div>
</div>
<!-- Modal Success -->
<div class="modal fade" id="successModal" tabindex="-1" aria-labelledby="successModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="successModalLabel">Success</h5>
</div>
<div class="modal-body">
<span class="text-success">✔️ Search completed successfully!</span>
</div>
</div>
</div>
</div>
</body>
</html>
7. Copy dan pastekan script di bawah ini ke Visit.html
Masukkan Password Untuk Melihat Script (Password sama dengan di atas)
<link href="https://cdn.jsdelivr.net/gh/javabitpro/css@main/javabitprovisit.css" rel="stylesheet">
<div class="fab-container2">
</div>
<div class="fab-container">
<div class="fab fab-icon-holder">
</div>
<ul class="fab-options">
<li>
<span class="fab-label">Youtube</span>
<div class="fab-icon-holder">
<a target="_blank" href="https://www.youtube.com/@javabitpro-editor/videos"><i class="fa-brands fa-youtube"></i></i></a>
</div>
</li>
<li style="margin-bottom: 10px;">
<span class="fab-label">Website</span>
<div class="fab-icon-holder">
<a target="_blank" href="https://www.javabitpro.com/"><i class="fa-solid fa-globe"></i></a>
</div>
</li>
</ul>
</div>
<link href="https://cdn.jsdelivr.net/gh/javabitpro/css@main/javabitprovisit.css" rel="stylesheet">
<div class="fab-container2">
</div>
<div class="fab-container">
<div class="fab fab-icon-holder">
</div>
<ul class="fab-options">
<li>
<span class="fab-label">Youtube</span>
<div class="fab-icon-holder">
<a target="_blank" href="https://www.youtube.com/@javabitpro-editor/videos"><i class="fa-brands fa-youtube"></i></i></a>
</div>
</li>
<li style="margin-bottom: 10px;">
<span class="fab-label">Website</span>
<div class="fab-icon-holder">
<a target="_blank" href="https://www.javabitpro.com/"><i class="fa-solid fa-globe"></i></a>
</div>
</li>
</ul>
</div>
8. Klik ikon Save.
9.Klik tombol Terapkan/Deploy lalu pilih Deployment baru/New deployment.
10. Pastikan jenisnya adalah Aplikasi web, hak aksesnya adalah Anyone/Siapa saja lalu klik Terapkan/Deploy.
11. Silahkan izinkan akses otorisasi jika diperlukan (Tutorial ada divideo)
12. Klik atau salin URL yang sudah di Deploy. Website URL sudah siap digunakan.
Note.
Apabila ada kolom data yang berisi URL/Link atau berisi Date/Tanggal Lahir, maka sesuaikan script Code.gs pada point 62 dan 67. (Perhatikan gambar di bawah ini)
