Google Sheets is my best buddy when doing SEO audit and analysis, but sometimes it gets tricky if you need to perform a more complicated analysis.
And that’s when I stumbled upon Google App Script and it has helped me streamline processes that otherwise would take hours (or even days) to manage manually.
This is my cheat sheet of Google Apps Script for SEO.
What is Google Apps Script?
Google Apps Script allows you to automate tasks and extend the functionality of Google Sheets. Apps Script uses JavaScript, which makes it a great tool for automating repetitive tasks, integrating APIs, and building custom workflows.
As I’m not a coding person, I turn to ChatGPT for help a lot when building the Apps Script I need and tweaking it when necessary. If the first result is not working, provide a more specific instruction or even cell reference to fine-tune your script.
How to use Google Apps Script
A quick step-by-step guide on how to create Google Apps Script:
- Open Google Sheets .
- Go to Extensions > Apps Script to open the Script Editor.
- Write your script (e.g., to automate tasks or manipulate data).
- Save your script by clicking the floppy disk icon or using File > Save.
- Click the Run button (▶) to execute the script.
- Grant authorization if prompted (first time running).
Custom Function in Google Sheets for SEO Audit
A custom function in Google Apps Script is a user-defined function that you can create to perform specific tasks in Google Sheets (or other Google Workspace tools). It operates similarly to Google Sheets formula like SUM() or AVERAGE(), but you create it to handle custom logic or processes.
1. Check HTTP Status Code
Checking HTTP status codes help monitor website health, ensuring proper redirects, and diagnosing issues like broken links or server failures.
/**
* Get HTTP Status Code.
*
* @param {string} url - URL to check.
* @customfunction
*/
function httpstatuscode(url) {
var result = [];
url.toString().trim();
var options = {
'muteHttpExceptions': true,
'followRedirects': false,
};
try {
result.push(UrlFetchApp.fetch(url, options).getResponseCode());
}
catch (error) {
result.push(error.toString());
} finally {
return result;
}
}2. Decode URL
If you have a multilingual site that uses non-Latin languages for some regions, you probably have seen an encoded URL. When you are extracting URLs from sitemap created by Yoast, you will get a list of cryptic URLs that leave you very confused.
This function decodes the UTF-8 encoded URLs back into their original form, making it easier to interpret and analyse URL structures, identify keywords, and ensure URLs are clean and accessible.
Of course, you still need to speak the language to understand it. For example, I can understand a decoded Chinese URL, but not a Thai one. Still it makes my Google sheets look cleaner and nicer.
/**
* Get a clean URL from UTF-8 encoded URL,
*
* @param {string} encodedString - The encoded URL.
* @customfunction
*/
function DecodeURL(encodedString) {
return decodeURIComponent(encodedString.replace(/\+/g, ' '));
}3. Extract subfolder
This is a function I used to break down a website’s URL and identify the subfolders, so I can better understand the site’s architecture. It makes it easier to filter and analyse a site by different sections – such as multilingual subfolders or blog sections. Instead of filter the URL by conditions, I can directly extract the subfolder name in a new column and filter them.
/**
* Extract a specific subfolder from a given URL.
*
* @param {string} url - The URL from which to extract the subfolder.
* @param {number} level - The level of the subfolder to extract (1-based index).
* @param {boolean} trailingslash - Whether to include the trailing slash in the returned subfolder. Default is FALSE.
* @return {string} The extracted subfolder or an error message if the level is invalid.
* @customfunction
*/
function extractSubfolder(url, level, trailingslash = false) {
// Step 1: Remove the protocol part (http:// or https://) from the URL
// This leaves us with just the domain and the path.
var urlParts = url.replace(/https?:\/\//, '').split('/');
// Step 2: Remove the domain part from the array of URL parts
// After this, urlParts will contain only the subfolders and the final path.
urlParts.shift();
// Step 3: Validate the level parameter
// Check if the requested level is within the valid range of subfolders available.
if (level < 1 || level > urlParts.length) {
return 'Invalid level specified'; // Return an error message if the level is out of range.
}
// Step 4: Extract the desired subfolder based on the specified level
// Subtract 1 from the level because arrays are 0-indexed, but levels are 1-indexed.
var subfolder = urlParts[level - 1];
// Step 5: Add a trailing slash if required
// If trailingslash is true, append a "/" to the subfolder.
return trailingslash ? subfolder + '/' : subfolder;
}4. Get RedirectURL destination
This function checks the original URL to see if it leads to a different, final destination due to a redirect (e.g., 301 or 302 redirects). This helps identify the actual target URL, which is crucial when managing link structures or analysing broken and redirected links.
/**
* Retrieves the final destination URL after following any redirects from the given URL.
*
* @param {string} url The initial URL to check for redirection.
* @return {string} The final destination URL or a message indicating no redirection occurred.
* @customfunction
*/
function getRedirectDestination(url) {
if (!url) {
return "Error: No URL provided.";
}
var options = {
'muteHttpExceptions': true,
'followRedirects': false
};
/**
* Recursively follows the redirect chain and returns the final URL.
*
* @param {string} url The current URL being checked.
* @param {string} originalUrl The original URL (for comparison).
* @return {string} The final destination URL or an indication of no redirection.
*/
function RedirectedUrl(url, originalUrl) {
try {
var response = UrlFetchApp.fetch(url, options);
// Check if there is a redirect (status code 3xx)
if (response.getResponseCode() >= 300 && response.getResponseCode() < 400) {
var headers = response.getHeaders();
var location = headers['Location'] || headers['location'];
if (location) {
// If the redirect URL is relative, make it absolute
if (location.startsWith('/')) {
var baseUrl = url.match(/^[a-zA-Z]+:\/\/[^\/]+/)[0];
location = baseUrl + location;
}
// Recursively follow redirects
return RedirectedUrl(location, originalUrl);
}
}
// If there is no redirect, return the original URL
return url === originalUrl ? "No redirection occurred." : url;
} catch (error) {
return 'Error: ' + error.message;
}
}
return RedirectedUrl(url, url);
}
5. Check canonical in bulk
This function is useful to verify whether the canonical tags on a site are correctly implemented across numerous pages, avoiding issues like duplicate content – so you don’t need to go to each page.
function checkCanonical(url) {
try {
var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
var html = response.getContentText();
// Use a more robust regular expression to capture the canonical tag
var canonicalMatch = html.match(/<link\s+rel=["']canonical["']\s+href=["']([^"']+)["']\s*\/?>/i);
if (canonicalMatch && canonicalMatch[1]) {
var canonicalUrl = canonicalMatch[1].trim();
// Normalize both URLs to remove trailing slashes and compare
var normalizedUrl = url.replace(/\/+$/, '');
var normalizedCanonicalUrl = canonicalUrl.replace(/\/+$/, '');
if (normalizedCanonicalUrl === normalizedUrl) {
return "Canonical matches URL";
} else {
return "Canonical does not match URL: " + canonicalUrl;
}
} else {
return "No canonical tag found";
}
} catch (e) {
return "Error fetching URL: " + e.message;
}
}6. Fetch meta title and meta description
This is just an alternative way to extract meta title and meta description other than using IMPORTXML.
Meta title
/**
* Gets the meta title of a given URL.
*
* @param {string} url The URL of the webpage.
* @return {string} The meta title of the webpage, or an error message if it fails.
* @customfunction
*/
function metaTitle(url) {
try {
// Make a GET request to the provided URL
var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
// Check if the request was successful
if (response.getResponseCode() !== 200) {
return 'Error: Unable to fetch the URL.';
}
// Get the response content (HTML of the page)
var html = response.getContentText();
// Use a regex to extract the title from the HTML content
var titleMatch = html.match(/<title>([\s\S]*?)<\/title>/i);
// Check if a title was found
if (titleMatch && titleMatch.length > 1) {
return titleMatch[1];
} else {
return 'Error: Title tag not found.';
}
} catch (error) {
return 'Error: ' + error.message;
}
}Meta description
/**
* Gets the meta description of a given URL.
*
* @param {string} url The URL of the webpage.
* @return {string} The meta description of the webpage, or an error message if it fails.
* @customfunction
*/
function metaDescription(url) {
try {
// Make a GET request to the provided URL
var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
// Check if the request was successful
if (response.getResponseCode() !== 200) {
return 'Error: Unable to fetch the URL.';
}
// Get the response content (HTML of the page)
var html = response.getContentText();
// Use a regex to extract the meta description from the HTML content
var descriptionMatch = html.match(/<meta[^>]*name=["']description["'][^>]*content=["']([^"']+)["'][^>]*>/i);
// Check if a description meta tag was found
if (descriptionMatch && descriptionMatch.length > 1) {
return descriptionMatch[1];
} else {
return 'Error: Meta description not found.';
}
} catch (error) {
return 'Error: ' + error.message;
}
}7. Get localized URL
The localizedURL custom function fetches a webpage, scans its HTML content for <link> tags with rel=”alternate” attributes, and extracts the localized URL for a specified hreflang code. This allows users to quickly find the corresponding localized version of a webpage.
This is useful when managing multilingual website. It helps me automate the process of checking hreflang attributes and extract their corresponding URLs.
function localizedURL(url, hreflangCode) {
if (!url || !hreflangCode) {
return "Invalid URL or hreflang code";
}
try {
// Fetch the HTML content
const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
const responseCode = response.getResponseCode();
if (responseCode !== 200) {
return `HTTP Error: ${responseCode}`;
}
const html = response.getContentText();
// Enhanced regex to match <link> tags with any attribute order and extra attributes
const regex = new RegExp(
`<link\\s+[^>]*?rel=["']alternate["'][^>]*?hreflang=["']${hreflangCode}["'][^>]*?href=["'](.*?)["']`,
'i'
);
const match = html.match(regex);
if (match && match[1]) {
return match[1]; // Return the localized URL
} else {
return `No hreflang "${hreflangCode}" found in the HTML`;
}
} catch (e) {
return `Error fetching URL: ${e.message}`;
}
}Wrapping It Up
Using Google Apps Script for SEO has been a total game-changer for me. I don’t need to spend hours with some repetitive tasks, and I’m able to focus more on strategy and analysis.
If you’re managing SEO for your website or clients and want to save time while increasing efficiency, I highly recommend diving into Google Apps Script (with the help of ChatGPT). It may seem intimidating at first, but trust me, once you get the hang of it, the possibilities are endless!
