Google sheet tools - mass url alive check tool

Google Sheet Bulk URL Alive Checker Tool 2023

by

Have you ever found yourself with a long list of URLs and wanted to know which ones were still active and which ones were broken or returned an error? It can be a tedious and time-consuming task to check each URL manually, espicially if they have hundreds or not thousands of URLs to check, these things can only be done with a Tool. This is exactly why I created this post! There’s a solution: a Google Sheets tool that does the work for you!

Alive URL Checker tool by xedricity.com
Alive URL Checker tool by xedricity.com

I was recently asked to help a client check if their product’s URL was still alive. It was a simple enough task, but the problem is they have hundreds of products pages and I don’t have the luxury to check them one by one. So, this idea came to my mind and thought I just needed to use the Bulk URL Alive Checker Tool that works with Google sheet.

The Bulk URL Alive Checker Tool for Google Sheets is a simple script you can run on your spreadsheet that will automatically check the status of all your URLs on the Column A, and the script will provide a report on the sheet Column B with that response code the URL is returning. It even lets you know if there were any errors in the process!

In this post, I’ll walk through how to set up the tool in your own Google Sheet.

How it Works:

With just a few lines of code, you can easily check the status of all the URLs in a column and get a report on any errors that may have occurred, this works by accessing the URL and the script will update the sheet depending on the response code of the URL. Simply enter the code below into a new Google Sheets script and run it on your sheet to get started.

Copy the Code:

Here’s the full code.

function checkUrls() {
  // get the number of rows in the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var numRows = sheet.getLastRow();
  
  // loop through each row
  for (var i = 1; i <= numRows; i++) {
    // get the URL in column A
    var url = sheet.getRange(i, 1).getValue();
    
    // try to fetch the URL
    try {
      var response = UrlFetchApp.fetch(url);
      var statusCode = response.getResponseCode();
      
      // check the status code
      if (statusCode >= 300 && statusCode <= 399) {
        // input the error in column B
        sheet.getRange(i, 2).setValue("Redirection error: status code " + statusCode);
      } else if (statusCode >= 400 && statusCode <= 499) {
        // input the error in column B
        sheet.getRange(i, 2).setValue("Client error: status code " + statusCode);
      } else if (statusCode >= 500 && statusCode <= 599) {
        // input the error in column B
        sheet.getRange(i, 2).setValue("Server error: status code " + statusCode);
      } else {
        // set the value in column B to "website is okay"
        sheet.getRange(i, 2).setValue("website is okay");
      }
    } catch (e) {
      // input the error in column B
      sheet.getRange(i, 2).setValue("Error: " + e);
    }
  }
}

How To Use:

To use this tool, simply enter a list of URLs in column A of your sheet. Then, go to the scirpts and run it, wait for the results. The tool will check each URL and report any errors that it encounters in column B. If a URL is active and returns no errors, it will leave the cell in column B words such as “website is okay” (see image).

  1. Copy the whole code above the Go to Your Sheet > Extensions > and Apps Script

2. Paste the Whole Code, Save and Run

Make sure you already entered the URL’s you want to check on the Column A before you click the “RUN” button.

One of the great things about this tool is that it is completely customizable you can add things on may need or change the script the way it reports good websites. You can modify the code to fit your specific needs, such as adding additional columns for more detailed error reporting or changing the way the results are displayed or have styles in it.

Overall, this Google Sheets tool is a quick and easy way to check the status of a large number of URLs up to 1000 of URL’s can be checked using this tool, though the whole process may take a little time. Whether you’re a web developer or just need to check a list of links for a project, this tool can save you time. Please note that, I did not write these code, I only have updated the alreay existing code to fit my need. Feel free to use it yourself add/modify.

Leave a Comment