8
Hubstaff entities synced daily
Case Study
Workforce Data Trapped in a Time-Tracking Tool
Eight Hubstaff entities. Automated daily sync. A failing 6-minute trigger fixed to run in 22 seconds. Project delivery, utilisation, and cost dashboards — rebuilt from scratch.
A global learning & development company tracked all project work inside Hubstaff — but leadership had no way to answer basic questions: which projects are running over budget, which team members are under-utilised, what is the true cost per project? Every answer required a manual CSV export and spreadsheet lookup. Worse, the existing Google Apps Script pipeline had already failed in production — the daily trigger was hitting the 6-minute Apps Script quota. We audited, fixed, and extended the pipeline across all 8 Hubstaff entities, then built a Zoho Analytics reporting layer on top.
Professional Services · Global·Hubstaff APIGoogle Apps ScriptZoho AnalyticsGoogle Sheets
Z
Global L&D CompanyHubstaff APIGAS
Daily sync · 22s
Active Projects
18
▲ +3 this month
Hubstaff
Team Members
24
across all projects
Avg. Utilisation
87%
▲ +6pp vs last wk
tracked / planned
Total Cost (week)
$28,440
▼ +4.2% WoW
pay rate × hours
Pipeline trigger
22s
▲ was 6+ min
16x faster
Project Delivery Utilisation + Cost
Zoho AnalyticsLift & Shift 2026
108h / 120h90%$9,180
PI Scoring Dashboard
84h / 80h105%$7,140
Client KPI Scorecard
52h / 60h87%$4,420
Squad Perf NDM
38h / 40h95%$3,230
Health Check
34h / 30h113%$2,890
On track Near limit Over budget
Team Utilisation This Week
8 entitiesAriel M.
38h
Priya K.
42h
Tom B.
28h
Sara L.
36h
Dev R.
40h
Tracked Hours — Weekly Trend
Daily sync▲ +36% tracked hours since pipeline restored
Task Completion Scorecard
HubstaffCompleted
284In Progress
97Open
143Overdue
18Client Delivery Summary
BillingClient A
$15,810
186h
Client B
$12,070
142h
Client C
$8,330
98h
Client D
$6,460
76h
Pipeline Fix — Speed
16x fasterBefore fix
6+ min
Per-row writes · quota exceeded
After fix
22s
5,724 tasks · batched writes
At a glance
Results That Speak for Themselves
16x
Pipeline speed improvement after fix
22s
5,724 tasks processed (was 6+ min)
0
Manual exports required
From challenge to outcome
The Problem & Our Approach
Workforce Data Trapped in a Time-Tracking Tool
Hubstaff is excellent at capturing time and activity data — but it is not a reporting tool. The client had months of rich project delivery data sitting in Hubstaff with no way to answer basic operational questions. Every answer required a manual export, a spreadsheet lookup, and significant manual effort — repeated every week. The daily pipeline had already failed in production.
- No analytics layer — Hubstaff's built-in reports are fixed; no custom cross-entity queries possible.
- Eight data entities stored in separate API endpoints with no unified view.
- Manual exports — any reporting required downloading CSVs and stitching them in a spreadsheet.
- No cost visibility — staff cost per project or task was not calculable without a manual exercise.
- No utilisation tracking — whether team members were deployed or idle was invisible to management.
- Critical bug: per-row Sheets writes were timing out the daily trigger — the pipeline had already failed in production with hundreds of task updates exceeding the 6-minute Apps Script quota.
- Data source ownership tied to one employee's Google account — when they left, the sync stopped entirely.
A GAS Pipeline Across 8 Hubstaff API Endpoints
Lets Viz audited, fixed, and extended the existing Google Apps Script pipeline that pulls data from the Hubstaff API and writes it into Google Sheets — which Zoho Analytics then reads as a live data source. The pipeline covers all eight Hubstaff data entities, runs on a daily time-driven trigger, and feeds dashboards for project delivery, team utilisation, and cost tracking.
- Critical fix — batched Sheets writes: the update loop was calling setValues once per row (50–200ms each). Fix: sort updated rows by index, group contiguous rows into blocks, write each block with one setValues call. Result: 5,724 tasks processed in 22 seconds — 16x faster than the failing run.
- 8 Hubstaff endpoints covered: /organization, /projects, /tasks, /activities, /clients, /teams, /members, /users — cursor-paginated, OAuth token auto-refreshed.
- Google Sheets as staging layer — one tab per Hubstaff entity. Zoho Analytics reads each tab as a live data source.
- Derived query tables (v3, v4) join Activities + Tasks + Users + Projects. All dashboards reference derived tables — insulated from raw schema changes. One query update fixes everything upstream.
- Resilience fix: data source ownership migrated to a shared service account. One departing employee can no longer break the analytics pipeline.
What We Achieved
Daily automated sync — all 8 Hubstaff entities update every morning. Zero CSV downloads, zero copy-paste.
Project delivery visibility — planned vs. tracked hours and activity levels across all active projects, updated daily.
Cost per project calculable for the first time — member pay rates joined with tracked hours.
Pipeline 16x faster — batched writes brought a failing 6-minute trigger down to 22 seconds for 5,724 tasks.
Resilient to staff changes — data source ownership on a shared account; one departing employee cannot break the pipeline.
Zoho Analytics insulated from schema changes — derived-table query chain means raw column renames require one query update, not a dashboard rebuild.
Pipeline Architecture — Four Layers
| Layer | What It Does |
|---|---|
| Hubstaff API | REST API with OAuth token (auto-refreshed). Cursor-paginated for large entity sets. |
| Google Apps Script | Daily trigger pulls each endpoint, appends new records, upserts changed records using batched Sheets writes. Contiguous-row grouping keeps runs well within the 6-minute quota. |
| Google Sheets | Master data store — one tab per Hubstaff entity. Staging layer between Hubstaff and Zoho Analytics. |
| Zoho Analytics | Reads Sheets tabs as a live data source. Derived query tables (v3, v4) join entities. Dashboards reference derived tables — insulated from raw schema changes. |
Dashboards Built in Zoho Analytics
| Dashboard / Report | What It Answers |
|---|---|
| Project Delivery Utilisation | Planned hours vs. tracked hours vs. activity level per active project. Flags projects running ahead or behind on delivery pace. |
| Project Delivery Utilisation with Cost | Adds staff cost (member pay rate × tracked hours). Surfaces true delivery cost per project. |
| Team Utilisation | Hours tracked per team member per week. Identifies under-utilised and overloaded members. |
| Task Completion Scorecard | Tasks by status (open, in progress, completed, overdue) per project. Delivery health at task level. |
| Client Delivery Summary | Aggregated delivery hours and cost per client for billing reconciliation and profitability reviews. |
“
Great experience with Rohit and their team of devs who worked across our Zoho One workspace.
Ariel·Client — Google Review
The Pipeline Resilience Fix
When a team member who had authorised the Zoho Analytics Google Drive connection left the company, their OAuth token expired and all three syncing tables — Tasks, Activities, and Users — stopped updating simultaneously. The dashboards went stale with no obvious error surfaced to the team.
- Diagnosed via Zoho Analytics Audit History — 'Fetching Google Spreadsheet failed' on all three tables.
- Identified the correct re-authentication path: hover data source → gear icon → Re-Authenticate (not the Edit Setup modal).
- Migrated to a fresh Google Sheet under a shared service account — no single employee can break the sync again.
- Audited all 7 Zoho Analytics query tables for stale table name references; updated all affected queries in one session.
- Architecture principle: no dashboard query references a raw Sheets tab directly. All queries go through derived tables — when the raw source changes, only one query needs updating.
The Pattern — Not the Industry
Most companies that use Hubstaff, Toggl, Harvest, or any time-tracking platform are capturing detailed data about how their team spends every hour — and doing almost nothing analytical with it. The data exists. APIs exist. What is missing is the bridge between the tracking tool and a reporting layer that leadership can actually use.
- Google Apps Script runs inside Google Workspace — no new infrastructure, no recurring middleware cost.
- Zoho Analytics connects to Google Sheets as a live data source — dashboards update automatically.
- The derived-table pattern insulates dashboards from API changes and schema migrations.
- Any workforce tool with an API can be wired into this architecture — Hubstaff, Toggl, Harvest, Jira, or a mix.
- If your team is manually exporting CSVs from a time-tracking tool to build a weekly report — that is the problem we solve.
Discuss a similar Professional Services project
Lets Viz runs a paid discovery audit ($500–1,000, credited toward the project) to scope your requirements, data model, and architecture before writing a line of code.
From Lets Viz
Helping companies make data-driven decisions to achieve their Goals
Experience superior data analysis with our custom dashboards. Specialising in all tools and technologies, we turn data into visually compelling, actionable insights.
NDA-safe · No obligation · Clear next steps