Extract subfolders from URLs

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

With this script you can input a range of URLs and the formula will extract the number of subfolders you choose from the URLs (can match trailing slash and non-trailing slash). 🚀

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:

/**
 * Extract subfolders from URLs
 * @param {A1:A200} url - A range of URLs or a single URL
 * @param {1} subfoldernum - number of subfolders you want to extract
 * @param {false} trailingslash - whether or not to include the trailing slash. Default is false
 * @customfunction
 */

function subfolderextract(url, subfoldernum, trailingslash) {

    // if the url is empty, return error
    if (!url) return 'You need to enter a URL';

    // if subfolder is not defined, return all subfolders
    if (!subfoldernum) subfoldernum = 999;

    // if subfoldernum is not a number, return error
    if (isNaN(subfoldernum)) return 'You need to enter the number of subfolders you want to extract.';

    // if trailingslash is not defined, set to true
    if (trailingslash === undefined) trailingslash = false;

    // if trailingslash is not a boolean, return error
    if (typeof trailingslash !== 'boolean') return 'You need to enter true or false for the trailing slash.';

    if (!Array.isArray(url)) {

        // if there is no domain or domain is not a valid URL, return error  
        if (!url.match(/^https?:\/\//)) return 'You need to enter a valid URL.';

        // split the URL into an array
        let urlArray = url.split('/');

        // extract the subfolder
        let subfolder = urlArray.slice(3, subfoldernum + 3).join('/');

        // if trailingslash is true, add a trailing slash only if the URL doesn't already have a trailing slash
        if (trailingslash) subfolder = subfolder.replace(/\/$/, '') + '/';

        // if trailing slash is false, remove the trailing slash
        if (!trailingslash) subfolder = subfolder.replace(/\/$/, '');

        // return the subfolder
        return subfolder;

    } else {

        let subfolderArray = [];

        // remove empty values
        url = url.flat(Infinity).filter((value, index, array) => {

            // if the value is not empty, return true
            if (value) return true;

            // if the value is empty and the index is 0, return false  
            if (index === 0) return false;

            // if the value is empty and the index is the last value in the array, return false
            if (index === array.length - 1) return false;
            return true;
        });


        // loop through the array of URLs
        for (let i = 0; i < url.length; i++) {

            // if there is no domain or domain is not a valid URL, return error  
            if (!url[i] || !url[i].match(/^https?:\/\//)) {
                subfolderArray.push('');
                continue;
            }

            // split the URL into an array
            let urlArray = url[i].split('/');

            // extract the subfolder
            let subfolder = urlArray.slice(3, subfoldernum + 3).join('/');

            // if trailingslash is true, add a trailing slash only if the URL doesn't already have a trailing slash
            if (trailingslash) subfolder = subfolder.replace(/\/$/, '') + '/';

            // if trailing slash is false, remove the trailing slash
            if (!trailingslash) subfolder = subfolder.replace(/\/$/, '');

            // push the subfolder into the subfolderArray
            subfolderArray.push(subfolder);

        }

        // return the subfolder
        return subfolderArray;

    }
}

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

=subfolderextract(A2:A100, 1, false)

Replace A2:A100 with any range of URLs (you can also run the formula on a single cell).

Replace 1 with any number of subfolders you want to extract. Eg.

/category (1 subfolder)
/category/sofas (2 subfolders)

Change false to true if you want to include trailing slashes on the subfolders returned. Eg. if true:

/category/ (1 subfolder, trailing slash true)
/category/sofas/ (2 subfolders, trailing slash true)

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 👇