26 January 2024

How to Check Broken Links Using Google Sheets

By Ronald Smith

Checking for broken links can feel like a never-ending task, especially when you have a long list of website addresses. It’s not practical to manually enter each URL into a web browser to find out if it’s working or not. But fear not, because I’m about to show you a free and nearly automatic way to tackle this issue using Google Sheets.

By the time you’re done reading this guide, you’ll have your very own Google Sheet that allows you to list as many URLs as you want in one column. The column right next to it will display the HTTP status of each URL. This will give you a clear picture of whether the page is accessible, redirected, broken, or something else entirely.

Step 1: Set Up a New Google Sheet

To get started, create a new Google Sheet and either import your list of links or add them manually. Make sure to organize them in a column labeled “URL,” and create another column labeled “Status,” just like this:

Step 2: Let’s Open Google Apps Script!

To get started, click on the “Extensions” tab in the toolbar at the top of your Google Sheet. From here, select “App Scripts” to open up the Google Apps Script editor. This is where the real magic happens! You’ll be able to create your very own custom functions without needing any coding skills whatsoever!

Step 3: Time to Paste the Code!

Now it’s time to bring in the code that will make everything work smoothly. All you have to do is copy the code provided below. Then, simply paste it into the editor. Finally, give your project a catchy name like “Get HTTP Status”. You’re doing great!

function getHTTPStatus(uri) {

var response_code;

try {

response_code = UrlFetchApp.fetch(uri).getResponseCode().toString();

} catch(error) {

response_code = error.toString().match(/ returned code (\d\d\d)\./)[1];

} finally {

return response_code;

}

}

Step 4: Save and Close

Just click on the little Disk icon to save your project. Then you can close the Apps Script browser window.

If you’re wondering what the function does and want to understand it better, I’ll explain it to you below.

What the Function Does:

This script is called getHTTPStatus. It’s made for getting response codes from web addresses (URLs). It’s a simple tool that helps you see the status of a URL and the type of response it gives.

Here’s how it works:

  1. Setting Up: It starts by preparing to capture the response code from the URL.
  2. Try and Catch Errors: It uses a method to handle any possible errors that might come up while checking the URL.
    • Try: It tries to connect to the URL. If it succeeds, it gets and saves the status code.

    In this section, I’ll explain how the getHTTPStatus function works and how it can help you check the status of a URL and identify any potential issues based on the response code.

    First, let’s break it down step by step:

    1. Step 1: Establish a connection: The first thing the function does is establish a connection with the URL you provide. It tries to access the webpage and retrieve the response.
    2. Step 2: Check for connection errors: If there’s a problem with the connection, the function will catch the error message and extract the response code from it. This code will help you identify the specific issue that occurred.
    3. Step 3: Handle response codes: The function then checks the response code to determine the status of the URL. Different response codes indicate different outcomes. For example, a code of 200 means the URL is reachable and accessible.
    4. Step 4: Store the response code: If a connection error occurs, the function extracts the response code from the error message and stores it for later use.
    5. Step 5: Finish up: Ultimately, the function returns the response code it obtained, regardless of the previous outcomes.

    In essence, the getHTTPStatus function is a tool that allows you to check a URL’s status and identify potential issues based on the response code.

    Step 5: Testing the Function

    Now that we have the function ready, it’s time to test it. To do this, go to the cell next to the first URL and enter the following formula:

    =getHTTPStatus(A2)

    If the URL is reachable, you should see a return value of 200. This means that the access was successful.

    If you want to apply this to other URLs, you can extend the formula to additional cells. Simply drag the corner of the cell downwards, as shown here:

    If you’re looking for a quick way to fill in cells in Google Sheets with a formula, you can simply double-click on the bottom-right corner of the cell. This action will automatically populate the remaining cells with the formula.

    My Final Thoughts

    So there you have it: a straightforward setup in Google Sheets that allows you to effortlessly check the HTTP status of multiple URLs.

    In conclusion, here’s a brief summary of common HTTP status codes and what they mean. For more detailed information on HTTP codes, take a look at our articles on The ABCs of HTTP Status Codes.

    • 200: The request was successful.
    • 301: The page has permanently moved to a new URL.
    • 400: The request was invalid due to incorrect syntax.
    • 401: You need to log in to access the page.
    • 403: Access to the page is denied.
    • 404: Uh-oh! It looks like the page doesn’t exist. Sorry about that.
    • 500: Oops! There seems to be a general error on the server. We’re working on fixing it.
    • 503: Hold tight! The server is temporarily unavailable, probably because it’s overloaded or undergoing maintenance. We’ll be back up and running soon.