I have two implementations where I try to get the duration of an arbitrary driving route and set either an arrival or departure time using Apps Script in Google Sheets. I've tested them with multiple origins, destinations, and time combinations, but I'm unable to return a duration that differs by the arrival or departure time. I've validated that the route times do vary when directly accessing Google Maps.

Here's a Google spreadsheet demonstrating and tracking all of this.

Implementation 1 (time is hardcoded in the script, but I've varied it for testing):

function GetDuration(location1, location2, mode) {
   //var arrive= new Date(2022, 07, 04, 18);// 7th of July 06:00 am
   var arrive= new Date(2022, 07, 04, 17);
   //var arrive = new Date(new Date().getTime()   (10 * 60 * 60 * 1000));//arrive in ten hours from now
   //var directions  = Maps.newDirectionFinder().setDepart(arrive)
   var directions  = Maps.newDirectionFinder().setArrive(arrive)
  .setOrigin(location1)
  .setDestination(location2)
  .setMode(Maps.DirectionFinder.Mode[mode])
  .getDirections();
 return directions.routes[0].legs[0].duration.text;
}

And Implementation 2 (time is a variable adrive read in from GSheet):

const GOOGLEMAPS_DURATION = (origin, destination, adrive, mode = "driving") => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }
  if (origin.map) {
    return origin.map(DISTANCE);
  }
  const key = ["duration", origin, destination, adrive, mode].join(",");
  const value = getCache(key);
  if (value !== null) return value;
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
//    .setDepart(adrive)
    .setArrive(adrive)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("No route found!");
  }
  const { legs: [{ duration: { text: time } } = {}] = [] } = data;
  setCache(key, time);
  return time;
};

How can I get one of these implementations to work with either a departure or arrival time?

CodePudding user response：

Use .setDepart() with a datetime value that is in the future, and get the duration_in_traffic field in the .getDirections() response. Note that the field is only available when the departure time is not in the past but in the future.

Please find below a custom function to get driving durations and other such data. It checks arguments, iterates over ranges of values in one go, and uses CacheService to cache results for up to six hours to help avoid exceeding rate limits.

To test the function, put datetime values that are in the future in cells D2:D, then insert this formula in cell J2:

=GoogleMapsDistance(A2:A13, B2:B13, "minutes", "driving", D2:D13)

'use strict';


/**
* Gets the distance or duration between two addresses.
*
* Accepts ranges such as S2:S100 for the start and end addresses.
*
* @param {"Hyde Park, London"} start_address The origin address.
* @param {"Trafalgar Sq, London"} end_address The destination address.
* @param {"miles"} units Optional. One of "kilometers", "miles", "minutes" or "hours". Defaults to "kilometers".
* @param {"walking"} travel_mode Optional. One of "bicycling", "driving", "transit", "walking". Defaults to "driving".
* @param {to_date(value("2029-07-19 14:15:00"))} depart_time Optional. A reference to a datetime cell. The datetime cannot be in the past. Use "now" to refer to the current date and time.
* @return {Number} The distance or duration between start_address and end_address at the moment of depart.
* @license https://www.gnu.org/licenses/gpl-3.0.html
* @customfunction
*/
function GoogleMapsDistance(start_address, end_address, units = 'kilometers', travel_mode = 'driving', depart_time = new Date()) {
  // version 1.2, written by --Hyde, 19 July 2022
  //  - see https://stackoverflow.com/a/73015812/13045193
  if (arguments.length < 2 || arguments.length > 5) {
    throw new Error(`Wrong number of arguments to GoogleMapsDistance. Expected 2 to 5 arguments, but got ${arguments.length} arguments.`);
  }
  const _get2dArray = (value) => Array.isArray(value) ? value : [[value]];
  const now = new Date();
  const endAddress = _get2dArray(end_address);
  const startAddress = Array.isArray(start_address) || !Array.isArray(end_address)
    ? _get2dArray(start_address)
    : endAddress.map(row => row.map(_ => start_address));
  return startAddress.map((row, rowIndex) => row.map((start, columnIndex) => {
    let [end, unit, mode, depart] = [end_address, units, travel_mode, depart_time]
      .map(value => Array.isArray(value) ? value[rowIndex][columnIndex] : value);
    if (!depart || depart === 'now') {
      depart = now;
    }
    try {
      return start && end ? googleMapsDistance_(start, end, unit, mode, depart) : null;
    } catch (error) {
      if (startAddress.length > 1 || startAddress[0].length > 1) {
        return NaN;
      }
      throw error;
    }
  }));
}


/**
* Gets the distance or duration between two addresses as acquired from the Maps service.
* Caches results for up to six hours to help avoid exceeding rate limits.
* The departure date must be in the future. Returns distance and duration for expired
* departures only when the result is already in the cache.
*
* @param {String} startAddress The origin address.
* @param {String} endAddress The destination address.
* @param {String} units One of "kilometers", "miles", "minutes" or "hours".
* @param {String} mode One of "bicycling", "driving", "transit" or "walking".
* @param {Date} depart The future moment of departure.
* @return {Number} The distance or duration between startAddress and endAddress.
* @license https://www.gnu.org/licenses/gpl-3.0.html
*/
function googleMapsDistance_(startAddress, endAddress, units, mode, depart) {
  // version 1.1, written by --Hyde, 19 July 2022
  const functionName = 'GoogleMapsDistance';
  units = String(units).trim().toLowerCase().replace(/^(kms?|kilomet.*)$/i, 'kilometers');
  if (!['kilometers', 'miles', 'minutes', 'hours'].includes(units)) {
    throw new Error(`${functionName} expected units of "kilometers", "miles", "minutes" or "hours" but got "${units}" instead.`);
  }
  mode = String(mode).toLowerCase();
  if (!['bicycling', 'driving', 'transit', 'walking'].includes(mode)) {
    throw new Error(`${functionName} expected a mode of "bicycling", "driving", "transit" or "walking" but got "${mode}" instead.`);
  }
  if (!depart || !depart.toISOString) {
    throw new Error(`${functionName} expected a depart time that is a valid datetime value, but got the ${typeof depart} "${depart}" instead.`);
  }
  const _isMoreThan10SecsInThePast = (date) => Math.trunc((date.getTime() - new Date().getTime()) / 10000) < 0;
  const _simplifyLeg = (leg) => {
    const { distance, duration, duration_in_traffic } = leg;
    return { distance: distance, duration: duration, duration_in_traffic: duration_in_traffic };
  };
  const cache = CacheService.getScriptCache();
  const cacheKey = [functionName, startAddress, endAddress, mode, depart.toISOString()].join('→');
  const cached = cache.get(cacheKey);
  let firstLeg;
  if (cached) {
    firstLeg = _simplifyLeg(JSON.parse(cached));
  } else {
    if (_isMoreThan10SecsInThePast(depart)) {
      throw new Error(`The departure time ${depart.toISOString()} is in the past, which is not allowed.`);
    }
    const directions = Maps.newDirectionFinder()
      .setOrigin(startAddress)
      .setDestination(endAddress)
      .setMode(Maps.DirectionFinder.Mode[mode.toUpperCase()])
      .setDepart(depart)
      .getDirections();
    if (directions && directions.routes && directions.routes.length && directions.routes[0].legs) {
      firstLeg = _simplifyLeg(directions['routes'][0]['legs'][0]);
    } else {
      throw new Error(`${functionName} could not find the distance between "${startAddress}" and "${endAddress}".`);
    }
    cache.put(cacheKey, JSON.stringify(firstLeg), 6 * 60 * 60); // 6 hours
  }
  const meters = firstLeg['distance']['value'];
  const seconds = firstLeg['duration_in_traffic']
    ? firstLeg['duration_in_traffic']['value']
    : firstLeg['duration']['value'];
  switch (units) {
    case 'kilometers':
      return meters / 1000;
    case 'miles':
      return meters / 1609.344;
    case 'minutes':
      return seconds / 60;
    case 'hours':
      return seconds / 60 / 60;
  }
}

See Directions examples / Traffic information for more information.

The consumer account quota for Google Maps Direction queries is 1,000 calls per day, while for Google Workspace Domain accounts it is 10,000 calls per day. The caching of results helps avoid exceeding the limit. See Quotas for Google Services.

CodePudding user response：

That's interesting, I would like to suggest using events or trigger, and update for all the routes/arrivals and destinations

Some additional reading..

Common problems: Before you begin there are some issues others have seen with 3rd party components like maps sheets zapier, that might help you look for formatting the data to correctly update, please see here

Instant Vs. Polling: The Google Sheets trigger is marked "instant" but it still takes a few minutes to trigger. The triggers for Google Sheets are unique among Zapier triggers. When there is a trigger event in the spreadsheet, Zapier gets a notification webhook from Google about this. After that, Zapier sends Google Sheets a request for new data, so it uses both the polling and instant trigger methods. This process takes about 3 minutes overall.

Code Sample 1: Based on Arrival Time, simple

function GetYourDurationBasedonArrivalTime(point1, point2, mode) {
   //set your arrival time 5 hr times 60x60x millisec
   var arrivalTime = new Date(new Date().getTime()   (5 * 360 * 1000));

   // use your arrival time in your configuration
   var myDirections  = Maps.newDirectionFinder().setArrive(arrivalTime)
  .setOrigin(point1)
  .setDestination(point2)
  .setMode(Maps.DirectionFinder.Mode[mode])
  .getDirections();
 return myDirections.routes[0].legs[0].duration.text;
}

Code Sample 2: Automate it, so if you want, you can trigger. Please update it according to your needs.

//CREATING CUSTOM MENU on GOOGLE SHEETS
function onOpen() { 
  var ui = SpreadsheetApp.getUi();
   
  ui.createMenu("Google Travel Time")
    .addItem("Run","getDistance")
    .addItem("Set Triggers","createEveryMinutesTrigger")
    .addItem("Delete Triggers","deleteTrigger")
    .addToUi();
}
 
// GET TRAVEL TIME AND DISTANCE FOR EACH ORIGIN AND DESTINATION
function getDistance() {
   
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var inputSheet = ss.getSheetByName("Inputs");
   var range = inputSheet.getRange("B2:I");
   var inputs = range.getValues();
   var outputSheet = ss.getSheetByName("Outputs");
   var recordcount = outputSheet.getLastRow();
   var timeZone = "GMT 5:30";
   var now = new Date();
   var rDate = Utilities.formatDate(now, timeZone, "MM/dd/yyyy");
   var rTime = Utilities.formatDate(now, timeZone, "HH:mm:ss");
   var numberOfRoutes = inputSheet.getLastRow()-1;
   
   
  for(i=0;i<numberOfRoutes;i  ){
   var setDirections = Maps.newDirectionFinder() 
     .setOrigin(inputs[i][1])
     .setDestination(inputs[i][2])
     .setDepart(now)
     .setMode(Maps.DirectionFinder.Mode["DRIVING"]); 
     
    var wayCount = inputs[i][7];
     
    for(j=0;j<wayCount;j  ){
      setDirections.addWaypoint("via:" inputs[i][3 j]);
    }
     
    var directions = setDirections.getDirections();
    
    var traveltime = directions.routes[0].legs[0].duration_in_traffic.value;
    var distance = directions.routes[0].legs[0].distance.value;
    var route = inputs[i][0];
     
     
    outputSheet.getRange(i 1 recordcount,1).setValue(route);
    outputSheet.getRange(i 1 recordcount,2).setValue(now);
    outputSheet.getRange(i 1 recordcount,3).setValue(secToMin(traveltime));
    outputSheet.getRange(i 1 recordcount,4).setValue(distance/1000);
    outputSheet.getRange(i 1 recordcount,5).setValue((distance/traveltime)*(3600/1000));
    outputSheet.getRange(i 1 recordcount,6).setValue(traveltime);
    outputSheet.getRange(i 1 recordcount,7).setValue(rDate);
    outputSheet.getRange(i 1 recordcount,8).setValue(rTime);
  }
}
 
 
// AUTOMATE IT
// RUN FUNCTION EVERY n MINUTES BETWEEN GIVEN TIME DURATION
function runGetDistance() {
  var date = new Date();  
  var day = date.getDay();
  var hrs = date.getHours();
  var min = date.getMinutes();
   
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inputSheet = ss.getSheetByName("SetTriggers");
  var startHour = inputSheet.getRange("B1").getValue();
  var endHour = inputSheet.getRange("B2").getValue();
   
  if ((hrs >= startHour) && (hrs <= endHour) && (min >= 0) && (min <= 59 )) {
    getDistance();
  }
}
 
 
//CREATE TRIGGER  
function createEveryMinutesTrigger(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var inputSheet = ss.getSheetByName("SetTriggers");
  var runningInterval = inputSheet.getRange("B6").getValue();
   
   
  ScriptApp.newTrigger("runGetDistance")
    .timeBased()
    .everyMinutes(runningInterval)
    .create();
}
 
 
//DELETE TRIGGER
function deleteTrigger() {
   
  // Loop over all triggers and delete them
  var allTriggers = ScriptApp.getProjectTriggers();
   
  for (var i = 0; i < allTriggers.length; i  ) {
    ScriptApp.deleteTrigger(allTriggers[i]);
  }
}
 
function secToMin(duration){
  var minutes = parseInt((duration/60));
  var seconds = parseInt(duration`);
   
  return "00:" minutes ":" seconds;
}