How to Add Automatic Timestamps to Cells in Google Sheets
August 12, 2023
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
- 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
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.
Hello! I am Haroon Khan
Google apps script developer. I have 6+ years of Experience with Professional Google Apps Script development.