Build an IP geolocation enrichment function

Northwind exports a raw visitor log every week — timestamps, paths and a column of IP addresses. The IPs are the most useful column and the least readable one: nobody can tell 203.0.113.7 apart from 198.51.100.4 at a glance, so the “where are our visitors” question never gets a quick answer.

This is a custom spreadsheet function. Drop =GEO_IP(A2) next to the IP column and it returns the country and city for that address, looked up from a free geolocation API. Identical IPs are cached for six hours, so a log with the same visitor appearing fifty times only ever costs one API call.

What you’ll need

• A Google Sheet with IP addresses in a column — the visitor log export.
• Nothing else to install. GEO_IP is a custom function, so it lives in the bound script and is called like any built-in formula.
• The free ipapi.co endpoint, which needs no API key for light use. Heavier volumes need a paid plan and a key.

The script

// Geolocation endpoint. {ip} is swapped for the address at call time.
const GEO_API_URL = 'https://ipapi.co/{ip}/json/';

// How long a lookup stays in the cache, in seconds (6 hours).
const GEO_CACHE_TTL = 6 * 60 * 60;

// Cache keys are namespaced so they cannot collide with other cached data.
const GEO_CACHE_PREFIX = 'ip:';

/**
 * Looks up the country and city for an IP address.
 * Use it in a cell as =GEO_IP(A2).
 *
 * @param {string} ip  The IP address to locate.
 * @returns {string[]} A two-cell row: [country, city].
 * @customfunction
 */
function GEO_IP(ip) {
  // Blank cell — return an empty string so the formula does not error.
  if (!ip) return '';

  const cache = CacheService.getScriptCache();
  const cacheKey = GEO_CACHE_PREFIX + ip;

  // 1. Serve a cached result if we have one. Repeated IPs cost nothing.
  const hit = cache.get(cacheKey);
  if (hit) return JSON.parse(hit);

  // 2. Cache miss — call the geolocation API for this IP.
  const url = GEO_API_URL.replace('{ip}', encodeURIComponent(ip));
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  const data = JSON.parse(res.getContentText());

  // 3. The API returns an "error" field for bad or reserved addresses.
  if (data.error) return [['Unknown', '']];

  // 4. Build the result row and cache it before returning.
  const out = [[data.country_name || '', data.city || '']];
  cache.put(cacheKey, JSON.stringify(out), GEO_CACHE_TTL);
  return out;
}

How it works

1. GEO_IP is tagged @customfunction, so it appears in the spreadsheet’s formula autocomplete and runs once per cell it is placed in.
2. A blank cell returns an empty string immediately — important when the formula is filled down past the last row of data.
3. It checks the script cache first. The cache key is the IP with an ip: prefix, so the same address looked up twice in the same six hours is served instantly with no API call.
4. On a cache miss it calls ipapi.co, substituting the IP into the URL. The API returns a JSON object with country_name, city and other fields.
5. Reserved or malformed addresses come back with an error field set — the function returns Unknown rather than crashing the cell.
6. A successful lookup is returned as a two-element row ([[country, city]]) so the result spills into two columns, and stored in the cache for next time.

Example run

With IP addresses in column A, put =GEO_IP(A2) in B2 and fill it down:

A (IP)	B (Country)	C (City)
203.0.113.7	United Kingdom	London
198.51.100.4	Germany	Berlin
203.0.113.7	United Kingdom	London
192.168.1.1	Unknown

Row 4 repeats the IP from row 2, so it is served from the cache — no second API call. Row 5’s private address has no public location, so it returns Unknown.

Use it

GEO_IP is a spreadsheet formula. Type it straight into a cell:

=GEO_IP(A2)

It returns two cells — country and city — so leave the column to its right empty for the result to spill into. To enrich a whole log at once, put the formula in B2 and double-click the fill handle to copy it down the column.

Watch out for

• ipapi.co’s free tier is rate-limited (around 1,000 lookups a day). A large log filled all at once can hit the limit and start returning errors — the six-hour cache softens this, but a paid key is the real fix for high volume.
• City-level accuracy is approximate. Geolocation maps an IP to the registered network, which can be a data centre or an ISP hub a long way from the actual visitor. Treat country as reliable and city as a hint.
• VPN and mobile-carrier IPs resolve to the provider’s location, not the user’s — a London visitor on a VPN may show up in Amsterdam.
• Custom functions cannot use services that need user authorisation, and each call has a 30-second limit. GEO_IP only fetches a URL, so it is fine, but do not bolt on anything that needs broader permissions.
• The cache is best-effort. Apps Script can evict entries early under memory pressure, so the six-hour TTL is a ceiling, not a guarantee.
• Recalculating the sheet re-runs every GEO_IP cell. Once a log is enriched, paste the results as static values so a stray edit does not trigger a fresh wave of API calls.