I’ve been doing a bit of nerding around with a side project, which involves editing a bunch of addresses in Google Sheets and having to geocode them into raw lat/lng coordinate pairs.
I went ahead and coded up a quick App Script macro for Google Sheets that lets you select a 3-column wide swath of the spreadsheet and geocode a text address into coordinates.
Update 10 January 2016:
The opposite is now true too, you can take latitude, longitude pairs and reverse-geocode them to the nearest known address. Make sure you use the same column order as in the above image: it should always be Location, Latitude, Longitude.
I’ve moved the source to Github here:
https://github.com/nuket/google-sheets-geocoding-macro
It’s pretty easy to add to your Google Sheets, via the Tools -> Script Editor. Copy and paste the code into the editor, then save and reload your sheet, and a new “Geocode” menu should appear after the reload.
Update 15 March 2021:
I’ve added code to allow for reverse geocoding from latitude, longitude pairs to the individual address components (street number, street, neighborhood, city, county, state, country).
It flies!
I found a small glitch, maybe because of HTML code?
Replace < by < in line 22, so it reads:
for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {
I mean the chars “& l t ;” which resolve as “<" but don´t work in spreadsheet
:-O
Fixed! Thanks for the heads-up.
Thanks. Now, do you have some voodoo for spatial joins in Google spreedsheets? 🙂
Linked your solution here:
http://wiki.glitchdata.com/index.php?title=Google_Spreadsheet
wow, you just made my day!
Just geocoded my first 1000 addresses. I will do some more in the next days 🙂
Thank you very much, that is definitly the best solution what I found in www!!!
warm regards ulrix
can’t seem to get the macro to work as posted. here’s a print screen of my sheet.
https://www.dropbox.com/s/rf9twgrcmbrotu0/geocode_gsheets_macro_error.tiff?dl=0
My guess: It could be a matter of formatting the address the way it would be in the country where it is used. There are seemingly endless ways in which addresses get punctuated…
Thanks for the great starting point! I have refined this code a little and created a google sheets add-on for all to use. The code has been open sourced here: https://github.com/mlucool/geocode-google-sheets
Feel free to update/contribute.
Hi Max,
Neat script. Is there a way to do it the other way around?
With Kind Regards,
Remi
Something similar like:
function reverseGeocode() {
var sheet = SpreadsheetApp.getActiveSheet();
var cells = sheet.getActiveRange();
var location;
var addressColumn = 5; // this will be the destination where the location (address) should be added
var addressRow;
var lat = parseFloat("523.528.854");
var lng = parseFloat("49.037.551");
var latlng = new google.maps.LatLng(lat, lng);
var geocoder = Maps.newGeocoder().setRegion('nl');
// Must have selected 2 columns (Lat, Lng).
// Must have selected at least 1 row
if (cells.getNumColumns() != 2) {
Logger.log("Must select the Lat and Lng columns.");
return;
}
geocoder.geocode({ 'latLng': latlng }, function (results, status) {
if (status == google.maps.GeocoderStatus.OK) {
if (results[1]) {
console.log("Location: " + results[1].formatted_address);
}
}
});
}
/**
* Adds a custom menu to the active spreadsheet, containing a single menu item.
*
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
*
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name: "Geocode Selected Cells",
functionName: "geocodeSelectedCells"
}];
sheet.addMenu("Macros", entries);
};
I’ve updated the code to do reverse geocoding. Cheers.
This is a wonderful code. You made my day. Thank you!!
Great tool. Thanks for this.
Quick question: I found an error saying
TypeError: Cannot find function toLowerCase in object Mon Jan 25 2016 03:00:00 GMT-0500 (EST).
Any thoughts about how can I get it fixed?
Thanks in advance.
As far as I can tell it’s not in the geocoding macro, maybe it’s in another macro on your sheet?
very nice works perfectly….
Thanks. This was a real time-saver for a Church project I was doing.
Hello,
First, thanks for this awesome piece of code.
When I try to run the code on a large amount of postal addresses I get the following error message:
“Service invoked too many times in a short time: geocode. Try Utilities.sleep(1000) between calls.”
How would you get around that ?
In case you haven’t already added this — after the
if (location.status == 'OK') {block in eitheraddressToPositionorpositionToAddress— you’d have to add theUtilities.sleep(1000)call, to slow down the rate at which you’re asking Google to geocode things.This saved my life. Thank you! Is there a way to lift the daily restriction? I just received this message after doing 1,000 addresses.
Message details
Service invoked too many times for one day: geocode.
Thank you!
That’s a limit set by Google, I think it can be lifted, but I think it involves paying them some kind of fee.
This script is a huge timesaver for me, thanks so much!
Super Typ!!!!!! Vielen Dank 🙂
Works directly out of the box!
Hi Max,
Thanks for sharing this great code.
It was a huge timesaver for me, thanks so much!
Glad it helped!