Automated Geocoding for Logistics Data using Open Route Service - n8n Workflow

Logistics and Supply Chain Managers dealing with large address databases.
Data Analysts who need reliable geographic coordinates for mapping or reporting.
Businesses using Google Sheets as a primary database for location data.
Users looking for advanced n8n templates focusing on external API integration and looping logic.

This n8n workflow provides a powerful solution for organizations that need to quickly and accurately convert addresses stored in Google Sheets into precise GPS coordinates and supplementary geographic details. Manually processing hundreds or thousands of addresses is inefficient; this n8n automation template solves that by integrating Google Sheets directly with the Open Route Service API.

The workflow reads required data (address and country code), queries the geocoding service in batches, and meticulously writes back detailed longitude, latitude, borough, and neighborhood information. Furthermore, this specific n8n workflow design incorporates crucial rate-limiting logic (using a Wait n8n node) to ensure high-volume processing is stable and complies with API usage policies, making it a reliable n8n solution for ongoing data enrichment.

The process begins with the Manual n8n Trigger, designed to run the workflow on demand, typically for large batch operations. First, the Google Sheets n8n node, labeled 'Collect Addresses', reads all necessary input fields (address and country) from your specified spreadsheet.

These records are passed to the 'Loop Over Addresses' Split In Batches n8n node, which ensures that each address is processed sequentially. For every item in the batch, the 'Query Open Route API' HTTP Request n8n node executes a geocoding lookup, dynamically injecting the address and country code into the API call parameters.

Upon receiving the API response, the 'Extract Results' Set n8n node parses the JSON output, extracting key information such as GPS coordinates and descriptive location tags (like borough and localadmin). The next Google Sheets n8n node, 'Save Results', updates the corresponding row in the spreadsheet with the newly acquired geocoded data.

Finally, a '5 sec' Wait n8n node introduces a crucial 5-second delay before the loop continues to the next address. This delay mechanism is essential for respecting API rate limits, ensuring the stability and successful completion of the entire n8n workflow, especially when dealing with high volumes of data.

1. Import the n8n template: Copy the provided JSON code and paste it into your n8n instance using the 'New' -> 'Import from JSON' function.


2. Setup Google Sheets Credentials: Configure the Google Sheets n8n node ('Collect Addresses' and 'Save Results') by adding your Google Sheet API credentials. Select your target document ID and sheet name. Ensure your sheet has columns mapped for address, country, longitude, latitude, borough, neighbourhood, and localadmin.


3. Setup API Key: In the 'Query Open Route API' HTTP Request n8n node, you must obtain a free API key from the Open Route Service documentation and insert it into the api_key query parameter.


4. Testing the n8n workflow: Click the 'When clicking ‘Test workflow’ n8n trigger' node to run a test and verify data flow and successful writing back to your spreadsheet.

When clicking ‘Test workflow’ (Manual n8n trigger): The starting point for the batch processing n8n workflow.
Collect Addresses (Google Sheets n8n node): Reads raw address data from the sheet. Configured for a Read operation.
Loop Over Addresses (Split In Batches n8n node): Splits the spreadsheet data into individual items to enable sequential API calls, vital for efficient API usage within the n8n template.
Query Open Route API (HTTP Request n8n node): Core geocoding action. Key Configuration: Uses https://api.openrouteservice.org/geocode/search and dynamically pulls {{ $json.address }} and {{ $json.country }} for the lookup.
Extract Results (Set n8n node): Data transformation node used to map API results (coordinates, borough, etc.) into clean variables suitable for storage.
Save Results (Google Sheets n8n node): Updates the Google Sheet. Key Configuration: Operation is set to update, matching rows based on the item ID or row number to ensure accurate data enrichment.

• 5 sec (Wait n8n node): Implements a crucial 5-second delay between iterations of the loop to manage the Open Route Service API rate limit, ensuring this n8n workflow runs reliably at scale.