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?

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: enter image description here
Then go to the Google Developers Console and select the URL for the API: enter image description here
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
enter image description here

after
enter image description here

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()
  • This is great. Is it possible for the script to run automatically if a url is added to the sheet? – Craig.Pearce Aug 26 '17 at 4:48
  • @Craig.Pearce Like, via a Google Forms you mean? – Jacob Jan Tuinstra Aug 26 '17 at 6:10
  • No, I was thinking more along the lines of an event listener so that if user puts a url in A2 that this script is automatically run and places the shortened version in B2. – Craig.Pearce Aug 26 '17 at 14:07

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))

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.

protected by Community Feb 9 '17 at 13:26

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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