Incorrect range height, was 64 but should be 938 using setValues

by mcclosa   Last Updated January 07, 2018 23:03 PM

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.



Related Questions



LAMPU JALAN TENAGA SURYA MURAH?

Updated November 18, 2017 15:03 PM

Data Validation relative reference copy script

Updated November 27, 2017 02:03 AM