Library / Calendar & Scheduling
Calendar & Scheduling
Plan daily technician routes from Google Sheets with OSRM, Gmail and Telegram
Runs every morning, reads today's visits from Google Sheets, orders stops using OSRM drive times with nearest neighbour and 2-opt, then emails the run sheet via Gmail.
What it does
## Plan the shortest technician route for today and email the run sheet ### How it works Every morning this workflow reads today's visits from a Google Sheet and plans the order the technician should drive them in. It asks the OSRM routing service for a matrix of real road times between the depot and every stop, then optimises the order in two stages: nearest neighbour picks a first route by always hopping to the closest unvisited stop, and 2-opt then reverses segments of that route for as long as each reversal shortens the total drive. Visits marked first or last in the sheet stay pinned where they are. Arrival times are chained forward from the start of the day (previous departure plus drive time plus a buffer), so every stop gets a real ETA rather than a guess. Anything that lands after closing time is flagged. The numbered route goes back to the sheet, the technician gets an emailed run sheet, and dispatch is messaged only when the day no longer fits inside working hours. If the routing service cannot be reached the workflow does not fail: it falls back to straight-line distances at an average speed and says so on the run sheet. ### Setup steps - Create a Google Sheet with a Visits tab and the headers visit_id, visit_date, customer, address, phone, job, lat, lng, duration_minutes, pin, status, plus a Routes tab with the headers route_date, visit_id, sequence, customer, address, phone, job, eta, depart, drive_minutes, drive_from, visit_minutes, pin, past_end_of_day, drive_times. - Add Google Sheets credentials and select that document in Get Today's Visits and Save Route To Sheet. Re-pick visit_id under Column to Match On if it resets. - Open Prepare Stops and edit the config block: depot coordinates, timezone, day start and end, default visit length and buffer. - Add Gmail credentials to Email Route Sheet and set the technician's address. - Add Telegram credentials to Alert Dispatcher and set the dispatcher's chat ID. - Fill a few rows with today's date and real coordinates, then execute the workflow once to check the route sheet. ### Customization - Change the trigger time, or swap the schedule trigger for a webhook so dispatch can replan after a new job lands. - Set RETURN_TO_DEPOT to false for technicians who take the van home. - Raise BUFFER_MINUTES for sites with parking or security check-in. - Point the request at your own OSRM instance, or at anot
The steps
- Run Each Morning
- Get Today's Visits
- Prepare Stops
- Request Drive Times
- Build Drive Time Matrix
- Optimize Visit Order
- Add Arrival Times
- Split Out Stops
- Save Route To Sheet
- Email Route Sheet
- If Day Runs Long
- Alert Dispatcher
Similar automations
Data Sync & ETLSync Clockify Time Entries to Syncro Timers13 stepsData Sync & ETLSync Clockify Time Entries to Syncro Timers14 stepsMCP Servers & ToolsCalculate Czech Transit Routes as a Mobility MCP Server3 stepsContent Writing & GenerationTranslate and summarize daily AI news with OpenAI and send to Telegram9 steps