Checking HTTP Status Codes in Google Sheets

I’m a big fan of Google Sheets which is why I probably spend far too long in there trying to make tools.

One such task that can come in pretty handy from time to time is to check the HTTP response code of a number of URL’s.

Lets build that function!

In your Google Sheet we’re going to assume you have a list of URL’s in the first column. When we have the final function complete, your URL’s can be anywhere you want and we just add the cell reference to that function call in the sheet.

So in the cell to the right of the URL add in the following.

=HTTPRESPONSE(A2)

This will run the function on cell A2.

This should error and show something similar to the screenshot as we don't have a function called HTTPRESPONSE() to use in Google Sheets yet. We’ll need to create that.

In the Google Sheets menu click on Extensions then choose Apps Script

This will bring you to a coding window in another tab, that should look something like this.

To create the basic function we’ll need to add in this code

function HTTPRESPONSE(url) { 
    const response = UrlFetchApp.fetch(url);

    return response.getResponseCode() 
}

This creates a function called HTTPRESPONSE that has one argument, the URL.

This then pings that URL using the built-in Apps Script class UrlFetchApp . We then run a check on the response of that fetch and return that back to the cell that requested it.

Once this code is back in save the code and return back to your Google Sheet and you should see your status codes. If you don’t and it gets stuck on the below screen you’ll just need to give the sheet a refresh.

All being well you should see something like this.

So it appears to work.

Not all of those URL’s actually send a 200 response on the first request.

By default, UrlFetchApp defaults to follow all redirects and get the response code of the final URL through any redirects. Also, we have an error triggering on the page that doesn't exist.

To get around this we have something else we need to add some parameters to the UrlFetchApp call. Head back to the Apps Script window and add the config object and also add that object as a parameter on the fetch function.

function HTTPRESPONSE(url) {
  const config = {
    muteHttpExceptions: true,
    followRedirects: false,
  }

  const response = UrlFetchApp.fetch(url, config);

  return response.getResponseCode()
}

Once that's added in, save the function and if you come back to your sheet now and refresh you should see the completed header checker.

The HTTPRESPONSE function can now be used anywhere within this sheet.

If you want to know anything more about the UrlFetchApp class functions, you can read up on that at the UrlFetchApp documentation .