Group by Words

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

With this script you can input a range of keywords or a range of Google Search Console query data and the formula will group the data by the amount of words you specify 🚀

Check out the GIF to see it in action 🎥


How to add the Script to Google Sheets

1. Copy the script below:

/**
 * Group Google Search Console data or a range of keywords by words
 * @param {A2:E} range - your range 
 * @param {1} numWords - the numbers of words to group by
 * @param {"words"} format - the format of your range ("gsc" or "words")
 * @param {"clicks"} sort - the column to sort the data ("clicks","impressions","ctr","position")
 * @customfunction
 */

function groupbywords(range, numWords, format, sort) {

    // if the range is not an array, return error. If the range is empty, return error. 
    if (!Array.isArray(range) || range.length === 0 || !range) {
        return "Please enter a valid range";
    }

    // if numWords is not defined, set it to 1
    numWords = numWords || 1;

    // if sort is not defined, set it to "clicks"
    sort = sort || "clicks";

    // remove header row
    range = range.slice(1);

    // if format is not defined or range is a single column array, set it to "words"
    format = range[0].length === 1 ? "words" : format || "words";

    // english stopwords from advertools https://advertools.readthedocs.io/en/master/advertools.stopwords.html
    const stopWords = ["a", "about", "above", "across", "after", "afterwards", "again", "against",
        "all", "almost", "alone", "along", "already", "also", "although", "always",
        "am", "among", "amongst", "amount", "an", "and", "another", "any",
        "anyhow", "anyone", "anything", "anyway", "anywhere", "are", "around",
        "as", "at", "back", "be", "became", "because", "become", "becomes",
        "becoming", "been", "before", "beforehand", "behind", "being", "below",
        "beside", "besides", "between", "beyond", "both", "bottom", "but", "by",
        "ca", "call", "can", "cannot", "could", "did", "do", "does", "doing",
        "done", "down", "due", "during", "each", "eight", "either", "eleven",
        "else", "elsewhere", "empty", "enough", "even", "ever", "every",
        "everyone", "everything", "everywhere", "except", "few", "fifteen",
        "fifty", "first", "five", "for", "former", "formerly", "forty", "four",
        "from", "front", "full", "further", "get", "give", "go", "had", "has",
        "have", "he", "hence", "her", "here", "hereafter", "hereby", "herein",
        "hereupon", "hers", "herself", "him", "himself", "his", "how", "however",
        "hundred", "i", "if", "in", "indeed", "into", "is", "it", "its", "itself",
        "just", "keep", "last", "latter", "latterly", "least", "less", "made",
        "make", "many", "may", "me", "meanwhile", "might", "mine", "more",
        "moreover", "most", "mostly", "move", "much", "must", "my", "myself",
        "name", "namely", "neither", "never", "nevertheless", "next", "nine", "no",
        "nobody", "none", "noone", "nor", "not", "nothing", "now", "nowhere", "of",
        "off", "often", "on", "once", "one", "only", "onto", "or", "other",
        "others", "otherwise", "our", "ours", "ourselves", "out", "over", "own",
        "part", "per", "perhaps", "please", "put", "quite", "rather", "re",
        "really", "regarding", "same", "say", "see", "seem", "seemed", "seeming",
        "seems", "serious", "several", "she", "should", "show", "side", "since",
        "six", "sixty", "so", "some", "somehow", "someone", "something",
        "sometime", "sometimes", "somewhere", "still", "such", "take", "ten",
        "than", "that", "the", "their", "them", "themselves", "then", "thence",
        "there", "thereafter", "thereby", "therefore", "therein", "thereupon",
        "these", "they", "third", "this", "those", "though", "three", "through",
        "throughout", "thru", "thus", "to", "together", "too", "top", "toward",
        "towards", "twelve", "twenty", "two", "under", "unless", "until", "up",
        "upon", "us", "used", "using", "various", "very", "via", "was", "we",
        "well", "were", "what", "whatever", "when", "whence", "whenever", "where",
        "whereafter", "whereas", "whereby", "wherein", "whereupon", "wherever",
        "whether", "which", "while", "whither", "who", "whoever", "whole", "whom",
        "whose", "why", "will", "with", "within", "without", "would", "yet", "you",
        "your", "yours", "yourself", "yourselves"
    ];

     // remove empty values, non-strings and remove stopwords
    range = range.filter(function(d) {
        return d[0] && typeof d[0] === "string" && stopWords.indexOf(d[0].toLowerCase()) === -1
    });

    // get first column from data if not a single range
    let firstColumn = range[0].length > 1 ? range.map(function(d) {
        return d[0]
    }) : range;

    // get n words from each row in first column
    const words = firstColumn.map(function(d) {
        return d.split(" ").filter(function(d) {
            return d.length > 0
        }).slice(0, numWords).join(" ")
    }).filter(function(d, i, self) {
        return self.indexOf(d) == i && d.split(" ").length == numWords;
    });


    // aggregate data by n words and push to newData array
    var newData = [];

    for (var i = 0; i < words.length; i++) {
        var word = words[i];

        // if word is 1 character or empty, skip
        if (word.length == 1 || word == "") {
            continue;
        }

        // filter range by word
        let rows = range.filter(function(d) {
            return d[0].indexOf(word) > -1
        });

        // if format is 'words' push to newData
        if (format === "words") {
            newData.push([word, rows.length]);
        } else {
            // else aggregate by column and push to newData
            var row = [word];
            for (var j = 1; j < range[0].length; j++) {
                var sum = 0;
                for (var k = 0; k < rows.length; k++) {
                    // if the row is empty, replace with 0    
                    if (rows[k][j] === "") {
                        rows[k][j] = 0;
                    }
                    // sum rows 2-3
                    if (j == 1 || j == 2) {
                        sum += rows[k][j];
                    } else {
                        // average rows 4-5 
                        sum += rows[k][j] / rows.length;
                    }
                }
                row.push(sum);
            }
            newData.push(row);
        }
    }

    // if the format is words, sort by the count of words
    if (format === "words") {
        newData.sort(function(a, b) {
            return b[1] - a[1]
        });

        // add header row
        newData.unshift(["Words", "Count"]);


    } else {
        // else sort by specified gsc column 
        switch (sort) {
            case "clicks":
                newData.sort(function(a, b) {
                    return b[1] - a[1]
                });
                break;
            case "impressions":
                newData.sort(function(a, b) {
                    return b[2] - a[2]
                });
                break;
            case "ctr":
                newData.sort(function(a, b) {
                    return b[3] - a[3]
                });
                break;
            case "position":
                newData.sort(function(a, b) {
                    return a[4] - b[4]
                });
                break;
        }
        // add header row
        newData.unshift(["Words", "Clicks", "Impressions", "CTR", "Avg. Position"]);
    }
    return newData;
}

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

=groupbywords(A:E,2,"gsc","clicks")

Replace A:E with a range of your own Google Search Console Query data.

Replace 2 with the number of words you want to group by.

Replace “gsc” with “words” if you want to format by word frequency.

Replace “clicks” with another column from your Google Search Console data you want to sort by. The sort options are:

  1. clicks (DESC)
  2. impressions (DESC)
  3. ctr (DESC)
  4. position (ASC)

Group Ahrefs exports and related keywords in Keywords in Sheets PRO

We’ve added some more formatting options if you’re a Keywords in Sheets PRO customer. You can group Ahrefs Keyword Explorer & Site Explorer -> Organic keywords exports.

Ahrefs Keyword Explorer

=groupbywords(A:E,2,"ahrefs keywords","volume")

Ahrefs Site Explorer

=groupbywords(A:E,2,"ahrefs site","volume")

Related Keywords

=groupbywords(A:E,2,"related keywords","volume")

Make to sure select the full range starting from the keywords column (Ahrefs keyword explorer includes an ID column, don’t select it).

We’ll also be adding the group by words function across all our Keywords in Sheets functions very soon.

Stopwords is currently only in english. We’ll be adding stopwords in all other languages very soon too.


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 👇


More Scripts

Submit your response

Your email address will not be published. Required fields are marked *