Monthly CharlieHR headcount snapshot in Google Sheets
On the first of every month, log every active employee's team, location, start date, and remaining holiday allowance to a Google Sheets tab.
Integrations
CharlieHR
Google Sheets
Type
Categories
- HR & People
- Operations
On the 1st of every month at 6:00am, take a snapshot of our current team from CharlieHR and append it to a Google Sheets tab so finance, ops, and leadership have a clean monthly history to chart from. This should be a code workflow with discrete deterministic nodes, no agent reasoning in the pipeline.
Trigger: cron, 1st of every month at 6:00am.
Steps:
1. Walk every team member in CharlieHR using List Team Members, paginating through all pages until exhausted.
2. Use List Teams once up front to build a lookup from team id to team name.
3. For each active team member, call Get Team Member Leave Allowance to pull the current remaining holiday allowance and the days used so far. Skip members who are not active so the snapshot reflects the current roster.
4. Build one row per active member with these columns, in this order: snapshot_date, team_member_id, full_name, team, working_location, start_date, employment_status, remaining_holiday_days, used_holiday_days. snapshot_date is the date the workflow runs, formatted YYYY-MM-DD. Resolve team by joining the member's team id against the lookup from step 2.
5. Append every row in a single batch call to Google Sheets using Append Values, targeting a tab called "headcount_snapshots" in a spreadsheet I will configure. Do not clear the sheet, do not write a header (assume it already exists), just append underneath the last row so history accumulates.
Same shape every month, same columns, same fields. Keep the implementation simple and deterministic.
What it does
- On the first of every month, pull the current roster from CharlieHR with team, working location, start date, and employment status.
- Append one row per active employee, including remaining holiday days and days used so far this year.
- Build a clean historical record that finance, ops, and leadership can chart and pivot from.
- Runs hands-off in the background, so nobody on the people team has to remember to export anything.
What you’ll need
- A CharlieHR account with Super Admin access (needed to generate the API credentials).
- A Google account that can edit the destination spreadsheet.
- A spreadsheet with a tab called headcount_snapshots ready to receive new rows.
How to customize it
- Change the day or time, or run it weekly instead of monthly.
- Add or remove columns such as department, manager, or salary band.
- Filter to a specific team, office, or employment status before appending.
Use cases
- Data Sync
- Daily Digests
