Cara Mudah Memanggil Data dari Google Sheet ke HTML Table Tanpa Google App Script
Selamat datang kembali di postingan Code With AI. Kali ini kami akan membahas bagaimana cara memanggil data dari google sheet secara mudah tanpa perlu ribet menggunakan google app script. Data nantinya akan ditampilkan menggunakan gaya/format dari datatables.net.
Anda dapat melihat videonya di bawah ini atau membaca postingan ini.
1. Pertama Anda harus mengcopy/mendownload template yang telah kami sediakan di bawah ini
<!DOCTYPE html>
<html lang="en">
<head>
<base target="_top">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Change the title here -->
<title>Code With AI</title>
<link href="https://fonts.googleapis.com/css?family=Open+Sans:300,400,700" rel="stylesheet">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.1/css/all.min.css">
<link href="https://cdn.datatables.net/1.13.1/css/jquery.dataTables.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/responsive/2.4.0/css/responsive.dataTables.min.css" rel="stylesheet">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
/* Custom styles for mobile responsiveness */
@media (max-width: 768px) {
.container-fluid {
padding: 10px;
}
#dataTable {
font-size: 12px;
}
#dataTable th,
#dataTable td {
padding: 5px;
}
#dataTable_length select,
#dataTable_filter input {
font-size: 12px;
padding: 2px;
}
}
/* Centered title styles */
.centered-title {
text-align: center;
padding: 20px 0;
font-size: 14px;
font-weight: bold;
}
/* Add more custom styling here */
</style>
</head>
<body>
<header>
<!-- Change the title here -->
<div class="centered-title">Google Sheet To HTML Table</div>
</header>
<main class="container-fluid">
<div class="table-responsive">
<table id="dataTable" class="display responsive nowrap" style="width:100%">
<thead class="bg-primary text-white"></thead>
</table>
</div>
</main>
<footer class="text-muted text-center mt-2">
<!-- Change footer information here -->
<p style="font-size:12px;"> © Code With AI 2024 <br><a style="color:green;text-decoration:none" href="https://www.codewithai.my.id/"
target="_blank">WWW.CODEWITHAI.MY.ID</a></p>
</footer>
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.datatables.net/1.13.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.4.0/js/dataTables.responsive.min.js"></script>
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.10.2/dist/umd/popper.min.js"
integrity="sha384-7+zCNj/IqJ95wo16oMtfsKbZ9ccEh31eOz1HGyDuCQ6wgnyJNSYdrPa03rtR1zdB" crossorigin="anonymous">
</script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.min.js"
integrity="sha384-QJHtvGhmr9XOIpI6YVutG+2QOK9T+ZnN4kzFN1RtK3zEFEIsxhlmWl5/YESvpZ13" crossorigin="anonymous">
</script>
<script>
google.charts.load('current', { packages: ['table'] });
function drawVisualization() {
var query = new google.visualization.Query('https://spreadsheets.google.com/tq?key=YOUR GOOGLE SHEET ID&output=html&gid=YOURGIDNUMBER&range=YOUR RANGE&&usp=sharing');
query.setQuery('SELECT *');
query.send(handleQueryResponse);
}
function handleQueryResponse(response) {
if (response.isError()) {
alert('There was a problem with your query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
var dataArray = [];
for (var i = 0; i < data.getNumberOfRows(); i++) {
var row = [];
for (var j = 0; j < data.getNumberOfColumns(); j++) {
row.push(data.getValue(i, j));
}
dataArray.push(row);
}
$(document).ready(function () {
var table = $('#dataTable').DataTable({
data: dataArray,
columnDefs: [{ targets: [0, 1, 2], className: 'all' }],
columns: [
// Adjust column indices and titles here
{ title: 'Name' },
{ title: 'Position' },
{ title: 'Office' },
{ title: 'Age' },
{ title: 'Start date' },
{ title: 'Salary' }
],
lengthMenu: [[5, 10, 25, 50, -1], [5, 10, 25, 50, "all"]],
order: [[0, 'asc']], // Sort by column 2 (Area) ascending
destroy: true,
});
});
}
google.setOnLoadCallback(drawVisualization);
</script>
</body>
</html>
Atau mendownloadnya pada link ini:
Jika Anda menyalin kode di atas, silakan paste-kan ke Notepad atau Text Editor lainnya, kemudian simpan dengan nama table.html.
Langkah selanjutnya dapat disimak dalam video yang saya share di bawah ini;
