Learn to Perform IP Address Lookup with Google Sheets

Hey, friends today you will learn How to Perform IP Address Lookup with Google Sheets. Learn How to determine the country, city and ISP name of IP addresses in bulk with Google Sheets and IP2Location service.

Using the visitor’s IP address, websites may detect their geographic location and deliver more relevant content. A weather website, for example, may utilise your IP address to establish your approximate location and immediately present a weather forecast for your current city. A currency exchange website can decide your default currency depending on your IP address’s detection of your country.

With a simple HTTP request, there are free web IP search sites, such as ip2c.org, that will reveal the nation of your client’s IP address. At Digital Inspiration, we use that service to determine the payment service provider on the checkout page.

Bulk IP Lookup with Google Sheets

IP2Location is another viable option for obtaining more detailed geolocation information for any IP address. The IP location lookup service can return the client’s nation, city name, region, ISP name, and other information.

If you have a large number of IP addresses, you can use Google Sheets to estimate the appropriate geographic data for each of them in a few simple steps:

• Make a copy of the Google Sheet for bulk IP lookups by clicking here.
• Copy and paste the IP address list into column A, one per row. The lookup service is compatible with both IPv4 and IPv6 addresses.
• Fill in the blanks with your key in cell E1. If you just have a few IP addresses, use demo as the key or obtain your own API key from ip2location.com.
• Click the Run button to run the script, then watch as the geographic information and ISP names populate the sheet.

How IP2Location Script Works

Internally, the Google Sheet employs the IP2location web service in conjunction with Google Apps Script to convert IP addresses into geographic regions.

For enhanced performance, it employs the UrlFetchApp service to conduct numerous HTTP requests in a single batch. Here’s the complete source code:

const ip2location = () => {
  // Get all the input data from Google Sheet
  const ss = SpreadsheetApp.getActiveSheet();
  const data = ss.getDataRange().getDisplayValues();

  // Use your own API key or use demo key
  const apiKey = data[0][4] || "demo";

  // Generate API URL for IP address
  const getUri_ = (ipAddress) => {
    const API_URL = "https://api.ip2location.com/v2";
    return `${API_URL}/?ip=${ipAddress}&key=${apiKey}&package=ws4`;
  };

  const requests = [];

  for (let r = 2; r < data.length; r++) {
    const [ipAddress, countryName] = data[r];
    // Only process rows where the country is blank
    if (ipAddress && !countryName) {
      requests.push({ url: getUri_(ipAddress), rowNumber: r + 1 });
    }
  }

  // Make API calls in bulk using the UrlFetchApp service
  UrlFetchApp.fetchAll(requests).forEach((content, i) => {
    // Parse the JSON response
    const { city_name, country_name, isp, response } = JSON.parse(content);

    // If the response is populated, the API call failed
    if (response) throw new Error(response);

    // Write the response data to Google Sheet
    const values = [[country_name, region_name, city_name, isp]];
    ss.getRange(requests[i].rowNumber, 2, 1, 4).setValues(values);
  });

  // Flush all changes
  SpreadsheetApp.flush();
};