Group a range of URLs by domain

This script is part of the Keywords in Sheets Free Add-On.

With this script you can input a range of URLs and the script will group them by domain and return the number of times each domain appears in the range. Perfect for seeing which domains rank in the top 10 for the most keyword when combined with our search result functions. 🚀

Check out the GIF to see it in action 🎥

Get the Google Sheets Add-On

Learn more below 👇

How to add the Script to Google Sheets

1. Copy the script below:

/**
 * Group a range of URLs by domain and return the number of times each domain appears in the range. 
 * @param {A2:A100} range - your range 
 * @customfunction
 */

function groupbydomain(range) {

  // if no range, return error
  if (!range) return 'You need to enter a range.'

  // if range includes multiple columns, return error
  if (range[0].length > 1) return 'Your range must be a single column of data.';

  // convert array url strings to domains and remove empty cells
  const domains = range.flat().map(url => url.replace(/(https?:\/\/)?(www.)?/g, '').split('/')[0]).filter(Boolean);

  // remove duplicates from domains array
  const unique = [...new Set(domains)];

  // count number of times each domain appears in domains array
  const uniqueCount = unique.map((item) => {
     return [item, domains.filter((domain) => domain === item).length];;
  });

   // sort array by number of times each domain appears in domains array and return
   let uniqueCountSorted = uniqueCount.sort((a, b) => b[1] - a[1]);

   // add header row
   uniqueCountSorted.unshift(['Domain', 'Count']);

   return uniqueCountSorted;
}

2. Head over to Google Sheets

Or if you’re really smart, create a new sheet by going to: https://sheets.new

Select Appscript from the Extensions menu.

Paste the script and save it.

3. Add the formula to any cell in your sheet

=groupbydomain(A2:A100)

Replace A2:A100 with any range of URLs. Must be a single column of URLs.

This script is useful for analysing the comma-separated top ranking results data from the search results functions inside of the Keywords in Sheets Add-on, when paired with the row to col function.

See example below:

Thanks for stopping by 👋

I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. 🤓

Questions? Get in touch with me on social or comment below 👇