Weekly Recruitee hiring pipeline snapshot in Google Sheets

Every Monday at 7am, build a hiring pipeline snapshot from Recruitee and append it to a Google Sheet so we accumulate a week-over-week trend instead of only ever seeing today's numbers. Use a cron trigger set to every Monday at 7am.

Start with Recruitee List Offers to get the roles that are currently published. Treat those published roles as the set of open roles for the week, and ignore closed, filled, archived, and talent pool entries.

For each open role, call Get Pipeline Stages for Offer to resolve that role's pipeline stage names, then call List Candidates filtered by that offer to count candidates. From those candidates compute: the number of new applicants created in the last seven days, the total number of active candidates, the count of candidates sitting in each pipeline stage, and the count of disqualified candidates. List Candidates supports filters for offer, qualification state, and date range, so use those filters for the seven day new applicant window and for the disqualified count rather than pulling everything and filtering by hand where it can be avoided.

Recruitee list endpoints page with offset and limit and return a total in the response, so page through by advancing offset by limit until you have collected total items or the returned array comes back empty. Counting off a single unpaged first page would undercount any role with a busy pipeline, so make sure every count is taken over the full set.

Then append one row per open role to Google Sheets with Append Values. Each row should carry the week's date, the role title, new applicants in the last seven days, total active candidates, the count sitting in each pipeline stage, and the disqualified count. Append Values writes after the last row of the detected table, which is exactly the behaviour we want: it must never overwrite prior weeks. One row per role per week, so the sheet accumulates into a trend history that can be charted or pivoted. Keep the stage counts in a consistent column order across runs, taking the order from the sheet's existing header row, and write a zero for any stage a particular role's pipeline does not include, so the columns stay aligned even when roles use different pipeline templates.

After the rows are written, post a short message to the recruiting channel using the Slack Bot Send a Message operation. The message should confirm the week's snapshot was logged, link to the sheet, and explicitly name any open role that received zero new applicants in the last seven days, since those are the roles most likely to be quietly stalling. Keep it to a few lines rather than restating every number that is already in the sheet.

Every step here is deterministic, so build this as a code workflow: list, count, append fixed columns, post. If a single role's stage or candidate lookup fails, still write the rows for the roles that succeeded and note the skipped role in the Slack message rather than failing the whole run.