Skip to content

Instantly share code, notes, and snippets.

@sumitramteke
Created December 17, 2015 10:27
Show Gist options
  • Save sumitramteke/978874f16dc3fed85ec5 to your computer and use it in GitHub Desktop.
Save sumitramteke/978874f16dc3fed85ec5 to your computer and use it in GitHub Desktop.
This will find GMAP Coordinates for list of companies in "HR and Client meeting details" drive sheet
function setCoordinates() {
var coordinateCell = "Q";
var addressCell = "P";
var companyCell = "A";
var coordColNum = 16;
var addrColNum = 15;
var compColNum = 0;
// if true then it will replace coord with new values
var keepOrgCoordFLAG = true;
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getMaxRows();
var values = sheet.getRange(2, 1, rows-1, 16).getValues();
var latLong, company, address;
var coordinate;
var self = this;
values.forEach( function(currRow, index) {
var contentCount = currRow.length;
address = currRow[addrColNum];
if (address == '' || address == undefined) // address is absent
return false;
else {
coordinate = currRow[coordColNum];
if(keepOrgCoordFLAG && coordinate != undefined)
return false;// if not ask for any update in current value of coordinates
company = currRow[compColNum];
coordinate = self.getLatitudeLongitude(company, address);
sheet.getRange(coordinateCell + '' + Number(index+2)).setValue(coordinate);
}
});
}
function getLatitudeLongitude(company, address) {
var locationInfo = company + ", " + address;
geoResults = Maps.newGeocoder().geocode(locationInfo);
Utilities.sleep(1000);
// Get the latitude and longitude
if(geoResults.status == "OK") {
var lat = geoResults.results[0].geometry.location.lat;
var lng = geoResults.results[0].geometry.location.lng;
return '@' + lat + ',' + lng;
} else return '';
}
// test doc link : https://docs.google.com/a/synerzip.com/spreadsheets/d/19j88E_KcVAGyWZd7FjT3lVTYEslpFOn7mNQNZdOo8YE/edit?usp=sharing
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment