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

    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
    Javier

    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

      I will let you know and update the post if i found a solution for it …sorry about that

      • Reply
        Brian

        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!

  • Reply
    kata

    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?

Leave a Reply

Your email address will not be published. Required fields are marked *