For privacy reasons YouTube needs your permission to be loaded. For more details, please see our Privacy Policy.
Have you ever needed to calculate the distance between two addresses in your Google Sheets? Whether you’re managing logistics, planning a trip, or handling deliveries, knowing the distance between locations can be crucial. In this tutorial, we’ll show you how to use Google Apps Script and the OpenRouteService API to automate this process.
function updateDistance() {
const spreadsheetId = ‘SPREADSHEET ID HERE’; // Replace with your spreadsheet ID
const sheetName = ‘Distance’; // Replace with your sheet name
const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
const range = sheet.getDataRange();
const values = range.getValues();
const apiKey = ‘API KEY HERE’;
for (let i = 1; i < values.length; i++) {
const startAddress = values[i][1]; // Column 2 for ‘Start’
const destinationAddress = values[i][2]; // Column 3 for ‘Destination’
const distanceValue = values[i][3]; // Column 4 for ‘Distance’
// Skip rows where Distance already has a value
if (distanceValue) {
Logger.log(`Row ${i + 1}: Distance already calculated, skipping.`);
continue;
}
Logger.log(`Row ${i + 1}: Start Address = ${startAddress}`);
Logger.log(`Row ${i + 1}: Destination Address = ${destinationAddress}`);
if (startAddress && destinationAddress) {
try {
// Get coordinates for Start address
const urlStart = `https://api.openrouteservice.org/geocode/search?api_key=${apiKey}&text=${startAddress}`;
Logger.log(`Fetching coordinates for Start address from: ${urlStart}`);
const responseStart = UrlFetchApp.fetch(urlStart);
const locationStart = JSON.parse(responseStart.getContentText());
const coordsStart = locationStart.features[0].geometry.coordinates;
Logger.log(`Start Coordinates: ${coordsStart}`);
// Get coordinates for Destination address
const urlDestination = `https://api.openrouteservice.org/geocode/search?api_key=${apiKey}&text=${destinationAddress}`;
Logger.log(`Fetching coordinates for Destination address from: ${urlDestination}`);
const responseDestination = UrlFetchApp.fetch(urlDestination);
const locationDestination = JSON.parse(responseDestination.getContentText());
const coordsDestination = locationDestination.features[0].geometry.coordinates;
Logger.log(`Destination Coordinates: ${coordsDestination}`);
// Get the distance between Start and Destination
const directionsUrl = `https://api.openrouteservice.org/v2/directions/driving-car`;
const payload = {
coordinates: [coordsStart, coordsDestination],
};
Logger.log(`Fetching distance from: ${directionsUrl} with payload: ${JSON.stringify(payload)}`);
const options = {
method: “post”,
contentType: “application/json”,
headers: {
“Authorization”: apiKey,
},
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
const directionsResponse = UrlFetchApp.fetch(directionsUrl, options);
const responseCode = directionsResponse.getResponseCode();
if (responseCode !== 200) {
Logger.log(`Row ${i + 1}: Error = Request failed with response code ${responseCode}. Full response: ${directionsResponse.getContentText()}`);
continue;
}
const directionsData = JSON.parse(directionsResponse.getContentText());
const distance = directionsData.routes[0].summary.distance / 1000; // Convert meters to kilometers
// Log the calculated distance
Logger.log(`Row ${i + 1}: Distance = ${distance.toFixed(2)} km`);
// Update the Distance column
sheet.getRange(i + 1, 4).setValue(distance.toFixed(2)); // Column 4 for ‘Distance’
} catch (error) {
Logger.log(`Row ${i + 1}: Error = ${error.message}`);
}
} else {
Logger.log(`Row ${i + 1}: Missing address`);
}
}
}
By following these steps, you can automate the calculation of distances between two addresses in Google Sheets using the OpenRouteService API. This automation can save you time and effort, especially if you frequently need to calculate distances for logistics, planning, or delivery purposes.
