FLIGHT_DISTANCE(), AIRPORT_LOCATION(), and AIRLINE_NAME() Custom Functions for Google Sheets

Code.gs

/**
 * @fileoverview Custom functions for looking up airport data in Google Sheets.
 *
 * Uses Great Circle Mapper to look up airport data.
 *
 * @link http://gcmap.com
 *
 * Click "Extensions > Apps Script" and paste the script. This library has a
 * dependency on Cheerio. Click "Add a Library" and enter the script ID:
 * `1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0`.
 *
 * @link https://github.com/tani/cheeriogs
 */

/**
 * Get the flight distance between two airports.
 *
 * @param {string} origin - the IATA airport code for the origin airport
 * @param {string} destination - the IATA airport code for the destination airport
 * @return {?number}
 * @customfunction
 */
function FLIGHT_DISTANCE(origin, destination) {
  origin = origin.trim();
  if (origin.length === 0) {
    return null;
  } else if (origin.length !== 3) {
    throw `Invalid airport code for origin: ${origin}`;
  }

  destination = destination.trim();
  if (destination.length === 0) {
    return null;
  } else if (destination.length !== 3) {
    throw `Invalid airport code for destination: ${destination}`;
  }

  const response = UrlFetchApp.fetch(
    `http://www.gcmap.com/dist?P=${origin}-${destination}`
  );
  const content = response.getContentText();
  const $ = Cheerio.load(content);

  let distance = $("#mdist tfoot td.d").text();
  distance = distance.replace(/[^\d]/g, "");
  distance = Number(distance);

  return distance;
}

/**
 * Get the city and country for an airport.
 * 
 * @param {string} code - the IATA airport code to look up
 * @return {?string}
 * @customfunction
 */
function AIRPORT_LOCATION(code) {
  if (code.length === 0) {
    return null;
  } else if (code.length !== 3) {
    throw `Invalid airport code: ${code}`;
  }

  const response = UrlFetchApp.fetch(`http://www.gcmap.com/airport/${code}`);
  const content = response.getContentText();
  const $ = Cheerio.load(content);

  // Extract the city (locality) and country from the location table
  const locality = $("span.locality").text().trim();
  const country = $("span.country-name").text().trim();

  if (!locality || !country) {
    return 'Unknown';
  }

  if (locality.toLowerCase() === country.toLowerCase()) {
    return locality;
  }

  return `${locality}, ${country}`;
}

/**
 * Get the name for an airline.
 * 
 * @param {string} code - the IATA airline code to look up
 * @return {?string}
 * @customfunction
 */
function AIRLINE_NAME(code) {
  code = code.trim();
  if (code.length === 0) {
    return null;
  } else if (code.length !== 2) {
    throw `Invalid airline code: ${code}`;
  }

  const response = UrlFetchApp.fetch(`https://www.iata.org/en/publications/directories/code-search/?airline.search=${code}`);
  const content = response.getContentText();
  const $ = Cheerio.load(content);

  const row = $('table.datatable tbody tr').filter(function() {
    const codeCell = $(this).find('td:nth-child(3)').text().trim();
    return codeCell === code;  // Ensure exact match
  });

  if (row.length) {
    let airlineName = row.find('td:first-child').text().trim();
    airlineName = normalizeAirlineName(airlineName);
    return airlineName;
  }

  return null;
}

/**
 * @param {string} name - The original airline name.
 * @return {string}
 * @internal
 */
function normalizeAirlineName(name) {
  // Remove anything after 'dba'
  name = name.replace(/\bdba.*$/i, '');
  // Remove anything after 'Airlines', 'Airline', 'Airways', keeping only up to that point
  name = name.replace(/\b(Airlines?|Airways?)(.*)/i, '$1');
  // Remove everything after corporate indicators like 'Inc', 'Ltd', 'Corporation', 'Co.', 'Company', 'Limited', etc.
  name = name.replace(/\b(Inc|Ltd|Corporation|Co\.|Company|Limited|LTD[A-Za-z]*).*$/gi, '');
  // Clean up any leftover punctuation or extra spaces
  name = name.replace(/[,.\-]+/g, '').trim();
  return name;
}