Home > Software engineering >  Trouble to change the distance to miles and duration in hours
Trouble to change the distance to miles and duration in hours

Time:11-29

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.

  • Related