Colleges collect thousands of feedback responses every semester using Google Forms.
Once the data crosses 20,000+ rows, Google Sheets becomes slow, hard to filter, and unreliable.
I built Insight4Excellence, a production-ready feedback analytics portal that handles large Google Sheets datasets using server-side aggregation, caching, and a clean analytics dashboard.
This post is useful if you are:
- Working with large Google Forms or Sheets datasets
- Building analytics dashboards
- Optimizing backend performance
- Interested in scalable system design
🚀 The Problem
Google Forms + Sheets are great for data collection, but not for large-scale analysis:
- Sheets UI becomes slow with 20k+ rows
- No advanced filtering or aggregation
- Hard to compare departments, courses, or faculty
- Limited visualization options
- No security or role-based access
Most importantly, frontend-only filtering does not scale. Pulling 25k rows into the browser just to filter or visualize them is inefficient and bad for performance.
🏗️ What I Built: Insight4Excellence
Insight4Excellence is a secure analytics portal that connects directly to Google Sheets and transforms raw feedback data into actionable insights.
Key capabilities:
- Secure admin login (JWT-based authentication)
- Connect any Google Sheet containing feedback data
- Auto-detected filters (Department, Course, Year, Faculty, etc.)
- Server-side aggregation for analytics
- Paginated filtered data view
- CSV export for reports
- Optional AI-powered insights using Google Gemini
The goal was simple:
Move all heavy computation to the backend and keep the frontend fast.
🧠 High-Level Architecture
The system follows a clean, scalable architecture:
Frontend (React + TypeScript)
Renders charts, filters, and dashboards using summarized data only.Backend (Node.js + Express)
Fetches raw sheet data, performs aggregation, filtering, and caching.Google Sheets API
Acts as the data source (read-only via service account).Cache Layer (Node-Cache)
Prevents repeated expensive API calls.Optional AI Layer (Gemini)
Converts analytics into strategic insights.
The frontend never receives raw 25k rows — only aggregated JSON.
⚡ Why Server-Side Aggregation Matters
Instead of sending all sheet data to the browser:
- Raw data is fetched once from Google Sheets
- Cached in memory with a TTL
- Aggregated on the backend
- Only summarized results are sent to the client
This approach:
- Reduces payload size by ~70%
- Improves response time drastically
- Keeps the UI responsive even on low-end devices
- Makes pagination and filtering predictable
🔥 Handling 25K+ Rows Efficiently
This was the most important part of the project.
Techniques I used
1. Intelligent Caching
- Raw sheet data cached for 10 minutes
- Analytics results cached separately
- Metadata (headers, filters) cached independently
2. Optimized Filtering
- Converted filter values to
Setlookups (O(1)) - Avoided repeated array scans
- Deduplicated simultaneous requests
3. Pagination
- Filtered results are paginated (50 rows per page)
- Prevents large payloads
- Improves perceived performance
4. Minimal Google Sheets API Calls
- Sheets API is only called on cache miss
- Most requests are served from memory
Resulting Performance
| Scenario | Response Time |
|---|---|
| Cached analytics | < 100ms |
| Filter change | < 50ms |
| Pagination | < 20ms |
| First load (25k rows) | 3–8 seconds |
📊 Dynamic Filters & Analytics
The system automatically detects filterable columns from the sheet headers, such as:
- Department
- Course
- Year
- Faculty
- Semester
Feedback responses are normalized across:
- Likert scales (Strongly Agree → Strongly Disagree)
- Numeric ratings (1–5)
- Qualitative ratings (Excellent → Poor)
This allows consistent aggregation and meaningful visualizations using charts and summary cards.
🤖 AI-Powered Insights (Optional)
Instead of stopping at charts, I added an optional AI insights layer using Google Gemini.
Admins can generate:
- Strengths and weaknesses analysis
- Department-wise improvement suggestions
- Strategic observations beyond raw numbers
AI is kept optional by design so the core system works independently and securely.
🔐 Security Considerations
- JWT-based authentication with protected routes
- Google Sheets accessed via service account
- No credentials exposed to the frontend
- Environment-based configuration for secrets
Security was treated as a requirement, not an afterthought.
📚 What I Learned
This project taught me more than any small demo app:
- Why frontend-only analytics don’t scale
- How caching strategy directly impacts UX
- Designing APIs for performance, not just correctness
- Balancing flexibility with simplicity
- Building systems meant for real users and real data
🔮 What’s Next
Some planned improvements:
- Role-based access (department admins)
- Semester-wise trend comparison
- PDF report generation
- Advanced visualization options
- Historical analytics snapshots
🧩 Final Thoughts
Insight4Excellence started as a college requirement, but turned into a real-world system design exercise. It reinforced the importance of backend-driven analytics, performance optimization, and clean architecture when dealing with large datasets.
If you’re working with Google Forms, Sheets, or analytics-heavy dashboards — move the heavy lifting to the backend. Your users (and servers) will thank you.
If you’ve built analytics dashboards or handled large datasets, I’d love to hear how you approached performance and scalability. Feedback and suggestions are welcome.
Top comments (0)