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

Weekly Amazon S3 bucket security audit posted to Slack

Every Monday, check every S3 bucket for public exposure, missing encryption and weak backup settings, then get the risks ranked in Slack.

Agentic Task

Turn procurement portal tenders into CRM deals each morning

Every weekday at 7am, sign in to the tender portals you track, filter new notices against your bid criteria, and open a deal for the ones worth chasing.

+1

Agentic Task

Draft polite follow-ups for emails that never got a reply

Every weekday at 4pm, spot the threads that went quiet, stage a ready-to-send nudge in your mailbox, and get a ranked Slack recap.

Agentic Task

Flag VIP client emails in Zoho Mail and alert your team

Keep a spreadsheet of your most important senders, and every email from one gets labeled, posted to your team channel, and logged automatically.

Deterministic Code

Send Yotpo review requests when Shopify orders ship

The moment an order is fulfilled, we register it with Yotpo so the review request is timed off real delivery, and we log every sync so failures never go unnoticed.