GAS Library Guide: How to Automatically Check for Broken Links in Google Sheets

Purpose of the Library

The main purpose of this library is to automatically determine if the URLs listed in a spreadsheet are currently accessible (i.e., whether they are "live" or "dead").

It uses UrlFetchApp, a standard GAS service, to attempt to visit each URL. It then retrieves the resulting HTTP status code (e.g., 200 OK, 404 Not Found, 500 Server Error) and records it in the adjacent cell in an easy-to-understand format, such as "✅ OK (200)" or "❌ 404 Not Found".

Primary Use Cases

This library is extremely helpful in the following situations:

• Checking for Broken Links After a Site RedesignAfter a website renewal, you can bulk-check if redirects from old pages to new pages are working correctly, or if pages that no longer exist are properly returning a 404 error.
• SEO & Backlink ManagementPeriodically check lists of external pages your site links to, or pages that link back to your site (backlinks), to prevent SEO penalties from link rot.
• Pre-Publication Blog Post ChecksEfficiently verify that all reference URLs or image links within a draft article are accessible before publishing.
• Error Page AuditingBy using the check404Only function, you can quickly isolate only the URLs that are returning a 404 error from a massive list.

How to Use the Library (Setup)

This library (ID: 1mTRw0sYI7DHJ9psMiSIZ0owuExPQ3eaLAFSyu5TYnbclwp9TMBWjxwJj) is already public, so you can use it immediately by following these steps in your own Google Sheet.

1. Open Your Sheet and the Script EditorOpen the Google Sheet where you want to perform the URL check.Go to the top menu and select Extensions > Apps Script.
2. Add the LibraryIn the Apps Script editor, click the + icon next to "Libraries" in the left-hand menu.In the "Script ID" field, paste the following ID:1mTRw0sYI7DHJ9psMiSIZ0owuExPQ3eaLAFSyu5TYnbclwp9TMBWjxwJjClick "Look up". When the library (URLChecker) is found, click "Add".(Note: The default identifier is URLChecker, but you can change it to MyChecker or anything you like. This guide will assume you are using URLChecker.)
3. Paste the Sample CodeIn your Code.gs file, paste the following "caller code." This code acts as the switch to "run" the library's functions.

/**
 * [Runs runCheckOnActiveSheet]
 * Checks all URLs on the currently active sheet.
 */
function runCheckOnActiveSheet() {
  // 1. Get the currently active sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 2. Run the library function ('URLChecker' is the identifier from step 2)
  URLChecker.checkURLsInColumnA(sheet);
}

/**
 * [Runs run404CheckOnActiveSheet]
 * Checks URLs on the active sheet and marks 404 errors only.
 */
function run404CheckOnActiveSheet() {
  // 1. Get the currently active sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 2. Run the library function
  URLChecker.check404Only(sheet);
}

/**
 * Automatically adds a custom menu when the
 * spreadsheet is opened or reloaded.
 */
function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Custom URL Check')
      .addItem('Check All URLs', 'runCheckOnActiveSheet')
      .addItem('Check 404 Errors Only', 'run404CheckOnActiveSheet')
      .addToUi();
}

1. Save and RunSave the script (click the floppy disk icon).Return to your Google Sheet and reload the page.A new menu item, "Custom URL Check," will appear at the top.Paste the URLs you want to check into column A. With that sheet active, select the function you want to run (e.g., "Check All URLs") from the custom menu. The script will start processing automatically.

Library Code Explained

This library provides two main functions:

1. checkURLsInColumnA(sheet)

This function checks column A of the provided sheet row by row and records all HTTP status codes in column B.

• sheet.getRange("A:A").getValues() gets all data from column A.
• A for loop processes one URL at a time.
• UrlFetchApp.fetch(url, options) sends the actual HTTP request.
• The muteHttpExceptions: true option is crucial. When true, the script will not stop if it encounters a 404 or 500 error; instead, it allows you to catch and retrieve the error status code.
• headers: {'User-Agent': ...} is set. Some servers block requests from the default GAS User-Agent (identifying it as a bot), so this fakes a common browser (Mozilla) User-Agent to ensure access.
• response.getResponseCode() retrieves the status code (200, 404, etc.).
• results.push(...) temporarily stores results in an array. Finally, resultRange.setValues(results) writes all results to column B at once (which is much faster than writing row by row).
• Utilities.sleep(200) adds a 200ms (0.2-second) pause between requests. This is an important rate-limiting measure to avoid overwhelming the target server with too many requests in a short time.

2. check404Only(sheet)

The basic structure of this function is identical to checkURLsInColumnA, but the logic for writing results is different.

• It only records a result with results.push(['❌ 404 Not Found']) if the status code is 404.
• For all other cases (like a 200 OK), it uses results.push(['']), leaving the cell in column B blank.

This allows you to quickly see only the URLs that are broken, making it easy to identify and fix the problem spots.

‍

‍