API Integration Tutorials

Enrich Network Telemetry in Google Sheets [API Integration]

  1. By Apipheny Staff

IPWho is a handy tool for enriching network telemetry like IP addresses to enable deeper analysis of where the traffic is coming from and what it might be doing.

There’s a few ways you can get network data from your server or log feeds into Google Sheets, we’ll assume you’re at that step already. If you’d like a hand collecting this data reach out and we’ll see what we can do.

In this tutorial, you will learn how to connect the IPWho IP Geolocation API to Google Sheets using Apipheny in 5 steps:

    1. Install the Apipheny Add-on

    1. Obtain an API Key

    1. Choose an endpoint

    1. Enter your API request into Apipheny

    1. Run the API request in your Google Sheet

Step 2.) Obtain an API Key

After providing an email you should see the API token sent through to you shortly afterwads

IPWho Geolocation

Copy your token to a safe location, because you’ll need it in the next steps.

Step 3.) Choose an endpoint

In this section, we’ll show you how to browse the IPWho API documentation to find the API URL which retrieves the information you need. If you already know your URL, or if you want to use the same example URL as us, just skip to Step 4.

The left side of the documentation page lists different categories, each with its own endpoints.

An example endpoint is Bulk Lookup

GET https://api.ipwho.org/bulk/{ip1,ip2,ip3,...}?apiKey=sk.xxx

Which gives you the latest geolocation, TOR, VPN and assessed threat level of a given set of IP addresses.

Step 4.) Enter your API Request into Apipheny

Now go back to your Google Sheet and make sure that the Apipheny add-on is open on the Import tab. With the Import tab open, enter these details into the add-on:

Method: At the top of the Apipheny sidebar, select the HTTP method (GET or POST) required by your API endpoint. For this example, we are using the GET method.

API URL: In Step 3, we explained how you can find the endpoint that you need. Now copy and paste your complete URL into the Apipheny add-on, where it says API URL Path, followed by any parameters required for your query (if applicable).

For this example, we are using the Bulk Lookup endpoint. This is what the whole URL looks like:

https://api.ipwho.org/bulk/76.102.11.203,110.44.117.244?apiKey=sk.xxx

If you use this URL, don’t forget to replace 76.102….117.244 with the IP Addresses you want to get data for, and replace [sk.xxx] with your actual API key

Headers are not required for this API request, you can leave that section blank in Apipheny.

Step 5.) Run the API request

The last step is to click the Run button at the bottom of the Apipheny add-on and then wait for the data to be imported into your Google Sheet.

Here’s what our request looked like when completed:

A screenshot of a spreadsheet showing IP address data that has been enriched with location and threat data. To the right of the screen is the Apipheny extension that has generated the enrichment in the sheet.

That’s it! You’ve successfully connected IPWho enrichment data to Google Sheets using their API and the Apipheny add-on.

After making a successful request, try querying a different endpoint, or try using one of the more advanced features in the Apipheny add-on.

    • Save and schedule your API request

    • Use the custom =APIPHENY() function to call the API request inside your spreadsheet

    • Create an API request by referencing the value of a cell in your API URL with three curly braces eg. {{{Sheet1!A1}}}

Prefer Video? Checkout the Step by Step guide too

TAGS :