The Scenario
You're a DevOps engineer. The access log from last night has 50,000 rows. Your job before the weekly security sync is to pre-filter it: add a country code and ASN column to each row so the security team can slice by country and identify bot traffic from known hosting ASNs. Full geolocation — city, region, coordinates — is more than you need and would slow things down. You need the lightweight version.
The log landed in Excel last night and someone converted it to a Google Sheet this morning. You have until 2 PM.
The bad version:
- Export the 50,000 rows to CSV.
- Write a Python script hitting the IPinfo lite batch endpoint: 50 batches of 1,000 IPs, handle rate limits, parse the response, write country code and ASN to a second CSV.
- Re-import the enriched CSV, join it with the original log on the IP column, verify row alignment.
For 50,000 rows, that script takes 20 minutes to write and another 10 to run, assuming you don't hit a rate limit error that restarts the batch.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It batches the IPinfo lite lookup automatically, handles pagination across all 50,000 rows, and writes country code and ASN to your columns without an intermediate export.
Open the SheetXAI sidebar and paste:
For all IPs in column A, use IPinfo lite batch lookup (batches of 1,000) to retrieve the country code and ASN name and write them to columns B and C, processing rows 1 through 50000.
What You Get
- Column B: two-letter country code for each IP (US, DE, CN, etc.)
- Column C: ASN organization name (e.g., "AS16509 Amazon.com, Inc.")
- All 50,000 rows processed in sequential batches — no rate limit errors surfaced to you
- Rows where lite lookup returns no data get empty cells in B and C, not errors — easy to filter out
What If the Data Is Not Quite Ready
The IP is not in column A — it's embedded in a log line
Column A contains raw log lines in the format "2024-04-01 08:32:11 GET /api/v2/data 200 203.0.113.44". Extract the IP address from each row (last space-separated token), run IPinfo lite batch lookup to get country code and ASN, and write to columns B and C.
I want to pre-filter to only rows from certain high-risk countries before the security review
Run IPinfo lite batch lookup on all IPs in column A, write country code to column B and ASN name to column C. Then flag any row where the country code is in this list — CN, RU, KP, IR, BY — as "High Risk" in column D, and leave column D blank for all other rows.
I want the ASN number as well as the name for firewall rule generation
Run IPinfo lite batch lookup for all IPs in column A. Write the country code to column B, the ASN number (e.g., AS16509) to column C, and the ASN organization name to column D. Where lite lookup returns no data, write "Unknown" in columns B and C.
Full pre-filter pipeline for the security sync
Column A has 50,000 raw log IPs. Run IPinfo lite batch lookup in batches of 1,000. Write country code to column B and ASN name to column C. Flag column D: "High Risk" for CN/RU/KP/IR/BY, "Hosting ASN" if the ASN name contains "Amazon," "Google," "Microsoft," "DigitalOcean," or "Linode," "Clean" otherwise. Write a summary tab called "Pre-Filter Summary" with counts per flag category.
Pre-filter complete before the meeting starts.
Try It
Get the 7-day free trial of SheetXAI and open any Google Sheet with a large access log or IP list, then ask it to run IPinfo lite batch enrichment and flag the high-risk rows before your security review. See also full geolocation enrichment with city and region or reverse-DNS hostname resolution. Hub: IPinfo + Google Sheets.