Problem

We wanted to run a regional test campaign on form fill leads from our website. But—classic marketing blocker—we had no location data, just a column full of IP addresses. Without knowing which states or countries our leads were from, targeting was guesswork.

Solution

I wrote a Google Apps Script that pulled geolocation data (state, country, and more) using the IPGeolocation.io API. This let us enrich our form submission data directly in Google Sheets.

Implementation

  • Tool: Google Apps Script

  • API Used: IPGeolocation.io

  • Input: IP addresses in column C

  • Output: City, District, State, Country, Zip, Latitude, Longitude in columns D to K

    function updateIpGeolocations() {
    const apiKey = ‘API_KEY’;
    const ss = SpreadsheetApp.getActive();
    const sheet = ss.getSheetByName(‘Sheet1’);

    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;
    const ips = sheet.getRange(2, 3, lastRow – 1, 1).getValues();
    const out = []; ips.forEach(function(row) { const ip = row[0];
    if (!ip) { out.push([”, ”, ”, ”, ”, ”, ”, ”]);
    return; }
    const url = `https://api.ipgeolocation.io/v2/ipgeo?apiKey=${apiKey}&ip=${encodeURIComponent(ip)}`; try { const resp = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
    if (resp.getResponseCode() === 200) { const data = JSON.parse(resp.getContentText());
    const loc = data.location || {}; const city = loc.city || ”; const district = loc.district || ”; const state = loc.state_prov || ”;
    const country = loc.country_name || ”; const zip = loc.zipcode || ”; const lat = loc.latitude || ”;
    const lon = loc.longitude || ”;
    const stateCountry = [state, country].filter(Boolean).join(‘, ‘); out.push([city, district, state, country, zip, lat, lon, stateCountry]);
    }
    else
    { out.push([`HTTP ${resp.getResponseCode()}`, ”, ”, ”, ”, ”, ”, ”]); } } catch (e) { out.push([`Error: ${e.message}`, ”, ”, ”, ”, ”, ”, ”]); } Utilities.sleep(1000); });
    sheet.getRange(2, 4, out.length, 8).setValues(out);
    const headerRange = sheet.getRange(1, 4, 1, 8);
    const headers = headerRange.getValues()[0];
    const desired = [ ‘City’, ‘District’, ‘State’, ‘Country’, ‘Zipcode’, ‘Latitude’, ‘Longitude’, ‘State, Country’ ];
    if (headers.some(h => !h))
    {
    headerRange.setValues([desired]);
    }
    }

Results

  • Parsed 600+ IPs into clean geographic data.

  • Created a ready-to-filter list of leads by state and country.

  • Ran geo-targeted ad experiments without guessing locations.

  • Bonus: The sheet auto-updates with each new form entry.

View other case studies