Tip of the Week: Use Google Sheets to Check for Broken Links

 

 

Step 2: Create the Script That Checks Status Codes

Here’s where the magic happens.

In Google Sheets, go to Extensions→ Apps Script. Google will create a new Apps Script file and give you a placeholder function. Remove the placeholder function and name your file, and then paste in the initial status checker function.

The initial version of the function looks like this:

 

function getStatusCode(url){
   var response = UrlFetchApp.fetch(url);
   return response.getResponseCode();
}

 

 

 

What this does:
• Takes in a URL
• Fetches it
• Returns the HTTP status code

Codes to note:
200 = Good
404 = Not Found
3xx = Redirect
and so on.

However, this rough version has limits. Fetching only returns successful responses and follows redirects—meaning you’ll get misleading results or outright errors for anything that isn’t a clean 200.

So we improve it in step 5 - but first, let's test to make sure the rough version is working. 

 

Step 3: Test the Function in Your Sheet

 

On the right-hand side, click the blue “DEPLOY” button. When the window opens, click the settings icon next to “Select type” and choose "Web app".

Give your deployment a clear name, set the appropriate access permissions, and, if you feel like sharing this greatness today, make it available to your team.

 

 

Now that your function is saved, go back to your spreadsheet (but leave the function window open for later steps).

In the cell next to your first URL, enter:

 

=getStatusCode(A2)

 

 

 

 

 

But this early version has two problems:

1. It returns errors for codes outside the 2xx range, which interrupts workflow.

2. It follows redirects automatically—so a URL that returns 301 → 200 will misleadingly appear as 200 only.

So we refine the script to handle real-world behavior.

 

Step 4: Strengthen the Script to Capture Real Status Codes

Here’s the improved version that avoids false readings and handles redirect logic correctly:

function getStatusCode(url){
   var options = {
     'muteHttpExceptions': true,
     'followRedirects': false
   };
   var url_trimmed = url.trim();
   var response = UrlFetchApp.fetch(url_trimmed, options);
   return response.getResponseCode();
}

 

 

 

 

 

What changed and why:

• muteHttpExceptions: true — prevents the script from breaking when the server throws an error, allowing us to capture the status code instead.
• followRedirects: false — keeps redirects from being “hidden” behind a final 200 code.
• url.trim() — removes accidental spaces that can make a working URL look broken.

Once you save this version and test again, your sheet will correctly identify 200s, 404s, 503s, 301s, and more—all without throwing messy error messages.

This alone solves most use cases. But if you’re handling very large lists, there’s one more upgrade you’ll want.

 

Step 5: Prevent Hitting Google’s Daily Request Limits

If you run enough URLs, Google will eventually throw a quota error:

“Service invoked too many times for one day: urlfetch.”

To avoid that—and to speed up repeat checks—you can cache results. This prevents the script from fetching the same URL over and over.

Here’s the optimized version:

function getStatusCode(url) {
  var url_trimmed = url.trim();
  var cache = CacheService.getScriptCache();
  var result = cache.get(url_trimmed);
  
  if (!result) {
    var options = {
      'muteHttpExceptions': true,
      'followRedirects': false
    };
    var response = UrlFetchApp.fetch(url_trimmed, options);
    var responseCode = response.getResponseCode();

    cache.put(url_trimmed, responseCode, 21600); // 6 hours
    result = responseCode;
  }

  return result;
}

 

Now your spreadsheet can handle hundreds—or thousands—of links without falling over.

 

Save time with Google Sheets Scripts 

This tiny script turns Google Sheets into a lightweight diagnostics tool that every SEO, marketer, and developer should keep in their back pocket. It saves hours of manual checking, eliminates guesswork in Search Console reports, and ensures you’re working with real status codes—not assumptions.

If you’re tackling a large audit, migration, or cleanup and want support tightening the technical side of your SEO, we’re here to help make the process smooth.

Need help with technical SEO or site cleanup?
Book a Consultation with Us Here

Or Learn More About Our SEO Services Here