2

I have a column of URLs with hidden URL values (meaning that when you click on it you are redirected to the real website e.g. www.disney.com):

enter image description here

Cell example:

=HYPERLINK("https://www.example.com/go.php?website=128660","https://www.example.com/go.php?website=128660") 

I need to visit each link manually to get the real URL and put it in the cell next to it.

What is the best way to automate this process?

1

Check redirectnator

enter image description here

Main purpose of the script is to check server code status but you can also look for the final url.

Here's the code

function redirectCheck(url, user, pwd) {
  try {
    function getResp(url, user, pwd){  
      var resp = UrlFetchApp.fetch(url, {
        muteHttpExceptions: true,
        followRedirects: false,
        headers: {
          'Authorization': 'Basic ' + Utilities.base64Encode(user+':'+pwd)
        }
      });
      return resp;
    }

    var response = getResp(url, user, pwd);
    var rCode = response.getResponseCode();
    var redirectCount = 0;
    var tCode = rCode.toString();
    var location = url;
    var domain = getDomain(url);

    while (rCode == 301 || rCode == 302 && redirectCount <= 10) {
      redirectCount++;
      header = response.getHeaders();
      location = getFullUrl(header['Location'],domain);
      domain = getDomain(location);
      Logger.log('location: '+location);
      response = getResp(location, user, pwd);
      rCode = response.getResponseCode(); 
      tCode = tCode + " > " + rCode.toString();
      Utilities.sleep(500);// pause in the loop for 500 milliseconds
    }     


    Logger.log('redirectCount: '+redirectCount);
    return tCode + "|" + redirectCount + "|" + location;


  } catch (error) {
    Logger.log(error);
    return "Error| |"+error;
  }
}

function getDomain(url) {
  var domain = '',
      protocol;
  if (url.indexOf("://") > -1) {
    domain = url.split('/')[2];
    protocol = url.split('/')[0];    
    //remove port number
    domain = domain.split(':')[0];
    //add protocol back
    domain = protocol+"//"+domain;
  }  

  return domain;
}

function getFullUrl(url,prevDom) {
  var fullUrl,
      domain = getDomain(url);
  if(domain == ''){
    fullUrl = prevDom+url;
  } else {
    fullUrl = url;
  }       

  return fullUrl;
}

function redirectCheckTest() {
  var test = redirectCheck('www.google.com');
  Logger.log('test: '+test);
}
| improve this answer | |
  • Thanks a lot! This is what I need! But the next challenge is that my website requires login to redirect those links. I've tried "Username", "Password" fields provided by Redirectnator tool but it doesn't work. Do you have any ideas? – Olex Apr 27 at 18:11
  • Do you have an URL to share ? what tech the website is using ? – pjmg Apr 27 at 21:06
-1

Please use the following formula:

=IFNA(ArrayFormula(REGEXEXTRACT(A1:A,".+\w+")))

(adjust the range to your needs)

Functions used:

| improve this answer | |
  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Apr 27 at 12:44
  • 1
    Thanks for your help! I think I wasn't clear enough in my question. Please have a look on the updated one. – Olex Apr 27 at 12:57
  • I see your edit. So how could the sheet know which site the link redirects to? Is the real URL info anywhere in any cell? – marikamitsos Apr 27 at 13:00
  • There is no real info in the cell. I need to visit URL to get the real one. – Olex Apr 27 at 13:02
  • "I need to visit each link manually to get the real URL and put it in the cell next to it." There is no other way. That is the whole point of masking a URL. The masking action takes place on the server side, so you do have to click on the link. – marikamitsos Apr 27 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.