Internal Operations / Enterprise Architecture
The MJO Dashboard: A Full-Stack Operations Platform
The Mission
Manhattan Justice Opportunities is a court-mandated diversion program serving 3,000+ participants per year with a team of 35. I designed and built the day-to-day operations platform from scratch: a centralized AppSheet dashboard backed by Google Apps Script automations, Acuity Scheduling integration, and Slack webhooks. The system handles foot traffic management, court compliance automation, inventory logistics, and real-time team coordination. It's used by every staff member, every day.
System Architecture
Dashboard Modules
- Front Desk Single-Pane-of-Glass Real-time Mission Control for reception. Pulls live appointments from Acuity API, provides one-click Quick Check-In (pre-populated participant log entries), and a walk-in Sign-In form with rotation assignment logic. Both check-in methods fire Slack webhooks to the office channel. If a case manager is assigned, the system threads their tag under the notification. Virtual columns flag safety concerns instantly upon name entry.
- Phone Distribution System Ticketed request flow replacing unstructured Slack DMs. Case managers submit requests via form (participant, delivery method, turnaround). AppSheet Bot fires a webhook on row creation notifying the office manager. On fulfillment, a second bot notifies the original requester with the phone number and status. Full audit trail of every hardware assignment for leadership reporting.
- Participant Analytics Dual-purpose module combining a real-time walk-in log with historical traffic analysis. Split-panel view shows today's visitors alongside the all-time table. Four chart views (weekly volume, time-of-day distribution, day-of-week breakdown, monthly trends) directly inform how walk-in shifts are staffed throughout the week.
- Resource Portal Searchable database of 100+ NYC service providers with gallery and map views. Per-user favorites via USEREMAIL(), a verification system that timestamps successful referrals ("Last verified X days ago"), and PDF uploads for program brochures and intake forms. Browse by category (housing, healthcare, mental health, employment, legal aid) or view all providers on an interactive map.
- Supplies and Distribution E-commerce-style inventory with session-based shopping carts per user. Add items, adjust quantities, confirm checkout, which deducts from live counts. Multiple staff can browse and check out simultaneously without conflicts. When any item hits quantity = 3, a Slack webhook fires a low-stock alert to the office manager. Every transaction logged for budgeting.
Backend Automation
- Compliance Reporting Suite Four integrated scripts replacing a 5-6 hour/week manual process (~3 min/case x 30+ daily cases). Pulls upcoming court dates from master sheets with 10-week lookahead, batch-generates Google Docs from templates with placeholder replacement, organizes into nested Drive folders (Year > Month > Week > Day > Court Part), and drafts consolidated emails grouped by court part with weekend-skip date logic. Tracks progress across runs with execution time guards for the 6-min Apps Script limit.
- Community Service Tracker Full ETL pipeline processing attendance from 4 partner organizations. Bilingual form parsing (EN/ES column unification), fuzzy org-name matching ("Manhattan Justice Opportunities" / "mjo" / "manhattan justice" all map to canonical codes), and timestamp-based sign-in/sign-out pairing. Dual processing modes: incremental trigger on each form submission + full batch rebuild. In-sheet modal dialog with searchable participant reports, total hours, and monthly breakdown.
- Appointments Fetcher: Pulls upcoming staff appointments from Acuity across all calendars in a rolling 6-week window. Multi-calendar iteration with form field extraction for intake notes.
- Wait Times Crawler: Hourly scheduled script that checks Acuity availability across all calendars and appointment types with a 100-day lookahead. Writes live wait times and maintains a historical log for the Monitor dashboard.
- Groups Fetcher: Pulls group class schedules and client rosters from Acuity for current and next month. Joins class availability with individual bookings into a single row-per-client view.
- Group Calendar Generator: Acuity API integration that produces monthly calendar layouts with color-coded class types and facilitator assignments.
- MCJC-ifier: Case categorization engine that routes referrals by precinct, charges, and eligibility criteria. Generates CSVs and email content for court partner intake.
- Court Date Sync: Bidirectional data sync between master case sheets and working documents. Intelligent filtering, deduplication, and date history preservation across multiple tabs.
Code Samples
GenerateComplianceDrafts.gs
// Batch-generates compliance reports into nested Drive folders
// Tracks progress across runs to handle 6-min execution limit
function generateComplianceReports() {
var startTime = new Date().getTime();
var BATCH_SIZE = 15;
var props = PropertiesService.getScriptProperties();
var currentRowIndex = parseInt(props.getProperty('currentRowIndex')) || 1;
var data = sheet.getDataRange().getValues();
for (var i = currentRowIndex; i < data.length; i++) {
if (processedCount >= BATCH_SIZE) break;
// Build nested folder: Year > Month > Week > Day > Part
var yearFolder = getOrCreateFolder(mainFolder, yearStr);
var monthFolder = getOrCreateFolder(yearFolder, monthStr);
var weekFolder = getOrCreateFolder(monthFolder, weekFolderName);
var dayFolder = getOrCreateFolder(weekFolder, dayFolderName);
var partFolder = getOrCreateFolder(dayFolder, part);
// Copy template and replace placeholders
var tempDoc = DriveApp.getFileById(templateId).makeCopy(partFolder);
var body = DocumentApp.openById(tempDoc.getId()).getBody();
body.replaceText("\\{\\{Participant Name\\}\\}", participantName);
body.replaceText("\\{\\{Docket \\#\\}\\}", docket);
body.replaceText("\\{\\{Adjourn\\. Date\\}\\}", adjournDate);
// Guard against 6-min timeout
if (new Date().getTime() - startTime > 280000) {
props.setProperty('currentRowIndex', i + 1);
return; // Resume on next run
}
processedCount++;
}
props.setProperty('currentRowIndex', currentRowIndex + processedCount);
}
SlackCheckIn.gs
// Fires when Quick Check-In action is triggered in AppSheet
// Posts to office Slack channel + threads CM if assigned
function sendCheckInNotification(participant, visitReason, cmName, cmSlackId) {
var webhookUrl = PropertiesService.getScriptProperties()
.getProperty('SLACK_WEBHOOK_URL');
// Post main notification to office channel
var mainPayload = {
"text": "Walk-in: *" + participant + "*\n" +
"Reason: " + visitReason + "\n" +
"Status: Waiting Area"
};
var response = UrlFetchApp.fetch(webhookUrl, {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(mainPayload)
});
// Thread the case manager tag if assigned
if (cmSlackId) {
var threadTs = JSON.parse(response.getContentText()).ts;
var threadPayload = {
"text": "<@" + cmSlackId + "> your client has arrived.",
"thread_ts": threadTs
};
UrlFetchApp.fetch(webhookUrl, {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(threadPayload)
});
}
}