The Scenario
You're a transport data analyst and your team runs a congestion dashboard for a city arterial network. Every morning, someone pulls live traffic flow data for 50 key road segment coordinates and pastes it into the sheet manually. That someone is you, and it takes 45 minutes every single morning because there's no automated way to do it. Your manager has been asking why the dashboard isn't real-time. You've been saying you're working on it.
The bad version:
- Open TomTom's traffic flow API documentation, find the flow segment endpoint, construct an API call for the first coordinate pair, parse the response, read the current speed and free-flow speed, type them into the sheet
- Do that 50 times
- Spend the last 10 minutes calculating the congestion ratio yourself because the API doesn't return it directly
You're doing this every morning. It's 45 minutes of manual data entry before you can even start analyzing what you've collected.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the road segment coordinates, calls TomTom traffic flow data for each one, and writes the results back into the columns you specify.
For each road coordinate pair in columns A and B of this sheet, fetch the TomTom traffic flow data and write the current speed, free-flow speed, and congestion ratio into columns C, D, and E
What You Get
- Column C: current speed in km/h for each road segment
- Column D: free-flow speed (typical uncongested speed) for that segment
- Column E: congestion ratio (current speed divided by free-flow speed — lower means more congested)
- Null values where TomTom returned no data for a coordinate, so you can spot bad entries in your coordinate list
What If the Data Is Not Quite Ready
Road segments are identified by name, not coordinates
For each road name in column A, geocode the midpoint of that road using TomTom, write the lat/lng into columns B and C, then fetch the TomTom traffic flow data and write current speed, free-flow speed, and congestion ratio into columns D, E, and F
You need travel time data instead of speed
For each road segment lat/lng in column A of my Excel sheet, get the TomTom flow segment data and write the current travel time, free-flow travel time, and road name into columns B, C, and D
Only flag segments above a congestion threshold
For each coordinate pair in columns A and B, fetch TomTom traffic flow data and write current speed and free-flow speed into columns C and D. Calculate the congestion ratio and write it into column E. Flag any row where the ratio is below 0.6 in column F with "congested".
Full congestion snapshot with summary statistics
For each road coordinate pair in columns A and B, fetch TomTom traffic flow data and write current speed, free-flow speed, and congestion ratio into columns C, D, and E. Then write the average congestion ratio across all segments in cell G1, and list the 5 most congested segment coordinates in a summary block starting at cell G3.
One prompt fetches all 50 segments, calculates the ratios, and builds the summary block.
Try It
Get the 7-day free trial of SheetXAI and open the Google Sheet with your road segment coordinates, then ask it to pull live TomTom traffic flow data. If you're also working with incident-level data rather than flow speeds, see the traffic incidents export spoke.