About This Project
A structured team diagnostic grounded in Google's Project Aristotle research required significant manual coordination per team: intake tracking, eligibility checks, file creation, communications sequencing, and results delivery. As program demand scaled, that manual overhead was becoming the primary bottleneck.
I designed and built an end-to-end automation system in Google Workspace (Forms, Sheets, Apps Script) and Zapier that reduced per-team manual effort to near zero, handling the full 4-week lifecycle automatically from sign-up to results delivery.
This demonstrates: learning systems architecture, Google Workspace and Apps Script development, automation logic design, scalable infrastructure thinking, and end-to-end program ops ownership. This was built as a working production system, not a prototype.
The Problem
The Team Effectiveness Assessment—grounded in Google Project Aristotle research across 4 pillars: Psychological Safety, Dependability, Structure & Clarity, and Purpose & Impact—required significant manual coordination per team:
- Manual scheduling and eligibility checks against headcount thresholds
- Per-team file creation from templates
- Multi-step communications sequencing over a 4-week window
- Results compilation and personalized delivery to each team leader
As program demand scaled, this bottleneck capped how many teams could participate per cycle.
The Solution
An end-to-end automation system handling the full 4-week lifecycle of each team's assessment—from intake to results delivery—requiring near-zero per-team manual intervention.
System Architecture
1. Sign-Up — Google Form
Leaders self-register with: name, team name, HR partner contact, preferred launch date.
2. Validation — Apps Script on Form Submit
Checks direct reports count against eligibility threshold.
If ≥ 5 direct reports → launch/close dates calculated + confirmation triggered
3. Automated Communications — Zapier 10-Step Sequence
| Step | Trigger | Communication |
|---|---|---|
| 00 | Leader interest | Program overview + intake form link |
| 01a | Sign-up (eligible) | Confirmation with launch/close dates |
| 01b | Sign-up (ineligible) | Ineligibility notice |
| 02a | Pre-launch (T−1 week) | Reminder + how-to-nominate instructions |
| 03 | Launch day | Assessment live—link + close date CTA |
| 04.1 | T+2 days | Participation reminder |
| 04.2 | T−1 day | Final participation push |
| 05 | Close day | Assessment closed confirmation |
| 06 | Results ready | Results link + debrief prep checklist |
4. Automated File Creation — Apps Script on Launch Day
- Creates team-specific Google Drive folder
- Copies template files (Doc, Sheet, Form) with team name and dates injected via
{{variables}} - Logs folder and results URLs back to sign-up sheet
5. Automated Results Delivery — Apps Script on Close Day
- Toggles form off
- Retrieves team folder from sheet reference
- Opens results Google Doc; replaces all
{{variables}}with live data from responses sheet - Sends results notification to team leader
Key Functions
| Function | Trigger Type | Action |
|---|---|---|
onFormSubmit | Form submission | Validates eligibility; logs dates |
checkLaunchDates | Daily time-based | Fires launch workflow when today == launchDate |
checkCloseDates | Daily time-based | Fires close workflow when today == closeDate |
newTeamEffectivenessAssessment | Called by checkLaunchDates | Creates folder, copies templates, logs URLs |
closeTeamEffectivenessAssessment | Called by checkCloseDates | Toggles form off, sends close notification |
sendResultsToDoc | Post-close | Injects live data into results Doc; triggers results comms |
Debrief System
Built alongside the automation: a facilitator template deck auto-populated with team results, structured around a 5-step process:
Assessment → Debrief → Focus Area → Action Items → Change
Included speaker notes, timing guidance, a Start/Stop/Continue activity framework, and linked pillar toolkits for each of the 4 Project Aristotle dimensions.
Scale & Outcomes
- Designed to support unlimited simultaneous cohorts with no manual per-team overhead
- Leader time commitment: ~2 hours total over 4 weeks
- Full end-to-end tested via mock submissions
- System built to be maintained by non-technical team members via sheet-based configuration
This Demonstrates
- Learning systems architecture—designed a complete operational infrastructure, not just a program
- Google Workspace development—Forms, Sheets, Apps Script, Drive folder management, template injection
- Automation logic design—eligibility branching, date-triggered workflows, multi-step Zapier sequences
- Scalable infrastructure thinking—system designed to run unlimited cohorts without incremental overhead
- End-to-end ownership—from problem identification through architecture, build, testing, and production deployment
- Non-technical handoff design—built so the system could be maintained without Apps Script knowledge