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 won’t exactly apply to every configuration. For 32 bit installation, follow the instructions to install CAPICOM, copy it to WindowsSystem32, and register it (regsrv32.exe capicom.dll). Note that it says run as administrator. If you don’t know how to do that, see these instructions. However there’s not a great need to encrypt your credentials in the registry nowadays, and CAPICOM is getting harder to get hold of for modern WIndows system. I fyou have problems, let me know on our forum and I’ll explain how to create a non encrypted version that doesn’t need capicom.
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.
Google describes the process like this
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 "https://ramblings.mcpher.com/somewhere-to-keep-those-api-keys-google/"
' 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 like 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
For help and more information join our forum, follow the blog, follow me on Twitter