Monthly CharlieHR headcount and leave snapshot to Google Sheets

PromptCreate

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 does this prompt do?

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 do I need to use this?

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 can I 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.

FAQs

Do I need to clear the sheet every month?

No. Each run appends new rows underneath the existing ones, so your full month-by-month history builds up over time.

What happens to people who leave the company?

Each snapshot only includes team members who were active at the moment it ran, so historical rows still reflect who was on the team that month.

Can I point this at a different spreadsheet or tab?

Yes. You pick the spreadsheet and the tab when you set it up, and you can swap either of them later without rebuilding the workflow.

Do I need to be a CharlieHR admin?

Yes. Generating the API credentials this workflow uses requires Super Admin access in CharlieHR.

Can finance just chart straight from the sheet?

Yes. The rows land as plain columns, so any pivot table, chart, or BI tool that reads Google Sheets can use them directly.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.