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

[@ZahratAlafrah]

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

[@MISKaligodam]

great sheet...👍🏼👍🏼 Nice Work

[@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

[@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); } } } }

[@RahulGupta-bn4rj]

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

[@israelterorisprikopat]

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

[@LimkarGajanan]

How to use same in matrix form

[@caeswarrama]

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

[@UnknownGod-m4g]

Can you tell how to multiply the distance with a number

[@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); } } } }

[@BenjaminUrrea-j2v]

me too

[@OSAMAMALIK-k8o]

thanks alot