After facing countless frustrations trying to find a way to geocode 300+ addresses to latitude and longitude coordinates for an upcoming project, I decided to investigate scripting Google Spreadsheets to handle the task.
The ImportData() solution that has been spread around is clever, but, unfortunately, Google limits spreadsheets to 50 or fewer ImportData() formulas per sheet. For situations where many more geocodes are needed, this won't work.
This is where I turned to Google Apps Script for the first time, and I've published a script in the script gallery called "Batch Geocode."
Here's how to use it.
First, open your Google Spreadsheet and select Tools -> Script Gallery. Search for "Batch Geocode," and click "Install."
After installation, you will need to close and re-open your spreadsheet. Upon re-opening, you will find a "Geocoder" menu added up top.
Prepare your data for geocoding. For my spreadsheet, I had to concatenate several fields first to get a valid location field.
After copying the formula down the spreadsheet, I had complete addresses ready for geocoding.
Next, hightlight the addresses you want geocoded, and select "Geocode addresses" from the "Geocoder" menu.
You will be prompted for which columns you would like to overwrite with the latitude and longitude values, respectively. These should be two column letters, separated by a comma. If you would like to insert new columns for the data, leave this field blank. Press "OK."
That's all there is to it. The script throttles the process to a maximum of 5 geocoding requests per second, to ease the burden on Google servers and avoid rate limits, so you may have to wait a bit.
However, you will be notified once the script has completed, and your latitude and longitude values will appear in the specified columns!
Until the script has been approved by Google for addition to the script gallery, the raw script can be downloaded here, and installed manually through your spreadsheet's script editor.
