Logistics Distance & Time Calculation via Open Route API - n8n Workflow

• Logistics Managers and Analysts needing to optimize delivery routes.


• Businesses that frequently handle bulk route planning based on spreadsheet data.


• n8n users looking for advanced examples of API looping and data manipulation.


• Automation specialists seeking reliable n8n templates for geospatial calculations.

Managing large datasets of shipment origins and destinations requires efficient tools to estimate time and distance accurately, especially for commercial trucks (HGV). This robust n8n workflow solves the problem of manual route calculation by automating the entire process.

Starting with a list of coordinates in a Google Sheet, this n8n template iteratively queries the high-performance Open Route API, optimized for heavy goods vehicles, to retrieve precise travel metrics. The workflow then cleanly extracts distance, duration, and the number of driving steps, writing the results back to the original spreadsheet. This reliable n8n automation ensures you have up-to-date logistics metrics without manual data entry or complex scripting.

This n8n workflow operates on a strict batch processing loop, starting with the execution of the n8n trigger:

1. Trigger and Data Collection: The workflow starts with a Manual Trigger, which is immediately followed by the 'Collect Routes' n8n node. This node connects to Google Sheets, reading the list of routes containing required fields (departure/destination longitude and latitude).


2. Batch Processing: The 'Loop Over Items' n8n node takes the collected spreadsheet data and splits it into individual items, ensuring each route is processed one at a time.


3. API Request: For each item (route), the 'Request Open Route API' n8n node sends an HTTP request to the Open Route Service API, specifically using the driving-hgv profile to get truck-optimized routes.


4. Result Extraction: The 'Extract Results' n8n node cleans and transforms the complex JSON response, pulling out key metrics like distance, duration, and n_steps.


5. Data Persistence & Rate Limiting: The 'Save Results' n8n node updates the corresponding row in the Google Sheet with the new metrics. Crucially, a '5 sec' Wait n8n node is placed after the save operation to implement rate limiting, preventing API overload before the n8n workflow proceeds to the next route item.

To install this specific n8n workflow template, follow these steps:

1. Import: Copy the provided JSON code and paste it into your n8n instance using the 'Import Workflow' function.


2. Google Sheets Setup: You will need a Google Sheets Credential. Configure the two Google Sheets n8n node instances ('Collect Routes' and 'Save Results') by linking them to your credential and specifying the Document ID and Sheet Name. Ensure your sheet columns match the expected fields (especially id, longitudedeparture, latitudedeparture, etc.).


3. Open Route API Setup: Obtain a free API key from Open Route Service.


4. Credential Configuration: Edit the 'Request Open Route API' n8n node and input your API key into the designated query parameter field. This is vital for the n8n node to function correctly.


5. Execution: After configuration, run the n8n workflow by clicking the 'Test workflow' trigger, or activate it for automated execution.

When clicking ‘Test workflow’ (Manual Trigger): This simple n8n trigger starts the process manually, perfect for testing or one-off batch runs.
Collect Routes (Google Sheets n8n node): Reads the initial input data (coordinate pairs) from the specified Google Sheet. This is the source for all route calculations.
Loop Over Items (Split In Batches n8n node): This core n8n node ensures sequential processing of hundreds of items, managing the iteration required for batch processing.
Request Open Route API (HTTP Request n8n node): Sends the critical request to calculate the route. Key configuration uses the driving-hgv profile and dynamically maps departure/destination coordinates using expressions based on the current item in the loop.
Extract Results (Set n8n node): Uses expressions to efficiently parse the resulting JSON object from the API, extracting distance, duration, and n_steps for subsequent use.
Save Results (Google Sheets n8n node): Performs an Update operation, matching the row based on a unique ID and writing back the calculated distance and duration metrics.

• 5 sec (Wait n8n node): Implements a fixed delay after each successful update to mitigate the risk of hitting API rate limits during the execution of this high-volume n8n workflow.