Quick Links

Other stuff

Ask questions, get help, comment, collaborate

Site owners

  • Bruce Mcpherson
Services‎ > ‎Excel Liberation‎ > ‎

Google Oauth2 VBA authentication

One of the things that people have a lot of trouble with is authentication to web services. There are many different solutions, some of which are already covered on this site- for example - Basic HTTP Authentication from VBA , Digest authentication from Google Apps Script, Google Apps ScriptDB, Delegation to Google Apps Script to name but a few. 

Recently, Kyle Beachill did a nice guest post on this site,  showing a VBA implementation of OAUTH2. In this post, I've taken this a little further, with an implementation that minimizes the need for client secrets to be passed around. 

All associated libraries are available in the cDataSet.xlsm library downloadable here,  or through gistthat, as described here. You'll need the cRest module.

I recommend that you keep a single workbook for initial authorization for each scope you plan to use on this machine. You only need to run it once. There is a blank workbook with the necessary modules - oauth2credentials.xlsm - which you can download from the downloads page. That way any other workbooks that you run on this machine never need your oauth2 credentials in them.

CAPICOM

This is a method of encryption that is often not installed by default. It is used to store your If you get an error about being unable to create a capicom object, you'll need to install capicom. Here's a Microsoft Engineer describing how to do it. These instructions wont exactly apply to every configuration. For 32 bit installation, follow the instructions to install CAPICOM, copy it to Windows\System32, and register it (regsrv32.exe capicom.dll)

Overview

OAUTH2 is a standard dance that many have implemented for their web service authentication. The implementations are not always syntactically the same, but the steps are standard. Here I'll cover how to implement Google Authentication, and add others to the classes later. 

The oauth2 dance seems complicated at first, but in principle it goes like this.
  • An initial get request to a web service with which you have registered your app , passes some client identification and scope information to an authorization endpoint URL. This phase is called user consent, and usually would involve the user, through a browser, accepting that the access is allowed for the scope being requested (for example, access to google drive data)
  • If successful, an access code is returned to a redirect URL associated with your app. In the case of a native app such as Excel, a redirect URL is not possible, so the code is returned as part of the returned URL. 
  • A post is then made, this time to a token request endpoint, with some of the same data used in the access request, the access code, and additional client secret information - all as part of post data
  • If successful, an access token and a refresh token, along with an expiry time is returned. The access token can be used in the header of any requests made within the expiry time. 
  • Even if the access token has expired, the user consent stage can be avoided if a refresh token is available. In this case, the app requests a new access token by sending the refresh token, along with some client secret data, to the refresh URL. If this is successful the new access token can now be used, with later refreshes repeated when it expires.


Implementation

There are a few challenges implementing this in VBA, as hiding the required refresh tokens, client secrets and even receiving the access code through a dummy redirect URL is quite complex. Web applications can use cookies, and have control over return URLS. I did cover Cookies and Excel in other articles, but in this case I decided to go with Kyle's approach and use the registry, but this time I store and encrypt all the information needed to access and refresh authentication after one initial usage. 

NOTE: I have not implemented encryption on Excel 64 bit yet. Registry items will remain unencryped if you are using Excel 64bit. Will update that later.

Usage

In normal usage, this pattern would be used. Here I'm getting authenicated for access to google Drive documents, and printing the authorization header that can then be used in subsequent drive requests. See the comments on having a single workbook with your credentials in them.

    With getGoogled("drive")
        Debug.Print .authHeader
        .tearDown
    End With

Here's how I would use it - in this case, to execute a google apps script that is accessible only to me, after the authentication process

Private Sub showLinkedinConsole()
    Dim url As String, cb As cBrowser
    Set cb = New cBrowser
    
    ' see http://excelramblings.blogspot.co.uk/2012/10/somewhere-to-keep-those-api-keys-google.html
    ' for how to store credentials in a google lockbox

    url = "https://script.google.com/a/macros/mcpher.com/s/" & _
    "AKfycbza96-Mpa47jlqXoPosk64bUfR8T7zO5POZMYyN45InrvX8gm28/exec" & _
    "?action=show&entry=linkedinauth"
    
    With getGoogled("drive")
        If .hasToken Then
            Debug.Print cb.httpGET(url, , , , , .authHeader)
        Else
            MsgBox ("failed to authenticate: " & .denied)
        End If
        .tearDown
    End With

    cb.tearDown
    
End Sub

First time usage

In the above examples, there is no authentication dialog for userConsent, since existing or refreshed access tokens, stored and encrypted in the registry are being used. They look like this



However, the first time a particular windows user executes this , credentials need to be supplied for each scope. This can be provided in 3 ways. 
  • By downloading the data from the google apps console (when you register your app you have the opportunity to download a json file with your settings)
  • By providing the clientSecret and clientID as arguments to the authentication request. 
  • By doing an authrequest that clones the clientSecret and clientId from an already authenticated scope

The simple way to get started

I would recommend that you keep all your credentials in one workbook, that you use only for that. You will only need to run it once for each scope, and then any other workbooks that need to do oauth2 will not need the credentials in them. Here's an example of what the code looks like. This shows the inital setup on this machine for 3 scopes (viz, drive and analytics). All you have to do is substitute the arguments in the first one with your credentials, and execute this once. You can download a blank workbook with the necessary modules - oauth2credentials.xlsm - which you can download from the downloads page.  If you don't have the CAPICOM encryption library on your machine, it will complain. See the beginning of this page for how to install it. CAPICOM is important to encrypt and protect your credentials, since they are stored in the windows registry.

Private Sub firstTimeOauth2()
    
    ' if you are calling for the first time ever you can either provide your
    ' clientid/clientsecret - or pass the the jsonparse retrieved from the google app console
    ' normally all this stuff comes from encrpted registry store
    ' first ever

    'or you can do first ever like this
    With getGoogled("viz", , "xxxxxxxx.apps.googleusercontent.com", "xxxxxxxxxxxxx")
        Debug.Print .authHeader
        .tearDown
    End With

    With getGoogled("drive", , , , , "viz")
        Debug.Print .authHeader
        .tearDown
    End With
    
    With getGoogled("analytics", , , , , "viz")
        Debug.Print .authHeader
        .tearDown
    End With

End Sub

The Google Console data. 

When you register your app, you can download the associated JSON. This includes all the client and endpoint information needed. The first time you use the VBA cOauth2 class, you can force it to generate a new authorization package by using this data.

Here I've just copied the contents of the JSON file as a VBA string. (For simplicity I replaced all the double quotes with single quotes in the json data), and return as a cJobect.  Here's the pattern.

Private Function makeMyGoogleConsole() As cJobject
    Dim consoleJSON As String
 
     consoleJSON = _
    "{'installed':{'auth_uri':'https://accounts.google.com/o/oauth2/auth'," & _
      "'client_secret':'xxxxxxxxx'," & _
      "'token_uri':'https://accounts.google.com/o/oauth2/token'," & _
      "'client_email':'','redirect_uris':['urn:ietf:wg:oauth:2.0:oob','oob']," & _
      "'client_x509_cert_url':'','client_id':'xxxxxxx.apps.googleusercontent.com'," & _
      "'auth_provider_x509_cert_url':'https://www.googleapis.com/oauth2/v1/certs'}}"
      
      Set makeMyGoogleConsole = JSONParse(consoleJSON)

End Function

You then use it like this
    Set myConsole = makeMyGoogleConsole
    With getGoogled("drive", myConsole)
        Debug.Print .authHeader
        .tearDown
    End With

Client ID and secret as arguments

You can also pass these as arguments the first ever authentication. In this case, the end points will be the usual Google Endpoints. 

    With getGoogled("drive", , "xxxxxxx.apps.googleusercontent.com", "xxxxxxxxxxxxxxxx")
        Debug.Print .authHeader
        .tearDown
    End With

Cloning Client ID and secret from another scope

In this case, we'll be authenticating the drive scope for the first time, using the already authenticated viz scope to provide the credentials

    With getGoogled("drive", , , , , "viz")
        Debug.Print .authHeader
        .tearDown
    End With


In all cases, the initial dialog will look this this



You can provoke this dialog, and a refresh of the entry in the registry at any time by authentication with any of these parameters provided. 

More detail and code


 You can get me on Google plus, Twitter or this forum.

For help and more information join our forum,follow the blog or follow me on twitter .