Tutorial Praktis
Tutorial Analytics GViz SQL
Bangun dashboard analitik penjualan dinamis dengan filter query SQL GViz.
Pelajari cara me-query, memfilter, dan mengagregasi data langsung di server Google menggunakan Google Visualization API (GViz) dan query SQL.
1. Setup Google Sheets
- Buat Google Sheet publik bernama Analitik Penjualan.
- Beri nama tab sheet
sales. - Header di Baris 1:
region,salesperson,revenue,status. - Bagikan sheet sebagai Anyone with the link can view.
- Tambahkan Spreadsheet ID ke
.env:
.env
GSHEET_SPREADSHEET_ID=1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms
2. Komponen Vue Filter SQL Dinamis
pages/analytics.vue
<script setup lang="ts">
interface SaleRecord {
region: string
salesperson: string
revenue: number
status: string
}
const selectedRegion = ref('North')
const minRevenue = ref(1000)
// Computed string query GViz SQL
const sqlQuery = computed(() => {
return `SELECT A, B, C, D WHERE A = '${selectedRegion.value}' AND C >= ${minRevenue.value} ORDER BY C DESC`
})
// Fetch data terfilter menggunakan query reaktif
const { data: sales, pending, refresh } = await useGSheetAsObject<SaleRecord[]>('A1:D100', {
sheet: 'sales',
query: sqlQuery.value
})
watch([selectedRegion, minRevenue], () => {
refresh()
})
</script>
<template>
<div class="analytics-dashboard">
<h1>Dashboard Analitik Penjualan</h1>
<div class="filters">
<label>Wilayah:</label>
<select v-model="selectedRegion">
<option value="North">
Utara (North)
</option>
<option value="South">
Selatan (South)
</option>
<option value="East">
Timur (East)
</option>
</select>
<label>Min Pendapatan ($):</label>
<input
v-model.number="minRevenue"
type="number"
>
</div>
<div v-if="pending">
Mengeksekusi Query GViz SQL...
</div>
<table v-else>
<thead>
<tr>
<th>Wilayah</th>
<th>Salesperson</th>
<th>Pendapatan</th>
<th>Status</th>
</tr>
</thead>
<tbody>
<tr
v-for="(row, idx) in sales"
:key="idx"
>
<td>{{ row.region }}</td>
<td>{{ row.salesperson }}</td>
<td>${{ row.revenue }}</td>
<td>{{ row.status }}</td>
</tr>
</tbody>
</table>
</div>
</template>