The Challenge
A distributed engineering team was running work across chat, ad-hoc spreadsheets, and task comments that never matched daily standups. Managers discovered gaps at night — blank Today columns, frozen task progress, remarks that had not moved in weeks — only after chasing individuals in separate threads.
Buying another PM tool was not the constraint; adoption and review rhythm were. People would not maintain a second system if the sheet they already lived in did not prep rows, flag gaps, and send one honest evening summary to leadership.
The Approach
We treated the Google Sheet as the product: Daily Updates for narrative and hours, Current Tasks for delivery truth, Performance for scored outcomes, and a read-only Remark Progress Feedback tab for audits. Apps Script handled refresh, reminders, and digests — humans still authored Today and task remarks.
- Fixed daily schema — Date, employee, Yesterday (prefilled), Today, blockers, hours, progress, remarks, standup marker.
- Task coupling — Progress and remarks on Current Tasks with stale/missing detection against snapshots.
- Lead columns — Separate remark-by-lead fields on dailies and tasks so review notes do not overwrite team history.
- Time-based jobs — Hourly dashboard refresh, 6 PM manager email, 8 PM next-day row prep (Asia/Kolkata).
- Throttled onEdit — Light refresh on edit so dashboards stay current without timing out on every keystroke.
What We Built
Two script files — configuration, scoring, dashboard, and performance in one; jobs, email, menu, triggers, and formatting in the other. Menu actions cover refresh, sync tasks from dailies, repair collapsed dates, smoke test, and diagnose automation.
The evening job refreshes dashboard and performance, updates weekly review and remark feedback, applies lead review drafts on empty placeholder cells, sends missing-update reminders, absent warnings after consecutive blank days, and one HTML manager digest mirroring sheet columns — not a separate narrative.
How It Worked
Team members fill Today, hours, and progress; Yesterday rolls forward automatically. Scripts flag missing rows since a tracking start date, respect holidays and Sundays, and categorize stale task remarks. Managers receive one email at 6 PM with today’s dailies, missing dates, open remark issues, and performance snapshot — then act the next morning with names attached.
Sync from dailies appends dated snippets into matching task remarks when Today describes real module work — without overwriting human notes. Performance scores blend task completion, daily submission, hours, progress, and lead ratings into a transparent matrix HR can read.
Results
Blank Today rows became visible before they stacked into multi-day gaps. Task remark audits replaced “please update your tasks” broadcasts with categorized flags — missing, stale, partial. Managers stopped holding a second standup just to learn what the sheet already knew.
The sheet stayed the system of record because automation reduced prep work instead of adding homework. New hires inherited rows from join dates; tracking start dates prevented false missing flags. The team kept using chat for speed — but trusted the tracker for truth.
Takeaway
Not every ops problem needs a new SaaS seat. When the team already lives in Sheets, invest in schema discipline, snapshot-based remark audits, timed digests, and lead columns that separate coaching from authorship. Automation should prep, flag, and summarize — not ghost-write the work report.