Pull an HTTP Response Code in Google Sheet

Google Sheet is one of the amazing online spreadsheet that can be used for almost anything about calculations. In this 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 without opening each website manually.

HTTP Response Code in Google Sheet

If you are a blogger or webmaster monitoring 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 following formula to get HTTP Response Code.

=HTTPResponse(CELL CONTAINING LINK)

To make 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.

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.

Share This

Author: AtulHost

Hi, I'm Atul Kumar Pandey, a full time blogger by passion and profession. Since I started working on the web industries many years ago, with my all expertise I blog on atulhost.com, where my main objective is to build and market quality contents and reviews.

19 thoughts on “Pull an HTTP Response Code in Google Sheet”

  1. 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.

    1. You can make a list first and apply 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 bit tricky somehow on Google Sheet because everything is on formula.

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

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

  2. 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?

    1. 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 automatic at extent level, there is a privacy policy violation of multiple calls at one time.

      This is why I am working on external PHP call feature which 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 made the code work for auto crawl.

    2. 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 (\d\d\d)\./ )[1] ;
      }
      finally {
      return response_code ;
      }
      }

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

  3. 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 ?

    1. Yes it is possible your 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 time you have specified later it will get refresh. Still I suggest to modify the code as per your need like time and date as you want.

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

    Thanks…

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

  5. 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!

    1. 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.

  6. 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.

    1. 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.

Comments are closed.