OpsPilot

OpsPilot is a Google Apps Script operations command center for recurring team workflows. It combines spreadsheet planning, LMS term context, and Trello-style work-board movement into one dashboard so an operations team can preview, run, and verify weekly work from a single place.

This public-safe edition keeps the Apps Script architecture, dashboard workflow, and automation patterns while replacing internal team, board, spreadsheet, user, library, and deployment details with placeholders and mock data.

Product Overview

OpsPilot was built for repeated operational cycles where the team needs to know what is ready, what is assigned, what moved, and what still needs attention. The dashboard supports both big weekly automation and smaller mid-week actions:

• Sync board status back to the operations spreadsheet.
• Refresh a schedule/planning sheet based on current term context.
• Move this week's work into the active queue.
• Preview queue movement before running automation.
• Review person-level work stats and open workload.

Core Features

Weekly Automation

• Preview-first weekly workflow so users can inspect expected movement before running.
• Status sync between work-board custom fields and a spreadsheet master list.
• Schedule refresh that rebuilds the planning view from current term and active-course context.
• Launch flow that moves queued and upcoming cards into the correct active lists.
• Integration-card handling for special pre-copy and post-copy workstreams.

Mid-Week Queue Flush

• Queue preview that separates normal cards from integration cards.
• One-click queue movement after preview.
• Result summary with moved counts, assigned-candidate counts, and integration movement.
• Dashboard refresh after the run so the visible counts stay current.

Schedule Dashboard

• Workstream summary for setup, review, pre-copy, post-copy, and related planning work.
• Current-term and next-term planning context.
• Spreadsheet formatting for schedule output, summary rows, week columns, progress cells, and notes.
• Web dashboard rendering of the same schedule structure for fast review.

People Dashboard

• Active-team lookup from a spreadsheet.
• Person-level counts for assigned work, completed setup, completed review, open setup, open review, queue, upcoming, and integration work.
• Trello-style card summaries grouped under each person.
• Name matching helpers for display names, labels, and custom-field values.

Progress And Access Control

• Restricted tool access based on the signed-in Apps Script user and approved-user data.
• Progress polling through CacheService so long-running jobs can show status updates.
• Clear success and failure states in the UI.
• Local mock adapter for review without live Trello, LMS, or spreadsheet calls.

Technology Stack

Area	Implementation
Runtime	Google Apps Script V8
UI	HtmlService, HTML, CSS, browser-side JavaScript
Client/server bridge	google.script.run with success and failure handlers
Workspace APIs	SpreadsheetApp, CacheService, Session, Apps Script logging
External data pattern	Canvas/LMS-style library and Trello-style board library wrappers
Project shape	clasp-style source files plus appsscript.json

Architecture Notes

• OpsEntry.js exposes the Apps Script doGet entrypoint.
• OpsWebApp.js owns the web app controller functions, access checks, dashboard bootstrap, progress polling, weekly automation entrypoints, queue flush, and people stats.
• OpsAutomation.js contains the schedule build, board movement, status sync, term/week calculations, spreadsheet formatting, and card helper logic.
• OpsConfig.js centralizes placeholder configuration.
• OpsConfig.example.js documents the safe configuration shape for a real deployment.
• operations.html contains the dashboard UI, tabs, preview/result rendering, progress footer, and local mock adapter.

Configuration

Use placeholder values until connecting the app to a real environment:

const OPS_DASHBOARD_CONFIG = {
  spreadsheets: {
    operations: "REPLACE_WITH_SPREADSHEET_ID",
    approvedUsers: "REPLACE_WITH_SPREADSHEET_ID",
    activeTeam: "REPLACE_WITH_SPREADSHEET_ID"
  },
  trello: {
    boardId: "REPLACE_WITH_TRELLO_BOARD_ID"
  }
};

The Apps Script manifest uses placeholder library IDs:

{
  "userSymbol": "TrelloAPILibrary",
  "libraryId": "REPLACE_WITH_APPS_SCRIPT_LIBRARY_ID"
}

Local Demo

Open operations.html directly in a browser. When the page is not running inside Apps Script, it injects a mock google.script.run adapter that returns sample dashboard metrics, previews, progress updates, and people statistics.

Syntax check:

node --check OpsEntry.js
node --check OpsConfig.js
node --check OpsWebApp.js
node --check OpsAutomation.js

Public-Safe Data Handling

This repository was rebuilt with fresh Git history. Internal team names, school domains, real spreadsheet IDs, Trello board/list/custom-field IDs, Apps Script library IDs, deployment workflows, approved-user emails, and local credential files were removed or replaced with placeholders.

No open-source license is included because the original code was created for a job context.

What This Demonstrates

• JavaScript development inside the Google Apps Script platform.
• HtmlService UI development with google.script.run client/server calls.
• Workspace automation with SpreadsheetApp, CacheService, Session, and Apps Script web app patterns.
• Work-board automation patterns for previews, queue movement, custom-field sync, and person-level reporting.
• Quota-aware dashboard design with progress polling for long-running jobs.
• Operational product thinking: preview before action, clear status, restricted tools, refreshable dashboards, and audit-friendly summaries.