-2

I need to calculate the google maps derived distance between around 72,000 pairs of zip codes. I found a function online for google sheets which will do this, but I of course ran out of calls well before getting through the 72,000. So I set up billing within google maps api and now have an API key along with a ClientID. But I still can't get this to work. See below where "I tried adding this" based on what I've been able to find elsewhere. 

function GOOGLEMAPS(start_address,end_address,return_type) {
  // I tried adding this:  var key = "";
  // I tried adding this: var clientID = "YourClientIDHERE.apps.googleusercontent.com"
  // I tried adding this: maps.setauthentication(clientID,key);

  var mapObj = Maps.newDirectionFinder();

  mapObj.setOrigin(start_address);
  mapObj.setDestination(end_address);
  Utilities.sleep(6000);
  var directions = mapObj.getDirections();

  var getTheLeg = directions["routes"][0]["legs"][0];

  var meters = getTheLeg["distance"]["value"];

  switch(return_type){
    case "miles":
      return meters * 0.000621371;
      break;
    case "minutes":
        // get duration in seconds
        var duration = getTheLeg["duration"]["value"];
        //convert to minutes and return
        return duration / 60;
      break;
    case "hours":
        // get duration in seconds
        var duration = getTheLeg["duration"]["value"];
        //convert to hours and return
        return duration / 60 / 60;
      break;      
    case "kilometers":
      return meters / 1000;
      break;
    default:
      return "Error: Wrong Unit Type";
   }

}
GLearner
  • 21
  • 2
  • I think this helps: https://stackoverflow.com/questions/28948812/set-maps-api-key-in-script-editor – Entin Feb 12 '20 at 12:45

1 Answers1

0

The function works fine. I tested it between my current home and my childhood home and got 1212 miles. With Google Maps it was 1212 Miles. So pretty close I'd say.

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Yes I know the function works fine, but I'm trying to get my API connection to work properly so I can run it 72,000 times. – GLearner May 02 '19 at 17:15