How to Check Broken Links Using Google Sheets

Checking for broken links can be a tedious task, especially when you have a long list of URLs. Manually putting each URL into a browser to check isn’t practical. In this post, I’m going to show you how to do it for free and almost automatically, using Google Sheets.

By the end of this tutorial, you’ll have a Google Sheet that lets you list as many URLs as you want in one column. The column next to it will show you the HTTP status of each URL. This will help you understand if the page is accessible, redirected, broken, and so on.

Step 1: Create a New Google Sheet

Create a new Google Sheet and import your list of links, or you can manually add them. Organize them in a column labeled “URL” and another labeled “Status,” like this:

Step 2: Open Google Apps Script

In the Google Sheet’s toolbar at the top, go to Extensions > App Scripts.

This will open the Google Apps Script editor, where you can create your own custom functions. We’re going to create a function that takes a URL as input and returns its HTTP status. Don’t worry, no coding skills are needed for this part.

Step 3: Paste the Code

Copy the code provided below, paste it into the editor, and give your project a name, like “Get HTTP Status.“

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

Click on the small Disk icon to save your project. You can then close the Apps Script browser window.

If you’re curious about what the function does and want to understand it better, see below for an explanation.

What the Function Does:

This script, named getHTTPStatus, is designed to retrieve response codes from web addresses (URLs). It’s a straightforward tool for understanding the status of a URL and the type of response it provides.

Here’s a breakdown of its functions:

1. Setting Up: Initializes the process to capture the response code from the URL.

2. Try and Catch Errors: Employs a method to handle potential errors during URL checks.

   • Try: Attempts to connect to the URL. If successful, retrieves and stores the status code.

   • Catch: If a connection error occurs, this section extracts the response code from the error message and stores it.

3. Finish Up: Ultimately, returns the response code it obtained, regardless of the previous outcomes.

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

Step 5: Testing the Function

With the function now ready, it’s time to test it. Navigate to the cell next to the first URL and input the following formula:

=getHTTPStatus(A2)

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

To apply this to other URLs, you can extend the formula to additional cells. This can be done by dragging the corner of the cell downwards, as shown here:

Alternatively, a quick method is to double-click on the bottom-right corner of the cell. This action will automatically fill the remaining cells with the formula.

Ending Thoughts

And there you have it: a simple Google Sheets setup to check the HTTP status of multiple URLs without manual effort.

To conclude, here’s a brief overview of common HTTP status codes and their meanings. For more details on HTTP codes, explore our posts on The ABCs of HTTP Status Codes.

• 200: Request successful.
• 301: The page has permanently moved to a new URL.
• 400: Invalid request due to incorrect syntax.
• 401: Login required to access the page.
• 403: Access to the page is denied.
• 404: The page doesn’t exist.
• 500: General error on the server.
• 503: Server temporarily unavailable, often due to overload or maintenance.