Geotranslate Your IPs with Google Apps Script

IP-API.COM Has Free Geolocation Services That Improve EndPoint Data and Detection

This Google Apps Script tool is a simple but powerful way to enhance your endpoint security visibility right from within a Google Sheet.

By automating the process of checking public IP addresses against the ip-api.com geolocation service, it allows anyone—no scripting experience needed—to quickly enrich raw endpoint data with real-world geographic and ISP context. With a single click from a custom menu in your spreadsheet, the script fetches location details like city, region, country, and ISP for each IP address listed, helping you flag suspicious patterns or unexpected regions with ease.

This is especially useful when working with endpoint security logs, network traffic reports, or device inventory exports. Whether you’re reviewing systems that report in from offsite, analyzing device scan logs, or verifying compliance with geo-based security policies, this tool gives you instant enrichment without leaving your spreadsheet. It's a great way to add context to your existing security data and make smarter decisions—no extra software required.

/**
 * Adds a custom menu to the Google Sheet for IP address checking.
 * Runs automatically when the spreadsheet is opened.
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("IP Tools")
    .addItem("Check IP Addresses", "checkIPAddressesByIdAndTab")
    .addToUi();
}

/**
 * Main function to check IP geolocation and write results to column T.
 * Make sure to replace the spreadsheet ID and sheet/tab name.
 */
function checkIPAddressesByIdAndTab() {
  const spreadsheetId = "YOUR_SPREADSHEET_ID_HERE"; // <-- Replace with actual Sheet ID
  const sheetName = "YOUR_TAB_NAME_HERE";           // <-- Replace with actual Sheet/tab name

  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  const ipRange = sheet.getRange("S2:S" + sheet.getLastRow());
  const ipValues = ipRange.getValues();

  const results = [];

  for (let i = 0; i < ipValues.length; i++) {
    const ip = ipValues[i][0];
    if (ip) {
      try {
        const url = `http://ip-api.com/json/${ip}`;
        const response = UrlFetchApp.fetch(url);
        const data = JSON.parse(response.getContentText());

        if (data.status === "success") {
          const summary = `${data.city}, ${data.regionName}, ${data.country} (${data.isp})`;
          results.push([summary]);
        } else {
          results.push([`❌ ${data.message || "Failed"}`]);
        }
      } catch (e) {
        results.push([`⚠️ ${e.message}`]);
      }
    } else {
      results.push([""]);
    }
  }

  // Write the results to column T (column 20), starting at row 2
  sheet.getRange(2, 20, results.length, 1).setValues(results);
}

Remember to add the SHEET and DRIVE services, and after you get it working, move the ID and TAB name to a script property.