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!
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 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:
I usually also apply conditional formatting to highlight the ones that are 'ok' (green), 'error' (red), or a 'warning' ('yellow').
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
.
/**
* 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' Tools > Script editor
and you're good to go!
If you think this was useful to you, it might be for others also. Feel free to share: