Convert Latitude-Longitude Data to Addresses in Google Sheets

When pulling location data, you may come across datasets returned as GPS coordinates (latitude and longitude). This is great for machines, but less useful for humans looking to understand where something is located in the real world. In this walk through, I will show you how to convert lat-long data into a human-readable address, using Google Sheets and an API call to a free service.

To get started, you should have:

• The Pro version of API Connector for Google Sheets
• A Google Sheet with 2 columns of latitude and longitude data (you can follow this tutorial here if you'd like to follow along with the same dataset).

Contents

• Step 1: Sign Up For Positionstack And Get API Token
• Step 2: Prepare Lat and Long Data
• Step 3: Get Request URL For API Connector
• Step 4: Pull Data Into Google Sheets

Step 1: Sign Up For Positionstack And Get API Token

The first step to this will be getting access to an API that can perform a function known as "reverse geocode", i.e. take latitude and longitude coordinates and convert them to an address. For this walk through, we will use positionstack as they allow a fairly large amount of requests (25,000) per day. Let's go ahead and sign up for a free account to get access to their service.

After signing up you should be redirected to a quickstart guide. Take note of your API token in step 1 as you will need this to make requests later.

Step 2: Prepare Lat and Long Data

Because we are limited to only one range per API request, we need to combine our lat and long coordinates into one cell to later feed to API Connector.

1. Start by inserting a new blank column next to the lat and long columns in your sheet. After, copy and paste this formula into the top row in the new column: =CONCATENATE(ROUND(B2,4), ",", ROUND(C2,4))

2. Double click the small box in the bottom right corner to apply formula to all cells. You can name the new column "lat-long" or whatever you want.

Step 3: Get Request URL For API Connector

Let's build up our request URL now. From the quick start guide, scroll down to step 2 to see an example of how to make your request.

In order to plug in our list of coordinates from Google Sheets, we will need to modify the endpoint a bit for use inside API-Connector. I will be using the data we gathered in this earlier tutorial, but you can use your own data set.

• Base URL: http://api.positionstack.com/v1
• Reverse Geocode Endpoint: /reverse?access_key=YOUR_ACCESS_KEY&query=+++Crypto-Venues!D2:D124+++&limit=1

Note, you will need to change "D2:D124" based on how long the list of your data is you want to process. Don't include the column name in this request! Always select at least row 2!

Step 4: Pull Data Into Google Sheets

You should create a new blank sheet to output your addresses to.

After that, go ahead and fill in the "API URL path" with the URL we made in Step 3, "Destination sheet" with the new sheet we just created, and "Name" with whatever you fancy.

Ta-da! After a few minutes, you should have a list of place names with their respective addresses.

Following the coinmap tutorial, you can verify these are correct by comparing the venue name output from the original data retrieved from the coinmap API.

You may be able to increase the accuracy of the place name by rounding to a greater number of decimal places (or even not rounding at all!)