Maps.NewDirectionfinder（）。SetArrive＆amp; setDepart（Google Maps api）在gsheets应用程序脚本AREN AREN;

Question

我有两个实现方法，试图获得任意驾驶路线的持续时间，并使用Google表中的应用程序脚本设置到达或出发时间。我已经用多个起源，目的地和时间组合对它们进行了测试，但是我无法返回到达或出发时间有所不同的持续时间。我已经验证了直接访问Google地图时的路线时间确实有所不同。

这是

ed7plm/copy”时间在脚本中进行了硬编码，但是我已经为测试而变化了）：

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;
}

实现2（时间是变量 adrive 从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;
};

如何使其中一个实现方法与出发时间或到达时间一起工作？

Answer 1

请在下面找到自定义功能，以获取 MAPS 服务的驾驶或步行距离和其他此类数据。功能检查参数，可以一次迭代较大的值范围，并使用 cacheservice 来缓存结果长达六个小时，以避免超过速率限制。

要找到一个驾驶距离，您只需要指定 start_address 和 end_address 。

要查找驾驶时间，您需要另外指定单位的“小时” 或“分钟” ， travel_mode 和 dection_time 。请注意，您需要指定未来的时间开始旅行，因为持续时间取决于这是否是高峰时间和其他此类事情。

该函数使用。结果在 duration_in_traffic .getDirections（）响应。请注意，该字段仅在出发时间不是过去而是将来才能提供。。

要测试函数，请将DateTime值在将来的中在 d2：d 中，然后将此公式插入单元格 j2 ：

= googlemapsdistance（A2：A13，B2：B13，“分钟”，“驾驶”，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;
  }
}

参见 Directions示例/流量信息示例/流量信息以获取更多信息。

Google Maps Direction查询的消费者帐户配额每天1,000个电话，而Google Workspace域帐户每天10,000个电话。结果的缓存有助于避免超过极限。请参阅 Google Services Services的配额。

Answer 2

的是，我想建议使用事件或触发器，并为所有途径/到达和目的地进行更新

---

。

有趣 某些问题其他已经在第三方组件（如Map+Sheets+Zapier）中看到了，这可能有助于您寻找格式化数据以正确更新，请参阅在这里

Instant vs。民意调查：Google表扳机被标记为“瞬间”，但触发仍需几分钟。 Google表的触发器在Zapier触发器中是独一无二的。当电子表格中有一个触发事件时，Zapier就此从Google获得了通知Webhook。之后，Zapier向Google表发送了新数据请求，因此它同时使用了轮询和即时触发方法。这个过程大约需要3分钟。

---

---

代码样本1：根据到达时间，简单

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;
}

---

代码样本2：自动化它，因此，如果您愿意，可以触发。请根据您的需要进行更新。

//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%60);
   
  return "00:"+minutes+":"+seconds;
}