Chase missing contractor timesheets in Slack every Monday

Every Monday at 8am, before the payroll cutoff, chase the contractors who have not submitted last week's timesheet. Timesheet Portal does not send outgoing webhooks and is not a poll provider, so use a cron trigger.

Start by pulling the active placements from Timesheet Portal with List Jobs. Keep the placements that were live at any point during last week, meaning the working week that ended yesterday, and hold on to the client, the placement or job reference, the contractor code, the expected weekly hours and the charge rate for each one.

Then pull the submitted time with Get Time and Cost Report. Fetch one batched report covering the whole two week window that ends last Sunday, and never call this once per contractor. Timesheet Portal report endpoints are limited to 24 requests per hour, so a per contractor loop will exhaust the budget and the run will start failing partway through. The report takes a reportFields array of numeric field ids, so resolve the ids you need with List Report Fields first, and set useIsoDateFormat to true so dates come back as yyyy-MM-dd. Page through the results with pageIndex until a page returns fewer rows than the previous one.

Reconcile the two sets. For each active placement, work out whether any timesheet hours were submitted against it for last week. A placement with no hours at all is a missing timesheet. A placement with hours well below the expected weekly hours is a partial submission, so treat it as outstanding too and calculate the shortfall rather than the full week.

Before chasing anyone, suppress the people whose missing week is explained by booked leave. Call Get Leave Summary and check the missing week against each person's booked leave. If leave covers the entire missing week, drop them from the chase list completely and do not message them. If leave covers only part of the week, reduce the expected hours to the days they were actually meant to be working and only chase the remaining shortfall.

Resolve the remaining placements to real people. Take the contractor codes from the placements and look them up with Search Contractors by Code to get each person's name and email address. If a placement's worker is recorded as a user rather than a contractor, fall back to Search Users by Code. Both endpoints accept a batch of codes, so resolve them in as few calls as possible.

Now match each person to their Slack account. Use the Slack List Users operation to list the workspace and pair people up on email address, paging through with the cursor until the list is exhausted. For each match, use Open a Conversation to get the direct message channel id for that user.

Send each contractor a personalised direct message with Send a Message. Write it as a short, friendly, human nudge rather than a form letter. Name the client, name the placement, state the hours still outstanding and the week they relate to, and state the payroll cutoff so the urgency is obvious. Someone missing eleven hours on one placement should get a different message from someone missing a full week across three placements, and if a person has more than one placement outstanding, send them a single message covering all of it rather than one message per placement.

Use the two week window to identify repeat offenders. If a placement has no hours submitted in both of the last two weeks, that contractor has now been chased two weeks running. Say so plainly in their direct message and let them know the account manager is being notified, and mark them clearly in the summary.

Finally, post one consolidated summary to the operations channel with Send a Message. Rank the chase list by billable value at risk, which is the outstanding hours multiplied by the charge rate on the placement, highest first. Include the contractor name, the client, the placement, the outstanding hours and the value at risk for each row, plus the total value at risk across everyone. Add a separate section calling out the repeat offenders so ops can escalate them to the account manager, and another short section listing anyone who was chased but could not be matched to a Slack account, so they can be followed up by phone or email. Mention how many people were suppressed for booked leave so the numbers reconcile. Format it with Slack mrkdwn, using single asterisks for bold, and keep it scannable.

Be careful not to nag people wrongly. If a contractor cannot be resolved, or has no email, or has no Slack match, do not fail the run: leave them out of the direct messages and surface them in the summary instead. If no timesheets are missing at all, post a short all clear to the operations channel and send no direct messages.