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

bit.lygoo.glgoogle sheetsurl-shortening

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?

Best Answer

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()
Related Topic