Help Using Google Maps Longitude / Latitude Population on Locations and Companies

[chaselong]

I'm very interested in using the Mapping Reports in Helsinki, and to do that it looks like I need to populate the longitude / latitude fields for records on my Locations and Companies tables. I read here that there are Business Rules (both named get_lat_long) for these tables that will populate these fields automatically based on the address information on each record. I then read here that you need an API key from Google in order to use this feature now. I've turned on the two business rules, obtained a Google Maps API key, entered it in the "Private key for Google Maps API for Business" under System Properties -> Google Maps, and am still having no luck. When I update location records no lat / long information seems to be pulled in. Could anyone offer me some guidance as to what I'm missing? The problem appears to be on the SN instance side as the Google API dashboard doesn't show any traffic.

[chaselong]

I'm still having trouble getting this to work, so I decided to go a different route based on an old community article I found here. Instead of using the OOB get_lat_long business rules, I just disabled them and created new BRs (the same as the OOB ones but with the script from the old community post.) After some slight modifications to the script I got the new BRs to query the Google API, but it still isn't updating the longitude and latitude fields in SN. Can anyone see why it isn't updating them?

// get lat long from geocoder
var street = new String(current.street);
street = street.replaceAll("\r","");
street = street.replaceAll("\n"," ");
street = street.replaceAll(" ","+");

var city= new String(current.city).replaceAll(" ","+");
var state = new String(current.state).replaceAll(" ","+");
var zip= new String(current.zip).replaceAll(" ","+");

var address = "address=" + street + "," + city + "," + state + "," + zip + "&key=My_Google_API_Key";

gs.log("address=" + address);

var ws = new HTTPAdaptor("https://maps.googleapis.com/maps/api/geocode/json?");
var ret = ws.doGet(address);
var answer = new String(ret);

answer = answer.split(",");
if (answer.length > 1) {
current.latitude = answer[0];
current.longitude = answer[1];
} else {
current.lat_long_error = ret;
}

gs.log("lat long response:" + ret);

if (current.latitude.changes() || current.longitude.changes() ||
current.lat_long_error.changes()) {
current.update();
}

[chaselong · Accepted solution]

Finally got this to work. Posting the new business rule I ended up using in case anyone else is having an issue with the OOB methods. Disclaimer: I'm not really a coder, I put this together using what I found in the community and the OOB BRs / script includes, so this may not conform to coding best practices.

var street = new String(current.street);
street = street.replaceAll("\r","");
street = street.replaceAll("\n"," ");
street = street.replaceAll(" ","+");

var city = new String(current.city).replaceAll(" ","+");
var state = new String(current.state).replaceAll(" ","+");
var zip = new String(current.zip).replaceAll(" ","+");
var address = "address=" + street + "," + city + "," + state + "," + zip + "&key=YOURGOOGLEAPIKEY";
var ws = new HTTPAdaptor("https://maps.googleapis.com/maps/api/geocode/json?");
var ret = ws.doGet(address);
var lat = 0,
           lng = 0;

try {
   var output = new JSONParser().parse(ret); 
   if (output.status == "OK") {
             lat = output['results'][0]['geometry']['location']['lat'];
             lng = output['results'][0]['geometry']['location']['lng'];
             current.latitude = lat;
             current.longitude = lng;
             current.update();
   } else {
             gs.logErr("Get_Lat_Long Lookup Error: " + output.status);
             gs.logErr("Address: " + address);
             gs.logErr("API Response: " + ret);
   }
} catch (err) {
             gs.logErr("Geocoding error: " + jsonOutput);
   }

[Marcel H_]

Hi Chase, thanks for sharing the script that you created. I am trying to use the code to update latitude and longitude fields on custom tables that I've created and it doesn't seem to work for me. Wondering if you have any further details on what might be causing an issue? I obtained a Google API key, but not sure if something else could be conflicting with the updates to the latitude and longitude fields. The only changes that I made were to add u_ to the street, city, state, etc. names since I'm on a custom table.

[chaselong]

Hi Marcel,

I would think that would work. I haven't tried the script in any other context, but all the logic is contained in the script. It isn't calling any outside script include or anything else, so as long as the field names are correct I'd think it would work. Have you had anymore luck troubleshooting? Can you post the script with your modifications?