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: - Open your Google Sheet. Go to Extensions > Apps Script. - In the Apps Script editor, delete the starter code in Code.gs . Paste the full contents of Code.gs from the GitHub repository. - Save the project. Name it something like "IPGeolocation.io for Google Sheets." - 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. - 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 cal