📋 How to Perform IP Address Lookup with Google Sheets

1. Overview

\[ \begin{array}{l} \textbf{Google Sheets can be used to perform IP address lookups --} \\ \text{by integrating with a public IP geolocation API using Google Apps Script.} \\ \textbf{This allows you to automatically fetch location details --} \\ \text{for any list of IP addresses stored in your spreadsheet.} \end{array} \]

2. Core Steps

\[ \begin{array}{ll} \textbf{Step 1:} & \text{Create or open a Google Sheet with a column of IP addresses.} \\ \textbf{Step 2:} & \text{Sign up for a free/public IP lookup API service (e.g., ip-api.com).} \\ \textbf{Step 3:} & \text{Open the Script Editor in Google Sheets (Extensions $\rightarrow$ Apps Script).} \\ \textbf{Step 4:} & \text{Write a script to fetch IP details from the API and populate the sheet.} \\ \textbf{Step 5:} & \text{Run the script, grant permissions, and review the fetched location data.} \end{array} \]

3. Sample Google Apps Script

// Example: IP lookup using ip-api.com

function lookupIPAddresses() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1); // IPs in Column A starting row 2
  var ips = range.getValues();

  for (var i = 0; i < ips.length; i++) {
    var ip = ips[i][0];
    if (ip) {
      var url = "http://ip-api.com/json/" + ip;
      var response = UrlFetchApp.fetch(url);
      var data = JSON.parse(response.getContentText());

      if (data.status === "success") {
        sheet.getRange(i + 2, 2).setValue(data.country);
        sheet.getRange(i + 2, 3).setValue(data.regionName);
        sheet.getRange(i + 2, 4).setValue(data.city);
        sheet.getRange(i + 2, 5).setValue(data.isp);
      } else {
        sheet.getRange(i + 2, 2).setValue("Lookup failed");
      }
    }
  }
}

4. Important Notes

\[ \begin{array}{l} \text{• Ensure IP addresses are valid and properly formatted.} \\ \text{• Check the API's rate limits to avoid blocking requests.} \\ \text{• Apps Script needs permission to access external services (UrlFetchApp).} \\ \text{• Store API keys securely if using a service that requires authentication.} \end{array} \]

5. Conceptual Flow (in LaTeX)

The process can be described as:

\[ \text{Google Sheet IP List} \xrightarrow{\text{Apps Script + API}} \text{Location Details in Sheet} \]

Where: \[ \text{Details} = \{ \text{Country}, \text{Region}, \text{City}, \text{ISP} \} \]