Google-sheets – How to get hidden URL value in Google Sheets

google sheetsgoogle-apps-script

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?

Best Answer

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);
}
Related Topic