Table of Contents
Okay, this little hack has become a personal favorite for me and I had to share it. I had to clean up a sitemap with over 6000 URLs and I just happened to not have my Screamingfrog (God bless the frog) License with me. I remembered the good old script editor inside Google Sheets and decided t give it a try. So Here's how you get HTTP status codes in google sheets.
This can be also used to track if the backlinks you've built are still active by checking if they are still a 200 OK ( the page loads and still exists) or removed from the backlinking website Status code 404 (page not found), etc.
Total Time: 5 minutes
Open the Script Editor by Clicking on Extensions -> Apps Script.
function httpstatus( 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 ; } }
and save the copy-pasted code. Now your function/formula "httpstatus" will be recognized
Make Sure you give your app script project a name I've chosen "HTTP STATUS CODE" as my project name/
Click on Deploy on the top right corner and click deploy and select new Deployment.
Click on the cog icon and select Library and Deploy again.
Now you can return back to your spreadsheet and Type in a URL in cell A1. example: Google.com or https://dan.marketing
and in Cell B1( or wherever required) We can apply the formula "httpstatus("cell reference").
I choose to bulk check a lot of them so pasted an array of domains in A.
function httpstatus( 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 ;
}
}Paste the above script into the script editor.
Here's a quick list of HTTP status codes, Not all of them would be useful for a Digital Marketer but additional knowledge wouldn't hurt when you're working with your development team.
1×× Informational 100 Continue 101 Switching Protocols 102 Processing 2×× Success 200 OK 201 Created 202 Accepted 203 Non-authoritative Information 204 No Content 205 Reset Content 206 Partial Content 207 Multi-Status 208 Already Reported 226 IM Used 3×× Redirection 300 Multiple Choices 301 Moved Permanently 302 Found 303 See Other 304 Not Modified 305 Use Proxy 307 Temporary Redirect 308 Permanent Redirect 4×× Client Error 400 Bad Request 401 Unauthorized 402 Payment Required 403 Forbidden 404 Not Found 405 Method Not Allowed 406 Not Acceptable 407 Proxy Authentication Required 408 Request Timeout 409 Conflict 410 Gone 411 Length Required 412 Precondition Failed 413 Payload Too Large 414 Request-URI Too Long 415 Unsupported Media Type 416 Requested Range Not Satisfiable 417 Expectation Failed 418 I'm a teapot 421 Misdirected Request 422 Unprocessable Entity 423 Locked 424 Failed Dependency 426 Upgrade Required 428 Precondition Required 429 Too Many Requests 431 Request Header Fields Too Large 444 Connection Closed Without Response 451 Unavailable For Legal Reasons 499 Client Closed Request 5×× Server Error 500 Internal Server Error 501 Not Implemented 502 Bad Gateway 503 Service Unavailable 504 Gateway Timeout 505 HTTP Version Not Supported 506 Variant Also Negotiates 507 Insufficient Storage 508 Loop Detected 510 Not Extended 511 Network Authentication Required 599 Network Connect Timeout Error
Edit: I've added a spreadsheet as an example that you can copy for your own use case.
Copy the spreadsheet from here