Bulk IP Lookup in Google Sheets with IP2Location API

Introduction

In today’s data‑driven world, understanding where an IP address originates can reveal valuable insights for marketers, security analysts, and developers alike. Whether you need to segment audiences by country, detect suspicious traffic, or enrich a customer database, performing an IP address lookup in bulk is far more efficient when it’s integrated directly into a familiar tool such as Google Sheets. This article walks you through the complete workflow: from obtaining an IP2Location API key, to preparing a spreadsheet for thousands of lookups, to writing a lightweight Apps Script that calls the service and returns the country, city, and ISP name for each address. By the end, you’ll have a reusable, automated solution that turns raw IP lists into actionable geographic data without leaving your browser.

Setting Up IP2Location API Access

The first step is to create an account on IP2Location and subscribe to a plan that includes the IP Geolocation API. Once registered:

• Navigate to the API Dashboard and generate a new API key. Keep this key secret – it authenticates every request you’ll send from Google Sheets.
• Review the API documentation to understand the endpoint format, e.g.: https://api.ip2location.com/v2/?ip={IP}&key={YOUR_KEY}&package=WS25. The “WS25” package returns country, region, city, latitude, longitude, ISP, and more.
• Test the endpoint in a browser or with curl to confirm that the key works and the response is returned in JSON.

Having a working key and a clear endpoint URL is essential before moving on to the spreadsheet.

Preparing the Google Sheet for Bulk Lookups

Next, organize your data so the lookup process runs smoothly:

• Create a column titled IP Address and paste all the addresses you want to examine. Google Sheets can handle thousands of rows, but keep an eye on the API’s rate limits.
• Add three empty columns next to it: Country, City, and ISP. These will be populated automatically.
• Insert a separate cell (for example, B1) to store your API key. Referencing the key from a single cell makes future updates painless and keeps the script clean.

With the sheet structured, you’re ready to write the custom function that will call IP2Location for each row.

Creating a Custom Lookup Function with Apps Script

Google Sheets’ built‑in functions cannot call external APIs directly, but Apps Script can. Follow these steps:

• Open Extensions → Apps Script from the spreadsheet menu.
• Paste the following script, adjusting the range references to match your sheet layout:

function IP2LOOKUP(ip) {

  var apiKey = SpreadsheetApp.getActiveSpreadsheet().getRange(‘B1’).getValue();

  var url = ‘https://api.ip2location.com/v2/?ip=’ + encodeURIComponent(ip) + ‘&key=’ + apiKey + ‘&package=WS25&format=json’;

  var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});

  if (response.getResponseCode() !== 200) { return [‘Error’,’Error’,’Error’]; }

  var data = JSON.parse(response.getContentText());

  return [data.country_name || ‘N/A’, data.city_name || ‘N/A’, data.isp || ‘N/A’];

}

Save the script and grant the required permissions. Now, back in the sheet, use an array formula to populate the three result columns in one step:

=ARRAYFORMULA(IF(A2:A=””,,IP2LOOKUP(A2:A)))

This single formula calls the custom function for every non‑empty IP address, returning a three‑column array that fills Country, City, and ISP automatically. Remember to respect the API’s daily quota; if you have a very large list, consider splitting it into batches or adding a Utilities.sleep(200) pause inside the script.

Analyzing and Visualizing the Results

Once the lookup data appears, you can turn it into actionable insights directly inside Google Sheets:

• Use Pivot Tables to count how many IPs belong to each country or ISP, revealing geographic concentration.
• Apply Conditional Formatting to highlight rows from high‑risk regions or unknown ISPs.
• Combine the location data with other columns (e.g., conversion metrics) to calculate country‑specific ROI.
• If you need a map view, publish the sheet and connect it to Google Data Studio or use the built‑in Geo chart to plot cities on a world map.

Because the lookup function is stored in the spreadsheet, any new IP added later will be resolved instantly, keeping your analysis up‑to‑date without additional manual steps.

Conclusion

By leveraging the IP2Location API together with Google Sheets and a short Apps Script, you can transform a raw list of IP addresses into a rich dataset that includes country, city, and ISP information—all within a familiar spreadsheet environment. The process starts with securing an API key, continues with structuring the sheet for bulk processing, proceeds through building a reusable custom function, and finishes with visual analysis that turns raw data into strategic insights. This workflow not only saves time compared to manual lookups, but also scales effortlessly as your data grows, empowering marketers, security teams, and developers to make data‑driven decisions with confidence.