Turn a List of IPs Into Location Data in Google Sheets

You have a Google Sheets file full of IP addresses. Maybe they came from server access logs, a list of login attempts, ad-click exports, or a fraud review queue. You need to know where those IPs are, what network they belong to, and whether any of them are running through a VPN or proxy.

You could paste them into an online lookup tool one by one. Or you could turn your Google Sheet into a geo-enrichment pipeline that resolves hundreds of IPs with a formula.

This guide covers the setup (about 10 minutes of copy-paste), a formula reference you can bookmark, and four practical workflows for doing something useful with the enriched data.

TL;DR

• One Apps Script integration turns Google Sheets into a bulk IP enrichment tool. No add-ons, no code to write.
• Formulas like =IPGEO(A2, "location.country_name") fill in country, city, timezone, currency, ASN, VPN status, proxy detection, and threat scores.
• =IPGEO_BULK(A2:A500, "location.country_name") processes large lists in a single call instead of one formula per row.
• All you need is a free API key. Sign up, paste the key once, and the formulas work immediately.
• The full formula reference and source code live at the GitHub repository.

The short version: paste IPs in column A, use formulas in columns B onward, and the sheet fills itself with location and security data. The rest of this guide explains which formulas to use, when, and what to do with the output.

To geolocate IP addresses in Google Sheets, add the IPGeolocation Apps Script integration, store your API key once, and use custom formulas such as IPGEO for individual lookups or IPGEO_BULK for an IP range.

How to set it up

Several IP geolocation APIs work with Google Sheets through Apps Script. IPGeolocation, ipinfo, ip-api, and MaxMind GeoIP2 all return similar core data for country-level lookups. This guide uses IPGeolocation because it ships a ready-made Google Sheets integration with built-in formulas, bulk processing, and security signals, so you skip the Apps Script plumbing entirely.

The setup takes about 10 minutes:

1. Open your Google Sheet. Go to Extensions > Apps Script.
2. In the Apps Script editor, delete the starter code in Code.gs. Paste the full contents of Code.gs from the GitHub repository.
3. Save the project. Name it something like "IPGeolocation.io for Google Sheets."
4. In the function dropdown near the toolbar, select setIpGeolocationApiKey and click Run. Google will ask you to approve permissions (this is normal). A popup will ask for your API key. Paste it and click OK.
5. Go back to your spreadsheet tab and refresh the page. You should see a new IPGeolocation.io menu at the top. Sign up for a free API key if you don't have one. The key connects the sheet to the service and you only set it once per spreadsheet.

Test it by typing this in any empty cell:

=IPGEO("8.8.8.8", "location.country_name")

If it returns "United States," you're set.

Tip: If the IPGeolocation.io menu doesn't appear after refreshing, open Apps Script again and confirm the code saved correctly. A second refresh usually fixes it.

IP enrichment formulas

This is the table to bookmark. Every formula takes an IP address (or a cell reference like A2) as the first argument, a field path as the second, and optionally "security" as the third for security-related fields.

Location and network

What you need	Formula	Example output
Country name	=IPGEO(A2, "location.country_name")	Germany
Country code (2-letter)	=IPGEO(A2, "location.country_code2")	DE
City	=IPGEO(A2, "location.city")	Berlin
State or province	=IPGEO(A2, "location.state_prov")	Berlin
Latitude	=IPGEO(A2, "location.latitude")	52.5200
Longitude	=IPGEO(A2, "location.longitude")	13.4050
Timezone	=IPGEO(A2, "time_zone.name")	Europe/Berlin
Currency code	=IPGEO(A2, "currency.code")	EUR
ASN number	=IPGEO(A2, "asn.as_number")	AS13335
ASN organization	=IPGEO(A2, "asn.organization")	Cloudflare, Inc.

Security signals

What you need	Formula	Example output
VPN detected	=IPGEO(A2, "security.is_vpn", "security")	TRUE
Proxy detected	=IPGEO(A2, "security.is_proxy", "security")	FALSE
Tor exit node	=IPGEO(A2, "security.is_tor", "security")	FALSE
Bot detected	=IPGEO(A2, "security.is_bot", "security")	FALSE
Cloud provider	=IPGEO(A2, "security.is_cloud_provider", "security")	TRUE
Threat score (0-100)	=IPGEO(A2, "security.threat_score", "security")	72

Security formulas need that third "security" parameter. Without it, they return blank. There's also a dedicated =IPSECURITY(A2, "security.threat_score") formula that calls the security endpoint directly.

Shortcuts and bulk

Formula	What it does
=IPGEO_COUNTRY(A2)	Returns country name (shortcut)
=IPGEO_CITY(A2)	Returns city name (shortcut)
=IPGEO_TIMEZONE(A2)	Returns timezone name (shortcut)
=IPGEO_ASN_ORG(A2)	Returns ASN organization (shortcut)
=IPGEO_IS_VPN(A2)	Returns VPN detection (shortcut)
=IPGEO_THREAT_SCORE(A2)	Returns threat score (shortcut)
=IPGEO_BULK(A2:A500, "location.country_name")	Fills an entire column from a range
=IPGEO_JSON(A2)	Returns the raw JSON response (debugging)

IPGEO_BULK is the one to reach for when you have more than a few dozen IPs. Instead of dragging a formula down 500 rows (which fires 500 individual API calls), it processes the whole range in a single batch. Faster, fewer API calls, and the sheet stays responsive.

Note: Keep bulk ranges contiguous. IPGEO_BULK skips empty cells, so remove blank rows from the input range before running a bulk lookup to keep results aligned with the original IPs.

Four workflows that make the data useful

Looking up location data is the starting point. The value comes from what you do with it after the columns fill in.

Fraud and login review

Export login IPs from your auth system, paste them into column A, and bulk-enrich with country, city, VPN status, proxy status, and threat score. Then put conditional formatting to work:

• Color-scale the threat score column. Green for 0-19, yellow for 20-59, red for 60-100. Suspicious rows stand out immediately without reading a single cell.
• Filter by VPN. Show only TRUE rows. These are logins happening through VPN connections, which might be normal for remote employees or worth investigating for consumer accounts.
• Cross-check country against expected geography. If your users are mostly in the US and Germany, a cluster of logins from a country you don't serve is worth a second look.

Marketing campaign analysis

If you capture IP addresses alongside campaign clicks or conversions, enrich them to answer questions your analytics dashboard can't:

• Country and city breakdown that doesn't depend on the user's browser locale settings. IP-level country data is independent of what the user tells your form.
• Bot and cloud-provider signals: A concentration of clicks from cloud infrastructure is worth investigating, especially when it overlaps with bot, proxy, known-attacker, or elevated threat signals. A cloud-provider flag by itself is context, not proof of fraudulent traffic.
• Timezone grouping for scheduling. Pivot the enriched data by timezone to see when your actual audience is active, not when your analytics platform thinks they are. For a campaign with 2,000 clicks, =IPGEO_BULK(A2:A2001, "location.country_name") fills the country column in one call. Add a pivot table grouped by country, and you have a geographic distribution in under a minute.

Server log enrichment

Nginx and Apache access logs give you IP addresses, timestamps, URLs, and status codes. What they don't give you is who those IPs belong to.

Paste the IP column into a sheet. Enrich it with asn.organization and location.country_name, then pivot by ASN organization. A large share of requests may come from a relatively small set of networks. Some may be consumer ISPs, some cloud or hosting providers, some CDN infrastructure, and some networks you already recognize.

If an ASN you don't recognize is hammering a specific endpoint, that's a pattern worth investigating before it becomes an incident.

The sheet doesn't replace your monitoring stack, but it gives operations and security teams a quick way to triage access patterns without writing queries against a log aggregator. When someone asks "where is this traffic coming from," a pivot table by ASN answers it in a way that a raw IP list never will.

Compliance and access auditing

For teams with geographic access policies (data residency requirements, export controls, regional licensing), IP enrichment turns an access log into a compliance report.

The workflow: pull login records for a review period, enrich with country, and filter for logins originating outside your approved country list. If your policy says "US and EU only" and you see logins from a country not on that list, that's a finding that needs documentation. Adding the VPN column helps contextualize findings: a login from an unexpected country through a VPN has a different risk profile than one from a residential ISP in that same country.

This isn't a substitute for proper access controls. But when the auditor asks "were there any access events from outside approved regions during Q2," this is how you answer the question in 15 minutes instead of filing a ticket with engineering.

Handling larger datasets

Individual IPGEO formulas work fine for a few dozen rows. Beyond that, switch to IPGEO_BULK:

=IPGEO_BULK(A2:A1000, "location.country_name")

This processes the range as a single batch. The underlying API accepts up to 50,000 IPs per request, although practical Google Sheets batch sizes can be lower because Apps Script has its own execution and caching limits.

A few things to know about scale:

Caching. Results are cached for six hours inside Google Sheets using Apps Script's built-in cache service. If you look up the same IP twice within that window, the second call doesn't cost an API request.

Free tier quota. The free plan gives you 1,000 requests per day across all APIs. Bogon IPs (private ranges like 10.x.x.x, 192.168.x.x) don't count against quota, so dirty lists with internal IPs won't burn through your allowance.

Sheet performance. Google Sheets has its own execution limits for custom functions. If the sheet feels sluggish, switching from individual formulas to IPGEO_BULK is the single biggest performance improvement. The README's troubleshooting section covers other common slowdown causes.

What to watch out for

IP geolocation is approximate. Across IPGeolocation's dataset, country-level accuracy is 99%+, region/state is 90%+, and city-level is 85%+. City results should still be treated as estimates rather than exact positions, especially on mobile networks, VPNs, proxies, and networks where traffic exits far from the user.

VPN and proxy detection has false positives. A corporate VPN exit in another country looks identical to a privacy VPN from the network's perspective. Context matters. If your entire user base is enterprise employees, VPN flags will be noisy. Use threat_score thresholds rather than binary VPN flags for decision-making.

Google Sheets isn't a production system. This pipeline works well for periodic batch analysis, ad-hoc investigations, and generating reports. It's not a replacement for real-time API integration in your application code. For production use (per-request enrichment, middleware, real-time blocking), call the API from your backend.

The full formula reference and source code live at the GitHub repository, including a suggested five-tab template layout and additional troubleshooting. If a formula returns blank, the JSON dump formula (=IPGEO_JSON(A2)) shows the raw API response so you can check whether a field exists for that IP.

What's next

Drop the script into a sheet, paste your IPs, and run a bulk enrichment. Most people start with the location formulas and add security columns once they see how fast it fills in.