Halo teman-teman.
Dalam tutorial kali ini, kita berbicara tentang bagaimana cara membuat aplikasi web untuk berinteraksi dengan Google Sheets menggunakan Google Apps Script. Saat Anda membuat jenis aplikasi web demikian, Anda mungkin perlu menerapkan form pencarian untuk mendapatkan kembali data yang diambil dari Google Spreadsheet.
Tutorial ini akan menunjukkan cara membuat form web pencarian untuk mencari dan mendapatkan data dari Google Sheets. Anda dapat membagikan form web pencarian ini dengan orang lain dan membiarkan mereka mencari data di Google Sheet Anda dengan kriteria tertentu.
Ini berguna juga ketika Anda ingin membagikan sebagian dari Google Sheets Anda tanpa memberikan akses full Spreadsheet Anda. Dalam kasus seperti itu, Anda dapat membuat halaman web yang ramah pengguna untuk membagikan data Google Sheets Anda menggunakan metode ini.
Ada dua metode yang dapat Anda ikuti untuk bekerja dengan Google Apps Script. Yang pertama adalah Skrip yang terikat Wadah, dan yang kedua adalah Skrip Mandiri. Di sini saya menggunakan yang kedua, jadi tidak mengikat skrip ke Google Sheet.
Cara membuat web form pencarian ini untuk mendapatkan data dari Google Sheets
1. Siapkan Google Sheets Anda
Pada tutorial ini, Anda dapat menggunakan lembar halaman data Anda sendiri (Google Sheet) atau Anda dapat membuat salinan Google Sheet yang digunakan.
Contoh seperti gambar dibawah:
File Scripts= Google Apps di atas berisi dua file berikut. Di sini saya tidak akan menjelaskan kode baris demi baris. Sebagai gantinya, saya akan menjelaskan secara singkat tujuan dari setiap file dan fungsinya di bawah ini.
Silahkan copy Script Code Berikut ini:
- Index.html
- Code.gs
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js" integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" crossorigin="anonymous"></script>
<!--##JAVASCRIPT FUNCTIONS ---------------------------------------------------- -->
<script>
//PREVENT FORMS FROM SUBMITTING / PREVENT DEFAULT BEHAVIOUR
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
window.addEventListener("load", preventFormSubmit, true);
//HANDLE FORM SUBMISSION
function handleFormSubmit(formObject) {
google.script.run.withSuccessHandler(createTable).processForm(formObject);
document.getElementById("search-form").reset();
}
//CREATE THE DATA TABLE
function createTable(dataArray) {
if(dataArray && dataArray !== undefined && dataArray.length != 0){
var result = "<table class='table table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
"<thead style='white-space: nowrap'>"+
"<tr>"+ //Change table headings to match witht he Google Sheet
"<th scope='col'>ORDERNUMBER</th>"+
"<th scope='col'>QUANTITYORDERED</th>"+
"<th scope='col'>PRICEEACH</th>"+
"<th scope='col'>ORDERLINENUMBER</th>"+
"<th scope='col'>SALES</th>"+
"<th scope='col'>ORDERDATE</th>"+
"<th scope='col'>STATUS</th>"+
"<th scope='col'>QTR_ID</th>"+
"<th scope='col'>MONTH_ID</th>"+
"<th scope='col'>YEAR_ID</th>"+
"<th scope='col'>PRODUCTLINE</th>"+
"<th scope='col'>MSRP</th>"+
"<th scope='col'>PRODUCTCODE</th>"+
"<th scope='col'>CUSTOMERNAME</th>"+
"<th scope='col'>PHONE</th>"+
"<th scope='col'>ADDRESSLINE1</th>"+
"<th scope='col'>ADDRESSLINE2</th>"+
"<th scope='col'>CITY</th>"+
"<th scope='col'>STATE</th>"+
"<th scope='col'>POSTALCODE</th>"+
"<th scope='col'>COUNTRY</th>"+
"<th scope='col'>TERRITORY</th>"+
"<th scope='col'>CONTACTLASTNAME</th>"+
"<th scope='col'>CONTACTFIRSTNAME</th>"+
"<th scope='col'>DEALSIZE</th>"+
"</tr>"+
"</thead>";
for(var i=0; i<dataArray.length; i++) {
result += "<tr>";
for(var j=0; j<dataArray[i].length; j++){
result += "<td>"+dataArray[i][j]+"</td>";
}
result += "</tr>";
}
result += "</table>";
var div = document.getElementById('search-results');
div.innerHTML = result;
}else{
var div = document.getElementById('search-results');
//div.empty()
div.innerHTML = "Data not found!";
}
}
</script>
<!--##JAVASCRIPT FUNCTIONS ~ END ---------------------------------------------------- -->
</head>
<body>
<div class="container">
<br>
<div class="row">
<div class="col">
<!-- ## SEARCH FORM ------------------------------------------------ -->
<form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
<div class="form-group mb-2">
<label for="searchtext">Search Text</label>
</div>
<div class="form-group mx-sm-3 mb-2">
<input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Search Text">
</div>
<button type="submit" class="btn btn-primary mb-2">Search</button>
</form>
<!-- ## SEARCH FORM ~ END ------------------------------------------- -->
</div>
</div>
<div class="row">
<div class="col">
<!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
<div id="search-results" class="table-responsive">
<!-- The Data Table is inserted here by JavaScript -->
</div>
<!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
</div>
</div>
</div>
</body>
</html>
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
/* PROCESS FORM */
function processForm(formObject){
var result = "";
if(formObject.searchtext){//Execute if form passes search text
result = search(formObject.searchtext);
}
return result;
}
//SEARCH FOR MATCHED CONTENTS
function search(searchtext){
var spreadsheetId = '142FbylWc7109R0RsXQ42NuN_TFs-D3RTtIYJOSBR1QM'; //** CHANGE !!!
var dataRage = 'Data!A2:Y'; //** CHANGE !!!
var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRage).values;
var ar = [];
data.forEach(function(f) {
if (~f.indexOf(searchtext)) {
ar.push(f);
}
});
return ar;
}
2. File Code.gs
File ini berisi script pada sisi server. Fungsi handleFormSubmit yang disebutkan di atas dalam file Index.html meneruskan formObject ke fungsi processForm dalam file Code.gs ini.
Jika formObject berisi teks pencarian, itu diteruskan ke fungsi pencarian.
Dalam fungsi pencarian, ganti spreadhseetId dan dataRange sesuai Google Sheet dan rentang data Anda.
Fungsi pencarian mencari konten yang cocok di Google Sheet dan mengembalikan larik baris kembali ke fungsi processForm. Ini mengembalikan hasil kembali ke fungsi handleFormSubmit di file Index.html.
Untuk mengetahui Spreedshets ID bisa lihat gambar berikut:
Jika sudah mendapatkan Spreedshet ID nya silahkan di copy lalu pastekan pada line ke 17.