{"id":70343,"date":"2023-11-21T21:01:30","date_gmt":"2023-11-21T13:01:30","guid":{"rendered":"https:\/\/www.hongkiat.com\/blog\/?p=70343"},"modified":"2023-11-20T21:16:13","modified_gmt":"2023-11-20T13:16:13","slug":"google-sheets-check-http-status","status":"publish","type":"post","link":"https:\/\/www.hongkiat.com\/blog\/google-sheets-check-http-status\/","title":{"rendered":"How to Check Broken Links Using Google Sheets"},"content":{"rendered":"

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\u2019t practical. In this post, I\u2019m going to show you how to do it for free and almost automatically, using Google Sheets.<\/p>\n

\"Check<\/figure>\n

By the end of this tutorial, you\u2019ll 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<\/a> of each URL. This will help you understand if the page is accessible, redirected, broken, and so on.<\/p>\n

Step 1: Create a New Google Sheet<\/h2>\n

Create a new Google Sheet and import your list of links, or you can manually add them. Organize them in a column labeled \u201cURL<\/strong>\u201d and another labeled \u201cStatus<\/strong>,\u201d like this:<\/p>\n

\"Example<\/figure>\n

Step 2: Open Google Apps Script<\/h2>\n

In the Google Sheet\u2019s toolbar at the top, go to Extensions<\/strong> > App Scripts<\/strong>.<\/p>\n

This will open the Google Apps Script<\/strong> editor, where you can create your own custom functions. We\u2019re going to create a function that takes a URL as input and returns its HTTP status. Don\u2019t worry, no coding skills are needed for this part.<\/p>\n

\"Googel<\/figure>\n

Step 3: Paste the Code<\/h2>\n

Copy the code provided below, paste it into the editor, and give your project a name, like \u201cGet HTTP Status.<\/strong>\u201c<\/p>\n

\r\nfunction getHTTPStatus( uri )\r\n{\r\n  var response_code ;\r\n  try {\r\n    response_code = UrlFetchApp .fetch( uri ) .getResponseCode() .toString() ;\r\n  }\r\n  catch( error ) {\r\n    response_code = error .toString() .match( \/ returned code (\\d\\d\\d)\\.\/ )[1] ;\r\n  }\r\n  finally {\r\n    return response_code ;\r\n  }\r\n}\r\n<\/pre>\n
Step 4: Save and Close<\/h2>\n
Click on the small Disk icon to save your project. You can then close the Apps Script browser window.<\/p>\n
\"Disk<\/figure>\n
If you\u2019re curious about what the function does and want to understand it better, see below for an explanation<\/a>.<\/p>\n
What the Function Does:<\/h3>\n
This script, named getHTTPStatus<\/code>, is designed to retrieve response codes from web addresses (URLs). It\u2019s a straightforward tool for understanding the status of a URL and the type of response it provides.<\/p>\n
Here\u2019s a breakdown of its functions:<\/p>\n
    \n
  1. \n
    Setting Up<\/strong>: Initializes the process to capture the response code from the URL.<\/p>\n<\/li>\n
  2. \n
    Try and Catch Errors<\/strong>: Employs a method to handle potential errors during URL checks.<\/p>\n