skip to Main Content

Geocode addresses with Google Sheets and Google Geocoding API

Recently I discovered a powerful tool from the awesome Google Developers Youtube channel: Google Apps Script. This will let you integrate Google Apps and APIs easily. In this post I will show an example of integration between Google Sheets and Google Geocoding API.

I tried it writing a simple, but powerful example, experimenting with a use case that for sure will be useful to me in the future: geocoding a list of addresses taken from a spreadsheet.

1. Introduction

If you are new to Google App Script (like I was) here you can see an introductory video tutorial.

Another tutorial explains how to integrate Google Sheets with Google Maps and particularly with Google Geocode API.

I started from there and I created a script to geocode (that is going from an human readable address to the geographical coordinates of the location) a list of addresses written in a Google Sheets.

2. Integrating Google Sheets with the Google Geocoding API

To test the script just create a new Spreadsheet in Google Drive, and write down a list of addresses in the first column, like this:

Then click on Tools > Script editor… and write this code in the editor:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var range = sheet.getDataRange();
  var cells = range.getValues();
  
  var latitudes = [];
  var longitudes = [];
  
  for (var i = 0; i < cells.length; i++) {
    var address = cells[i][0];
    var geocoder = Maps.newGeocoder().geocode(address);
    var res = geocoder.results[0];

    var lat = lng = 0;
    if (res) {
      lat = res.geometry.location.lat;
      lng = res.geometry.location.lng;
    }
  
    latitudes.push([lat]);
    longitudes.push([lng]);
  }
  
  sheet.getRange('B1')
  .offset(0, 0, latitudes.length)
  .setValues(latitudes);
  sheet.getRange('C1')
  .offset(0, 0, longitudes.length)
  .setValues(longitudes);
}

Click run, save the script with a name, allow the script to write data on your document, and ta-dah, your addresses will be geocoded and coordinates will appear in the second and third column!

You can also save other data coming from the Geocoder in the sheet. For example here I save also the complete formatted address returned by the Geocoder in the fourth column:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();

  var range = sheet.getDataRange();
  var cells = range.getValues();

  var latitudes = [];
  var longitudes = [];
  var formatted_addresses = [];

  for (var i = 0; i < cells.length; i++) {
    var address = cells[i][0];
    var geocoder = Maps.newGeocoder().geocode(address);
    var res = geocoder.results[0];

    var lat = lng = 0;
    var formatted_address = '';
    if (res) {
      lat = res.geometry.location.lat;
      lng = res.geometry.location.lng;
      formatted_address = res.formatted_address;
    }
    
    latitudes.push([lat]);
    longitudes.push([lng]);
    formatted_addresses.push([formatted_address]);
  }

  sheet.getRange('B1')
  .offset(0, 0, latitudes.length)
  .setValues(latitudes);
  sheet.getRange('C1')
  .offset(0, 0, longitudes.length)
  .setValues(longitudes);
  sheet.getRange('D1')
  .offset(0, 0, formatted_addresses.length)
  .setValues(formatted_addresses);
}

Please refer to the documentation to know what other data are present in the object returned by the Geocoder.

3. Conclusion

In this tutorial you learned how to use the Google Geocoding API to automatically geocode addresses on a Google Sheets file.

Google Apps Script can do a lot more than integrating Google Sheets and Google Geocoding API. In the documentation you’ll find all services that can be used in your scripts. You can integrate GMail, Google Calendar, Docs, Drive, Forms and more. You can also connect to external public APIs from your scripts.

Have fun with Google Apps Script! 😉

augusto

Freelance developer and sysadmin

This Post Has 30 Comments
  1. Thank you for this.

    I have an issue when running the script in that if Google doesn’t recognise an address it gives an error
    TypeError: Cannot read property “geometry” from undefined. (line 15, file “”)

    This is all new to me so no idea.
    Any suggestions for a work around?

    1. Hey Shane, wrap lines 15 and 16 in an if(res){

      if(res){
      latitudes.push([res.geometry.location.lat]);
      longitudes.push([res.geometry.location.lng]);
      }
      }

  2. Sorry, my first response was a little incomplete:

    if(res){
    latitudes.push([res.geometry.location.lat]);
    longitudes.push([res.geometry.location.lng]);
    }
    else
    {
    latitudes.push([“0”]);
    longitudes.push([“0”]);
    }

  3. Sorry but with tne new “version”

    function myFunction() {
    var sheet = SpreadsheetApp.getActiveSheet();

    var range = sheet.getDataRange();
    var cells = range.getValues();

    var latitudes = [];
    var longitudes = [];

    for (var i = 0; i < cells.length; i++) {
    var address = cells[i][0];
    var geocoder = Maps.newGeocoder().geocode(address);
    var res = geocoder.results[0];

    if(res){
    latitudes.push([res.geometry.location.lat]);
    longitudes.push([res.geometry.location.lng]);
    }
    else
    {
    latitudes.push([“0”]);
    longitudes.push([“0”]);
    }
    }

    sheet.getRange('B2').offset(0, 0, latitudes.length).setValues(latitudes)
    sheet.getRange('C2').offset(0, 0, latitudes.length).setValues(longitudes);
    }

    i have a new error: "Carattere non valido. (riga 21, file "Codice")Ignora"
    can you helo me?
    thanks

  4. This script doesn’t work as is, continously shows error codes Illegal character. (line 21, file “Code”)
    “latitudes.push([“0”]);” Also Is there a way to create a googlemaps link to each address without doing so manually (i.e. by modifying this script or another? I don’t really require Geocached coords rather just to autolink addresses to google maps links. Any and all help would be greatly appreciated.

    1. This does work and I really appreciate the author’s work:

      function myFunction() {
      var sheet = SpreadsheetApp.getActiveSheet();

      var range = sheet.getDataRange();
      var cells = range.getValues();

      var latitudes = [];
      var longitudes = [];

      for (var i = 0; i < cells.length; i++) {
      var address = cells[i][0];
      var geocoder = Maps.newGeocoder().geocode(address);
      var res = geocoder.results[0];

      if(res){
      latitudes.push([res.geometry.location.lat]);
      longitudes.push([res.geometry.location.lng]);
      }
      else
      {
      latitudes.push([0]);
      longitudes.push([0]);
      }
      }

      sheet.getRange('B1').offset(0, 0, latitudes.length).setValues(latitudes)
      sheet.getRange('C1').offset(0, 0, latitudes.length).setValues(longitudes);
      }

  5. You all are amazing, my training does not include any idea about scripts or programming and the post was simple, reading all the comments about the errors, of course, and it worked perfectly, you avoided me a week of work. Thank you very much to all

  6. Love this! Most results are correct, but is there any way to include a country code listed in a second column to make it even more spot on?

    Thanks for this handy piece of script!

      1. Great work Augusto, but what I meant was if we can include it as input? I have a column with ZIP codes, which does the job quite well, but for some addresses it isn’t enough data. The tool picks another place with the same ZIP. The inclusion of the country code as input would solve this problem.

        This website gives the right information in how to include additional data, but I can’t get my head around on how to implement this in the script: https://developers.google.com/maps/documentation/geocoding/intro#ComponentFiltering

          1. Oh, now I understand your question. I would try to append the city and the country in the string passed to geocode() method. Google Geocoder is usually quite smart in understanding addresses.
            Try adding something like this inside the for-loop:

            var address = cells[i][0];
            var city = cells[i][1];
            var country = cells[i][2];
            var complete_address = address + ' ' + city + ' ' + country;
            var geocoder = Maps.newGeocoder().geocode(complete_address);

  7. Here is the entire script I use, where:
    1. I have a sheet with data from a mysql-database.
    2. I regularly append new data from the mysql-database to the end of the sheet (via another function, not included)
    3. Use your script (bit modified) to add geo information to the addresses.
    4. Use the script on new rows only through the included ‘getLastRowSpecial’ function.

    I am not a coder, so maybe things could have been better scripted (comments welcome), but… for me this works 🙂

    //****GLOBALS****
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Boekingen”);

    function addGeolocation() {
    //Select the column we will check for the first blank cell
    var columnToCheck = sheet.getRange(“P:P”).getValues();
    var lastRow = getLastRowSpecial(columnToCheck);

    var range = sheet.getRange(“J:L”);
    var filledcells = range.getValues();
    var subrange = sheet.getRange(“J:L”).offset(lastRow, 0, filledcells.length – lastRow);
    var cells = subrange.getValues();
    var latitudes = [];
    var longitudes = [];
    // Logger.log(cells);
    // Logger.log(lastRow);

    // for (var i = 0; i < cells.length && cells[i][0] !=""; i++) {
    for (var i = 0; i < cells.length; i++) {
    var lock = LockService.getScriptLock();
    lock.waitLock(20000);
    Utilities.sleep(500);
    var zip = cells[i][0];
    var city = cells[i][1];
    var country = cells[i][2];
    var complete_address = zip + " " + city + " " + country;
    if (!complete_address || complete_address[0] == "") {
    latitudes.push([0.0000000]);
    longitudes.push([0.0000000]);
    } else {
    var geocoder = Maps.newGeocoder().geocode(complete_address);
    var res = geocoder.results[0];
    if (!res || res == "") {

    latitudes.push([0.0000000]);
    longitudes.push([0.0000000]);
    } else {
    latitudes.push([res.geometry.location.lat]);
    longitudes.push([res.geometry.location.lng]);
    }
    // Logger.log(lastRow);
    sheet.getRange("P1").offset(lastRow, 0, latitudes.length).setValues(latitudes)
    sheet.getRange("Q1").offset(lastRow, 0, latitudes.length).setValues(longitudes);
    }
    }
    }

    /************************************************************************
    *
    * Gets the last row number based on a selected column range values
    *
    * @param {array} range : takes a 2d array of a single column's values
    *
    * @returns {number} : the last row number with a value.
    *
    * Source: https://yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
    */

    function getLastRowSpecial(range){
    var rowNum = 0;
    var blank = false;
    for(var row = 0; row < range.length; row++){

    if(range[row][0] === "" && !blank){
    rowNum = row;
    blank = true;

    }else if(range[row][0] !== ""){
    blank = false;
    };
    };
    return rowNum;
    };

  8. Hi Augusto,

    This is great work and i appreciate your effort on this.

    In regards to using the Geocoder, is it free or it is actually tied to Geocoding API in Google Cloud Platform? I have been searching around and found this but i am not sure if this pricing applies to our case in Apps Script?https://developers.google.com/maps/documentation/geocoding/usage-and-billing

    This question arises when i created a new Sheet and a new Apps Script project which i can use the Geocoder function already without having to bind it to a Cloud Platform Project or enable the API anywhere. Do you know how it works in the backend?

    1. Hi JS,
      I found this in the Apps Script documentation:

      https://developers.google.com/apps-script/guides/services/quotas?hl=en

      It seems that there are some quotas you cannot exceed, and I think that this apply also to Geocoding API calls used in an Apps Script.
      I don’t see any reference to billing or pricing there, so I assume that Google services used from Apps Script are free, and if you’ll eventually exceed a quota your script will throw an error.

      1. I can confirm this. On a spreadsheet with around 3000 rows I’ve managed to geotag them in a round or 3. Now that I’m up to date I just trigger the script for the new lines. The quota is reset daily.

  9. Hi Augusto, many thanks for your work.

    I need to slightly adjust the script for my scenario, because the spreadsheet has a specific layout because it’s an export from one system, and needs to keep the same layout as it will be imported into another system after the geocode runs. My spreadsheet has ZIP code in Column B, and Country in Column C, so my lat/long would need to go to D & E.

    Using your guidance in one of the questions above, I’ve tried to modify the ‘for’ section thus:

    for (var i = 0; i < cells.length; i++) {
    var zip = cells[i][1];
    var country = cells[i][2];
    var complete_address = zip + ' ' + country;
    var geocoder = Maps.newGeocoder().geocode(complete_address);
    var res = geocoder.results[0];

    Have I done it right? Does the 'cells[i][1]' refer to column B and 'cells[i][2]' refer to column C?

    I've also changed B1 and C1 at the bottom of the script to D1 and E1.

    My spreadsheet also has headings. How do I tell the script to skip the first line?

    I can't seem to get any result from the script. Or an error either. The script has been given permissions to run.

    Thanks.

  10. Hi Augusto and friends,

    Been searching for a while before I found this useful content. I have been trying to use Michaël’s script (@Michaël – Thanks) but encounter some error and not sure how to solve it.

    It would be great if anyone can help a helping hand. I have the address in Column A and want to output the coordinates in column D and E. This is my test sheet: https://docs.google.com/spreadsheets/d/1hUSdU_Lc03XmD91ci9bEVCfG76jDk6pI9RWg6P-FzGM/edit?usp=sharing and the following is the script I use :

    //****GLOBALS****
    // var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“People”);
    var sheet = SpreadsheetApp.getActiveSpreadsheet(“WAO 003_Test Geocode”).getSheetByName(“People”)
    function addGeolocation() {
    //Select the column we will check for the first blank cell
    var columnToCheck = sheet.getRange(“A:E”).getValues();
    var lastRow = getLastRowSpecial(columnToCheck);

    var range = sheet.getRange(“A:C”);

    var filledcells = range.getValues();
    var subrange = sheet.getRange(1,A).offset(lastRow, 0, filledcells.length – lastRow);
    var cells = subrange.getValues();
    var latitudes = [];
    var longitudes = [];
    Logger.log(cells);
    Logger.log(lastRow);

    // for (var i = 0; i < cells.length && cells[i][0] !=""; i++) {
    for (var i = 0; i < cells.length; i++) {
    var lock = LockService.getScriptLock();
    lock.waitLock(20000);
    Utilities.sleep(500);
    var Address = cells[i][0];
    var PostalCode = cells[i][1];
    var country = cells[i][2];
    var complete_address = Address;
    if (!complete_address || complete_address[0] == "") {
    latitudes.push([0.0000000]);
    longitudes.push([0.0000000]);
    } else {
    var geocoder = Maps.newGeocoder().geocode(complete_address);
    var res = geocoder.results[0];
    if (!res || res == "") {

    latitudes.push([0.0000000]);
    longitudes.push([0.0000000]);
    } else {
    latitudes.push([res.geometry.location.lat]);
    longitudes.push([res.geometry.location.lng]);
    }
    // Logger.log(lastRow);
    //sheet.getRange("P1").offset(lastRow, 0, latitudes.length).setValues(latitudes)
    //sheet.getRange("Q1").offset(lastRow, 0, latitudes.length).setValues(longitudes);
    }
    }
    }

    /************************************************************************
    *
    * Gets the last row number based on a selected column range values
    *
    * @param {array} range : takes a 2d array of a single column's values
    *
    * @returns {number} : the last row number with a value.
    *
    * Source: https://yagisanatode.com/2019/05/11/google-apps-script-get-the-last-row-of-a-data-range-when-other-columns-have-content-like-hidden-formulas-and-check-boxes/
    */

    ++
    function getLastRowSpecial(range){
    var rowNum = 0;
    var blank = false;
    for(var row = 0; row < range.length; row++){

    if(range[row][0] === "" && !blank){
    rowNum = row;
    blank = true;

    }else if(range[row][0] !== ""){
    blank = false;
    };
    };
    return rowNum;
    };

  11. Hello, this is great, thank you!
    But I get the error “Exception: Invalid argument: location (line 12, file “functions”)”

    which is “var geocoder = Maps.newGeocoder().geocode(address);”

    Any ideas on how to fix this?

    Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top