In this post, I will show you how to Find distance between multiple location in google sheets using Google Apps Script and how to calculate the duration.
Internally, it uses the Google Maps service of Google Apps Script to find the distance and directions between the Multiple Location

## Google Sheets Find distance between Multiple Location

In sheet where we add manually starting address, waypoints 1-5 and end address (Column A to H). distance and time is calculated using apps script and comes in column I and J

If we add the address in google maps It will look like this. You don’t need to add on the map, I just only show the demo. How it will look. I do all the things to understand them easily distance between multiple location in google sheets

## Apps Script: Find distance between Locations

```				```
function onOpen() {
// Or DocumentApp or FormApp.
}
function mainFun() {
var sheet = ss.getActiveSheet();
var data = sheet.getRange(2, 1, sheet.getLastRow()-1, 8).getValues();
Logger.log(data.length);
for(var i=0; i<data.length;i++){
var startPoint = data[i][1];
var endPoint = data[i][7];
if(startPoint && endPoint){
var wayPoints = [];
for(var j=2;j<=6;j++){
if(data[i][j]){
wayPoints.push(data[i][j])
}
}
var disMin = mapApiEndPoints(startPoint,endPoint,wayPoints)
Logger.log(disMin)
sheet.getRange(i+2, 9, 1, 2).setValues([disMin])
}
}
}
function mapApiEndPoints(startPoint,endPoint,wayPoints){
var directions  = Maps.newDirectionFinder().setOrigin(startPoint).setDestination(endPoint);
for (var i=0; i<wayPoints.length; i++) {
}
var res = directions.getDirections();
var time = 0; var distance = 0;
for(var j=0; j<res.routes[0].legs.length; j++ ){
var route = res.routes[0].legs[j];
//Logger.log(route)
time = time + route.duration.value;
distance = distance + route.distance.value;
}
var distanceKm = distance/1000
var timeMin = time/60
return [distanceKm,timeMin];
}
```
```

In sheet menu, tools > script editor. The script editor will open Paste the code below in script editor. After pasting the code please refresh the sheet.
You will see the custom menu, run that menu than authorize the code and after authorization distance and time are calculated.

### Hello! I am Haroon Khan

Google apps script developer. I have 6+ years of Experience with Professional Google Apps Script development.