Category: Scripts

The Two-Step Process

1. Add the provided script to Google Apps Script. (What is Google Apps Script?)
2. Apply the custom formula to your data in Google Sheets.

Step 1: Injecting the Script into Google Apps Script

1. Open the Google Sheet where the functionality is needed.
2. Click on Extensions > Apps Script from the top menu. You’ll land on a blank script page ready for your code.
3. Copy and paste the script below. (Make sure to replace any existing code if necessary.)
   • For an explanation of how the script works, see the breakdown section below.
4. Save your work via the save icon. You can name your project (if you want) and exit the editor.

function unescapeHTML(str) {
  var doc = XmlService.parse('' + str + '');
  var decodedStr = doc.getRootElement().getChild('body').getText();
return decodedStr.replace(/&/g, '&')
                .replace(/</g, '<')
                .replace(/>/g, '>')
                .replace(/"/g, '"')
                .replace(/'/g, "'")
                .replace(/•/g, '•');
}
function getPageTitle(url) {
  if (!url) return "Pending URL";
  try {
    var html = UrlFetchApp.fetch(url).getContentText();
    var titleMatch = html.match(/<title>\s*(.*?)\s*</title>/i);
    return titleMatch ? unescapeHTML(titleMatch[1]) : "No title found";
  } catch (e) {
    return "Error fetching URL";
  }
}

Step 2: Deploying the Script to Extract Page Titles and Decode HTML

1. Now that you’re in the Google Sheet, simply type or paste in your URL in a cell.
   1. Example: A1 = “https://www.carricoseo.com”
2. Type =getPageTitle(A1) to get the page title.
   1. Example: B1 = “=getPageTitle(A1)”
3. The cell will display the extracted page title or the decoded HTML string.
4. With this you can now effortlessly extract data for SEO analysis or content management purposes!

Quick Tips & Notes

• The functions (formulas) can be extended to multiple cells for batch operations. Aka: Drag and drop this down a list of cells!
• Usage Limitations: Google Apps Script has daily quotas. As long as you’re not doing this in significantly large bulk batches, you should have nothing to worry about.

Lastly, for those who want to expand their Google Sheets capabilities even further, check out my previous guide on How to Check HTTP Status Codes in Google Sheets. This tutorial compliments this one by showing you another useful application of Google Apps Script + Google Sheets to enhance your SEO toolkit. Appreciate this free resource? If you think this script might save you some time, feel free to buy me a coffee – or beer. Have a great day!

Troubleshooting

Encountering errors with your script? Here’s a quick guide to identify and fix common issues:

• Error fetching URL: This may occur if the URL is incorrect, the server is not responding, or permissions are restricted. Double-check the URL and your network status.
• Dynamic Content: If the page content, including the title, is generated dynamically via JavaScript, `UrlFetchApp` cannot execute the script to retrieve this content. Inspect the page to see if the title tag is set statically in the HTML.
• Google Apps Script Quotas: You might hit daily quota limits for URL Fetch calls. Review Google’s quota limits to ensure you haven’t exceeded them.
• Script Errors: Syntax errors or incorrect range references in your script can cause it to fail. Check the code for any typos or logical errors.

Additional Information

What is Google Apps Script?

Google Apps Script is a robust scripting platform powered by JavaScript that enables automation and extension of Google Sheets, Docs, and other G Suite applications. If you’re not using it, especially with the advent of AI, you should be!

Dissecting the Script

Here’s a detailed breakdown of the script’s functionality, explaining the purpose and mechanics of each section.

function unescapeHTML(str) { … }

• This converts HTML entities into their corresponding characters.
  • Example: When page title use special characters such as the “&” sign, typically it will return the value of that sign, which is “&amp”. Instead of our return values include these values, this function returns just the actual sign (“&”).

function getPageTitle(url) { … }

• Title Retrieval Logic: var titleMatch = html.match(//i);
  • This extracts the title from a webpage’s HTML content.
• Error Handling: try { … } catch (e) { … }
  • This structure ensures graceful handling of any exceptions during URL fetching.

Dissection Glossary

• Regular Expression (Regex): A sequence of characters that forms a search pattern. In programming, regex is used for string searching and manipulation. For example, in the `getPageTitle` function, a regex is used to find and extract the content within HTML title tags.
• HTML Entities: Special characters used in HTML coding that represent characters not available on a standard keyboard, such as < for the less-than sign. These entities prevent conflicts with HTML syntax, which uses similar characters for tags and attributes.

(back to the script section)

We’ve all been there… One second an important URL is up and running like normal, the next you find out it’s a 404! This has likely cost many companies significant revenue as well as a lack of trust when site owners find out before you do.

What if I told you that Google Sheets isn’t just for number-crunching – it’s a freaking lifestyle. And Google Sheets is here to save the day.

You should be able to check the https response status code of any URL you have in a Google Sheet with a simple “=getStatusCode(cell)” formula, however you can’t straight out of the box.

That’s why you’re here and that’s what I’m about to show you how to do. And here’s the kicker: it’ll take less than 5 minutes to set up (shave that to 2 if you’re nifty with Ctrl + C & V). 

Stick with me, and I’ll drop you off at the corner of Efficiency and Sheet Street. Ok enough with trying to be funny, let’s get to it:). 

Option 1 vs Option 2

Option 1: Requires adding a Google Apps Script (copy + paste code found below)

• Pros: Add to any Google Sheet of your choosing.
• Cons: Some people have code phobia… (if you’re an SEO, you should get over this quickly!!)

Option 2: Uses a pre-made Google Sheet template

• Pros: Literally just copy and paste your URLs and then press a button.
• Cons: Not much flexibility.

Option 1: Custom Formula

Step 1: Add the script to Google Apps Script within a Google Sheet

1. Open Google Sheets: Launch the Google Sheet where you want the functionality.
2. Go to Extensions: In the top menu, click on Extensions > Apps Script.
3. Blank Script: You’ll be redirected to a blank script page.
4. Paste Code (found below).
   1. Make sure you paste over all of the auto generated data.
   2. Breakdown of the script found here
5. Save and Close: Click on File > Save, and then close the editor.

function getStatusCode(url) {
  // Trim any leading/trailing whitespace from the URL
  var url_trimmed = url.trim();
  // Handle empty URLs
  if (url_trimmed === '') {
    return 'Error: Empty URL';
  }
  // Get the script cache for storing results
  var cache = CacheService.getScriptCache();
  var cacheKey = 'status-' + url_trimmed;
  try {
    // Check if the cache key is too long (limit is 250 characters)
    if (cacheKey.length > 250) {
      return 'Error: URL too long for caching';
    }
    // Check if the result is already in the cache
    var cachedData = cache.get(cacheKey);
    if (cachedData) {
      return cachedData; // Return the cached status code or error message
    }
    try {
      // Configure options for UrlFetchApp
      var options = {
        'muteHttpExceptions': true, // Don't throw exceptions on HTTP errors
        'followRedirects': false, // Don't follow redirects
        'connectTimeout': 5000 // 5 seconds timeout
      };
      // Fetch the URL
      var response = UrlFetchApp.fetch(url_trimmed, options);
      var responseCode = response.getResponseCode();
      // Store the status code as a string
      var result = responseCode.toString();
      // Cache the result for 6 hours (21600 seconds)
      cache.put(cacheKey, result, 21600); // Cache the status code string
      // Return the status code
      return result;
    } catch (error) {
      // Enhanced Error Handling
      var errorMessage = 'Error: Unable to fetch URL';
      // Check for specific error types and provide more informative messages
      if (error.message.includes('DNS')) {
        errorMessage = 'Error: Hostname not found (DNS error)';
      } else if (error.message.includes('Timeout')) {
        errorMessage = 'Error: Connection timeout';
      } else if (error.message.includes('Unsupported')) {
        errorMessage = 'Error: Unsupported URL format';
      } else if (error.message.includes('Invalid argument')) {
        errorMessage = 'Error: Invalid URL';
      } else if (error.message.includes('Forbidden')) {
        errorMessage = 'Error: Access forbidden (403)';
      } else if (error.message.includes('Unauthorized')) {
        errorMessage = 'Error: Unauthorized access (401)';
      } else if (error.message.includes('Not Found')) {
        errorMessage = 'Error: Page not found (404)';
      } else if (error.message.includes('Internal')) {
        errorMessage = 'Error: Internal server error (500)';
      } else {
        // Include part of the original error message for debugging
        errorMessage += ' - ' + error.message.substring(0, 100);
      }
      return errorMessage; // Return the error message
    }
  } catch (error) {
    // Handle cache-related errors
    return 'Error: Cache issue - ' + error.message;
  }
}

function getStatusCode(url) {
  // Trim any leading/trailing whitespace from the URL
  var url_trimmed = url.trim();

  // Handle empty URLs
  if (url_trimmed === '') {
    return 'Error: Empty URL';
  }

  // Get the script cache for storing results
  var cache = CacheService.getScriptCache();
  var cacheKey = 'status-' + url_trimmed;

  try {
    // Check if the cache key is too long (limit is 250 characters)
    if (cacheKey.length > 250) {
      return 'Error: URL too long for caching';
    }

    // Check if the result is already in the cache
    var cachedData = cache.get(cacheKey);

    if (cachedData) {
      return cachedData; // Return the cached status code or error message
    }

    try {
      // Configure options for UrlFetchApp
      var options = {
        'muteHttpExceptions': true, // Don't throw exceptions on HTTP errors
        'followRedirects': false, // Don't follow redirects
        'connectTimeout': 5000 // 5 seconds timeout
      };

      // Fetch the URL
      var response = UrlFetchApp.fetch(url_trimmed, options);
      var responseCode = response.getResponseCode();

      // Store the status code as a string
      var result = responseCode.toString();

      // Cache the result for 6 hours (21600 seconds)
      cache.put(cacheKey, result, 21600); // Cache the status code string

      // Return the status code
      return result;

    } catch (error) {
      // Enhanced Error Handling
      var errorMessage = 'Error: Unable to fetch URL';

      // Check for specific error types and provide more informative messages
      if (error.message.includes('DNS')) {
        errorMessage = 'Error: Hostname not found (DNS error)';
      } else if (error.message.includes('Timeout')) {
        errorMessage = 'Error: Connection timeout';
      } else if (error.message.includes('Unsupported')) {
        errorMessage = 'Error: Unsupported URL format';
      } else if (error.message.includes('Invalid argument')) {
        errorMessage = 'Error: Invalid URL';
      } else if (error.message.includes('Forbidden')) {
        errorMessage = 'Error: Access forbidden (403)';
      } else if (error.message.includes('Unauthorized')) {
        errorMessage = 'Error: Unauthorized access (401)';
      } else if (error.message.includes('Not Found')) {
        errorMessage = 'Error: Page not found (404)';
      } else if (error.message.includes('Internal')) {
        errorMessage = 'Error: Internal server error (500)';
      } else {
        // Include part of the original error message for debugging
        errorMessage += ' - ' + error.message.substring(0, 100);
      }

      return errorMessage; // Return the error message
    }

  } catch (error) {
    // Handle cache-related errors
    return 'Error: Cache issue - ' + error.message;
  }
}

(You can copy the above code by hovering your mouse over the top right of the code box and selecting “COPY”)

Step 2: Use the Formula to Check the Status Code of a URL​

Once the script is uploaded and saved to the Google Apps Script within the Google Sheet, you can now go back into the sheet and do the following:

1. Insert a URL: In a cell, type or paste the URL you want to check.
   1. Example: A1 = www.carricoseo.com 
2. Call the Function: In a new cell (let’s say B1), type =getStatusCode(A1), assuming A1 contains the URL.
3. View the Status Code: The cell will now display the URL’s HTTP status code.
   1. Example: B1 = 200

This script should pull any type of https status response code that is provided. Fun fact, there are over 60 status codes a server can respond with. Ever heard of a page having a 418 response?

Option 2: Template

Step 1: Make A Copy

Access the template via the below button. Once in the sheet, select File > “Make a Copy” to save it to your Google Drive.

You will be prompted with an warning message about an App Script. This is how the sheet operates. Proceed through the warnings.

Status Code Checker In Google Sheets:

Free template

Step 2: Paste URLs​

In your newly copied sheet, paste your list of URLs in Column A, starting at cell A3. Please note: each URL should be in its own row.
I recommend pasting without formatting (CTRL + SHIFT + V if on windows), as this could prevent errors.

Step 3: Run the Script​

At the top of the sheet, I’ve made a custom menu to make this extremely easy for you. In the toolbar, you’ll find a “Get Status Codes” menu. Click this.

From the dropdown, select Retrieve Status Codes.

Please note that the first time you run this script, it will ask you to authorize the script. Say yes, and run the script again.

And that’s it!

The script will process each URL one by one and display the HTTP status codes in Column B. It also has some robust error handling, in the event you run into anything crazy.

Troubleshooting

• No Status Code Appearing
  • Ensure your URLs are pasted correctly in Column A, starting at cell A3.
• Getting an Error Message
  • Make sure you’ve granted the necessary script permissions when prompted.
  • If it’s an error message in the Status column, then it’s an error with your URL! Which is a good thing, the script has done it’s job and identified an issue you should look into. Check the URL in your browser as a first step.
  • If issues persist, try clearing the cache and re-running the status retrieval.

That’s all folks!

If you liked this script, there are a ton of other scripts out there that can help you achieve great efficiency! This GitHub has a bunch of really cool Google Apps Scripts (more advanced stuff found here as well) or you can subscribe to my email list because I’ll be creating many more like this one in the near future!

Thanks for checking out one of my many free resources devoted to helping SEO and Google Ads professionals achieve a more efficient workflow and better organization. If you think this script might save you a bunch of time and money, feel free to buy me a coffee – or beer. Have a great day!

Additional Information​