Getting URL response HTTP status code in GoogleSheets

Many (most, if not all) SEOs love to work with Google Sheets. Right? RIGHT?

Well, at least we have to work with it sometimes, so you better make the most out of it.
While I have recently already published an article on how you can use the importxml function in Google Sheets to make your SEO life a bit easier, let’s now get to something very basic.

Oftentimes, you have a list of URLs and you need to get the current status code.
Yes, you could copy/paste all these lovely URLs into your trusted ScreamingFrog (I ❤️ ya, lil froggy!), but you can also do that within your Google Sheet! How?

Use Apps Script to get the live HTTP status code from any URL

So, let’s enter the wonderful world of Apps Script. Yes, it’s OK if you haven’t heard of that before.
I have only discovered this recently myself, and I have been a developer and SEO for well over ten years. Yeah, speaks for me, right? So don’t worry, you can’t know everything. (This is my excuse….)

About following redirects

Oh, please note: There are quite similar Apps Scripts out there on the interwebz, but I have seen none that does not follow redirects. This one does not follow redirects. Read why below. I got reasons! :)

On my list, I don’t just want to see 200 and 404 codes. If you’re using the UrlFetchApp.fetch() method without the parameter 'followRedirects': false, it will follow all the redirects until it reaches the final one and return its status code.
If we’re setting it to false, it will return the status code of the URL you have actually entered, which might be a 301, 302, 410 or whatever. This makes much more sense for us SEOs.

In case you want to read up on that, here’s Google’s documentation on UrlFetchApp.fetch() method.

Alrighty, let’s goooo already

You go ahead and open up a fresh and shiny Google Sheet – and follow along. It’s not too tough, I promise :)

In the toolbar, click on Extensions, then Apps Script.

A new tab will open an present a very beautiful editor. Don’t be scared. It won’t bite. In this editor, delete the placeholder function and copy/paste the following code in there.

function httpstatuscode(uri) {
    let response_code;
    let params = {
      'followRedirects': false
    }
    try {
        response_code = UrlFetchApp.fetch(uri, params).getResponseCode().toString();
    } catch (error) {
        response_code = error.toString().match(/ returned code (\d\d\d)\./)[1];
    } finally {
        return response_code;
    }
}

Afterwards, save the script (CTRL/CMD+S or click the icon) and give it a cool name.
Not sure what to do? Check the quick screen video.

Right after that, you need to deploy the script to make it available in your Google Sheet.
To do that, click on the nice blue "Deploy" button and select "New Deployment".

In the following overlay, select a deployment type by clicking the cog icon and selecting "Library".
Give it a nice name (again) and click on "Deploy". Again, I have created a video for that as well.

Ok, so now let’s see the magic happen, shall we!?

Using your Apps Script in your sheet to get the live HTTP status code

Now, go back to your spreadsheet. Yes, you can close the Apps Script editor tab.

In your sheet, you will now have the httpstatuscode function available.

In your first column (A), add a list of URLs you want to check the status code for.
In the second column (B), you will enter the function as follows (I will start at A2, since A1 is my header row)

=httpstatuscode(A2)

This passes the URL in A2 to our function, which in return will get the status code and display it nicely in our B2 cell.
Now drag this cell down to your last URL, in my case this is row 9. And et voilà, you’re all set.

I would like to apologize to womenintechseo for (mis)using their URLs to show how this script works.
You all should check out their website. Check their blog. They are amazing! Follow them on Twitter.

Did you find this helpful?

Well, if you didn’t: Bugger off.I don’t care. Go here. Or read up on this interesting topic.

Oh, you liked it?

Love ya, too! Share it maybe? Spread some love.