Use Google Apps Script to Calculate Distance and Time in Google Sheets | Aryan Irani
HTML-код
- Опубликовано: 29 авг 2024
- 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
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
great sheet...👍🏼👍🏼
Nice Work
Thank you for this explanation .... We have a problem... Exception: Service invoked too many times for one day: route
THANKS!! This helped me a lot! I made some adjustments to the code with GPT
developers.google.com/maps/documentation/directions/get-directions?hl=es-419#TextValueObject DOCUMENTATION
Great to hear!
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?
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);
}
}
}
}
TypeError: Cannot read properties of undefined (reading 'logs'). I can't fixed it. please help.
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);
}
}
}
}
me too
Please explain the code you wrote in 'distance' & 'time' variables...
Can you tell how to multiply the distance with a number
can i get average distance between the locations? When there is more than one route to destination.
To compare 2 location. It use google map free. Is it okay? Or there are limitation request?