Bulk meta title & description SEO-feedback in Google Sheets
How to get textual and statistical SEO-feedback for many meta titles & descriptions in Google Sheets. Use this in conjunction with conditional formatting to also provide visual feedback. Et voila: a pretty powerful way to automagically check many, many title tags and descriptions at once!
Let me first grab your attention by showing what this article is about:
That's right: automated checking of many, many meta titles & descriptions, displayed visually with nice colors. Here is how you can do that too.
For free, that is!
The kinda-ok-way: built-in functions
Of course, you could use some function nesting with the built-in Google Sheets formulas. For example, if you feel that any title-tag that is longer than 55 characters is too long, you can check it quite easily with this (in this example, the title-tag text is in cell A1):
=if(len(A1) > 55;"Too long";"Not too long")You can even apply this automagically to many title-tags (thousands, if you want), by using =arrayformula(). But what if you want more? What if you also want to check if a title-tag is too short? Or if it's - almost - too long, but not just yet? And if it's too long or too short: by how much?
I feel you. That is why I created this custom function... Well not really. I needed it myself some time ago, and decided to share afterwards. That's different, but who cares. Onwards!
The ninja-way: $(this) custom function
The following custom function expects a range of lengths of a given text type (for now: "title" or "description"). This means that you need to give it more than 1 cell as input (making it work for a single cell is something i didn't need, but you could change the code to have it accept both kinds of input if you want).
Let me explain.
If you have a column in your spreadsheet that holds a lot of title-tags (let's call this column B), and you have another column next to it, that holds the lengths of these titles (column C), then you call the function like this (in cell D1):
=metaTextFeedback(C:C;"title")Or, if you like to nest functions, you just input all titles, but calculate their lengths on the fly, like this:
=metaTextFeedback(len(B:B);"title")The function will apply a set of rules on the lengths. Which rules it uses is decided by the value of the second parameter: "title" or "description". It will output specific feedback about every single length:
- if it's too long, too short, or 'almost' too long
- and if so, provide a count of how many characters should be removed or added
- it will also make a distinction between an 'error' (too long/short) and a 'warning' ('almost')
Add conditional highlighting
I usually also apply conditional formatting to highlight the ones that are 'ok' (green), 'error' (red), or a 'warning' ('yellow'):
You can quickly tell if you look at the image at the top of this article: I've got work to do ;)
P.s.: beware of false positives
Make sure that there actually is a text to give feedback about. I usually use this with a list of URLs in another column. Then I can apply this function:
=arrayformula(if(isurl(A:A);metaTextFeedback(C:C;"title");""))This makes sure that if there is a (valid) URL in column A, the function will check if the length of the title of that page is ok, and it will ignore all rows that do not have a valid URL in column A.
The code
/**
* Provide textual feedback about the length of a meta text (title or description)
*
* @param {A:A} range with numbers (lengths of titles, for example)
* @param Rules Which rules to apply to this length
*
* @return feedback for given meta text length
*
* @customfunction
*/
function metaTextFeedback(range, rules) {
Utilities.sleep(200); // to prevent overloading if used many times
var result = [];
if(range.map) {
for(var i in range) {
var l = range[i];
if(rules == 'title') {
// meta title: max 55 chars, optimum length between 30 & 45. Between 45 & 55 is ok, but maybe too long for mobile
if (l >= 0 && l < 30) {
result.push("Title tag error: " + (30 - l) + " chars short");
} else if (l >= 30 && l <= 55) {
result.push('Ok');
} else if (l > 55 && l <= 60) {
result.push("Title tag warning: almost too long");
} else if (l > 60) {
result.push("Title tag error: " + (l - 60) + " chars too many");
} else {
result.push("Title tag error: don't know :(");
}
}
else if(rules == 'description') {
// meta decription: max 155 chars, optimum length between 120 & 135. Between 135 & 155 is ok, but maybe too long for mobile
if (l >= 0 && l < 120) {
result.push("Meta description error: " + (120 - l) + " chars short");
} else if (l >= 120 && l < 145) {
result.push('Ok');
} else if (l >= 145 && l <= 158) {
result.push("Meta description warning: almost too long");
} else if (l > 145) {
result.push("Meta description error: " + (l - 158) + " chars too many");
} else {
result.push("Meta description error: don't know :(");
}
}
} // endfor
}
else {
result.push('Gimme an array!');
}
return result;
}Copypaste this function into Google Sheets' script editor and you're good to go!
What's next?
Not much really. But if you think this was useful to you, it might be for others also. Feel free to share:
Email, LinkedIn, WhatsApp, or click / push the link below to copy it: