How to Check List of URLs for 404 Errors using Google Sheets

In this tutorial I will cover how to bulk check a list of URLs for 404 errors using Google Sheets. The process will be so simple that you will put the list of URL that you want to check for 404 or any other status code, and soon you will get status code for all the URLs. I have been using this method since last few days to check around 11,000 URLs in bulk for 404 errors, and it has been working very nicely. Takes just a few minutes to check so many URLs as these run in parallel using Google’s massive infrastructure.

So, if you are a marketer, a blogger, a SEO specialist, or anyone with a need to bulk check huge list of URLs for 404 errors, this method is as easy as it gets. Of course, there are tools out there that can probably do a better job (like, Screaming Frog), but what I wanted was something that runs online and can be used by anyone for free, and Google Sheets exactly does that. So, without much ado, let’s see how to bulk check list of URLs for 404 errors.

In the above screenshot you can see my list of URLs on the left side, and their status code on the right side. The first cell in the last column shows the formula that I have applied.

How to Check List of URLs for 404 Errors using Google Sheets:

To check our URLs, we are going to add some code to Google Sheets. But don’t worry, it is amazingly simple. And you don’t need to have any technical knowledge. I am going to walk you through the exact steps required. Also, this setup is required for the first time only, and then you can keep using the same sheet again without a need to re-apply these steps.

Step 1: Create a new Google Sheets.

Step 2: We will add the code via Script Editor. For that, click on Tools, and then Script Editor.

Step 3: This will open a blank script Editor window, as shown below. Remove all the code that is present in it.

Step 4: Now comes the main part. Copy all the code below, and add it to Google Sheets.

function HTTPResponse( uri )
{
var response_code ;
try {
response_code = UrlFetchApp .fetch( uri ) .getResponseCode() .toString() ;
}
catch( error ) {
response_code = error .toString() .match( / returned code (\d\d\d)\./ )[1] ;
}
finally {
return response_code ;
}
}


Once added, your Script Editor should look like screenshot below. Go ahead and click on the Save button to save the script.

Now you are all set.

Step 5: Close the Script Editor. Go back to your Google Sheet that you created. Now, in any column, add the list of URLs that you want to check for 404 errors or any other status code. Next to that, add this function:

=httpresponse(cell that has URL)

For example, if your URL is in cell A2, then above function will read =httpresponse(A2)

This will replace the function with HTTP status code. The URLs that are fine with give 200 status code, while the ones that have problem might give codes like 404, 500, 400 etc. You can see list of HTTP Status Codes here.

Do the same for all the URLs. As I said, I have been using this with more than 11,000 URLs, and it works absolutely fine.

Do note that once you add the function, nothing might happen immediately. Give it some time to return the result.

Here are a few tips based on my experience of working with a huge list of URLs:

• If you open that Google Sheets again, it might again start fetching fresh status code data for all the URLs, which might make the sheet unresponsive for a while. So, as a practice, once I have all the info I need, I remove the formula from the column and then close the sheet, so that next time I open, it opens immediately.
• Google Sheets recalculates the value of the function frequently. This might happen even if nothing changes. During that time, the sheet might become unresponsive. So, the best option is that once the status codes for all the URLs have been found, copy them to an Excel.
• For the reason mentioned above, don’t try to do any further calculation on those URLs in Google Sheets. For example, it might not be a good idea to create pivot table etc. Instead, copy the data from this Google sheet to another Google Sheet or Excel, and perform any further calculations there. Use this sheet to just get status codes of URLs.

Conclusion:

This Google Sheet is a powerful tool in my arsenal now, as it has saved me lot of time in checking huge lists for 404 or any other errors. It does not do any advanced functions, like checking redirect URLs etc., but that’s not what this is meant for. The sole purpose of creating such a sheet was to be able to quickly bulk check list of URLs for 404 errors, and that’s exactly what this does.

Do you use any other method to check URLs for 404 errors? Let me know in the comments below.

Source: StackOverflow.