The Scenario
A web analytics engineer at a media company exports server logs every night. This week the marketing director sent a message: "Can you break down sessions by region for the Q2 regional campaign report?" The engineer opens the nightly export — 2,000 rows of raw IP addresses in column A, no location data. They have seen this request before. Last quarter it took most of a Friday to process.
The bad version:
- Write a script to loop through 2,000 IP addresses, call a geolocation API for each one, and handle the rate limiting that kicks in around row 300.
- Parse the JSON responses to extract country, city, latitude, and longitude — four separate fields from a nested object — and write them into four separate columns without misaligning any rows.
- Discover that roughly 200 rows are private IP ranges that return no geolocation data, go back and add error handling, re-run those rows, and then re-export the file.
The regional breakdown report is due to marketing by end of week. The engineer still has three other tickets open.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that runs inside your Google Sheet. It reads the IP addresses, calls Geoapify's IP geolocation API for each row, and writes the location fields back into the correct columns.
For each IP address in column A of this sheet, use Geoapify IP geolocation to fetch the country, city, and coordinates, and write them into columns B, C, D, and E
What You Get
- Column B receives the country name for each IP address.
- Column C receives the city name.
- Column D receives the latitude.
- Column E receives the longitude.
- Private IP ranges, reserved addresses, and any IPs that returned no match are flagged in column F rather than left as blanks, so the engineer can account for them in the session count.
What If the Data Is Not Quite Ready
IP addresses are mixed in with other strings in column A and need to be filtered first
Filter column A to keep only valid public IP address strings, then run Geoapify IP geolocation on each valid IP and write country, city, latitude, and longitude into columns B, C, D, and E. Log any non-IP values in column F.
The report needs country code rather than country name for joining to another dataset
Use Geoapify IP geolocation for each IP in column A — write the ISO 3166-1 alpha-2 country code into column B, city name into column C, and coordinates into columns D and E
The sheet has duplicate IPs that should only be looked up once and then joined back
Deduplicate the IP addresses in column A — run Geoapify IP geolocation only on unique IPs, then join the country, city, and coordinate results back to all rows that share the same IP, writing them into columns B through E
Geolocate IPs, flag private ranges, and build a country-level session summary in a second sheet
Run Geoapify IP geolocation on all IPs in column A — write country, city, latitude, and longitude into columns B through E. Mark private or unresolvable IPs in column F. Then create a Country Summary sheet that counts sessions per country and calculates the percentage of total sessions each country represents.
The cleanup, enrichment, exception handling, and summary generation all happen in one instruction.
Try It
Get the 7-day free trial of SheetXAI and open any log export or analytics dump with IP addresses and ask it to append the location fields. See the related spoke on appending administrative boundaries to coordinates, or return to the Geoapify hub.