Print

Import Positionstack Data to Google Sheets

In this guide, we’ll walk through how to pull data from the Positionstack API directly into Google Sheets, using the API Connector add-on for Sheets.

We'll first get an API key from Positionstack, and then set up a request to pull in data to your spreadsheet.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Get Your Positionstack API Key

Positionstack API offers forward and reverse geocoding services. Getting a location name from coordinates or IP address presents a reverse geocoding, while the operation of turning a location's name or address into coordinates is called forward geocoding.

  1. In order to use Positionstack API, you will need to register and get your personal API key.
  2. Go to this page and fill it with the necessary information and click on sing up.
  3. After that, you will be redirected to the page with your personal API key that you will need for using Positionstack API, as depicted in the picture below. Copy and save it!

Part 2: Pull Positionstack API Data into Google Sheets

For this example request we'll show how to use forward and reverse geocoding API services. Substitute in your own API key where it reads YOUR-API-KEY.

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • ApplicationCustom
    • MethodGET
    • Request URLhttp://api.positionstack.com/v1/forward?access_key=YOUR-API-KEY&query="Eiffel Tower"&country=FR
  3. Create a new tab and click Set current to use that tab as your data destination.
  4. Name your request and click Run.
  5. Now all the places in France that have Eiffel Tower in their name will appear in the sheets with their addresses and coordinates. This is called forward geocoding.
    positionstack_api_5
  6. To run a request with reverse geocoding, try the following URL: http://api.positionstack.com/v1/forward?access_key=YOUR-API-KEY&query=46.006,14.5428

Part 3: Run Requests Based off Cells

If you have a bunch of data addresses or coordinates and you want to perform geocoding for all of them, you can do that by creating a request based on cell. For example, if you want to pull addresses for the set of coordinates we got from the first example, we will need to create a new column and concatenate those coordinates into coordinate pairs, with the function:

=CONCATENATE(A2, ",",B2)

After that make your request in the following way (please note: you should specify your sheet name and cell positions of your data):

https://api.positionstack.com/v1/forward?access_key=YOUR-API-KEY&query=+++sheet_name!C2:C11+++

Set the destination sheet different than this one and run it! You will now be able to see the pulled data. Please note: with a free plan you can run up to 25.000 requests (more information available here).

Part 4: API Documentation

Official API documentation: https://positionstack.com/documentation

Leave a Comment

Jump To