Google Sheets Custom Functions for wannabe domain investors (like me)

Context

Over the last 6 months, I've been spending a ridiculously increasing amount of time trying to understand how the world of investing in domain names work. While I've not had any luck with either buying or selling domains, I did find myself constantly working on some of the more obvious and repetitive operational tasks in the process.

Problem

My general process involves getting a ginormous list (ranging from 40,000 to 2.7 million rows) of domain name data and then trying to filter, sort, curate it based on a whole bunch of criteria such as the TLD, whether or not a domain name contains a number, hyphen etc.

Some are more easy to detect using the existing spreadsheet functions like REGEXMATCH -

But the data points I was interested in were -

1. identify the composition of a name based on consonants (c), vowels (v), numbers (n) or special characters (x)
2. remove vowels from a name to see what kind of pattern / short domains emerged
3. check availability of .com domain names or even get the metadata from the existing ones

Prior reading

• Custom Functions in Google Sheets
• Quotas and limitations of Google Apps Script — specifically, if you're using a normal @gmail account, you can make 20,000 API calls to look-up a .com domain data and 100,000 API calls if you're on a paid Workspace account, a day

Solution

1. DOMAIN_PATTERN: returns the CVNX notation of a domain name

2. REMOVE_VOWELS: removes the vowels from a name

3. DOT_COM_DATA: returns data about an available .com domain

Codebase

1. to find the CVNX notation of a given name -

/**
 * Returns the CVNX notation of a domain name.
 *
 * @param {string} name The name of the domain (without the TLD).
 * @return The CVNX notation of the domain name.
 * @customfunction
 */
function DOMAIN_PATTERN(name) {
  const vowels = ["a", "e", "i", "o", "u"];
  const consonents = ["b", "c", "d", "f", "g", "h", "j", "k", "l", "m", "n", "p", "q", "r", "s", "t", "v", "w", "x", "y", "z"];
  const letters = name.toLowerCase().split("");
  let pattern = [];
  letters.map(letter => vowels.includes(letter) ? pattern.push("V") : (consonents.includes(letter) ? pattern.push("C") : (!isNaN(letter) ? pattern.push("N") : pattern.push("X"))));
  return pattern.join("");
}

2. to remove vowels from a given name -

/**
 * Removes the vowels from a name.
 *
 * @param {string} name The name of the domain.
 * @return The name with the vowels removed.
 * @customfunction
 */
function REMOVE_VOWELS(name) {
  const vowels = ["a", "e", "i", "o", "u"];
  const letters = name.toLowerCase().split("");
  let newName = [];
  letters.map(letter => vowels.includes(letter) ? null : newName.push(letter));
  return newName.join("");
}

3. to retrieve data about an existing .com domain name -

/**
 * Returns data about an available .com domain.
 *
 * @param {string} name The name of the domain.
 * @return Registrar name, registration & expiration date of a .com domain.
 * @customfunction
 */
function DOT_COM_DATA(name) {
  const nameComponents = name.replace(/\s+/g, '').split(".");
  if (nameComponents.length > 2) return "INVALID INPUT";
  if (nameComponents.length == 2 && nameComponents[1] != "com") return "TLD NOT SUPPORTED";
  name = nameComponents[0];
  const url = `https://rdap.verisign.com/com/v1/domain/${name}.com`;
  const response = UrlFetchApp.fetch(url,{ muteHttpExceptions: true });
  if (response.getResponseCode() !== 200) return "AVAILABLE";
  let comData = [];
  const jsonData = JSON.parse(response.getContentText());
  const registrar = jsonData.entities[0].vcardArray[1][1][3];
  const registrationDate = jsonData.events[0].eventDate.replace("T"," ").replace("Z","");
  const expirationDate = jsonData.events[1].eventDate.replace("T"," ").replace("Z","");
  comData.push([registrar, registrationDate, expirationDate]);
  return comData;
}

Demo

you can make a copy of my original Google Sheet to test these Custom Functions ☺️

Next steps

Here are two more things that I'm working on (unsuccessfully) -

1. the word break problem — it took me a while to arrive at this problem statement as i knew what i wanted but just didn't know what it was called 😅. Essentially, given any stream of text input, I'd like a custom function to camel case the output as per dictionary words or isolate the ones that aren't dictionary words — example, for an input of mousetrap, I'd expect the output to be MouseTrap and for the input pinqlabel, I'd want the output to be PinqLabel (where the first letter is automatically capitalized and the beginning of the next available dictionary word is capitalized too)
2. an open-source api that could tell us the organic traffic a articular domain name gets. Turns out this is a guesstimate by all means as mostly, from what i understand, tools rely on the associated keywords