google spreadsheet by using Distance Matrix API Pass the arrival time

by Pathum Anjana   Last Updated September 16, 2019 23:03 PM

Hi I've used a simple script before to retrieve the following data set but now I need the urgent help from this community to pass the arrival date to this code. To summarize the requirement: I'm trying to Find travel time with traffic data between two locations or zip-code.

Inputs parameters would be from Location, destination, arrival time(this is 6AM OR 8AM), mode of transportation, traffic model.

Based on the above input parameters in my google script function it should return travel time but I need to Include arrival time Eg : 6AM

So far I've tried this way but not sure since I'm not familiar with scripting functions and I'm posting this for proper validation

function onOpen() {//adds new menu item to active spreadsheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name : "Calculate distance", functionName : "findDistance"}];
  sheet.addMenu("Distance measure", entries);
}



function findDistance() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadSheet.getSheetByName('Sheet1');//sheet with locations
  var originColumnNumber = 1;//column with origin location
  var destinationColumnNumber = 2;//column with destination location
  var distanceColumnNumber = 3;
  var durationColumnNumber = 4;
  var range = sheet.getDataRange();
  var data = range.getValues();
  var dataLength = data.length;
  var ignoreErrors = true;//set to false to see errors
  var ignoreHeaderRow = true;
  var firstRow = ignoreHeaderRow ? 1 : 0;

  for (var i = firstRow; i < dataLength; i++) {
    var row = data[i];
    var origin = row[originColumnNumber - 1];
    var destination = row[destinationColumnNumber - 1];
    var arrive = new Date();
    arrive.setHours(9,0,0,0);
    var directions, distance, duration;

    if(row[distanceColumnNumber - 1] && row[durationColumnNumber - 1]) {//skipping calculated row
      continue;
    }

    try {
      directions = DISTANCE_AND_TIME(origin, destination);//Use DM_DISTANCE_AND_TIME if Distance Matrix Web Api needed. In that case don't forget to set 'apiKey' variable
      distance = directions[0][0];
      duration = directions[0][1];
    } catch(e) {
      if(ignoreErrors) {
        distance = 'N/A';
        duration = 'N/A';
      } else {
        throw e;
      }
    }

    sheet.getRange(i + 1, distanceColumnNumber).setValue(distance);
    sheet.getRange(i + 1, durationColumnNumber).setValue(duration);

    Utilities.sleep(500);
  }
}

/**
 * Find distance and driving time using Distance Matrix API.
 *
 * @param {string} origin Starting point.
 * @param {string} destination Finishing point.
 * @return {string[]} distance and driving time.
 * @customfunction
 */
function DM_DISTANCE_AND_TIME(origin, destination) {
  var apiKey, url, directions, distance, duration,arrive;

  if(!origin || !destination) {
    throw new Error("Invalid arguments");
  }

  apiKey = "MY_API_KEY";
  url = "https://maps.googleapis.com/maps/api/distancematrix/json" +
      "?key=" + apiKey +
      "&origins=" + encodeURIComponent(origin) +
      "&destinations=" + encodeURIComponent(destination) +
      "&mode=TRANSIT"
      "&arrival_time=arrive";

  directions = JSON.parse(UrlFetchApp.fetch(url));

  if(directions.status !== "OK") {
    throw new Error(directions.status + (directions.error_message ? (": " + directions.error_message) : ''));
  }

  if(directions.rows[0].elements[0].status !== "OK") {
    throw new Error(directions.rows[0].elements[0].status);
  }

  distance = directions.rows[0].elements[0].distance.text;
  duration = directions.rows[0].elements[0].duration.text;

  return [[distance, duration]];
}


Related Questions


Updated September 16, 2019 09:03 AM

Updated June 25, 2019 19:03 PM

Updated September 17, 2019 08:03 AM

Updated April 27, 2019 08:03 AM

Updated May 29, 2019 06:03 AM