IP Geolocation In Google Sheets

Introduction

IP geolocation is the process of finding associated geographic coordinates (often expressed as latitude and longitude) of an internet-connected computing or a mobile device using digital information, such as an IP address. As we have seen in the past, the (global) IP address of a device is its unique identifier across the internet.

The process of IP geolocation involves mapping the IP address to the country, region (city), latitude/longitude, ISP, and other useful things. Why might this process be useful? Consider that every website owner wants to know this kind of information for the people who visit their site. It is exactly what services like Google Analytics offer (of course, they do many other things, too).

Free IP Geolocation API

In this example, we will use the API from freegeoip.app, which provides a free IP geolocation API for software developers. It uses a database of IP addresses associated with cities and other relevant information like time zone, latitude, and longitude.

The API is limited to 15,000 queries per hour by default. Once this limit is reached, all of the requests will result in HTTP 403, forbidden, until the quota is cleared. The query limit, I think, is adequate for the typical Google Sheets user.

The image above demonstrates the response from this API in JSON format. You can see the available data, which we will read using Google Apps Script.

GAS code

The GAS code that follows includes three functions. The getIpGeolocationData gets the location-related data based on the given IP and the return type. The getResponseJsonData performs the request to the server and converts the response into a JSON object. Finally, the isBlank function checks if the input string is blank, null, or undefined. We have already seen and used the last two functions in the previous GAS post.

The getIpGeolocationData function provides several return options to the user:

• GEO: latitude and longitude
• TIME: time zone
• CITY: city name
• ZIP: zip code
• REGION: region’s name
• COUNTRY: country’s name
• (blank): a combination of the city name, region name, and country name

/* Start of the GAS code. */
  
/*
--------------------------------------------------
Written By:    Christos Samaras
Date:          26/04/2020
Last Updated:  10/05/2020
E-mail:        [email protected]
Site:          https://myengineeringworld.net
--------------------------------------------------
*/
  
/**
 * This function returns location-related data based on an IP. 
 *
 * @param {A1} ip A cell or a string value that contains the IP of interest. 
 * If this parameter is omitted (e.g. null or empty string), 
 * the IP of the Google Server storing the spreadsheet will be used.
 * @param {B1} returnType A cell or a string value that has the type of data that will be returned. 
 * Available options: "GEO", "TIME", "CITY", "ZIP", "REGION", "COUNTRY". 
 * If this parameter is omitted (e.g. null or empty string), a combination of city, region, and country will be returned.
 * @return The location-related data based on the given IP and the return type.
 * @customfunction
 */
function getIpGeolocationData(ip = '', returnType = '') 
{
  
  /*
    --------------------------------------------------------------------------
    Gets the location-related data based on the given IP and the return type.
    --------------------------------------------------------------------------
  */
  
  // Declaring the variable that will contain the JSON data returned from the request.
  let ipData;    
  
  // Send the request to the server and return the JSON data.
  try
  {
    // If the IP is empty, the IP of the Google Server storing the spreadsheet will be used.
    ipData = isBlank(ip)? getResponseJsonData('https://freegeoip.app/json') : getResponseJsonData('https://freegeoip.app/json/' + ip);
  }
  catch(error)
  {
    // In case of error, return an error message.
    return 'Request error';
  }
  
  // Check if the variable contains data.
  if(isBlank(ipData))
    return 'Response error';
  
  // Declaring the variable that will contain the returned location data.
  let locationData; 
  
  // Return the appropriate data based on the returnType parameter. 
  switch(returnType.toUpperCase())
  {
    
    // Get the latitude and longitude.
    case 'GEO':
      locationData = ipData['latitude'] + ', ' + ipData['longitude'];
      break;
    
    // Get the time zone.
    case 'TIME':
      locationData = ipData['time_zone'];
      break;          
 
    // Get the city name.      
    case 'CITY':
      locationData = ipData['city'];
      break;      
    
    // Get the zip code.            
    case 'ZIP':
      locationData = ipData['zip_code'];
      break;           
      
    // Get the region name.    
    case 'REGION':
      locationData = ipData['region_name'];
      break;
    
    // Get the country name.          
    case 'COUNTRY':
      locationData = ipData['country_name'];
      break;    
    
    // Default case: get a combination of city, region, and country.
    default:
      locationData = ipData['city'] + ', ' + ipData['region_name'] +  ', ' + ipData['country_name'];
      break;
      
  }
  
  // Return the location-related data to the sheet.
  return locationData;
  
}
 
function getResponseJsonData(url)
{
  
  /*
    ----------------------------------------------------------
    Performs the request and converts the response into JSON.
    ----------------------------------------------------------
  */  
   
  // Check that the URL is not empty.
  if(isBlank(url))
    return;
  
  // Fetch the data.
  let response = UrlFetchApp.fetch(url);
  
  // Get the response text.
  let json = response.getContentText();
  
  // Convert to JSON.
  return JSON.parse(json);
  
}
 
function isBlank(str) 
{
    
  /*
    --------------------------------------------------------
    Checks if the input string is blank, null or undefined.
    --------------------------------------------------------
  */  
 
  return (!str || /^\s*$/.test(str));
  
}
 
/* End of the GAS code. */ 

The getIpGeolocationData function will be used directly in the spreadsheet (check the image above). Therefore, I have added the necessary description in the parameters and an example, following the approach that was analyzed here.

IP Geolocation and accuracy 

The accuracy of IP geolocation depends on the information you are seeking. The accuracy is quite high (probably higher than 95% or even 98%). For more detailed information (e.g., city or lat/long), the accuracy drops to even below 50% in some cases.

Sample spreadsheet in Google Sheets

The above functions are included in this sample Google Sheets file.

Note that the file is in the View only mode. To enable editing, go to File → Make a copy. Once the file is copied on your Drive, you can use it whenever you want.

Read also

Pulling Data From A COVID-19 API Into Google Sheets
How To Create & Use A Custom Function In Google Sheets

Page last updated: 10/05/2020