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

Question

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?

Carnay

After downloading a database of IPs v.s. cities, You could use the VLOOKUP() worksheet function – Porphyry

Answer 1

After some digging I've figured out how to do this.

1. Copy the import_json_appsscript.js script on https://gist.github.com/chrislkeller/5719258. This will be the script that will create the ImportJSON() function to a Google spreadsheet

2. Go to Google spreadsheet, on the menu bar got to Tools > Script Editor

3. Copy paste import_json_appsscript.js into the Script Editor and save it, Double check that you can see the ImportJSON() function on the Select function drop down menu.

4. On the Spreadsheet use function =ImportJSON(url, query, options), for example =ImportJSON("http://freegeoip.net/json/75.148.30.137", "/city", "noHeaders") to retrieve Baltimore from the FreeGeoIP call.

Hope that helps, it certainly answers my question.

Carnay

I had to use iplocate.io/api/lookup/8.8.8.8 as the service above returned an error, however this still worked great! – Priedieu

Had to use IPStack per below answer: https://mcmap.net/q/1267901/-on-google-spreadsheet-how-do-you-call-the-city-country-of-an-ip – Brachyuran

Answer 2

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.

Madel

thanx, what we need to do to show something like this: Mumbai, Maharashtra, India – Latoria

Answer 3

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.

Dardar

Answer 4

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

Fitted

Answer 5

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"
}

Ramunni