Find distance between Multiple Location in Google sheets

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

Find distance between Multiple Location in Google sheets

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

  • We can add multiple rows means multiple addresses in google sheet
  • We must have to add the start address (Column B) and end address (Column H)

How Google Maps Looks

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

Find distance between Multiple Location in Google sheets

Apps Script: Find distance between Locations

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('Fetch Distance and Time', 'mainFun')
      .addToUi();
}

function mainFun() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  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++) {
    directions.addWaypoint(wayPoints[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.