Fetch a URL and get the status code and redirects

Coming soon to the Keywords in Sheets Free Add-On.

Dive into URLs with this nifty script! Input any URL, decide if you want to chase redirects, and see the status codes with ease (combines status codes and redirects formulas) 🚀

Check out the GIF to see it in action 🎥

---

How to add the Script to Google Sheets

1. Copy the script below:

/**
 * Fetch a URL and get the Status Code and Redirects  
 *
 * @param {"https://example.com"} url - input your url. 
 * @param {false} followRedirects - input true/false whether you want redirects to be followed.
 * @param {false} useCache - input true/false whether you want to cache the results.
 * @customfunction
 */
function fetchurl(url, followRedirects, useCache) {

  // Ensure that a valid URL is provided
  if (!url) return ['you need to enter a valid URL'];

  // Default values for followRedirects and useCache if not provided by the user
  followRedirects = followRedirects || false;
  useCache = useCache || false;

  // Constants for cache expiration and maximum redirects
  const CACHE_EXPIRATION_TIME = 21600; // 6 hours in seconds
  const MAX_REDIRECTS = 5; // Number of maximum redirects to follow

  // Access the cache service provided by Google Apps Script
  const cache = CacheService.getScriptCache();

  // Fetch data from a given URL, and optionally retrieve from cache or store in cache
  function fetchDataFromUrl(url) {
    if (useCache) {
      // Attempt to retrieve the URL data from the cache
      const cachedData = cache.get(url);
      if (cachedData) {
        let parsedData = JSON.parse(cachedData);
        return {
          getHeaders: function () { return parsedData.headers; },
          getResponseCode: function () { return parsedData.responseCode; }
        };
      }
    }

    // Options for fetching the URL
    let fetchOptions = {
      'muteHttpExceptions': true, // Suppress exceptions for HTTP errors
      'followRedirects': false    // Manual handling of redirects
    };

    let response;
    try {
      // Fetch the actual data from the URL
      response = UrlFetchApp.fetch(url, fetchOptions);

      // Store fetched data in the cache for future use
      let headers = response.getHeaders();
      let dataToCache = {
        headers: headers,
        responseCode: response.getResponseCode(),
        redirectUrl: headers['Location'] || null  // Extract redirect URL if present
      };
      cache.put(url, JSON.stringify(dataToCache), CACHE_EXPIRATION_TIME);
    } catch (e) {
      // Return an error message if fetching fails
      return { error: 'Error fetching the URL' };
    }

    return response;
  }

  // Convert a relative URL to an absolute URL
  function makeAbsolute(base, relative) {
    if (relative.startsWith('http://') || relative.startsWith('https://')) {
      return relative; // It's already an absolute URL
    }
    if (relative.startsWith('/')) {
      // If it's a root-relative path
      let baseUrlParts = base.split('/');
      return baseUrlParts[0] + '//' + baseUrlParts[2] + relative;
    }
    // Handle other relative paths
    let index = base.lastIndexOf('/');
    return base.substr(0, index) + '/' + relative;
  }

  // Start by fetching the initial URL provided
  let initialResponse = fetchDataFromUrl(url);

  // Handle fetch errors, if any
  if (initialResponse.error) return [initialResponse.error];

  let statusCode = initialResponse.getResponseCode();
  let firstRedirectUrl = initialResponse.getHeaders()['Location'] || null;
  if (firstRedirectUrl) {
    // Convert relative redirect URL to absolute, if needed
    firstRedirectUrl = makeAbsolute(url, firstRedirectUrl);
  }

  // Handle cases where we don't want to follow redirects or where there's no redirect to follow
  if (!followRedirects || !firstRedirectUrl) {
    let firstRedirectStatusCode = firstRedirectUrl
      ? fetchDataFromUrl(firstRedirectUrl).getResponseCode()
      : null;
    return [[statusCode, firstRedirectUrl, firstRedirectStatusCode]];
  } else {
    let currentUrl = firstRedirectUrl;
    let finalRedirectUrl = firstRedirectUrl;
    let counter = 0;
    let response;

    // If followRedirects is true, keep following the redirects until MAX_REDIRECTS is reached or no more redirects are found
    while (counter < MAX_REDIRECTS && (response = fetchDataFromUrl(currentUrl)).getHeaders()['Location']) {
      finalRedirectUrl = makeAbsolute(currentUrl, response.getHeaders()['Location']);
      currentUrl = finalRedirectUrl;
      counter++;
    }

    // Fetch the final URL after all redirects to get its status code
    let finalRedirectStatusCode = fetchDataFromUrl(finalRedirectUrl).getResponseCode();

    return [[statusCode, firstRedirectUrl, finalRedirectUrl, finalRedirectStatusCode]];
  }
}

---

2. Head over to Google Sheets

Or if you’re really smart, create a new sheet by going to: https://sheets.new

Select Appscript from the Extensions menu.

Paste the script and save it.

---

3. Add the formula to any cell in your sheet

=fetchurl(A1,false,false)

Replace A1 with the cell you’re referencing (this is the URL you’d like to fetch)

Replace false with true if you’d like to follow all redirects up to a maximum of 5.

Replace the final false with true if you’d like to get the response from the cache (quicker). Every time a url fetch, the response is stored in a cache for 6 hours. This parameter allows you to use use the cached response or not (say if you wanted the most up-to-date version).

---

---

More Scripts

• ‘Award-winning’ digital PR headlines with GPT-3
• Append your keywords with all locations, from any country
• Automatically generate title tags & meta descriptions with GPT-3
• Bulk check indexing and coverage reports in Google Sheets