Google URL Shortener API

Google has it's own URL shortener with an API you can connect with programmatically. Let's go over some methods we can use to do so.

The cynical part of me is convinced that this isn't part of Google's business model and is simply another way for them to collect usage data about its customers. But we'll continue anyway.

First we'll need to apply for an API key. You could use oAuth but I prefer the easier authentication method. Go get your API key now! It is reusable across several of their APIs, like PageSpeed.

The API provides four methods:

  1. Shorten a long URL
  2. Expand a short URL
  3. Look up a short URL's analytics
  4. Look up a user's history

For this exercise we will only be looking at the first two. The first thing we do is declare two constants: our API key and the base URL for all API requests:

Const API_KEY As String = "your Google API key here"
Const BASEURL As String = "https://www.googleapis.com/urlshortener/v1/url"

If you do not already have them, I would also grab the MSXML Object Library functions.

A few notes about the functions. They do not

  • check for "error" in the URL, but assume the presence of "error" means a bad response
  • check for syntactically valid URL strings (ex: through regex or HTTP HEAD request) – we'll try that later
  • cache the responses in case we check the same URL more than once

Shorten a URL using goo.gl

The following function takes a URL and API key and returns the shortened URL as a string. A POST request is required, and a JSON response is returned.

Function GetGooglURL(url As String, apiKey As String) As String
  Dim xml As Object  ' MSXML2.XMLHTTP60
  Set xml = GetMSXML

  If xml Is Nothing Then Exit Function

  With xml
    .Open "POST", BASEURL & "?key=" & API_KEY, False
    .setRequestHeader "Content-Type", "application/json"
    .Send Replace("{""longUrl"": ""http://www.google.com/""}", "http://www.google.com/", url)
  End With

  If InStr(xml.responseText, "error") = 0 Then  ' no error occurred
    ' parse out short URL from JSON response
    GetGooglURL = Trim$(Split(xml.responseText, """")(7))
  End If

End Function

Instead of trying to match up the double quotes with the input URL, I simply took the string from the goo.gl API page and used the Replace function on it. We then use the Split function to parse out the shortened URL.

Expand a URL using goo.gl

To expand a URL, simply call the same API with a different method name. Pass in the short URL and the API key as arguments. Note that the input string isn't checked to see if it is actually a goo.gl short URL. This method uses a GET request.

Function ExpandGooglURL(shortURL As String, apiKey As String) As String
' http://code.google.com/apis/urlshortener/v1/getting_started.html
  Dim xml As Object  ' MSXML2.XMLHTTP60
  Set xml = GetMSXML

  If xml Is Nothing Then Exit Function

  With xml
    .Open "GET", BASEURL & "?key=" & API_KEY & "&shortUrl=" & shortURL, False
    .Send
  End With

  If InStr(xml.responseText, "error") = 0 Then  ' no error occurred
    ' parse out short URL from JSON response
    ExpandGooglURL = Trim$(Split(xml.responseText, """")(11))
  End If

End Function

Again we use my favorite Split function to parse the returned URL from the API.

Sample Usage

Sub TestGoogl()

  Dim url As String
  Dim result As String

  url = "http://www.jpsoftwaretech.com/"

  result = GetGooglURL(url, API_KEY)
  Debug.Print "short url for " & url & " is: " & result
  
  result = ExpandGooglURL(result, API_KEY)
  Debug.Print "original url is " & result
  
End Sub

As the API page suggests, we could simply call the URL and check the Location header to get the original URL. Note that this works for any redirect, not just goo.gl links. To do so, visit Checking header responses.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
learn excel dashboards