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?
Please find below a custom function to get driving or walking distances and durations and other such data from the Maps service. The function checks arguments, can iterate over larger ranges of values in one go, and uses CacheService to cache results for up to six hours to help avoid exceeding rate limits.
To find a driving distance, you only need to specify start_address and end_address.
To find a driving duration, you need to additionally specify units of "hours" or "minutes", the travel_mode, and depart_time. Note that you need to specify the future time you will start the trip, because durations depend on whether it is a rush hour and other such things.
The function accomplishes the duration fetch using .setDepart(). The result is in 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.
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("14:15"))} depart_time Optional. A reference to a cell that contains a time or a datetime. Use "now" to refer to the current date and time. Times will default to today, or tomorrow when the time is already past. To specify an exact date, use to_date(value("2029-07-19 14:15")). The datetime cannot be in the past.
* @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) {
// version 1.4, written by --Hyde, 17 August 2024
// - 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 startAddress = Array.isArray(start_address) || !Array.isArray(end_address)
? _get2dArray(start_address)
: _get2dArray(end_address).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);
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.4, written by --Hyde, 17 August 2024
// - cache 'now' results for an hour
const functionName = 'GoogleMapsDistance';
const _clean = (v) => typeof v === 'string' ? v.trim().toLowerCase() : v;
units = String(_clean(units)).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 = _clean(mode);
if (!['bicycling', 'driving', 'transit', 'walking'].includes(mode)) throw new Error(`${functionName} expected a mode of "bicycling", "driving", "transit" or "walking" but got "${mode}" instead.`);
let departTime = _clean(depart);
if (departTime && departTime !== 'now' && !departTime.toISOString) throw new Error(`${functionName} expected a depart time that is "now" or a valid datetime value, but got the ${typeof depart} "${depart}" instead.`);
const departISOString = departTime === 'now'
? new Date(new Date().setMinutes(0, 0, 0)).toISOString() // cache 'now' results for an hour
: departTime && departTime.toISOString ? departTime.toISOString() : '';
const now = new Date();
if (!departTime || departTime === 'now') departTime = now;
if (departTime.getTime() < 2 * 24 * 60 * 60 * 1000) { // detect bare time
departTime.setFullYear(now.getFullYear(), now.getMonth(), now.getDate() + (departTime.toTimeString() < now.toTimeString()));
}
const _isMoreThan10SecsInThePast = (date) => date.getTime() - now.getTime() < -10000;
const _simplifyLeg = (leg) => ({ distance: leg.distance, duration: leg.duration, duration_in_traffic: leg.duration_in_traffic });
const cache = CacheService.getScriptCache();
const cacheKey = [functionName, startAddress, endAddress, mode, departISOString].join('→');
const cached = cache.get(cacheKey);
let firstLeg;
if (cached) {
firstLeg = JSON.parse(cached);
} else {
if (_isMoreThan10SecsInThePast(departTime)) throw new Error(`The departure time ${departISOString} is in the past, which is not allowed.`);
const directions = Maps.newDirectionFinder()
.setOrigin(startAddress)
.setDestination(endAddress)
.setMode(Maps.DirectionFinder.Mode[mode.toUpperCase()])
.setDepart(departTime)
.getDirections();
if (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?.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.