Check Bulk URLs with Google Sheets

Over at Placement SEO, the search engine marketing agency I co-own with my partner, Gary Simpson, we produce several thousand guest posts per month. It’s quite a feat keeping up with demand from our agency accounts and its certainly a heavy task to keep up with real time link tracking. After having searched the depths of the internet to find and locate both free and premium link checkers I still found myself unsatisfied. That’s why I want to share with you a Google Sheets link checker method I discovered that allows our agency to track guest post links in real time. The best part about this? It’s free. So let’s get started.

Who Does This Apply To?

  • Agency’s that purchase guest posts
  • Website Owners that purchase guest posts
  • people who are guest post vendors.

Keep in mind, the internet is ever shifting, meaning, its hard to control when a website goes down IF you do not own it. So for PlacementSEO, this strategy works great. If a link ever goes down, or if a permalink changes, our team gets an immediate email.

 

Step One: Open a new Google sheet which you will add all of the URLs you want to track. One the sheet is open, give the sheet a name in the top left corner of the window, as demonstrated in the screenshot below.

 

Once you have opened Google Sheets and have assigned a name to the sheet itself, you need to head over to the tools tab, and click on scripts editor. Checkout the screenshot below per demonstration.

 

Once you have opened the Scripts Eiditor, go ahead and clear the default code and paste the exact code below.

 

— code begin – do not copy this line, only copy below —

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 ;
}
}

—– code end, do not copy this line, copy above —-

 

You can view my example below.

 

After you paste the code, do not forget to name this code sheet as circled in red via the screenshot above. Once you have pasted the script above into the scripts editor you should be able to close the window AFTER CLICKING THE SAVE BUTTON.

 

Now that we’ve codded our script, we need to add the function to our spreadsheet, which is VERY easy to accomplish.

The function you will be copying into each cell is the following: =httpresponse(). Between the parentheses you will put which row number your link is in. Checkout the screenshot below.

The function =httpresponses, calls for the script to run that we added via the scripts editor. The (a1), tells the script to check the status of the link provided in Column A1. The status provided will be either the number 200, which means the link is live, or 404, which means the link went offline. You can copy and paste the function easily which auto populates the parentheses data. Checkout the screenshot below.

You can see that when I copied and pasted the function into row 10, the () automatically filled in as (A10). Now, lets add some links to track. In this demo I am going to throw some live links from PlacementSEO and also purposefully throw some 404s in there.

You can see three things happening in the screenshot above. The loading message means the script is checking the link to see if its accessible. The 200’s mean that the links are live, and the 404s mean the links are down and you should check on them.

A  consideration to make.

For one, you have to login to the sheet, delete the links, and then undo the deletion to re-run the script check. Why? Because as soon as you close Google Sheets, the sheet itself goes offline, therefore not even a bot can force the script to run.  That said, if you can find me a way to automate this, ill pay you FIFTY DOLLARS just to tell me how, ill do all the work myself.

 

Now its you’re turn. How do you bulk check links? Let me know in the comments below.

Written by John King
Self made Entrepreneur that co-manages PlacementSEO.com, a growing internet marketing agency. To learn more about our agency vision, SEO secrets and growth hacking, follow me on Facebook.