Headcount and compensation planning built on your Gusto data

Build me an internal app that my finance and HR leads open every quarter instead of rebuilding a headcount and compensation spreadsheet from Gusto exports. It is a planning workbench: it reads the live roster out of Gusto, lets planners model raises on top of it, and applies only the rows that get approved.

The main view is a table of every employee grouped by department. Each row shows name, job title, hire date, tenure in years and months, current pay rate with its payment unit, annualized cost, the effective date of their last pay change, and months since that change. Handlers build this from Gusto: List Departments for the departments and their assigned members, List Employees for the roster and employment status, Get Employee Jobs for each person's job title, hire date and job history, and List Job Compensations for the pay records on their primary job. Treat the compensation with the most recent effective date on or before today as current, and the one before it as the previous rate, so the app can also show how big their last increase was.

Under each department header show a rollup: headcount, total annualized compensation, average tenure, and how many people are past the raise threshold. Show the same figures for the whole company across the top. Filters for department, employment status (default to active only, with options for onboarding and terminated) and months since last raise, including a one click preset for more than 18 months. Every numeric column sorts.

On top of that sits a planning layer the app owns. A planner types either a proposed rate or a percent bump into any row, and the app computes the new annualized cost, the dollar and percent delta, and immediately reflows the department rollup and the company total so they see the budget impact before anything is real. Planned changes are saved in the app and scoped to a named planning round with a default effective date. Each row carries a status of draft, approved or applied, plus a note explaining the increase. Show current, planned and delta side by side at row, department and company level. Nothing in this layer touches Gusto.

Approving a row still does not write anything. A separate apply action does that. When a planner applies approved rows, the handler must re-read the employee's job and its compensation records from Gusto immediately before writing, because Gusto rejects any write carrying a stale record version and returns a 422 Invalid Version. Use the freshly read version, then call Create Job Compensation with the row's effective date for a genuine pay change so the history stays intact, or Update Compensation when the planner is correcting an existing record rather than raising someone. If Gusto still returns Invalid Version, re-read once and retry, and if it fails again surface Gusto's own error message on that row and leave it approved rather than applied. Store the resulting compensation id and the applied timestamp on the row so it flips to applied and can never be applied twice.

An export action sends the whole planned versus current table to Google Sheets with Append Values: one row per employee with department, title, current rate, current annualized cost, proposed rate, proposed annualized cost, dollar and percent delta, effective date and status, plus the planning round name and a timestamp, so finance can pull it straight into the model. Let the user pick the spreadsheet and the tab.

Add a Draft the comp review memo button that starts a background agent inside the app. The agent takes every approved and applied row in the current planning round, re-reads those people's job and pay history from Gusto with Get Employee Jobs and List Job Compensations, and writes the memo for the approval meeting: total spend added and the new company run rate, a section per department, and a paragraph per person covering title, tenure, time since their last increase, the size of that previous increase and the case for this one drawn from the planner's note. It creates the memo with Google Docs Create Document and fills it in with Batch Update Document, then writes the document title, link and generated timestamp back into the app so a Memos panel lists every memo the team has produced with a link to open it. Show the agent's run status in the app while it works.

Two Gusto behaviors to bake in. First, pay rate fields are only populated when the connected Gusto login has compensation access alongside employee and job access. If rates come back blank, do not silently render zeros or empty cells: show a banner explaining that this connection cannot see compensation, and keep the headcount, title and tenure columns working. Second, Gusto returns money as decimal strings, so parse and total with decimal math rather than floats, and annualize from the payment unit (hourly rate times standard weekly hours times 52, weekly times 52, monthly times 12, yearly as is). Paginate the employee list rather than assuming a single page, since rosters run into the hundreds.