Automated Occupancy Reporting for a Software Studio

Context

About this project

Every week, someone had to manually check the company's ERP system for employees whose estimated occupancy for the coming month fell below a threshold, then copy that list into a shared spreadsheet the project and resource managers used for planning. It took 25-35 minutes, depended on one person doing it correctly every time, and the metric itself was easy to get wrong: total estimated occupancy, not billable occupancy, a distinction that tripped up more than one attempt at automating it.

Challenge

The challenge

• The manual audit cost 25-35 minutes a week and was a single point of failure, one missed week or one copy-paste mistake and the planning list was wrong
• Three independent automation attempts, a scheduled agent routine, a script, and a browser extension, each got the ERP read-and-filter side working cleanly, then hit the exact same wall: writing the result back into the shared spreadsheet
• The spreadsheet's write API didn't support the formatted person-references the team expected, and every browser-side workaround was blocked by the platform's own security model, one after another
• Early attempts used the wrong occupancy metric and a date field that didn't exist, mistakes that only surfaced by cross-checking output against the ERP's own view
• There was no confirmed timeline for a native fix from the platform side, so waiting for one wasn't a real plan

Approach

What I built

1. 1

   Three attempts to find the real wall

   Ran a scheduled agent routine, a standalone script, and a browser extension against the same problem, not to guess which would work, but to find out precisely where automation broke. All three fully automated reading and filtering the ERP data. All three failed at the same single step.

2. 2

   Root cause and handoff, not another attempt

   Documented the exact technical blockers, the correct field names and calculation, and a written feature request for the platform team, rather than spending a fourth attempt forcing the same wall. Included a manual workaround to use in the meantime.

3. 3

   A reusable, on-demand analysis tool

   Built a standing tool that queries the ERP system directly and applies the correct occupancy calculation every time, replacing the need to re-derive the logic, and the mistakes that came with it, each time someone needed the numbers.

4. 4

   Redesigning around the blocker

   Instead of a fourth attempt at writing into the shared spreadsheet, rebuilt the deliverable itself: a scheduled workflow that generates a self-contained report and delivers it directly, removing the spreadsheet from the loop entirely.

5. 5

   Defensive design from the failures

   The final workflow resolves manager and lead relationships live from the organization's own reporting-line data instead of a manually maintained mapping, and probes the ERP's field names at runtime instead of hardcoding them, direct answers to the two mistakes that caused earlier failures.

Results

Outcome

• A standing on-demand tool now runs the same occupancy analysis reliably every time, instead of every week's numbers depending on someone remembering the right calculation
• A scheduled workflow replaces the spreadsheet-and-manual-check loop with a self-contained report generated and delivered automatically
• Manager and lead assignment resolves from the organization's own reporting-line data, removing a manually maintained mapping that could quietly drift out of date
• The original platform feature request became unnecessary once the workflow stopped depending on the capability it was asking for
• Field names are probed at runtime rather than hardcoded, so a future schema change surfaces as a clear failure instead of a silent wrong number