Case Study: Replacing a 40,000-Row Excel File for a Utility Interconnection Program

Replacing a 40,000-Row Excel File for a Utility Interconnection Program

Building a production review-tracking platform in three months, with solo tasking, lacking onsite databases, service accounts, and a budget.

---

The situation

A distributed engineering team reviews distributed energy resource (DER) interconnection applications for a large investor-owned utility across multiple operating companies. Every application moves through multiple review rounds — completeness review, engineering review, image review — and each new round has its own assignment, its own quality-control pass, and its own client-facing deliverable.

All of it was running out of a shared Excel workbook on SharePoint.

The workbook wasn’t naive. Over several years it had accumulated a genuinely sophisticated apparatus: computed columns, validated dropdowns, conditional formatting, saved filters and sheet views, metrics dashboards, ~dozen Office Scripts, and ~dozen Power Automate flows handling internal QC routing and client communications. A sibling workbook pulled Salesforce exports into the exact shape the tracker expected, and rows were pasted across and assigned individually by hand.

At roughly 40,000 rows and 8–12 concurrent users, Excel Online was no longer able to hold everything together:

• Computed columns silently stopped propagating past a certain table row, but without warning.
• Dropdowns and conditional formatting were lost on newly appended rows, unpredictably.
• Office Scripts broke without notice when the runtime changed underneath them, and timed out as row counts grew.
• Power Automate flows terminated at a two-minute ceiling for Office Scripts, while the maintenance scripts they were meant to trigger had ballooned past ten minutes — so users ran the longest scripts by hand.
• Table filters leaked, at times allowing for accidental edits on rows users believed were hidden.
• Loss of work began. Concurrent edits at times only resolved to whichever version the sync happened to choose, without a discernible pattern or entirely reliable recovery path.

The last one is what truly forced a rewrite. Everything else was friction the team had found surmountable. Losing a reviewer’s completed work is a correctness failure, and there was no design for the workbook that could fix it.

The constraints

This is the part that shaped every architectural decision, so it’s worth stating plainly. As an embedded consultant, I did not have:

• A Salesforce service account. Personal API tokens only, permitted no write access, no admin rights, and no channel through which to propose a schema change to the client’s CRM.
• A database on either side. Nothing on the utility’s premises, nothing on the consultancy’s.
• Project-management tooling. No consultant-inclusive issue tracker for formally proposing changes to client CRM
• Billable pathways. The replacement effort could not be charged to the client, which meant it had to happen during the team working through a full review caseload.
• Dedicated team/review. A dedicated engineer was assigned to help build the data-pull layer, but moved to another project and not backfilled. The first replacement was designed and built solo, though teammates now contribute features and fixes through the same pipeline.
• Supervisory architectural guidance. No senior software support, reference implementation, or internal precedent.

What I did have: GitHub Enterprise, a $5/month Cloudflare subscription, a similarly priced flat-rate AI coding subscription, and one quarter.

The binding constraint was that anything I built had to stand entirely outside both organizations’ infrastructure, while still being accessible from within both networks. This often ran contrary to how either organization normally expects software to be deployed.

What I built

flowchart LR
    U["Engineers &amp; client users<br/><i>admin · internal · external</i>"]

    subgraph cf["Cloudflare edge"]
        direction TB
        SPA["React SPA<br/>Pages"]
        API["Pages Functions<br/>/api/*"]
        DB[("D1<br/>SQLite")]
        CRON["Scheduled worker"]
    end

    subgraph ext["Client systems — read-only"]
        direction TB
        SF["Salesforce<br/>Reports API"]
        SP["SharePoint<br/>reference workbooks"]
    end

    GH["GitHub Actions"] -.->|deploy| SPA

    U --> SPA
    SPA --> API
    API <--> DB
    CRON --> DB
    CRON -->|"pull · every 15 min"| SF
    CRON -->|"pull · every few hours"| SP

A React and TypeScript single-page application on Cloudflare Pages, with serverless API functions and a D1 (SQLite) database at the edge, deployed through GitHub Actions. A separate scheduled worker handles synchronization.

Because I couldn’t write to Salesforce or extend it, the system treats the client’s CRM as a read-only upstream and maintains its own state, history, and reconciliation independently. That inversion — mirror the system of record and build on that mirror to it rather than directly integrate with the original system — is what made the project possible at all given the access I had to work with.

Ten primary surfaces: internal dashboard, review queue, case history, round editor, data-pull console, database query and audit log, client dashboard, media share (web browse groundwork), settings, and the team documentation site embedded but also encouraging deep search.

Selected capabilities:

• Round-and-task data model — one application, many rounds, each round carrying independent result / submission / internal-QC / client-QC state with potentially many different engineers involved

• Constraint-based assignment: weighted load metrics per operating company and review level, continuity recommendations that favor the engineer who handled the prior round, and qualification filtering across reviewer roles

• Automatic case flags derived from upstream data, alongside user-authored flags

• Role-gated permissions across admin, internal, and external (client) users

• Saved views, per-user column visibility, customizable navigation, keyboard shortcuts, and concurrent tabs within a user’s single application instance

• Per-case comment threads, notifications, presence api, and a full audit log with change attribution

• GitHub issue text creation from inside the application, so users can report bugs without leaving their work

• ~60 sequential schema migrations, applied through local → test → production promotion, without production data loss.

The hard part

Synchronizing against a report API that gives you no deltas.

Salesforce returns the current state of objects, including via report. The client’s instance does not have any functionality to inform users of all that changes over time. With well over ten thousand unique applications for the team, many carrying more than a dozen review rounds, the sync has to work out on every run which records are new, which fields moved, which records vanished from the upstream query, and what the prior state was — while remaining idempotent, because it is meant to run at least every fifteen minutes and any run may be a retry of the previous run.

The design that worked:

1. Pull the report and deduplicate to the most recent due date per application.
2. Match on the upstream identifier. New records are created; existing records have their current state archived into an append-only history before fields are updated.
3. Track meaningful field transitions specifically — due date, review level, assigned engineer, feeder — rather than diffing entire rows, so that history can remain queryable rather than turning into a pile of snapshots.
4. Reconcile in both directions: flag cases that are outstanding locally but no longer present upstream.
5. Log every run with structured metrics, so a failed sync is at least visible and doesn’t go silent.

A second job ingests versioned reference data from SharePoint on a slower cadence — distribution equipment lists, minimum daytime and peak load data, jurisdiction-specific queues — each versioned with effective dates, because equipment constraints determine which screening path an application takes and any provided data source has to be reconstructible for any historical date.

The data pull itself went from a manual Power Query refresh taking five to twenty-five minutes (~a dozen at most a day) to a scheduled job completing in under three (nearly 100 checks a day).

Results

In production since March 2026. Two minor releases and eleven patches since.

	Before	After
Platform	Two Excel workbooks, ~12 Office Scripts, ~12 Power Automate flows	One application, 3 consolidated/retained flows
Data refresh	Manual, 5–25 min	Scheduled, <3 min, every 15 min
Maintenance burden	~20 engineer-hours/week	Near zero
Waiting on the tool	~10 engineer-hours/week	Near zero
Time to find, mark, and process work	Baseline	~60-80% faster
Practical client throughput	~3 review rounds/day	5–6 sustained, no structural ceiling

Roughly a dozen active engineers including client-side users, tracking >15K applications.

The throughput number is the one that matters most for the solar applicants. The old workbook capped what the team could deliver in a day regardless of how many engineers were available, because the tool itself was a bottleneck. Removing that bottleneck has expanded what the team can commit to.

Stack

React 18 · TypeScript 5.7 · Vite 6 · TailwindCSS · Cloudflare Pages Functions · Cloudflare D1 · Drizzle ORM · Zod · Vitest + React Testing Library · GitHub Actions · jsforce · Microsoft Graph

---

I am a solar interconnection and DER engineer who builds the software the work runs on. If you’re building tools for grid interconnection, DER management, or utility workflow and are looking for someone who both enjoys review work and can ship a platform for it, I’d love to hear from you.