Automated Weekly Schedule Generator
The Problem
Team required new weekly schedule sheets every week but had no templates or standardized process. Manual workflow involved copying previous week's schedule sheet, manually updating all dates, renaming sheet with new date range, changing team assignments for bi-weekly rotation, and setting tab colors to highlight current week. Process consumed approximately 90 minutes weekly with frequent errors in date calculations, sheet naming, and team assignment updates requiring complete rework.
Team maintained 5-week forward planning window, requiring consistent schedule generation to prevent visibility gaps. Without templates, every schedule was manually created from previous week's copy, leading to inconsistent formatting and compounding errors. Bi-weekly complexity added cognitive load as alternating weeks required different team assignments and report distributions with no systematic way to track the rotation. Schedule creation was previously a management-level responsibility with no documentation or process guidelines for delegation.
The Solution
Built end-to-end automation using Office Scripts (TypeScript) and Power Automate scheduled trigger. Created two hidden template sheets for bi-weekly rotation, then developed TypeScript automation to handle complete schedule generation workflow. Script automatically hides oldest visible sheet to archive past weeks, colors current week tab for visual clarity, calculates 5 weeks ahead using date arithmetic, determines Monday to Sunday date range, checks even or odd week number to select correct template, copies appropriate template, renames with standardized format, sets Monday date in reference cell, and positions new sheet at workbook end.
Power Automate flow runs every Sunday morning when team is off, executing Office Scripts with zero manual intervention required. First TypeScript and Office Scripts project, self-taught through documentation and online resources to transition from VBA background. Solution ensures team always has consistent 5-week forward visibility with correct template rotation and accurate date calculations.
Architecture
Office Scripts TypeScript automation integrated with Power Automate weekly recurrence trigger. Two hidden template sheets (bi-weekly rotation pattern) stored in Excel workbook. Office Scripts uses worksheet visibility management, tab color manipulation, date arithmetic with 35-day offset calculation, conditional template selection based on week number parity, sheet copying and positioning methods, and range value setting for date references. Power Automate provides Sunday morning scheduled trigger running script action. All execution occurs server-side through cloud-based Excel.
Key Implementation Decisions
- •Office Scripts over VBA: Cloud-based execution enables Power Automate integration and scheduled automation
- •Hidden templates approach: Reusable templates remain accessible for updates while staying invisible to team during normal use
- •Sunday morning execution: Automation runs when team is off, ensuring new schedule ready for Monday morning
- •Tab color visual system: Current week highlighted automatically for instant identification
- •Even/odd week logic: Simple parity check determines correct template for bi-weekly rotation
- •TypeScript self-learning: First TypeScript project, transitioned from VBA through documentation and experimentation
The Results
Quantifiable Outcomes
- ✓Eliminated 90-minute weekly manual process, saving 78 hours annually
- ✓Zero schedule creation errors after automation deployment
- ✓Consistent 5-week forward planning maintained automatically
- ✓Full team adoption with continued use after role transition
- ✓Maintained cross-team support relationships for ongoing updates
- ✓Demonstrated TypeScript proficiency through self-directed learning
- ✓Successful programming language transfer from VBA to TypeScript
Lessons Learned
- →Programming language transfer accelerates new technology adoption: VBA background provided foundation for learning TypeScript
- →Template-based automation balances flexibility and consistency: Hidden templates allow easy updates while maintaining standardized output
- →Scheduled automation eliminates maintenance burden: Sunday execution ensures zero interruption to team workflow
- →Visual cues improve usability: Tab color system provides instant current-week identification without reading dates
- →Self-directed learning works for production systems: Built first TypeScript project through documentation alone, deployed to team successfully