The Scenario
Your team runs a monthly audience segmentation report for a media client. This month's source data landed in a Google Sheet: 900 rows of raw IP addresses pulled from nginx access logs, sitting in column A with nothing else filled in.
The report needs country, region, and timezone columns before you can build the geographic breakdown. Without them, the segmentation chart is a blank placeholder.
The bad version:
- Export the IP list as a CSV, write a Python script against BigDataCloud's bulk geolocation endpoint, debug the rate-limit handling when the script stalls at row 312.
- Parse the JSON response manually, match each result back to the original row by index, paste the three output columns back into the sheet.
- Realize the script used a different field name for region than what the report template expects, go back through the output and rename it before the client call.
You were hired to analyze the audience data — not to maintain API scripts. The client presentation is this afternoon, and the segmentation chart is still empty.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that runs inside your Google Sheet, reads the data you're looking at, and calls BigDataCloud on your behalf. You describe the task in plain language — it handles the endpoint selection, the API calls, and the column writebacks.
Open SheetXAI from the Extensions menu and type:
For every IP in column A, look up the country, region, and timezone using BigDataCloud and fill columns B, C, and D
What You Get
- Column B: country name (e.g., "United States", "Germany", "Brazil")
- Column C: region or state name as returned by BigDataCloud's geolocation response
- Column D: IANA timezone identifier (e.g., "America/New_York", "Europe/Berlin")
- Rows where the IP returns no geolocation result get a blank cell; SheetXAI notes the count of unresolved rows in the sidebar
What If the Data Is Not Quite Ready
The IP column has mixed formatting — some entries have port numbers appended
For every IP in column A (some entries have port numbers like 192.168.1.1:8080), strip the port, geolocate the base IP using BigDataCloud, and write country, region, and timezone into columns B, C, and D
Some rows are internal or reserved IPs that BigDataCloud won't resolve
Geolocate every IP in column A using BigDataCloud — skip any rows where the IP is in a private or reserved range (10.x, 172.16-31.x, 192.168.x) and mark those rows "internal" in column B
The source data spans two tabs — Sheet1 has one log file, Sheet2 has another
Geolocate the IPs in column A on Sheet1 and Sheet2 using BigDataCloud, combine the results, deduplicate by IP address, and write a consolidated country/region/timezone table to a new sheet called "Geo Results"
The report needs a full enrichment pass plus a geographic summary in one shot
For every unique IP in column A, fetch country, region, and timezone from BigDataCloud into columns B through D — then on a new sheet called "Summary" write a count of rows per country, sorted descending, and flag any country that accounts for more than 20% of total sessions
Ask for the cleanup, the enrichment, and the analysis in a single prompt — SheetXAI resolves all three steps without you having to chain them manually.
Try It
Get the 7-day free trial of SheetXAI and open any Google Sheet with a column of raw IPs — paste the prompt above into the sidebar and watch the country, region, and timezone columns fill in. Then check out the hub overview for more BigDataCloud tasks, or see the spoke on IP threat scanning if fraud flags are next on your list.