I want to change my distance in Miles and duration in hours format. Now distance is in kilometers and duration is in minutes so I want to change the code to change the format i.e. if duration is more than one 1 hour so it will be like 1 hour 20 minutes and distance in miles i.e. 10 miles but I'm stuck.
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 distance = distance/1000
var timeMin = time/60
return [distance,timeMin];
}
CodePudding user response:
A guess. Instead of this:
var distance = distance/1000
var timeMin = time/60
Try this:
var distance = distance/1000 * .62;
var timeMin = time/60/60;
Or (to get 1.23
instead of 1.23456789...
):
var distance = Math.round(distance/1000 * .62 * 100)/100;
var timeMin = Math.round(time/60/60 * 100)/100;
To get the 'XX h YY m' notation from seconds:
var s = 12345; // time in seconds
var h = Math.floor(s/3600);
var m = ('' Math.round(s/60 % 60)).padStart(2, '0');
var time = (h > 0) ? h ' h ' m ' min' : m ' min';
console.log(time) // output: 3 h 26 min
<iframe name="sif1" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>
Another approach with Date()
object:
var s = 12345;
var date = new Date(s*1000);
var timezone = date.getTimezoneOffset();
var h = date.getHours() timezone/60; // exclude timezone offset
var m = date.getMinutes();
var time = (h>0) ? h ' h ' m ' min' : m ' min'
console.log(time); // output: 3 h 25 min
<iframe name="sif2" sandbox="allow-forms allow-modals allow-scripts" frameborder="0"></iframe>
It gives a little different results because it doesn't round minutes properly: 350 sec -> 5 min 50 sec -> 5 min (not 6 min).
Dates and time are tricky beasts. I'm not even sure that my last example is 100% reliable for all timezones.