[Google Docs/Sheets] Redirect checker / statuscode

So I stumbled across this code on the internet.
It let's you check the statuscode and redirect url for the given URL in Google Sheets. Unfortunately the script doesn't return the final url or the final statuscode. So If I have a url that consists of 5 redirects, the script only shows the error and url of the first redirect url.

I was wondering if it's possible in Google Sheets to show all urls of a redirect chain, especially the final redirect url. If an url has 5 direct then it will show 5 different links. Example
I know it's possible within Excel with VBA (click for script), but don't have a clue how to reproduce it in Google Sheets

Hope someone can give me some directions to accomplish this in Google sheets

PHP Code: 

function statuscode(url, user, pwd) {
    try {
        var response = UrlFetchApp.fetch(url, {
            muteHttpExceptions: true,
            followRedirects: false,
            headers: {
                'Authorization': 'Basic ' + Utilities.base64Encode(user+':'+pwd)
            }
        });
        return response.getResponseCode();
    } catch (error) {
        
        return "Error";
    }
}



function location(url, user, pwd) {
    try {
        var response = UrlFetchApp.fetch(url, {
            muteHttpExceptions: true,
            followRedirects: false,
            headers: {
                'Authorization': 'Basic ' + Utilities.base64Encode(user+':'+pwd)
            }
        });
        header = response.getHeaders();
        location = header['Location'];
        return location;
    } catch (error) {
        return "Error";
    }
} 


bump. same question here