Menu
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?
378
Click the Run button, authorize the script and watch as the geographic details and ISP names are populated in the sheet. Internally, the Google Sheet uses the IP2location web service with Google Apps Script to transform IP addresses into geographic region.
How to determine the country, city and ISP name of IP addresses in bulk with Google Sheets and IP2Location service. Websites can determine the visitor’s geographic location using their IP address and serve more relevant content.
If you have a bulk list of IP addresses, you can use Google Sheets to estimate the corresponding geographic details for each of the addresses in few easy steps: Click here to make a copy of the Google Sheet for performing IP lookups in bulk. Paste the list of IP addresses in column A, one per row.
The ADDRESS Google Sheets formula returns a cell’s address in the form of a text. It is noteworthy that the resulting address is a string and not the cell reference. ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet]) row – this value indicates the row number where the cell, that we need the address for, is located.
After some digging I've figured out how to do this.
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
Go to Google spreadsheet, on the menu bar got to Tools > Script Editor
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.
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.
70
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.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
