Reverse Shortened URL to Get the Original URL Before the Shortening in Google Sheets

Question

I have a sheet where I collect tweets with links based on a search query. Twitter, however, is handing me for each tweet their short URL format (t.co) rather than the original URL that was shared.

Is there a way to use formulas or google code to retrace the short url and fetch the final destination URL? The original that was shared by the person authoring the tweet?

     What i have        |       what i'm looking for
---------------------------------------------------------------------------
https://t​.co/dura1sUSxm | https://www.jpost.com/Breaking-News/Russia-Saudi-Arabia-plan-deals-for-2-bln-for-Putins-visit-to-Riyadh-604200
https://t​.co/Ayy7ww8dFX | https://www.washingtonpost.com/national-security/trump-says-little-as-his-gop-allies-condemn-turkeys-incursion-into-syria/2019/10/09/c46210f6-eaab-11e9-9306-47cb0324fd44_story.html
https://t​.co/WLj6PipXkC | https://www.newsweek.com/teacher-fired-refusing-sign-pro-document-1262083
https://t​.co/UoqiqfaHup | https://www.reuters.com/article/us-environment-waste-idUSKBN1WP1RE
https://t​.co/hO9swbmeeZ | https://www.washingtonpost.com/national-security/trump-says-little-as-his-gop-allies-condemn-turkeys-incursion-into-syria/2019/10/09/c46210f6-eaab-11e9-9306-47cb0324fd44_story.html
https://t​.co/Ve8ZpCp1s1 | https://www.reuters.com/article/us-environment-waste-idUSKBN1WP1RE

Answer 1

Answer:

Yes, this is possible using the fetch method of UrlFetchApp, gethering the headers and reading the Location property.

Method:

You can use UrlFetchApp to get the headers of the destination which contain the final URL endpoint. It is important to put this in a loop however, as sometimes multiple URL shortening services are chained in tandem and so you may not get the destination URL after just one fetch.

Code:

function getLocation(url) {
  var fetched = UrlFetchApp.fetch(url, {
    followRedirects: false
  })

  if (fetched.getHeaders().Location !== undefined) {
    return getLocation(fetched.getHeaders().Location)
  }
  return url
}