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!.