The context
The client was a Customer Success team of around 50 people, each billing time against client projects. At that size, profitability and utilisation stop being something you can track by memory or by asking around. Managers needed a reliable, current view of both, and they needed it without relying on someone manually pulling numbers together every week.
The problem
The team had no visibility on two numbers that mattered most to the business: profitability per project and utilisation per person. Time was tracked in Harvest, but nothing connected that data back to Smartsheet, where the project portfolio and management dashboards actually lived. Managers were left checking two disconnected systems and doing the maths themselves, usually too late to act on it.
Before
- No visibility on project profitability
- No visibility on consultant utilisation
- Time tracking data (Harvest) sat separately from the project portfolio in Smartsheet
- Any reporting was manual, occasional, and out of date by the time anyone looked at it
What we built
Phase 1: a manual bridge
The first version was built with the tools available at the time, before Bridge, DataMesh, and Pivot App were part of the picture. Each week, data was exported manually from Harvest and cleaned up in Excel using Power Query. That transformed data was then pushed into Smartsheet using Data Shuttle. It was not automated end to end, but it got profitability and utilisation figures in front of managers for the first time.
Phase 2: full automation
Once Bridge, DataMesh, and Pivot App came into scope, we rebuilt the whole flow to remove the manual steps entirely. The profitability report followed the same path: originally built and calculated in Excel, then rebuilt on the same Harvest API and Smartsheet API integration in Bridge.
Profitability was not just surfaced in a standalone dashboard. Once it lived in Smartsheet, it was pushed back into the project portfolio and into each individual project plan, so PMs could see exactly where their own project stood without going to a separate report. We also added a predictive profitability figure, calculated from current profitability against the percentage of the project completed, so PMs could see where a project was on track to land, not just where it stood today.
How the pipeline works
On a schedule, a Bridge workflow calls the Harvest API to pull the latest time entries. A JavaScript module inside Bridge shapes that raw response into the structure Smartsheet needs, matching people and projects against existing records rather than creating duplicates, then calls the Smartsheet API to write the data in. From there, DataMesh syncs the transformed entries into the project portfolio and into each project plan, and Pivot App aggregates the raw entries into the by-project and by-person views managers actually look at. No manual export, no Excel step, and no one having to remember to run it.
Obstacles
Getting people to actually track their time
The pipeline only reflects reality if people log their time regularly. Automating the data flow does not fix that on its own, and a profitability or utilisation number built on a week of missing time entries is worse than no number at all, because it looks reliable when it is not. That meant the build was not finished once the pipeline was working. We also had to build a report that flags exactly where time tracking is falling behind, by person and by project, so managers can chase up missing entries before the profitability and utilisation figures are treated as fact. Of everything in this build, this is the piece that determines whether the rest of it can be trusted.
Row limits
At the time this was built, Smartsheet sheets were capped at 5,000 rows. Harvest time entries add up fast across 50 people, so the Bridge workflow had to limit how much history was pulled in on each run rather than importing everything available. That limit has since been raised, but the underlying lesson still holds: more rows in a sheet means slower performance and more chances for something to go wrong in Smartsheet or in Bridge, so keeping row counts down stayed a design goal even after the cap moved.
Aggregating before it reaches Smartsheet
Harvest's API does not natively return the data in the shape needed for profitability and utilisation reporting. Rather than importing everything raw and aggregating it inside Smartsheet, the JavaScript module inside Bridge does that aggregation before the data is written in. That kept row counts as low as possible on larger datasets, and avoided pushing that processing load onto Smartsheet itself.
Catching Bridge failures
Bridge workflows do sometimes fail mid-run, and a failed run can leave the data out of date without anyone noticing straight away. To catch that, DataMesh was set up to add new rows into the aggregate sheet each day rather than replacing the whole dataset, with a separate automation archiving rows that were no longer present in the import, unless the gap between the two was unusually large, which would suggest a failed run rather than genuinely removed data. On top of that, a daily report on the dashboard compares the row count on the "import" sheet against the "aggregate" sheet, so a Bridge failure shows up as a visible mismatch instead of going unnoticed until someone questions the numbers.
After
- Daily automated profitability tracking per project, visible directly in the project portfolio and in each project plan for PMs
- Predictive profitability per project, projecting where it will land based on current profitability against percentage complete
- Real-time utilisation dashboards for managers, broken down by person
- Centralised data pulled straight from the time tracking tool via API, no manual export step left in the process
- Early warning indicators built into the dashboards, so managers can see when a project is drifting off track before it becomes a problem
- A time tracking compliance report, so gaps in the data are caught and chased up instead of skewing the numbers unnoticed
Managers moved from having no reliable answer to "how is this project doing financially" to checking a dashboard that updates itself. PMs got the same visibility inside their own project plans rather than needing to go looking for it. And because the pipeline flags where time tracking is falling behind, the team could trust the numbers instead of treating them as a rough guess.
Why it matters
The value here was not just automating a report. It was closing the loop between two systems that had never talked to each other, and doing it in a way that scales, since the same Harvest API and Bridge pattern now powers both the profitability and the utilisation reporting, with one integration doing the work that used to take a weekly manual export and an Excel file.
It is also worth being direct about why this needed a custom build rather than an off-the-shelf connector. The native and third-party Harvest to Smartsheet integrations available today mostly move data one way, such as creating a project or syncing a single timesheet row. None of them calculate profitability, roll it up into a project portfolio, or build a utilisation dashboard from it. If you have hit that same wall, that is exactly the gap this kind of Smartsheet Bridge and DataMesh build is designed to close.
This is also where being upfront about cost matters: this stack, Bridge, DataMesh, Data Shuttle, and Pivot App, only makes sense if you already hold those premium apps or are prepared to invest in them. For teams that do, the payoff is a profitability and utilisation dashboard that updates itself daily, instead of a spreadsheet someone has to rebuild every week.
If your team is tracking time in Harvest, or another tool, but still cannot answer "which projects are profitable right now" without pulling a manual report, get in touch to talk through what a Smartsheet profitability or utilisation dashboard could look like for your team.
