How to Check URL Status Code in Google Sheets

Checking a URL’s HTTP status code in Google Sheets is straightforward with the help of Google Apps Script. Follow these steps to set it up:

  1. Open Google Sheets

Start by launching the Google Sheet where you want to implement this functionality.

  1. Access Apps Script

Go to Extensions > Apps Script from the top menu. This will open a blank script editor.

  1. Paste the Script

Copy and paste the following code into the editor:

function getStatusCode(url) {
  var url_trimmed = url.trim();
  if (url_trimmed === '') {
    return '';
  }

  var cache = CacheService.getScriptCache();
  var cacheKey = 'status-' + url_trimmed;
  var result = cache.get(cacheKey);

  if (!result) {
    var options = {
      'muteHttpExceptions': true,
      'followRedirects': false
    };

    try {
      var response = UrlFetchApp.fetch(url_trimmed, options);
      var responseCode = response.getResponseCode();
      result = responseCode.toString();
      cache.put(cacheKey, result, 21600); // Store in cache for 6 hours
    } catch (error) {
      result = 'Error: Unable to fetch URL';
    }
  }

  return result;
}
  1. Save and Close

Click File > Save, name the script, and then close the editor.

  1. Use the Function

Now, return to your Google Sheet and follow these steps:

  • Insert a URL: Enter a URL in a cell, e.g., A1 = www.example.com.
  • Call the Function: In a new cell, type =getStatusCode(A1) to fetch the status code for the URL in A1.
  • View the Result: The cell will display the HTTP status code (e.g., 200 for success, 404 for not found).

Example

  • A1: https://k-o.pro
  • B1: =getStatusCode(A1)
  • Result in B1: 200

This method makes it easy to monitor HTTP status codes directly within Google Sheets, saving time and streamlining workflows.

P.S. Here’s additional code in case the first one doesn’t work for you:

function getStatusCode(url) {
 var result = [];
 url.toString().trim();
 var options = {
 'muteHttpExceptions': true,
 'followRedirects': false,
 };
 try {
 result.push(UrlFetchApp.fetch(url, options).getResponseCode());
 }
 catch (error) {
 result.push(error.toString());
 } finally {
 return result;
 }
}

More posts