Google Sheets Function to get a shortened URL (from Bit.ly or goo.gl etc.)

Question

I have a Google spreadsheet that builds Google Analytics links. I'd like to shorten those links. I tried the Bit.ly example but even their example does not work.

How can I do this?

Answer 1

This little code makes it possible to create shortened URL's, with the use of Google Apps Script and the URL Shortener API.

Prerequisites

Under resources in the script editor (Tools > Script editor) select the Advanced Google Services and activate the UrlShortener:
Then go to the Google Developers Console and select the URL for the API:
Enable the API and paste the code in to the script editor. Press the bug button to authenticate the script

Code

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Shorten")
    .addItem("Go !!","rangeShort")
    .addToUi()  
}

function rangeShort() {
  var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
  var output = [];
  for(var i = 0, iLen = data.length; i < iLen; i++) {
    var url = UrlShortener.Url.insert({longUrl: data[i][0]});
    output.push([url.id]);
  }
  range.offset(0,1).setValues(output);
}

Explained

The onOpen() function will create a menu item for you to select, having the name Shorten. Every time you open the file this menu item will be added (if you're in the script editor, select the function from the drop-down menu and press the play button. This will execute that particular function and the item will be added to the menu).

Once the menu is created, it will show the menu item Go !!. Select the cell or range that you want to shorten and the script will create the shortened URL's and add them to the sheet.

Screenshot

before

after

Note

The code makes the assumption that, once you've selected either the cell or range to be shortened, that the result will be added to the right of the selected cell/range (OFFSET(0,1)).
If you simply want to overwrite the existing URL, then change the code into this: (OFFSET(0,0)).

Example

I've created an example file for you: Create Shortened URL's

References

1. url-shortener
2. offset(Integer,Integer)
3. onOpen()

Answer 2

TinyURL has an API that spits out a text file with the shortened link. If you use the IMPORT DATA function, you can grab the text from that page. You also need to use the CONCATENATE function to string the URL together.

The structure of the tinyurl API is http://tinyurl.com/api-create.php?url=YOURLINKHERE

IMPORT DATA from that link would obviously not go to a valid link but it would input: http://tinyurl.com/oegcbsx.

CONCATENATE will put your text in that spot.

CONCATENATE of A1 and http://tinyurl.com/api-create.php?url= would generate the correct URL you are looking for. The IMPORT DATA will then grab the new short URL!

Here is the formula! Substitute B2 for wherever you are putting your long URL

=IMPORTDATA(CONCATENATE("http://tinyurl.com/api-create.php?url=" & B2))

Answer 3

About the Bitly link http://dev.bitly.com/spreadsheets.html

You need to replace the A1 cell with the url you'd like to use.

It's also quite handy that they provide a way to fetch click count too.