I am attempting to loop through different latitudes and longitudes of a location and a destination and an arrival time through Google Directions APIs specified in my spreadsheet.
function getDeparture() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Houses');
var ss2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Details');
var propertyLinks = ss.getRange("A2:A").getValues();
var propertyLength = propertyLinks.length;
var arrival = ss2.getRange("B1").getValues()[0][0];
var person1 = ss2.getRange("B3").getValues()[0][0];
var person2 = ss2.getRange("B2").getValues()[0][0];
var allLat = ss.getRange("I2:I").getValues();
var allLng = ss.getRange("J2:J").getValues();
var person1Durations = [];
var person1Arrivals = [];
var person1Departs = [];
var person2Durations = [];
var person2Arrivals = [];
var person2Departs = [];
for (var i = 0; i < propertyLength; i++) {
var isEmpty = propertyLinks[i][0] === "" || allLat[i][0] === "" || allLng[i][0] === "" ? true : false;
if (!isEmpty){
var lat = allLat[i][0];
var lng = allLng[i][0];
// person1
var person1Direction = getDirection(lat, lng, person1, arrival);
var person1Duration = getDuration(person1Direction);
var person1Arrival = getArrival(person1Direction, arrival);
var person1Depart = getDepartTime(person1Direction);
person1Durations.push([person1Duration]);
person1Arrivals.push([person1Arrival]);
person1Departs.push([person1Depart]);
//person2
var person2Direction = getDirection(lat, lng, person2, arrival);
var person2Duration = getDuration(person2Direction);
var person2Arrival = getArrival(person2Direction, arrival);
var person2Depart = getDepartTime(person2Direction);
person2Durations.push([person2Duration]);
person2Arrivals.push([person2Arrival]);
person2Departs.push([person2Depart]);
}
}
ss.getRange("B2:B").setValues(person2Durations);
ss.getRange("C2:C").setValues(person1Durations);
ss.getRange("D2:D").setValues(person2Departs);
ss.getRange("E2:E").setValues(person1Departs);
ss.getRange("F2:F").setValues(person2Arrivals);
ss.getRange("G2:G").setValues(person1Arrivals);
}
function getDirection(lat, lng, destination, arrival) {
var address = "https://maps.googleapis.com/maps/api/directions/json?origin=" + lat + ", " + lng + "&destination=" + destination + "&mode=transit&arrival_time=" + arrival;
var res = UrlFetchApp.fetch(address);
var content = res.getContentText();
var json = JSON.parse(content);
var routes = json["routes"];
var legs = routes[0]["legs"];
return legs;
}
function getDuration(legs) {
var duration = legs[0]["duration"]["value"];
var minutes = Math.floor(duration / 60);
var seconds = duration % 60;
var result = Math.ceil(minutes + '.' + seconds);
return result;
}
function getArrival(legs, destArrival) {
var arrival;
if(legs[0]["arrival_time"] !== undefined){
arrival = legs[0]["arrival_time"]["text"];
}else{
arrival = destArrival;
}
return arrival;
}
function getDepartTime(legs, destArrival) {
var depart;
if(legs[0]["departure_time"] !== undefined){
depart = legs[0]["departure_time"]["text"];
}else{
depart = destArrival - legs[0]["duration"]["text"];
}
return depart;
}
As far as I can tell I am returning all the correct values. I am looping through all the locations I have specified in the sheet (64) and dismissing any empty cells in the range specified by
var propertyLinks = ss.getRange("A2:A").getValues();
On the following line:
ss.getRange("B2:B").setValues(person2Durations);
Is where I am getting the following error
Incorrect range height, was 64 but should be 938 (line 55, file "Code")
Any help to resolve this would be greatly appreciated.