Step-by-Step Guide to Checking URL Status Codes in Google Sheets

There are two ways you can check status codes within Google Sheets:

Option 1
Easy formula. Reference any cell containing a URL and instantly retrieve its status code.

Option 2
Ready-to-use Google Sheets template. Has custom formula and even more capabilities.

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

Open Google Sheets: Launch the Google Sheet where you want the functionality.
Go to Extensions: In the top menu, click on Extensions > Apps Script.
Blank Script: You’ll be redirected to a blank script page.
Paste Code (found below).
1. Make sure you paste over all of the auto generated data.
2. Breakdown of the script found here
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:

Insert a URL: In a cell, type or paste the URL you want to check.
1. Example: A1 = www.carricoseo.com
Call the Function: In a new cell (let’s say B1), type =getStatusCode(A1), assuming A1 contains the URL.
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?

Quick Tips & Notes

You can drag the formula to check all your URLs in synchronous order.
Be aware that Google Apps Script has API call limits.
- This is why I added the caching in the script. Note: You can learn how to remove it by clicking that link.

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.

Quick Tips & Notes

Is there a limit to the number of URLs I can check?
- Paste hundreds of URLs, and the script will handle them all in one go.
- Google Apps Script has daily quotas, which you should be mindful of.
While URLs must go in Column A (starting in A3) and Status Codes in Column B (starting in B3), feel free to use other columns for notes or organization – they won’t interfere with the script.
Status codes are cached for 6 hours to improve performance and minimize API calls. If needed, you can clear the cache to fetch updated results.
If a URL’s status code might have changed within the last 6 hours or you’re running tests, go to the “Get Status Codes” menu and select “Clear Cache“. This removes stored data for the URLs, ensuring fresh results.
To remove all content from rows 3 and below, go to the “Get Status Codes” menu and select “Clear Sheet“. The sheet will instantly reset from row 3 and below.

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

Script Breakdown

Here’s a line-by-line explanation of the provided script, why each piece is necessary, and what it does

Function Declaration: function getStatusCode(url) { … }
- This means we’re creating a function called getStatusCode that will use a URL as its input.
Trim the URL: var url_trimmed = url.trim();
- This line cleans up the URL by removing any unnecessary spaces at the beginning or end.
Check for Empty URL: if (url_trimmed === ”) { return ”; }
- If the URL is empty after trimming, the function exits immediately and returns an empty string.
Caching Setup: var cache = CacheService.getScriptCache();
- This sets up a “cache,” a place to store things temporarily. The idea is to remember status codes we’ve already checked so we don’t have to ask the website again.
Creating a Cache Key: var cacheKey = ‘status-‘ + url_trimmed;
- This creates a unique name to store our status code in the cache.
Check Cache: var result = cache.get(cacheKey);
- The code first checks if we’ve already looked up the status code for this URL and stored it in the cache.
If Not in Cache, Fetch URL:
- If the result isn’t in the cache, it uses UrlFetchApp.fetch(url_trimmed, options); to get the status code from the website.
Storing in Cache: cache.put(cacheKey, result, 21600);
- After getting the status code, it saves that into the cache for 6 hours
Return the Result: return result;
- Finally, the function gives you the status code it found.

(back to the script section)

What is “Google Apps Script?”

Google Apps Script is a powerful tool that lets you add functionality to Google Sheets, Docs, and other G Suite apps. Written in JavaScript, it provides a way to automate, extend, and integrate your Google services.

Why Use It for Google Sheets:

Automation: Automatically update data, send emails, or perform other repetitive tasks.
Custom Functions: Write your formulas to handle specific tasks tailored to your needs.
API Calls: You can interact with other services and get data directly into your Sheet.

(back to the top of page)

A Note on Caching in the Script

What is Caching?

Caching is like having a small notepad where you jot down answers to difficult questions. The next time the same question comes up, you simply look at your notepad instead of doing the hard work all over again. In our script, the CacheService serves as this notepad, storing the status codes of URLs we’ve already checked.

Why is Caching Used?

Fetching a status code from a website can take a few seconds. While that doesn’t sound like much, those seconds can add up if you’re checking lots of URLs. With caching, you can fetch the status code once and store it for future use. This makes your script faster and minimizes the number of requests you send to the website, which is generally a good practice. Our script stores this information for 6 hours—you won’t have to “ask” the website again during that time.

How to Remove the Caching feature

If you prefer to fetch fresh data every single time, removing caching is simple. Just make the following changes to your script:

Remove the below lines:
- var cache = CacheService.getScriptCache();
- var cacheKey = ‘status-‘ + url_trimmed;
- var result = cache.get(cacheKey);
- cache.put(cacheKey, result, 21600);
Replace the if (!result) condition with just if (true).

And BAM! Your script will now fetch the status code directly from the website every time you run it. Once again, be aware that Google Apps Script has API call limits.

Changelog

January 10th, 2025

# Script Updates
– Updated `getStatusCode` function: – Replaced `CacheService` with `PropertiesService` for persistent caching.

– Enhanced error handling with detailed messages for:

– DNS errors, timeouts, unsupported URL formats, and more.

– Added a 5-second connection timeout for URL fetch operations.

– Included validation for URL length (limit: 250 characters) and improved handling of empty/invalid inputs.

#New Feature: Google Sheets Template

– Includes `getStatusCode` functionality.

– Introduces a **”Get Status Codes”** menu with the following sub-menu options: – **Retrieve Status Codes**: Fetch HTTP status codes for URLs starting in cell A3. – **Clear Cache**: Clears stored status codes from script properties.

– **Clear Sheet**: Deletes all URLs and Status Codes from the active sheet.

– **Plug-and-Play**: Paste URLs starting in cell A3, use the custom menu to retrieve status codes with ease. —

February 14th, 2025

Step-by-Step Guide to Checking URL Status Codes in Google Sheets

Option 1 vs Option 2

Option 1: Custom Formula

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

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

Quick Tips & Notes

Option 2: Template

Step 1: Make A Copy

Step 2: Paste URLs​

Step 3: Run the Script​

Quick Tips & Notes

Troubleshooting

Additional Information​

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

Step 2: Paste URLs

Step 3: Run the Script

Additional Information