Detect invalid urls with googleappsscript

Detect invalid URLs in Google Sheets using Google Apps Script by looping through a list of URLs and checking their response status with UrlFetchApp. If the server returns an error code (like 404 or 500), flag it as invalid in the sheet. Ideal for maintaining clean, updated, and working links.

Step 1: Click on the Copy button to copy the code snippet.
Step 2: Paste the copied code into your project’s script editor.

Apps Scripts Blog

Read Blog

AppSScript Code

function checkURLs() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Replace "Sheet1" with the name of your sheet
  var range = sheet.getDataRange();
  var values = range.getValues();
  
  for (var row = 0; row < values.length; row++) {
    var url = values[row][0]; // Assuming URLs are in the first column of your sheet
    
    if (url.indexOf("http") === 0) { // Check if URL starts with "http" or "https"
      var options = {
        'muteHttpExceptions': true
      };
      var response = UrlFetchApp.fetch(url, options);
      var code = response.getResponseCode();
      var status = (code === 200) ? "Live" : "Not live"; // Check if URL returns a 200 response code
      
      sheet.getRange(row+1, 2).setValue(status); // Write the status in the second column of the current row
      sheet.getRange(row+1, 3).setValue(code); // Write the response code in the third column of the current row
    }
  }
}