Return link to redirect url

Question

I'm trying to get the url of the final destination of a specific website, but all the templates I've found to use as a function in my spreadsheet, only return the initial link:

https://stackoverflow.com/a/50733029

function getRedirect(url) {
  var response = UrlFetchApp.fetch(url, {'followRedirects': false, 'muteHttpExceptions': false});
  var redirectUrl = response.getHeaders()['Location']; // undefined if no redirect, so...
  var responseCode = response.getResponseCode();
  if (redirectUrl) {                                   // ...if redirected...
    var nextRedirectUrl = getRedirect(redirectUrl);    // ...it calls itself recursively...
    Logger.log(url + " is redirecting to " + redirectUrl + ". (" + responseCode + ")");
    return nextRedirectUrl;
  }
  else {                                               // ...until it's not
    Logger.log(url + " is canonical. (" + responseCode + ")");
    return url;
  }
}

This is the model where I put:
=getRedirect("https://c.newsnow.co.uk/A/1067471289?-833:12")

In the spreadsheet it returns:
https://c.newsnow.co.uk/A/1067471289?-833:12

I would like to collect the link to after redirect:
https://sports.ndtv.com/football/europa-league-bruno-fernandes-double-helps-manchester-united-thrash-real-sociedad-gareth-bale-stars-for-tottenham-2373767

user_7108653 · Answer

When I saw the HTML of the URL https://c.newsnow.co.uk/A/1067471289?-833:12, I thought that in this case, the value of https://sports.ndtv.com/football/europa-league-bruno-fernandes-double-helps-manchester-united-thrash-real-sociedad-gareth-bale-stars-for-tottenham-2373767 might be able to be directly retrieved using IMPORTXML and a xpath. The sample formula is as follows.

Sample formula:

=IMPORTXML(A1,"//a/@href")

In this case, please put the URL of https://c.newsnow.co.uk/A/1067471289?-833:12 to the cell "A1".

Result:

Using Google Apps Script:

When you want to use Google Apps Script, you can also use the following script. In this case, please put a custom formula of =SAMPLE("https://c.newsnow.co.uk/A/1067471289?-833:12") to a cell.

function SAMPLE(url) {
  const res = UrlFetchApp.fetch(url).getContentText();
  const v = res.match(/url: '([\s\S\w]+?)'/);
  return v && v.length == 2 ? v[1].trim() : "Value cannot be retrieved.";
}

Note:

In this sample formula and xpath is for the URL of https://c.newsnow.co.uk/A/1067471289?-833:12. So when you use this for other URLs, it might not be able to be used. So please be careful this.

Reference:

IMPORTXML