Build weekly mileage claims from your team's calendars

Every Friday at 4pm, build each field employee's weekly mileage claim from their calendar so nobody has to reconstruct their driving from memory on a Sunday night. Run this on a cron schedule.

Keep all of the settings together at the top of the workflow so they are easy to change without touching the logic. I need: the mileage reimbursement rate and its currency and unit (for example 0.67 per mile); the distance unit to report in (miles or kilometres); the roster of field employees, where each entry holds the person's name, their calendar id or email, their base address (home or office, whichever they actually drive from), and their Slack user id or handle for the direct message; a list of our own office and depot addresses that must never be claimed as a destination; and the list of keywords that mark an event as non-claimable (for example lunch, personal, dentist, internal, all hands, 1:1, interview). Also keep the target Google Sheets spreadsheet id and sheet name up there.

For each person on the roster, use Google Calendar List Events to pull their events for the working week that is ending, from Monday 00:00 to Friday 23:59 in their local time zone. Expand recurring events into their individual instances so each occurrence is treated as its own trip, and skip any event the person declined.

Then decide which events are genuine site visits. Keep only events whose location field contains a real street address, meaning something with a street number or street name and ideally a town, postcode or state. Drop anything that is a video call (a Zoom, Google Meet, Microsoft Teams or Webex link, a dial-in number, or a location that is just the word remote or online), drop all-day blocks and multi-day events, drop events located at any of our own office or depot addresses, and drop anything whose title or location matches the non-claimable keywords. Also drop events with an empty location, and events where the location is only an internal room or floor name such as Boardroom or Level 3. Use judgement here rather than a rigid pattern match, because location fields are written by hand and are often half finished.

Use Geolocation Forward Geocode to resolve each remaining address into coordinates and a clean formatted address, and geocode each person's base address too. Geocode each distinct address only once per run and reuse the result, rather than geocoding the same customer site repeatedly. If a geocode comes back with no result, a low confidence or partial match, or a result in an obviously wrong region, treat that stop as ambiguous: leave it out of the claim and record it for the person's summary rather than guessing at a distance.

Now build the legs. For each person and each day, sort that day's surviving stops into chronological order by start time, then chain them: base address to the first stop, each stop to the next stop, and the last stop back to the base address. A day with three visits therefore produces four legs. Use Geolocation Calculate Distance Matrix to get the driving distance and duration for these legs. Important: pass the legs as paired origins and destinations so you only ever request the specific pairs you need. Do not build a full matrix of every stop against every other stop, because this operation is billed per element (origins multiplied by destinations) and a full matrix would cost many times more than the handful of legs actually driven.

Convert each leg's distance into the configured unit and round to one decimal place, and express drive time in whole minutes. Then use Google Sheets Append Values to add one row per leg to the mileage log, with these columns: date, employee name, from address, to address, distance, drive time, and the meeting title as the business purpose. For the leg that returns to base, use the last meeting's title with a note that it is the return journey. After a person's legs are written, append a weekly total row for that person showing their total distance and total reimbursable amount, so the sheet reads as a per person claim rather than a loose pile of trips.

Finally, message each person privately. Use Slack Open a Conversation to get the direct message channel for their user id, then use Slack Send a Message to send them their weekly summary. The message should give their total distance for the week, the reimbursable amount calculated at the configured rate, and a short day by day breakdown of the legs so they can sanity check it. Then list every trip that was skipped or looked ambiguous, with the date, the meeting title, and the specific reason (no address in the location field, only a venue name with no street, address could not be found, treated as an office visit, matched a non-claimable keyword). Ask them to correct those calendar entries or reply with the details before payroll closes, and make it clear which items are genuinely excluded versus which just need an address filled in.

If a person has no claimable trips at all in the week, still send them a short message saying nothing was found and listing anything that was skipped, so silence is never mistaken for a missing run. If a calendar cannot be read or a person's base address is missing from the configuration, note that clearly in the run summary instead of writing partial or misleading rows into the mileage log.