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;

    // 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


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

Thanks for stopping by 👋

I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. 🤓

Questions? Get in touch with me on social or comment below 👇

More Scripts

Submit your response

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