Convert Addresses to Latitude & Longitude with Google Sheets

Are you looking for an easy way to convert addresses to coordinates without the hassle of dealing with APIs or coding? Well, I have some exciting news for you! In this blog post, I’ll guide you through the process of converting addresses to latitude and longitude using Google Sheets and the LocationIQ plugin. The best part? You don’t need any coding knowledge or API integration. It’s a straightforward, no-code solution that anyone can follow. So, let’s jump right in and discover how you can effortlessly perform these conversions using the familiar tool of Google Sheets. Let’s get started on this exciting journey!

Step 1: Create a Copy to get ‘Edit’ access

The first thing we need to do is ensure that you have the necessary permissions to edit the Google Sheet. Here’s what you need to do:

  1. Open the Google Sheet available here
  2. Go to the File menu at the top left corner of the screen.
  3. From the dropdown menu, select Make a copy. Remember, downloading won’t work because we need the functions to operate properly.
  4. A pop-up box will appear, informing you that the Apps Script file will be copied. This is essential for the sheet to function correctly.
  5. Congratulations! A copy of the template will now be created in your Google Drive.
  6. Now you can freely edit the sheet and input coordinates or addresses into your own copy.

Step 2: Activate the LocationIQ Plugin

Next, we need to activate the LocationIQ plugin within the Google Sheet. Follow these steps:

  1. Make sure you have ‘Edit’ access to the sheet, as mentioned in Step 1.
  2. Look for the LocationIQ tab at the top menu. It might take a few seconds to appear, but it should be there after the “Help” option.
  3. Select Settings from the drop-down menu.
  4. Google Sheets will ask for authorization through a pop-up. Click Continue to proceed.
  5. Sign in with your Google account and grant access. If you see a warning stating that “Google hasn’t verified this app,” click Advanced and then “Go to LocationIQ (unsafe).”
  6. Sign up for an account with LocationIQ at www.locationiq.com and obtain your Access Token. A quick guide on this is available here
  7. On the sheet, select Settings from the drop-down menu once again.
  8. Input your access token beside the API Key: box.

That’s it! The document is copied, the permissions are set — you’re all set to geocode and reverse geocode!

Forward Geocoding

Forward geocoding is the process of converting a human-readable address into geographic coordinates (latitude and longitude). It’s the opposite of reverse geocoding. Instead of starting with coordinates and finding the address, forward geocoding allows you to input an address and retrieve the corresponding latitude and longitude values.

In our Google Sheet, we can also perform forward geocoding using the LocationIQ plugin. This means that you can input an address, such as a street name, city, and state, and the forward geocoding functionality will automatically convert it into geographic coordinates. Here are steps to convert addresses to coordinates within the Google Sheet:

  1. Switch to the sheet named Forward Geocoding
  2. Paste the entire address in Column A
  3. Recommend address format is (house_number), (street), (neighbourhood), (city), (state), (country), (postcode)
  4. Select the LocationIQ tab from the Drop-down and choose Geocode

Well done on mastering the art of converting addresses to coordinates with the help of Google Sheets and the LocationIQ plugin. This incredible tool is a time and effort saver when dealing with location data. With just a few clicks, you can effortlessly convert addresses to coordinates and vice versa. I trust that this tutorial has proven valuable to you, and I encourage you to explore the additional features provided by LocationIQ to elevate your location-based projects. Enjoy mapping and happy exploring!