Google Docs Geocoding Tutorial

This is a tutorial for using the OpenCage geocoding API in Google Docs.

Please note

  • This Google sheets plugin is designed as a way to quickly test our service or to do a small (< 1,000) volume of geocoding. It is not designed for high volume production use.
  • 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 Clean Spreadsheets, who will gladly use our API to do the geocoding for you.

Let's start

  1. Sign up for an OpenCage Geocoding API key, or if you already have one log in.
  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

    google docs geocoding example step 1
  3. Open Extensions > Apps Script

    google docs geocoding example step 2
  4. Copy the script opencage.js into the scripts editor and save, name the project whatever you like, the default is "Untitled project"

    google docs geocoding example script editor
  5. Reload your Google Sheet. After a few seconds a new "Geocode" button should appear in the menu, next to "Help"

    google docs geocoding example step 3
  6. Select three columns, and then click on "Latitude, Longitude to Address"

    google docs geocoding example step 4

    • 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"

    google docs geocoding example enter key
  7. The results will appear in the third column you selected.

    google docs geocoding example see results

Forward geocoding is very similar:

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

    google docs forward geocoding example step 1
  3. Select the three columns and then click "Address to Latitude, Longitude" in the "Geocode" menu.

    google docs forward geocoding example step 2

    • 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.

    google docs forward geocoding example step 4

Start your free trial

2,500 geocoding API requests per day.

No credit card required.