Get the redirect location of a URL using Google Apps Script

Problem statement

Sometimes you may have a list of URLs that are being used for sharing on social media (ex: t.co, lnkd.in, youtu.be etc.), trigger a .new action, or are simply not the actual destination / final domain that you may want to evaluate.

Now, instead of manually checking for each of such URLs on a browser to see where they lead, this post will cover how to create a Custom Function in Google Sheets that can run the script and return the output as needed.

Architecture

The short version to this is to make use of the followRedirects parameter within the UrlFetchApp.fetch method and set it to false.

A couple other things that i've done in this solution is -

• made an optional use of the validator.gs library (shameless plug) to check if the input is indeed a valid url (or not) so as to not waste the UrlFetchApp quota.
• in case the validator library isn't being used then created a short (inadequate) if validation that checks to ensure that an input actually exists and that we don't trigger the UrlFetchApp against a blank input, ensure there is no @ symbol in the input and that there is at least 1 period.
• finally, check for the 3xx status codes returned by urls designed to redirect and accordingly return an output

Codebase

You can access the entire script on my GitHub repository here, or make a copy of this Google Sheet to access the script directly as well.

/**
 * 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) {
  try { // use the validator.gs library => 1OLVhM4V7DKQaPLM0025IO_Url3xr8QnnLqTlC7viE9AtEIIG_-IPVDY0
    if (!validator.isURL(input)) return "INVALID_URL";
  } catch (err) {
    console.log(err);
  }
  if (input == null || input == undefined || input.toString().includes("@") || !input.toString().includes(".")) return "INVALID_URL";
  let response;
  try {
    response = UrlFetchApp.fetch(input, {
      muteHttpExceptions: true,
      followRedirects: false,
      validateHttpsCertificates: false
    });
  } catch (error) {
    console.log(error);
    return error.toString();
  }
  const status = response.getResponseCode();
  console.log(status);
  if (/3\d\d/.test(status)) { // https://en.wikipedia.org/wiki/URL_redirection#HTTP_status_codes_3xx
    const location = response.getAllHeaders().Location;
    console.log(location);
    return location;
  } else {
    return "NO_REDIRECTS_FOUND";
  }
}

Result

I tested this across a bunch of input variations:

• with & without HTTP(S) protocols
• entering invalid URL formats
• checking popular URL shorteners (like Twitter, LinkedIn, YouTube)

The most peculiar is the one in row 5 where the output is simply the canonical version of the slug /about/ — perhaps the script could be further modified to capture the hostname and protocol from the input and prepend it to the output 🤷🏽‍♀️

Variations

Feel free to modify the script to suit your needs. I could already imagine some may not want the exact format that's described in this post but instead, may want to get the output in the following formats -

1. follow the output till the last url that does not redirect any further and return only that (single, final) url
2. follow the output till the last url and return all the urls (perhaps in a comma separated, new line format) in the same/single cell