Today, I’ll teach you how to use the free ipbase.com API and the Apipheny API integrator to import IP geolocation data into Google Sheets.
ipbase.com is an IP geolocation API that allows you to geolocate a specific IP address. Furthermore, it returns more data about your IP, like timezone, currencies, and more.
To import data from ipbase.com into Google Sheets, follow these steps:
Apipheny is an API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to unlimited API data sources, make unlimited API requests, and more. There is a 30 day free trial included.
1.) Install Apipheny by opening the following link on desktop and then clicking the Install button in the Google Marketplace: https://apipheny.io/install
2.) After you’ve installed Apipheny, open a Google Sheet and then click on the Extensions dropdown in the menu at the top.
In the dropdown list you should see Apipheny. Click Apipheny then click Import API to open the Apipheny sidebar in your Google Sheet.
Tip: you can open a new Google Sheet by entering this URL in your browser:
After logging in to your ipbase.com account, go to Dashboard and copy your API key.
Copy and paste your API key in a safe location because you’ll need it in the next steps.
Imagine you have the following table, and you want to replace the question marks with the country of the IP.
To achieve this, we create and save a new Apipheny function:
apikeyparameter as a header (2) so it can be re-used when you overwrite the URL later. You can use
https://api.ipbase.com/v2/info?ip=22.214.171.124as a placeholder for now
After that, you go into your saved request and copy the request ID.
The second parameter of the APIPHENY function lets you overwrite the request URL. We will use this functionality to inject the IP of the corresponding row.
Now let’s use the saved function with an URL overwrite, and some other Google Sheets functions to build the following:
=INDEX(ARRAY_CONSTRAIN(TRANSPOSE(APIPHENY("req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"; "https://api.ipbase.com/v2/info?ip="&A1));100;2);MATCH("data.location.country.alpha2";ARRAY_CONSTRAIN(TRANSPOSE(APIPHENY("req-xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"; "https://api.ipbase.com/v2/info?ip="&A1));100;1);0);2)
This will fetch the data from ipbase.com and filter down to only the value of the field with the key
data.location.country.alpha2. You can use any other field specified in the documentation.
If we insert this function into B1 and pull it down, it will fill column B with the country codes of the IP addresses like so:
Schedule requests for automatic updates
Reference cell values in requests
Stack multiple URLs in a single request
Run all saved requests at once
Crypto API tutorials and more:
Sponsored by Zink Virtual Tip Jar & Rewording.io Paraphrasing Tool
Accept donations on Twitch | Accept donations on Discord | Accept donations on YouTube | Accept donations on Telegram | Accept donations on Twitter | Accept donations on TikTok | Accept donations on Instagram | Accept donations on LinkedIn | Accept donations on Facebook | Accept donations on your Website