Google Sheets function to recursively resolve URLs and get status codes =getStatusCode() and =getRedirects()

resolveRedirect.gs

const options = {
  'muteHttpExceptions': true,
  'followRedirects': false
};

function getStatusCode(url) {
  const url_trimmed = url.trim();

  let cache = CacheService.getScriptCache();
  let result = cache.get(url_trimmed);

  if (!result) {
    const response = UrlFetchApp.fetch(url_trimmed, options);
    const responseCode = response.getResponseCode();

    cache.put(url_trimmed, responseCode, 21600);
    result = responseCode;
  }
  return result;
}

function getRedirects(url) {    
  const response = UrlFetchApp.fetch(url, options);
  const redirectURL = response.getHeaders()['Location'];
  // resolve recursively
  if (response.getResponseCode() == 301) {
    return getRedirects(redirectURL)
  }
  return redirectURL;
}

// adapted from https://blog.webverge.io/check-http-status-code-and-redirected-urls-on-google-sheets/#How_to_Check_HTTP_status_codes_and_Redirected_URLs_on_Google_Sheets