August 20, 2021

Automatic Backlink Tracker Using Google Sheets

Spread the love

Statuses and Codes:

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.

How to get HTTP STATUS CODE in Google Sheets using App Script:

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

  1. ezgif 2 e2286a2461

    Open the Script Editor by Clicking on Extensions -> Apps Script.

  2. Copy-paste the Below Code or from the copy-able code box here App-script

    Copy paste app 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

  3. Save and Deploy

    ezgif 2 9f8f122d9c

    Make Sure you give your app script project a name I've chosen "HTTP STATUS CODE" as my project name/

  4. Deploy the App Script Project

    ezgif 2 19056aa649

    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.

  5. Try It Out!

    ezgif 2 95bbad41ac

    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").

  6. Checking the Status Codes

    bulk check domain status

    I choose to bulk check a lot of them so pasted an array of domains in A.

App Script Code

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. 

HTTP STATUS CODES REFERENCE LIST

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

Dan.marketing is the personal website of Daniel (Dan) Antony, a digital marketer passionate about business and technology
© Copyright 2020 - Built with Oxygen By Dan Antony
envelopephone-handsetmap-marker
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram