Search API Connector Documentation

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.

In order to use Positionstack API, you will need to register and get your personal API key. Go to this page and fill it with the necessary information and click on sing up. 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. Therefore copy and save it!

PART 2: CREATE YOUR API REQUEST URL

Following the official documentation of Positionstack API, we will create our requests.

  • API root: http://api.positionstack.com/v1/
  • Endpoint: forward

For forward geocoding, the API link should be constructed like this:

https://api.positionstack.com/v1/forward?access_key=YOUR_API_KEY&query="location's name or address"

For reverse geocoding, the API link should be designed like this:

https://api.positionstack.com/v1/reverse?access_key=YOUR_API_KEY&query=latitude,longitude

PART 3: PULL POSITIONSTACK API DATA INTO GOOGLE SHEETS

In this section, we will show you how to use forward and reverse geocoding API services.

Forward geocoding

We will pull the places’ coordinates in France that have in their name words Eiffel Tower.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create tab, enter the API URL constructed like this:
http://api.positionstack.com/v1/forward?access_key=YOUR-API-KEY&query="Eiffel Tower"&country=FR

3. Now create a new tab, give it a name and click Set current to use that tab as your data destination.

4. Give a name to 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!

Reverse geocoding

To introduce to you reverse geocoding, we will query the following coordinate pair: (46.006, 14.5428)

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create tab the API URL constructed like this:
http://api.positionstack.com/v1/forward?access_key=YOUR-API-KEY&query=46.006,14.5428

3. Set the destination sheets, name you request as described above and run it!

PART 4: REQUESTS BASES OF 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 5: MORE EXAMPLE API URLS

  • Limit response to one record
GET http://api.positionstack.com/v1/forward?access_key=YOUR-API-KEY&query=46.006,14.5428&limit=1
  • Specify a country and region
GET http://api.positionstack.com/v1/forward?access_key=YOUR-API-KEY&query="Eiffel Tower"&country=FR&region="Corse"
  • Specify a language:
GET http://api.positionstack.com/v1/forward?access_key=YOUR-API-KEY&query=46.006,14.5428&language='ES'
Previous Import Pipedrive Data to Google Sheets
Next Import Product Hunt Data to Google Sheets

Leave a Comment

Table of Contents