AtulHost

How To Pull an HTTP Response Code in Google Sheet?

Published in

on

Google Sheet is one of the amazing and free online spreadsheet program that can be used for almost anything about calculations. In this blog post, I am going to share how to pull an HTTP Response code in Google Sheet. This might be helpful if you wanted to see HTTP Status code of multiple website in one go without opening each website manually in your browser. Cool, isn’t it? It is also a time saver.

HTTP Response Code in Google Sheet

If you are a blogger or webmaster monitoring a list of domains, sites or URL then you might need to check HTTP Status many times. To overcome this issue, I have made it simple. You will get 200 and 301 for working websites, or will get any other response code like 500 or 404.

HTTP Response
HTTP Response

We have to use the following formula to get HTTP Response Code.

=HTTPResponse(CELL CONTAINING LINK)

To make the above formula work we have to insert a code in Google Sheet that will make the formula run. To insert the formula, first open Script Editor.

Tools » Script Editor » Name Your Script Editor » OK » Add Below Code.

/*
Check Websites for HTTP Response
Source: https://www.atulhost.com/how-to-pull-an-http-response-code-in-google-sheet
*/
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 ;
 }
}

…and don’t forget to Save it… :)

HTTPResponse Code
HTTPResponse Code

Now you are ready to check as many as possible HTTP Header Status Code in just few moments and can save time on manual website checks.

Topic Tags: , , .

Post Author

Atul Kumar Pandey

Atul Kumar Pandey, the creator of the AtulHost website, is a business management graduate with a strong talent for business writing. He is also deeply passionate about technology, regularly exploring the latest trends and innovations. As an avid reader and techies, he enjoys trying out new ideas and staying ahead of the curve.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

Responses to “How To Pull an HTTP Response Code in Google Sheet?”

  1. Smith Avatar
    Smith

    Thanks for nice tutorial. I have column A(urls), B(status). Could you show me how to create a button on spreadsheet( for example call it links checker) to check status of all urls again when it’s clicked(as you know we want fresh status as some links get offline in the future)?Furthermore,how to automatically apply the =httpresponse formula when we insert new url manually or via form (it’s not practical to paste formula for huge list of urls on every cell)? Looking forward for your reply. Thanks in advance.

    Reply
    1. Atul Kumar Pandey Avatar
      Atul Kumar Pandey

      You can make a list first and apply the formula to a number of cells leaving a column of URLs blank and future whenever you try to add a URL the already set formula will search for its status. The button-based call function is a bit tricky somehow on Google Sheet because everything is on formula.

      However, if you know PHP then you can modify the function used behind the formula and add a crawl function, I am still working on this and may update this in the future.

      But being an SEO Specialist I also face this situation and do this small trick while managing a huge list of URLs. “Write the formula in further next column (column C in your case) and delete the first column that contains the old formula (column B in your case), it will make formula search for the live status again from scratch.”

      Reply
  2. Smith Avatar
    Smith

    Thanks for your reply. I tried use the following line on script editing but I keep getting undefined on column B when I try insert values via form:

    cell.offset(nextRow, col).setValue(“=HTTPResponse(https://www.atulhost.com)”);

    Do you think this way of copying formula will work or do I have any syntax error in above line ?

    You mentioned about php. do you mean I can write php script instead of javascript on script editing? I don’t know if script editing tool supports php. Is there any javascript code that crawls all the column B cells and update their status? How?

    When you said write formula on column C and delete entire column B, you mean I have to type the formula(=HTTPResponse(A1)) on cell C1 and drag the selection all the way down to copy the entire formula for all cells on column C? This method is useful when I only have 2 columns but I have to use a spreadsheet later that has more then two column and adding and deleting columns will alter the structure sheet and probably delete data by mistake!

    My goal is to make things automatic and run every hour or so(or use a button for crawler function) for a list urls but I think google scripting tool has lots of limitation! Do you think it is possible to make a script that crawls all the existing data and update their status? How?

    Reply
    1. Atul Kumar Pandey Avatar
      Atul Kumar Pandey

      I tried this method; it won’t work at all. It is the Google Sheet limitation that they want only basic functions to work in sheet. By doing things automatically at the extent level, there is a privacy policy violation of multiple calls at one time.

      This is why I am working on an external PHP call feature that will work outside Google Sheet for crawl functions. I appreciate your contribution and special attention to this code. I will soon update you once I successfully make the code work for the auto crawl.

      Reply
    2. Atul Kumar Pandey Avatar
      Atul Kumar Pandey

      Hello Smith,

      Finally somehow I tried a Time Driven trigger to recheck the values, for which we have to modify the code used above. There are 2 possible ways as,
      – Trigger every 12 hours.
      – Trigger every Monday at 09:00.

      function createTimeDrivenTriggers() {
// Trigger every 12 hours.
ScriptApp.newTrigger('YOUR-FUNCTION')
.timeBased()
.everyHours(12)
.create();
// Trigger every Monday at 09:00.
ScriptApp.newTrigger('YOUR-FUNCTION')
.timeBased()
.onWeekDay(ScriptApp.WeekDay.MONDAY)
.atHour(9)
.create();
}

      Just add one or both scripts as per your need at very begining of script. (See below)

      function createTimeDrivenTriggers() {
// Trigger every 12 hours.
ScriptApp.newTrigger('HTTPResponse')
.timeBased()
.everyHours(12)
.create();
// Trigger every Monday at 09:00.
ScriptApp.newTrigger('HTTPResponse')
.timeBased()
.onWeekDay(ScriptApp.WeekDay.MONDAY)
.atHour(9)
.create();
}
function HTTPResponse( uri )
{
var response_code;
try {
response_code = UrlFetchApp .fetch( uri ) .getResponseCode() .toString() ;
}
catch( error ) {
response_code = error .toString() .match( / returned code (ddd)./ )[1] ;
}
finally {
return response_code ;
}
}

      Let me know if this works for you or not; somehow it works for me on small data.

      Reply
      1. Nitin Avatar
        Nitin

        Is it possible to convert this into a batch function? The script ends up in a error with huge linklists. I believe it exhausts the spreadsheet quota.

        It gives me this error: Service invoked too many times in a short time: exec qps. Try Utilities.sleep(1000) between calls. (line 0).

        Reply
        1. AtulHost Avatar
          AtulHost

          Google sheet is open platform for users, that is why Google imposes some limit to the overuse of their any products. That is why if you have large chunk of data you need to break it down in small batches or rely on some professional tool dedicated for it.

          Reply
  3. Smith Avatar
    Smith

    Thanks . Is it possible to change the trigger to minuets instead of hours ? does the above code will update the status of entire columns of urls or single cell? furthermore , don’t you think Google is caching the previous result and in case url goes down we still get old server status ? does this code will solve that issue ?

    Reply
    1. Atul Kumar Pandey Avatar
      Atul Kumar Pandey

      Yes, it is possible you can search for time-driven based triggers its easy to use HTTPResponse code and it is applicable to all cells in which formula is present. We made this code time-driven so it will be cached for the time you have specified later it will get refreshed. Still, I suggest modifying the code as per your need like time and date as you want.

      Reply
  4. alex Avatar
    alex

    The function would not show response code other than 200 or 40x. URLs that return 30x still show 200 OK. Thoughts?

    Thanks…

    Reply
    1. AtulHost Avatar
      AtulHost

      Hi Alex, This code should show 200 and 500 on basic lookup. As now a days people used to organize their error page to something good looking one that is why it may show you 200 status, but it is very rare case (if you have accessed the site from your browser). It will show whatever response code is shown from server side of a website.

      Reply
  5. Elizabeth Avatar
    Elizabeth

    This is great!! Is there a way to do the same but for email addresses? Kind of like this:

    http://www.labnol.org/software/verify-email-address/18220/

    Thank you – you just saved me a ton of time!

    Reply
    1. Atul Avatar
      Atul

      This is only for tracking live http response codes for domain names. For email there are tones of things which needs to be implemented.

      Reply
  6. Dainis Avatar
    Dainis

    Thanks! This is exactly what I have been looking for.

    Reply
    1. AtulHost Avatar
      AtulHost

      Glad to know that it worked for you. Stay tuned.

      Reply
  7. Ryan Avatar
    Ryan

    This is insanely handy. Thanks for sharing!

    Reply
    1. AtulHost Avatar
      AtulHost

      Thanks for your feedback Ryan.

      Reply
  8. Satvik Avatar
    Satvik

    I receive messages like “-1” when the website is down or up. I created this excel long back and now want to disable this. Please guide how do I disable this feature as I am tired of looking at these emails in my inbox. Thanks!

    Reply
    1. AtulHost Avatar
      AtulHost

      It is simple, just delete the HTTPResponse script from the script editor where you have added one. Once you have removed you won’t be getting these up or down emails.

      Reply
  9. Winston Avatar
    Winston

    Hey Atul!

    Thanks for this super useful guide :)

    It works on my own personal file, however for the other Google Doc the value returned was Function Unknown.

    Can I check if its because I’m not the file owner despite creating the script? Or is there an extra step such as Publish > Deploy as Web App etc?

    Very new to the whole concept of coding, any help here would definitely be valuable!

    Thanks in advance,
    Winston.

    Reply
    1. AtulHost Avatar
      AtulHost

      Glad to know that it worked for you Winston. About Google Doc, I didn’t tried it yet but it should work there similarly. However you must try it using insert Excel cell inside Document.

      Reply
  10. Simon Avatar
    Simon

    Worked perfectly first time, thanks!

    Reply
    1. AtulHost Avatar
      AtulHost

      Thanks Simon for sharing your feedback.

      Reply
  11. David Avatar
    David

    Any way to make this an array formula? The function doesn’t work as is in an array formula.

    Reply
    1. Atul Kumar Pandey Avatar
      Atul Kumar Pandey

      Google has limits, otherwise we can convert this to an array formula.

      Reply
  12. Jon Avatar
    Jon

    Hi AtulHost,

    Thank you for the code, it worked well, however, the codes don’t seem to be correct, I am getting 200 status codes on pages that have had a 301 redirect, is there a way it could be modified to pick up the status code before it resolves as a 200?

    Reply
    1. Atul Kumar Pandey Avatar
      Atul Kumar Pandey

      Hi Jon, I’m glad you tried the code. About the code, it checks for the final page. If a domain is 301 redirecting to something else and returning a successful 200 status code then it will show 200 status. Unfortunately, due to limitations, it is not feasible to pick up the status code before it resolves to 200.

      Reply
  13. Dan Martin Avatar
    Dan Martin

    This is so helpful, thanks for posting it!

    Reply
  14. Kristian Avatar
    Kristian

    Thank you!!! Your code worked like a charm and saved me time.

    Reply