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
- Sign up for an OpenCage Geocoding API key, or if you already have one log in.
- 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
- Open Extensions > Apps Script
- Copy the script opencage.js into the scripts editor and save, name the project whatever you like, the default is "Untitled project"
- Reload your Google Sheet. After a few seconds a new "Geocode" button should appear in the menu, next to "Help"
-
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"
- The results will appear in the third column you selected.
Forward geocoding is very similar:
- Follow steps 1-5 above
- Create three columns, the first column should contain the address
-
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"
- The results will appear in the second and third column you selected.