As we have tried to geocode addresses in Google Spreadsheets automatically we have found a solution. It turned out, that this solution is not usefull if you want to produce Lat/Lon automatically. I’ve played a little bit and would like to show you an easier and simpler way.
First off all create a spreadsheet with the header line Location, Lat and Lon:
Next Max showed us the code to use but we will change it: We will produce two functions which have the address as input and provide a result for the lattitude and one for the longitude… So go to Tools->Scipt Editor and click on “create new project” in the new tab of the script edditor. In the new dialog choose “Blank Project”:
Replace the initial lines with the code from Max:
function getLat(address) {
if (address == '') {
Logger.log("Must provide an address");
return;
}
var geocoder = Maps.newGeocoder();
var location;
// Geocode the address and plug the lat, lng pair into the
// 2nd and 3rd elements of the current range row.
location = geocoder.geocode(address);
// Only change cells if geocoder seems to have gotten a
// valid response.
if (location.status == 'OK') {
lat = location["results"][0]["geometry"]["location"]["lat"];
return lat;
}
};
function getLon(address) {
if (address == '') {
Logger.log("Must provide an address");
return;
}
var geocoder = Maps.newGeocoder();
var location;
// Geocode the address and plug the lat, lng pair into the
// 2nd and 3rd elements of the current range row.
location = geocoder.geocode(address);
// Only change cells if geocoder seems to have gotten a
// valid response.
if (location.status == 'OK') {
lng = location["results"][0]["geometry"]["location"]["lng"];
return lng;
}
};
Save the code and publish it by clicking on Publish->Deploy as Web App give it a name and save it as the first version. You should see something similar to this:
<
p align=”justify”>Press deploy at the end. Press “OK” to close the last dialog you will see which gives you an adress for your webapp. Go back to your spreadsheet and reload it by pressing “F5”.
To use the function just type in a cell of your choice
=getlat(A2)
and in another
[=getlon(A2)
and you will see a nice and easy set of coordinates which can be used in a webmapping application by publishing your spreadsheet as a csv…
Awesome work! Tried several solutions before, and this is the only one that worked for me… so simple!
Works great!
Amazing! thank you so much!
“Approve”
greaaaaat !!!!!! thanks
Thanks a million for this piece of code. It inspired me to create a reverse geocode script!
function getAdd(lat, lng) {
// Return Address by taking the coordinates and reverse geocoding.
if (lat == “”) {
return “You have to provide latitudinal coordinates to the place”
} if (lng == “”){
return “You have to provide longitudinal coordinates to the place”
}
var response = Maps.newGeocoder().reverseGeocode(lat, lng); //Call the reverse Geocode service
for (var i = 0; i < response.results.length; i++) {
var result = response.results[i];
return result.formatted_address; //Output full address in cell
}
};
Hi there. This was working great for me up until this morning when Google Sheets starting giving me the following error: cannot find method reverseGeocode(object,(class)). Any reason why?
Just tested mine right now. Still working. Maybe it was a temporary issue.
That’s so odd, here’s what my function looks like–exactly like the one you posted. What am I doing wrong?
function getAdd(lat, lng) {
// Return Address by taking the coordinates and reverse geocoding.
if (lat == “”) {
return “You have to provide latitudinal coordinates”
} if (lng == “”){
return “You have to provide longitudinal coordinates”
}
var response = Maps.newGeocoder().reverseGeocode(lat, lng);
for (var i = 0; i < response.results.length; i++) {
var result = response.results[i];
return result.formatted_address;
}
};
Nothing wrong with the code. Something intermittent with the service.
Thanks for a really simply version of this! Is there a way to increase the envoke script limit?
Can I give you six stars for such simple step-by-step instructions? Thanks for speeding up my day.
Thanks! Just one problem: I have about 1000 addresses to geocode. Is there anything I can do about this error (see attached image)?
wait a day or use another coder 😉
Haha, thanks! I’ll try again Thursday and let you know how it turns out.
or you can try to alter the code so it will just be called once to get lat and lon together, should be easy to do. and so you wont use 2000 service units for 1000 addresses.
use only lat function but alter it to look like:
if (location.status == ‘OK’) {
lat = location[“results”][0][“geometry”][“location”][“lat”];
lon = location[“results”][0][“geometry”][“location”][“lon”];
//attention here just pseudeocde:
//coord = concatenate(lat, lon, separated by “;”)
//return coord
}
};
Any way to get your current location in Google Sheets automatically? Is there code that will use Google Maps to give you exactly where you currently are?
infinite thanks .this code might get my app in the air much quicker and better than expected.
Wow, you just made me look like a genius in front of my partner! Thanks!
look right:.. there is a donation button 😉
Thank you Rico for your donation!
You rule!
Thanks, this work brilliantly! Even better, it could throw customized native errors when it doesn’t work, and return a 2×1 array of lat & lon when it does. This avoids geocoding twice as many times as necessary (as addressed in an earlier comment), without needing to use other functions to separate the two numbers with a semicolon in between: function geoGetLatLon(address) { if (address == ”) { throw Error(“No address provided”); } var geocoder = Maps.newGeocoder(); var location; location = geocoder.geocode(address); if (location.status == ‘OK’) { lat = location[“results”][0][“geometry”][“location”][“lat”]; lng = location[“results”][0][“geometry”][“location”][“lng”]; return [[lat,lng]]; } else { throw Error(“Geocode failed”);… Read more »
hm I have searcheed for such a solution and it is great, but can it be used as arrayformula?