Dynamic Climbing Leaderboard (Google Sheets Integration)

A dynamic leaderboard for a climbing gym challenge, pulling live data from a Google Sheet via GViz API. It aggregates feet climbed per participant, splits staff vs climbers, shows progress toward a mile, and includes filtering and highlighting features.

See it here: Climbing Leaderboard

Capabilities

Fetches and processes Google Sheets data using GViz JSONP API.
Aggregates total feet climbed per email, using the latest full name and staff status.
Displays separate leaderboards for climbers and staff, with rankings, totals, and progress bars toward 5280 feet (1 mile).
Includes filtering by name/email, highlighting via URL params, and auto-refresh every 2 minutes.
Handles errors, loading states, and empty results gracefully.

Key HTML Snippet: Progress Bar in Table Row

The markup uses Vue directives for reactivity, with tables for leaderboards and inline-styled progress bars.

<tr v-for="(row, idx) in filteredPaying" :key="row.email">
    <td>{{ idx + 1 }}</td>
    <td>{{ row.fullName || '(no name)' }}</td>
    <td style="text-align:right;">{{ Math.round(row.totalFeet).toLocaleString() }}</td>
    <td>
        <div style="display:flex; align-items:center; gap:6px;">
            <div style="flex:1; background:#e5e7eb; border-radius:6px; overflow:hidden; height:12px; position:relative;">
                <div :style="{
                    width: progressPercent(row.totalFeet).toFixed(1) + '%',
                    background: progressPercent(row.totalFeet) >= 100 ? '#16a34a' : '#2563eb',
                    height: '100%',
                    transition: 'width .3s'
                }"></div>
            </div>
            <div style="min-width:45px; font-size:0.75rem; text-align:right;">
                {{ progressPercent(row.totalFeet) >= 100 ? '100%' : progressPercent(row.totalFeet).toFixed(1) + '%' }}
            </div>
        </div>
    </td>
</tr>

Key JavaScript Snippets (Vue 2 Options API)

The Vue 2 app handles data fetching, processing, and reactivity with key methods for loading and filtering.

Data Loading via Google Visualization API

Uses JSONP to fetch Google Sheets data asynchronously.

loadViaGViz() {
    return new Promise((resolve, reject) => {
        const SHEET_ID = '123';
        const SHEET_NAME = 'Form Responses 1';

        const timeout = setTimeout(() => {
            reject(new Error('Timed out loading sheet via GViz JSONP'));
        }, 10000);

        window.google = window.google || {};
        window.google.visualization = window.google.visualization || {};
        window.google.visualization.Query = window.google.visualization.Query || {};

        window.google.visualization.Query.setResponse = (payload) => {
            clearTimeout(timeout);
            try {
                const board = this.processGVizPayload(payload);
                resolve(board);
            } catch (e) {
                reject(e);
            }
        };

        const script = document.createElement('script');
        const base = `https://docs.google.com/spreadsheets/d/${encodeURIComponent(SHEET_ID)}/gviz/tq`;
        const params = new URLSearchParams({
            tq: 'select *',
            tqx: 'out:json',
            sheet: SHEET_NAME,
        });
        script.src = `${base}?${params.toString()}`;
        script.onerror = () => {
            clearTimeout(timeout);
            reject(new Error('Failed to load GViz JSONP script'));
        };
        document.body.appendChild(script);
    });
}

Data Processing and Aggregation

Processes GViz response, aggregates feet by email, tracks latest name and staff status.

processGVizPayload(data) {
    if (!data || !data.table) throw new Error('Bad GViz payload');
    const cols = data.table.cols.map(c => (c.label || '').toString().trim());
    const lower = cols.map(c => c.toLowerCase());
    const emailIdx = lower.findIndex(h => h === 'email address');
    const feetIdx = lower.findIndex(h => h === 'how many feet climbed');
    const nameIdx = lower.findIndex(h => h === 'full name');
    const timestampIdx = lower.findIndex(h => h === 'timestamp');
    const staffIdx = lower.findIndex(h => h === 'are you a staff member');

    if (emailIdx === -1 || feetIdx === -1 || nameIdx === -1 || timestampIdx === -1) {
        throw new Error(
            `Missing expected headers. Found: ${cols.join(
                ', '
            )}. Need Email Address, How many feet climbed, Full Name, Timestamp.`
        );
    }

    const accum = new Map();
    const rows = data.table.rows || [];
    for (const r of rows) {
        const cells = (r.c || []).map(cell => {
            if (!cell) return '';
            return typeof cell.v === 'number' ? cell.v : (cell.v || '').toString().trim();
        });

        const emailRaw = cells[emailIdx] || '';
        const email = this.normalizeEmail(emailRaw);
        if (!email) continue;

        let feetRaw = cells[feetIdx] || '';
        feetRaw = feetRaw.toString().replace(/,/g, '');
        const feet = parseFloat(feetRaw);
        if (isNaN(feet)) continue;

        const fullName = (cells[nameIdx] || '').toString().trim();
        const timestampVal = this.parseTimestamp(cells[timestampIdx]);
        let isStaff = false;
        if (staffIdx !== -1) {
            const staffVal = (cells[staffIdx] || '').toString().trim().toLowerCase();
            if (staffVal === 'yes') isStaff = true;
        }

        const existing = accum.get(email);
        if (!existing) {
            accum.set(email, {
                totalFeet: feet,
                latestTimestamp: timestampVal,
                fullName,
                isStaff,
            });
        } else {
            existing.totalFeet += feet;
            if (timestampVal > existing.latestTimestamp) {
                existing.latestTimestamp = timestampVal;
                existing.fullName = fullName;
                existing.isStaff = isStaff;
            }
            accum.set(email, existing);
        }
    }

    const arr = Array.from(accum.entries()).map(([email, info]) => ({
        email,
        totalFeet: info.totalFeet,
        fullName: info.fullName,
        isStaff: !!info.isStaff,
        latestTimestamp: info.latestTimestamp,
    }));
    arr.sort((a, b) => b.totalFeet - a.totalFeet);
    return arr;
}

Computed Properties for Filtering

Reactive filtering for climbers and staff based on name/email.

filteredPaying() {
    if (!this.filter.trim()) return this.paying;
    const f = this.filter.toLowerCase();
    return this.paying.filter(r => {
        return (
            (r.fullName && r.fullName.toLowerCase().includes(f)) ||
            (r.email && r.email.toLowerCase().includes(f))
        );
    });
}

Auto-Refresh on Mount

Loads data on mount and sets up 2-minute interval refresh.

async mounted() {
    this.parseHighlight();
    await this.load();
    this.autoRefreshHandle = setInterval(() => this.load(), 120000);
}

Portfolio javascript vue google-sheets api leaderboard climbing data-visualization filtering progress-bar