How to Convert IP Addresses to Geolocation Data in Google Sheets

Intro #

If you have a list of IP addresses in Google Sheets and want to add country, city, ASN, ISP, proxy, VPN, time zone, or other IP intelligence, you do not need to look up each address manually.

With the IP2Location.io for Google Sheets Apps Script, you can turn a normal spreadsheet into a simple IP enrichment tool.

There are no custom formulas to maintain.

You simply:

1. Put IP2Location.io field names in the header row.
2. Paste your IP addresses into any column.
3. Highlight the IP addresses you want to process.
4. Choose IP2Location.io → Lookup Selected IPs.
5. The results are written back to the sheet as static values.

The source code is available in the ip2location/ip2location-io-google-sheets GitHub repository.

Example:

What can you retrieve? #

IP2Location.io can return different types of IP intelligence depending on the API plan being used.

Common fields include:

Different IP2Location.io plans expose different numbers and types of attributes. The Google Sheets script is intentionally plan-agnostic: it asks for the fields represented by your headers and writes whatever the API makes available to your account. Current IP2Location.io plans range from 11 attributes on Free to 79 on Security.

Why use headers instead of formulas? #

A typical Google Sheets API integration might use formulas such as:

=LOOKUP_IP(A2, "country_name")

That works, but formulas can be recalculated by Google Sheets. When every formula makes an external API request, recalculation can mean additional API usage.

This integration takes a different approach.

Your sheet might look like this:

Highlight:

A2:A4

then run IP2Location.io → Lookup Selected IPs:

The script calls IP2Location.io and writes:

The resulting cells contain values, not formulas.

That means the information remains in the spreadsheet until you deliberately run another lookup.

Step 1: Install the Apps Script #

Open the Google Sheet where you want to perform IP lookups.

Go to Extensions → Apps Script:

The Apps Script editor opens in a separate browser tab. This is normal. Because you opened it from the spreadsheet, the Apps Script project is automatically bound to that spreadsheet.

You do not need to use the Deploy button.

Open the Code.gs file from the IP2Location.io Google Sheets GitHub repository and copy its contents into the Apps Script editor.

Delete the default sample function first, then paste the script. You can put IP2Location.io as the name of the project.

Click Save and close the tab.

Return to your Google Sheet and reload the page.

You should now see a new menu IP2Location.io:

with options including:

Lookup Selected IPs

Set / Update API Key

Remove API Key

Recheck Bulk API Access

API Status

The first time you use one of the functions, Google may ask you to authorize the Apps Script.

This is required because the script needs permission to modify your spreadsheet, make HTTPS requests to IP2Location.io, and store your API configuration.

Step 2: Configure your API key #

An API key is optional, but for Google Sheets usage I recommend configuring at least a free IP2Location.io API key.

From the Google Sheet, choose IP2Location.io → Set / Update API Key:

Paste your IP2Location.io API key and save it.

The API key is not stored inside a spreadsheet cell.

Instead, the script stores it using Google Apps Script’s UserProperties, which means each person using the spreadsheet can configure their own key.

The API key is also sent to IP2Location.io using an HTTPS Bearer authorization header rather than exposing it in the spreadsheet.

Can I use it without an API key? #

Yes. IP2Location.io allows up to 1,000 keyless API queries per day. For higher volume, you can sign up for a Free plan to get 50,000 queries per month. (Learn more about IP2Location Keyless)

There is one important consideration when using keyless mode from Google Apps Script.

UrlFetchApp, which Apps Script uses to call external APIs, sends requests through Google’s network infrastructure. Google documents that URL Fetch requests originate from a shared pool of Google IP ranges.

Because keyless IP2Location.io limits are associated with unauthenticated API usage, using an API key provides more predictable identification and quota management than relying on keyless requests made from Google’s infrastructure.

For regular Google Sheets usage, configuring the free API key is therefore recommended.

Step 3: Add the fields you want as headers #

Now decide what information you want returned.

Suppose your IP addresses are in column A.

You could create:

Or perhaps you are analyzing network traffic:

Or for security review:

The important concept is:

The header name tells the script which API field should be written into that column.

So, there is no configuration screen for mapping fields to columns.

The column position is entirely your choice.

Nested fields with dot notation #

IP2Location.io also returns structured objects.

For example:

{
  "as_info": {
    "as_number": "15169",
    "as_name": "Google LLC",
    "as_domain": "google.com"
  }
}

To retrieve just the AS name, use:

as_info.as_name

as your column header.

Other examples include:

continent.name

continent.hemisphere

country.capital

country.currency

time_zone_info.olson

time_zone_info.current_time

proxy.is_vpn

proxy.is_tor

proxy.is_data_center

proxy.is_ai_crawler

This makes it easy to flatten complex IP intelligence into spreadsheet columns.

You can also return whole JSON objects #

You do not have to select an individual nested property.

For example, use:

time_zone_info

and the complete object can be written into one cell as JSON:

{"olson":"Asia/Kuala_Lumpur","gmt_offset":28800,"is_dst":false,...}

Likewise:

proxy

can return the proxy-related object.

Arrays are also converted to JSON.

For example:

continent.hemisphere

might produce:

["north","east"]

This is useful when you want to preserve more of the API response for later processing.

Step 4: Highlight the IP addresses #

The input column does not have to be column A.

For example, if your sheet contains:

you can simply highlight:

C2:C100

The script treats whichever single column you highlighted as the IP input column.

Only the selected rows are processed.

So, if you have 10,000 IPs but only want to refresh rows 500 through 600:

C500:C600

Highlight just those cells and run the lookup.

There is also a safety limit of 10,000 selected rows per execution so accidentally selecting an entire Google Sheets column such as A:A does not result in hundreds of thousands of cells being processed.

Step 5: Run the lookup #

Choose IP2Location.io → Lookup Selected IPs:

The script:

Repeated IP addresses in the selected range are deduplicated during the API lookup.

For example:

8.8.8.8

1.1.1.1

8.8.8.8

contains three rows but only two unique IP addresses.

The script queries each unique IP once during that run, then writes the appropriate result to all matching rows.

Normal API vs. Bulk API #

You do not need to manually choose an endpoint.

The Apps Script determines which API to use.

For one IP:

Normal API

For multiple IPs with a Bulk-capable API key:

Bulk API

The IP2Location.io Bulk API currently supports up to 1,000 IPv4 or IPv6 addresses in one request and requires a paid plan.

Selections larger than 1,000 IPs are automatically divided into batches.

For users without Bulk access, the integration falls back to the Normal API.

When multiple Normal API requests are needed, the script uses Apps Script’s UrlFetchApp.fetchAll() rather than making every request sequentially.

The user experience remains the same either way:

Highlight IPs → Lookup Selected IPs

Example 1: Enrich server logs #

Suppose you export a web server log into Google Sheets:

Add:

country_name

as_info.as_name

isp

usage_type

as additional headers.

After enrichment you can filter or build a pivot table by:

• country
• network owner
• ISP
• network usage type

This can make a raw IP list much easier to investigate.

For example, a traffic spike might make more sense once you discover that a large percentage originates from cloud, hosting, crawler, or consumer ISP networks.

Example 2: Review login or authentication IPs #

Suppose your authentication export contains:

Add:

country_code

country_name

city_name

as_info.as_name

proxy.is_vpn

proxy.is_tor

proxy.is_data_center

fraud_score

where those fields are available with your API plan.

You can then use standard Google Sheets filters or conditional formatting to review:

• unusual countries
• VPN traffic
• Tor traffic
• data center IPs
• unfamiliar networks
• elevated fraud scores

These signals should be treated as context rather than automatic proof of malicious activity. A corporate VPN, for example, may legitimately appear as a VPN or data center connection.

Example 3: Analyze marketing traffic #

If campaign or lead exports include IP addresses, IP enrichment can help answer questions such as:

Which countries generate the most activity?

Which cities appear most often?

Which networks are sending the traffic?

Is traffic coming from residential or hosting infrastructure?

Are significant numbers of requests coming through proxies?

A simple sheet might use:

IP

country_code

country_name

region_name

city_name

latitude

longitude

isp

as_info.as_name

After enrichment, create a Google Sheets pivot table using country_name or region_name.

This can turn an otherwise anonymous IP column into useful aggregate geographic context.

Example 4: Prepare IP data for further analysis #

Sometimes Google Sheets is just an intermediate step.

You might receive:

10,000 IP addresses

from:

• server logs
• security investigations
• fraud review exports
• analytics data
• CRM exports
• advertising logs

Use the Apps Script to append the IP attributes you need, then export the enriched sheet as CSV for use in:

• Python
• BI software
• data warehouses
• reporting tools
• machine-learning workflows

Because the results are static values rather than formulas, exported CSV files contain the actual enrichment results.

What happens when my API plan does not include a field? #

Nothing breaks.

Suppose your sheet asks for:

country_name

city_name

fraud_score

proxy.is_vpn

but your current plan does not return all of them.

Fields that are returned are populated normally.

Fields that are not returned remain blank.

If a requested field is not returned by any successful lookup in that run, the script shows it in the completion details.

This deliberately says the field was not returned, rather than claiming that your subscription definitely does not support it. A value can sometimes be unavailable because of the queried IP itself rather than the plan.

IPv4 and IPv6 are both supported #

The integration supports both IPv4 and IPv6 lookups.

For Bulk queries, IPv6 addresses receive a little extra handling because an API may return the same IPv6 address in expanded notation.

For example:

2a03:2880:f806:b::

and:

2a03:2880:f806:000b:0000:0000:0000:0000

represent the same IPv6 address.

The script normalizes IPv6 addresses internally so the Bulk response can still be mapped back to the original spreadsheet row.

Your API key stays outside the worksheet #

A common mistake with spreadsheet API integrations is putting the API key into a visible cell:

A1 = my-secret-api-key

or hard coding it into the script.

This integration instead uses:

PropertiesService.getUserProperties()

Each user can configure their own IP2Location.io API key without adding it to the sheet itself.

This is especially useful if the spreadsheet is shared with multiple people.

The API Status menu option lets users check whether a key is configured and whether Bulk capability has been detected without displaying the actual API key.

A few practical limitations #

Google Sheets is excellent for ad-hoc analysis, enrichment, investigation, and reporting, but it is not a replacement for a production backend.

Apps Script itself has execution and URL Fetch quotas. Google currently documents daily URL Fetch limits as well as a six-minute execution limit for normal Apps Script executions.

For very large or continuous workloads, call the IP2Location.io API from your application, backend service, ETL pipeline, or one of the available SDKs instead.

Also remember that IP geolocation is not GPS. An IP address can provide useful network-associated geographic information, but it should not be interpreted as the exact physical location of an individual.