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)
Grabbing your APIs
In this stage we will go and grab our api from each service provider respectively:
- 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
Some apis requires you to have an Account and/ Or API Keys like Bit.ly, Click.me and Cut.by
Creating your Functions
In order for us to pull the data and the results from the api query, we need to use two functions:
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
We are going to use the Bit.ly API in the Function Below:
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!.
How about Google’s URL Shortener. Can you please write a code for that?
I just answered your Q on Stackoverflow 🙂 thank you
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.
thats exactly the reason i didnt include in the tutorial above .. thank you Henry
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?
I will let you know and update the post if i found a solution for it …sorry about that
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!
This works great. The only thing is the long url ends up being exposed l in the footnotes of the google spreadsheet. Anyway to avoid this?
can you show me a screenshot of what is happening Kathleen ?