javascriptgoogle-sheetsgoogle-apps-scripthttp-redirect

Is there a way to get all redirect locations from a given XML/HTML in Google Apps Script?

I have a google sheet where there is an Apps Script function called GET_REDIRECT_URL() and it does not work properly. Is there any way to fix this?

The sheet in question
You can access the script in Extensions => Apps Script. For example, in the VSCode Blog url, there is a meta refresh redirect, and the function is unable to extract it, throwing this error:

ERROR: ReferenceError: URL is not defined.

Also, some other URLs throw a different error or fail to fetch.

<!DOCTYPE html>
<html>
    <head>
        <title>Visual Studio Code Blogs</title>
        <meta http-equiv="refresh" content="0; url=/blogs/2025/07/17/copilot-coding-agent">
        <meta name="description" content="The latest Visual Studio Code blogs">
        <meta name="robots" content="noindex">
        <link rel="canonical" href="/blogs/2025/07/17/copilot-coding-agent" />
    </head>
</html>

This is my Apps Script implementation:

/**
 * Returns the redirect location of a url.
 *
 * @param {string} input The source URL being redirected.
 * @return The destination location/URL.
 * @customfunction
 */
function GET_REDIRECT_LOCATION(input) {
  // Basic validation
  if (!input) return "INVALID_URL";
  
  let url = input.toString().trim();
  if (!url || !url.includes(".")) return "INVALID_URL";
  
  // Add https if no protocol
  if (!url.startsWith("http")) {
    url = "https://" + url;
  }
  
  try {
    // First try with followRedirects: false to catch HTTP redirects
    let response = UrlFetchApp.fetch(url, {
      muteHttpExceptions: true,
      followRedirects: false
    });
    
    let status = response.getResponseCode();
    
    // Check for HTTP redirect (3xx status codes)
    if (status >= 300 && status < 400) {
      const headers = response.getAllHeaders();
      const location = headers.Location || headers.location;
      
      if (location) {
        return location;
      }
    }
    
    // Get the HTML content to check for meta redirects
    const content = response.getContentText();
    
    // Check for meta refresh redirect
    const metaRefresh = content.match(/content=["']?\d+;\s*url=([^"'\s>]+)["']?/i);
    if (metaRefresh && metaRefresh[1]) {
      let redirectUrl = metaRefresh[1];
      
      // Handle relative URLs - make them absolute
      if (redirectUrl.startsWith('/')) {
        const baseUrl = new URL(url);
        redirectUrl = baseUrl.protocol + '//' + baseUrl.host + redirectUrl;
      }
      
      return redirectUrl;
    }
    
    // Check for canonical URL as backup
    const canonical = content.match(/<link[^>]*rel=["']canonical["'][^>]*href=["']([^"']+)["']/i);
    if (canonical && canonical[1]) {
      let canonicalUrl = canonical[1];
      
      // Handle relative URLs
      if (canonicalUrl.startsWith('/')) {
        const baseUrl = new URL(url);
        canonicalUrl = baseUrl.protocol + '//' + baseUrl.host + canonicalUrl;
      }
      
      if (canonicalUrl !== url) {
        return canonicalUrl;
      }
    }
    
    return "NO_REDIRECTS_FOUND";
    
  } catch (error) {
    return "ERROR: " + error.toString();
  }
}

Even after trying to handle this edge case, it still will not work. Does anyone have any solutions?

There was another implementation here, but that one had the same problem.

Solution

  • Add this

    class URL {
  constructor(str) {
    this.protocol = str.startsWith('https://') ? 'https' : 'http';
    const l = this.protocol.length + 3;
    this.host = str.slice(l, str.indexOf("/", l));
  }
}

    It could be added anywhere in your Apps Script project, before or after the declaration of the GET_REDIRECT_LOCATION function, or in a new .gs file.

    Note: The above doesn't intend to replicate URL API, it's just a quick and dirty workaround.

    Explanation

    new URL() is the constructor of the URL API, which is not available by default in Google Apps Script in the same way that other Web API's aren't available.