Google Docs Geocoding Tutorial

Before we dive in to the tutorial ...

Please note that if your spreadsheet geocoding needs are more complex, or you'd just generally like someone else to solve this for you, we recommend you work with our friends at Love Spreadsheets, who will gladly use our API to do the geocoding for you.

Ok, ready?

You can use Google Sheets and the OpenCage Geocoder to quickly convert a list of coordinates to placenames (reverse geocoding) or vice versa (forward geocoding).

  1. register for an OpenCage Geocoder API key
  2. create a new Sheet

    Put your addresses in a column, or your coordinates in two columns (latitude, longitude)

    In our first example we show reverse geocoding

  3. open Tools > Scripts editor

  4. copy the script opencage.js into the scripts editor and save, name the project whatever you like, the default is "Untitled project"

  5. reload your Google Sheet. After a few seconds a new "Geocode" button should appear

  6. select three columns, and then click on "Latitude, Longitude to Address"
    • You will be prompted to give the script permission to run. You will need to select "Continue".
    • A popup will appear asking you which Google account you want to authorise. Select one.
    • The popup will display a warning that our app isn't verified. Click "Advanded"
    • Scroll down and then click on the link to "Go to your-project-name (unsafe)"
    • Now you will need to give the project access to your spreadsheets. Click "Allow"
    • A popup will appear in your spreadsheet asking your for your OpenCage API key. Enter it and click "Geocode"

  7. the results will appear in the third column you selected.

Forward geocoding is very similar:

  1. Follow steps 1-5 above
  2. create three columns, the first column should contain the address

  3. select the three columns and then click "Address to Latitude, Longitude" in the "Geocode" menu.

    • You will be prompted to give the script permission to run. You will need to select "Continue".
    • A popup will appear asking you which Google account you want to authorise. Select one.
    • The popup will display a warning that our app isn't verified. Click "Advanded"
    • Scroll down and then click on the link to "Go to your-project-name (unsafe)"
    • Now you will need to give the project access to your spreadsheets. Click "Allow"
    • A popup will appear in your spreadsheet asking your for your OpenCage API key. Enter it and click "Geocode"
  4. The results will appear in the second and third column you selected.

Start your free trial

2,500 API requests per day.

No credit card required.