Create Short URLs Using APIs and Google Docs

The spreadsheet component of Google Docs have some great functions that will help import web data into your online documents. At LEBSEO, we wanted to create a spreadsheet where the user can input a Long Url and Google Docs will automatically parse these URLs into multiple short URLs.

In this post we created this spreadsheet as a demo , and we used 5 different URL shorteners (and of course all of them use 301 redirect)

[list type=”check3″]

Bit.ly
s.coop
cut.by
Click.me
is.gd

[/list]

[br]

Grabbing your APIs

[br]

In this stage we will go and grab our api from each service provider respectively:

[list type=”info”]

Bit.ly = http://api.bitly.com/v3/shorten?login=USERNAME&apiKey=APIKEY&longUrl=URL&format=txt[/code]
s.coop = http://s.coop/devapi.php?action=shorturl&url=URL&format=simple[/code]
cut.by = http://cut.by/?longurl=”URL“&api_key=APIKEY[/code]
Click.me = http://clicky.me/app/api?username=USERNAME&password=PASSWORD&url=URL
is.gd = http://is.gd/create.php?format=simple&url=URL

[/list]

Some apis requires you to have an Account and/ Or API Keys like Bit.ly, Click.me and Cut.by

[br]

Creating your Functions

[br]

In order for us to pull the data and the results from the api query, we need to use two functions:

[list type=”check3″]

ImportData
concatenate

[/list]

the ImportData Function is going to import exactly the results from the web to the spreadsheet and the concatenate function will use it to help us create the URL with a variable to execute the API query.

example concatenate(“FA”,”CE”,”BOOK”) = the results for the formula will be FACEBOOK

[br]

We are going to use the Bit.ly API in the Function Below:

[br]

[code]= importData(concatenate(“http://api.bitly.com/v3/shorten?login=USERNAME&apiKey=APIKEY&longUrl=”,B1,”&format=txt”))[/code]

So let me explain, here we added a function where Google docs will go and execute the query of bit.ly api usings our URL located in B1. and we requested the format of the response to be TXT. which will help us get only the Short URL without any additional data.

By clicking enter, Google Docs with execute and provide you with the short URL.

Hope you enjoyed this Small tip, if you have any questions please do Ask!.

Cheers

9 thoughts on “Create Short URLs Using APIs and Google Docs”

Reply ↓

Henry November 30, 2012 at 4:29 pm

How about Google’s URL Shortener. Can you please write a code for that?

Reply ↓

Wissam Dandan Post author November 30, 2012 at 5:24 pm

I just answered your Q on Stackoverflow 🙂 thank you

Reply ↓

Henry November 30, 2012 at 5:32 pm

Thanks,
I am not really a programmer, so it’s not of much help.
I was looking for an easy way such as in this post.
It seems Google doesn’t have one line code implementation similar to the other shorteners.
Thanks anyways.

Reply ↓

Wissam Dandan Post author November 30, 2012 at 5:35 pm

thats exactly the reason i didnt include in the tutorial above .. thank you Henry

Reply ↓

Javier June 24, 2013 at 12:24 pm

Hi,
This works really really good. something I have notice is that if you contatenate a utm parameters before shortening the url, bitly can work with the “&” so it breaks the url and don’t short it properly. Should I change the “TXT” for other element?
Thanks

Reply ↓

Wissam Dandan Post author June 24, 2013 at 1:26 pm

I will let you know and update the post if i found a solution for it …sorry about that
- Reply ↓
  
  Brian July 16, 2013 at 6:59 pm
  
  The Bit.ly api accepts only URI encoded strings. The “?”, “=” in the Google Analytics encoded URL therefore breaks it. If you find a quick converter, let us know!