Past-Due Task Tracker

Business impact

Replaced an unvalidated spreadsheet tab with an accountable weekly process: every count has an owner, a snapshot date and an audit row, managers get a digest without chasing people, and the EOS scorecard updates itself from the same numbers.

Problem

Past-due task counts lived in a shared spreadsheet tab with no validation, no trend view and no accountability for who entered what. Moving the numbers onto the EOS scorecard was a manual chore, and the counts came from several systems, some of which have no export at all.

Solution

A Google Apps Script web app embedded in the team portal. Staff pick their name from the roster, enter counts for the items assigned to them, or fill the grid from an export or a screenshot; managers and admins see team trends, a Friday Slack digest and draft Issues for the leadership meeting; the weekly metrics are pushed to the Ninety.io scorecard through a Zapier catch hook.

Results & highlights

• Shipped v1.0 on 2026-09-30 and iterated through nine releases to v1.6.1 by 2026-10-14: spreadsheet import (v1.3), screenshot import (v1.4), single-total and separate-box items (v1.4.x), an 'in metrics' switch per item (v1.5), range completeness and an onboarding average (v1.6)
• Privacy by design: Excel/CSV exports and screenshots are parsed in the browser; only per-person counts reach the server, with the file's SHA-256 logged for duplicate detection and raw task rows never stored
• Screenshot import with Tesseract OCR: every queue column must add up to the TOTAL row, or the affected cells are flagged for human review before anything is saved
• Role precedence and effective-dated assignments: Import < Agent < Manager < Admin decides who may overwrite an entry, and switching an item on or off never rewrites earlier weeks
• Weekly scorecard push with dry run, push log and corrected-week re-push; Slack reminders and digests start switched off and are previewed before they are enabled
• Quality gate: 36 engine and integration tests at v1.6 (24 engine tests in pure Node, 12 integration tests through an Apps Script shim), plus idempotent, editor-run migrations

Competencies demonstrated

• Workflow automation: Entry-to-scorecard pipeline in Google Apps Script with a Zapier catch hook into Ninety.io.
• Privacy-by-design engineering: Browser-side parsing of exports and screenshots; only per-person counts and a file hash reach the server.
• OCR and data validation: Tesseract.js screenshot reader with column-sum verification against the TOTAL row and cell-level flags.
• Access control design: Role precedence for overwrites and effective-dated assignments that preserve history.
• Test automation: 36 engine and integration tests at v1.6 (plus import and OCR suites), including integration tests through an Apps Script shim in Node.

Architecture

Notes

• The roster and item names in the repository are fictional; agency data fixtures are not included.