Use Google Apps Script to Calculate Distance and Time in Google Sheets | Aryan Irani

Learn how to use Google Apps Script to automate distance and time calculations in Google Sheets. This powerful tool is great for logistics companies who need to plan routes and optimise delivery schedules. Streamline your operations and take your Google Sheets game to the next level with this tutorial.
Check out the blog: / from-point-a-to-point-...
Work with the Google Sheet: docs.google.co...
GitHub code link: github.com/ary...
Follow me on :
Twitter : cutt.ly/Rv4Ydun
LinkedIn : cutt.ly/Fv4YayL
Medium : / aryanirani123
Hi. I am Aryan Irani. I am Google Developer Expert for Google Workspace, Technical Blogger, Content creator.
#googleappsscript #googlemaps #googlesheets

[@AmaniKestrel-zo6ku]

Great tutorial! ❤️
Can you show how to return the result for :
-Distance in Number Values
-Duration in Minutes Values
Since the result shows Text String

[@MISKaligodam]

great sheet...👍🏼👍🏼
Nice Work

[@ZahratAlafrah]

Thank you for this explanation .... We have a problem... Exception: Service invoked too many times for one day: route

[@lucianocamposflores]

THANKS!! This helped me a lot! I made some adjustments to the code with GPT

[@lucianocamposflores]

developers.google.com/maps/documentation/directions/get-directions?hl=es-419#TextValueObject DOCUMENTATION

[@AryanIrani123]

Great to hear!

[@laurenmcintosh343]

I have put my data into a pivot table and need to filter out anything below 400mi or 6 hours in time. The filter does not seem to recognize the distance as a number and does not filter properly. Do you have a recommendation?

[@lucianocamposflores]

CHANGE the values from;
var distance = directions.routes[0].legs[0].distance.text;
var duration = directions.routes[0].legs[0].duration.text;
TO THIS and you will get the value:
var distance = directions.routes[0].legs[0].distance.value;
var duration = directions.routes[0].legs[0].duration.value;
BEWARE, values
distance will be meters
time will be in seconds.
If you want kilometers, minutes and time like a text, you can use this code:
*REMEMBER TO CHANGE THE OUTPUT COLUMN TO FIT YOUR NEEDS*
function calculateDistances() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Comunas valpo y distancias");
var numRows = sheet.getDataRange().getNumRows();
var data = sheet.getRange("D4:E" + numRows).getValues(); // Obtener todos los datos de D4:E
for (var i = 0; i < data.length; i++) {
var start = data[i][0];
var end = data[i][1];
var flag = data[i][4];
if (start && end && flag !== "done") {
try {
var directions = Maps.newDirectionFinder()
.setOrigin(start)
.setDestination(end)
.setMode(Maps.DirectionFinder.Mode.DRIVING)
.getDirections();
if (directions && directions.routes && directions.routes.length > 0 &&
directions.routes[0].legs && directions.routes[0].legs.length > 0) {
var distance = directions.routes[0].legs[0].distance.value;
var duration = directions.routes[0].legs[0].duration.value;
// Convertir metros a kilómetros
var distanceInKm = distance / 1000;
// Convertir segundos a minutos
var durationInMinutes = duration / 60;
// Calcular horas y minutos
var hours = Math.floor(durationInMinutes / 60);
var minutes = Math.round(durationInMinutes % 60);
sheet.getRange(i + 4, 6).setValue(distance); //asdasdsad asdasd asd asd as
sheet.getRange(i + 4, 7).setValue(duration);
sheet.getRange(i + 4, 8).setValue("done");
sheet.getRange(i + 4, 10).setValue(distanceInKm);
sheet.getRange(i + 4, 12).setValue(hours + "h " + minutes + "min");
sheet.getRange(i + 4, 11).setValue(durationInMinutes);
} else {
Logger.log("No se encontraron rutas o 'legs' para la fila " + (i + 4));
}
} catch (error) {
Logger.log("Error al obtener direcciones para la fila " + (i + 4) + ": " + error.message);
}
}
}
}

[@zinminhtetaung6351]

TypeError: Cannot read properties of undefined (reading 'logs'). I can't fixed it. please help.

[@lucianocamposflores]

That happens when google can't find the direction, try this code:
function calculateDistances() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("YOURsheetname");
var numRows = sheet.getDataRange().getNumRows();
var data = sheet.getRange("D4:E" + numRows).getValues(); // YOUR DATA RANGE GOES ON "D4:E" DONT FORGET TO PUT YOURS
for (var i = 0; i < data.length; i++) {
var start = data[i][0];
var end = data[i][1];
var flag = data[i][4];
if (start && end && flag !== "done") {
try {
var directions = Maps.newDirectionFinder()
.setOrigin(start)
.setDestination(end)
.setMode(Maps.DirectionFinder.Mode.DRIVING)
.getDirections();
if (directions && directions.routes && directions.routes.length > 0 &&
directions.routes[0].legs && directions.routes[0].legs.length > 0) {
var distance = directions.routes[0].legs[0].distance.text;
var duration = directions.routes[0].legs[0].duration.text;
sheet.getRange(i + 4, 6).setValue(distance); // COUNT wich column u want the result and change the number 6
sheet.getRange(i + 4, 7).setValue(duration);
sheet.getRange(i + 4, 8).setValue("done");
} else {
Logger.log("No routes could be found for these locations " + (i + 4));
}
} catch (error) {
Logger.log("Error to obtain directions for line " + (i + 4) + ": " + error.message);
}
}
}
}

[@user-qm2re5qy2g]

me too

[@RahulGupta-bn4rj]

Please explain the code you wrote in 'distance' & 'time' variables...

[@user-nj8pu1dr3q]

Can you tell how to multiply the distance with a number

[@caeswarrama]

can i get average distance between the locations? When there is more than one route to destination.

[@israelterorisprikopat]

To compare 2 location. It use google map free. Is it okay? Or there are limitation request?