Tutorials
GViz SQL Analytics Tutorial
Build a dynamic sales analytics dashboard with GViz SQL query filtering.
Learn how to query, filter, and aggregate data directly on Google's servers using Google Visualization API (GViz) and SQL queries.
1. Google Sheets Setup
- Create a public Google Sheet named Sales Analytics.
- Name the tab
sales. - Headers in Row 1:
region,salesperson,revenue,status. - Share sheet as Anyone with the link can view.
- Add Spreadsheet ID to
.env:
.env
GSHEET_SPREADSHEET_ID=1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms
2. Dynamic SQL Filtering Vue Component
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 GViz SQL query string
const sqlQuery = computed(() => {
return `SELECT A, B, C, D WHERE A = '${selectedRegion.value}' AND C >= ${minRevenue.value} ORDER BY C DESC`
})
// Fetch filtered data using reactive query
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>Sales Analytics Dashboard</h1>
<div class="filters">
<label>Region:</label>
<select v-model="selectedRegion">
<option value="North">
North
</option>
<option value="South">
South
</option>
<option value="East">
East
</option>
</select>
<label>Min Revenue ($):</label>
<input
v-model.number="minRevenue"
type="number"
>
</div>
<div v-if="pending">
Executing GViz SQL Query...
</div>
<table v-else>
<thead>
<tr>
<th>Region</th>
<th>Salesperson</th>
<th>Revenue</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>