Team Scorecard case study

Designing a Scalable Team Performance & NPS Platform on Google Apps Script

Internal Tool · Google Apps Script · Spreadsheet-backed application · Role-based workflows

This project focuses on designing and implementing a full-featured internal platform for performance tracking, team operations, and customer feedback management using Google Apps Script as the backend and Google Sheets as the data layer.

The system transforms fragmented spreadsheet workflows into a structured, role-aware, and interactive web application for Team Leaders and Agents.

Problem Context

The team originally relied on multiple spreadsheets to manage:

• Monthly KPI tracking

• Weekly performance breakdowns

• NPS survey review

• Team resources and operational links

• Internal announcements and team board updates

This approach introduced several limitations:

• No unified interface for agents and leads

• No role-based visibility or workflows

• No structured process for reviewing KPIs and-or customer feedbacks

• High cognitive load due to manual spreadsheet navigation

• Limited scalability when sheet formats changed

• Dashboard, filtered data limited "at-the same time" usage due to Google sheets limitations

System Architecture

Architecture pattern: Serverless frontend + Google Sheets as a database

• Backend: Google Apps Script

• Data layer: Google Sheets

• Frontend: HTML, CSS, and Vanilla JavaScript via HtmlService

• File ingestion: XLSX upload and conversion into survey sheets

Key Design Decisions

• Sheets as structured data tables
  Each sheet has a clear responsibility and predictable schema.

• Thin API layer
  Apps Script functions act as backend endpoints, such as getMonthlyResults, getWeeklyResults, getNpsReview, and saveNpsReviewUpdate.

• Client-side rendering
  The UI is rendered dynamically in the browser to improve perceived performance and reduce unnecessary round-trips.

• Configurable data mapping
  Survey fields are mapped through a dedicated configuration sheet so the Team Leader can adapt the system if the source format changes.

UX Strategy

The UI follows a data-first but human-centered design approach.

• Cards for scanability

• Tooltips for context without clutter

• Badges and pills for status communication

• Sticky headers for large scorecards

• Modern toast notifications

• Dark mode and light mode

Performance Considerations

• Batch reads with getRange().getValues()

• Batch writes where possible

• Use of Map() for fast identity and metric lookups

• Client-side rendering and filtering

• Reduced write frequency for review actions

• Separation of raw data sheets from workflow state sheets

Challenges and Trade-offs

• Spreadsheet as database
  This made the platform accessible and easy to maintain, but required careful schema discipline.

• Changing source formats
  Solved through configurable survey column mapping.

• Persistent workflow state
  Solved by storing comments and flags separately from raw survey data.

• Survey row instability
  Solved by replacing row-based keys with stable composite survey keys.

• Apps Script limitations
  Required minimizing backend calls and avoiding unnecessary writes.

Impact

• Reduced time spent navigating spreadsheets

• Centralized KPI, Weekly, and NPS workflows

• Introduced structured survey review and accountability

• Improved visibility for agents and Team Leaders

• Enabled Team Leaders to configure the system without changing code

• Created a more engaging and usable internal team platform

Conclusion

This project demonstrates how Google Apps Script and Google Sheets can be used to build a scalable, role-aware internal platform without introducing a traditional database or external backend.

By combining structured data modeling, stable identifiers, role-based workflows, and thoughtful UI design, the platform turns static spreadsheets into an interactive operational tool for performance and team management.