How tiring it could be to check IP location data manually? I know, a lot. But what if I tell you that you won’t have to check IP location data manually ever again! This is possible if you follow this ultimate guide to perform bulk IP Address lookup with Google Sheets.
Yes, once you follow this guide to set up the automation, it will get triggered each time you add a new row to Google Sheets, send the IP stored in a cell to IP2 Location, and then add emerging information to a new row, worksheet in Google Sheets.
To refer to a real-life example of this automation, think of a website you visited that read your geographic location as its visitor using your IP address and served relevant content to you. For example, a weather forecast website reads your IP address to gauge your approximate location and cater to you with weather reports of your current city. Or a currency exchange website can determine your default currency by reading your IP address to ascertain your IP address.
There are a plethora of free IP lookup services like ip2c.org that can reveal the country of your client’s IP address on a quick and simple HTTP request.
Bulk IP Address Lookup with Google Sheets
IP2 Location is a well-used alternative that extracts more detailed geolocation information from any IP address. You can retrieve the client’s country, city name, region, ISP name, and more if you choose the IP location lookup service.
Here’s a step-by-step guide to using google sheets to estimate the corresponding geographic details for each of the addresses in the bulk IP address list.
Step 1: Make a copy of the Google Sheet for performing IP lookups in bulk.
Step 2: Now, use column A to paste the list of IP addresses, one per row.
Fact: The Lookup service works for both, IPv4 and IPv6 addresses.
Step 3: Enter your key in cell E1. Use demo as the key or get your own key from ip2location.com, in case you have a small list of IP addresses.
Step 4: Click the run button, authorize the script, and wait as the geographic details and ISP names are populated in the sheet.
How does IP2 Location Service Work?
Internally, the Google Sheet uses the IP2location web service with Google Apps Script to transform IP addresses into geographic regions. Apparently, it utilizes the UrlFetchApp service to carry out multiple HTTP requests in a single go for enhanced performance. Take a look at the full source code:
| const ip2location = () => { // Get all the input data from Google Sheet const ss = SpreadsheetApp.getActiveSheet(); const data = ss.getDataRange().getDisplayValues(); // Use your own API key or use demo key const apiKey = data[0][4] || “demo”; // Generate API URL for IP address const getUri_ = (ipAddress) => { const API_URL = “https://api.ip2location.com/v2”; return ${API_URL}/?ip=${ipAddress}&key=${apiKey}&package=ws4;}; const requests = []; for (let r = 2; r < data.length; r++) { const [ipAddress, countryName] = data[r]; // Only process rows where the country is blank if (ipAddress && !countryName) { requests.push({ url: getUri_(ipAddress), rowNumber: r + 1 }); } } // Make API calls in bulk using the UrlFetchApp service UrlFetchApp.fetchAll(requests).forEach((content, i) => { // Parse the JSON response const { city_name, country_name, isp, response } = JSON.parse(content); // If the response is populated, the API call failed if (response) throw new Error(response); // Write the response data to Google Sheet const values = [[country_name, region_name, city_name, isp]]; ss.getRange(requests[i].rowNumber, 2, 1, 4).setValues(values);}); // Flush all changes SpreadsheetApp.flush(); }; |
And Voila! you are all done with performing bulk IP Address Lookup using Google Sheets.
