'On Google Spreadsheet, how do you call the CIty, Country of an IP?

I would like to know if there is a formula/script one could use on Google Spreadsheet to obtain the City,Location of an array of IP addresses.

i.e lets imagine that each cell on column A has 100 IP addresses, what formula/script should I use on column B to get the respective city and location?

Solution 1:^[1]

The easiest way to do this via an external service. IPInfo supports requesting a single field, so you can actually do this without using any addon (via IMPORTDATA function). Quick example:

For country: =IMPORTDATA("https://ipinfo.io/" & A1 & "/country")

For city: =IMPORTDATA("https://ipinfo.io/" & A1 & "/country")

Here A1 is IP address cell.

To avoid getting rate limited, you can register for a free account (50k monthly requests) and use a token.

Disclaimer: I work at IPInfo.

Solution 2:^[2]

If you are looking to write a macro of some sort, you could consider using freegeoip.net which lets you make a simple restful call to get back data in a variety of formats.

http://freegeoip.net/

For example, a call to find the location for the ip address 75.148.30.137 would look like this:

http://freegeoip.net/json/75.148.30.137

Also, here is a link to Google's documentation on how to make rest calls in a Google app:

https://developers.google.com/apps-script/guides/services/external

Good luck.

Solution 3:^[3]

I recently implemented the same scenario with IP Stack API. API will return a Json response with all geolocation details.

for(var i in IPs)
{
  var url = 'http://api.ipstack.com/'+ IPs[i][0]+ '?access_key=access_key&output=json';
  var httpResponse = UrlFetchApp.fetch(url);
  var rspns = httpResponse.getResponseCode();
  //deal with this response
}

Read full google sheet implementation from here; Find Geo location of Multiple IP addresses via Google Apps Script

Solution 4:^[4]

Please try:

function parseIPApi() {
  var url = 'https://ipapi.co/75.148.30.137/json';
  var parse = UrlFetchApp.fetch(url);
  console.log(parse.getContentText())
}

Result:

{
    "ip": "75.148.30.137",
    "version": "IPv4",
    "city": "Woodbridge",
    "region": "Virginia",
    "region_code": "VA",
    "country": "US",
    "country_name": "United States",
    "country_code": "US",
    "country_code_iso3": "USA",
    "country_capital": "Washington",
    "country_tld": ".us",
    "continent_code": "NA",
    "in_eu": false,
    "postal": "22191",
    "latitude": 38.6326,
    "longitude": -77.2661,
    "timezone": "America/New_York",
    "utc_offset": "-0400",
    "country_calling_code": "+1",
    "currency": "USD",
    "currency_name": "Dollar",
    "languages": "en-US,es-US,haw,fr",
    "country_area": 9629091.0,
    "country_population": 327167434,
    "asn": "AS7922",
    "org": "COMCAST-7922"
}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Shubham
Solution 2	Trevor
Solution 3	iJay
Solution 4	Max Makhrov

'On Google Spreadsheet, how do you call the CIty, Country of an IP?

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]

Solution 4:^[4]