Basic HTTP Authentication from VBA , Digest authentication from Google Apps Script, Google Apps ScriptDB, Delegation to Google Apps Script to name but a few. 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 - 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. CAPICOMThis 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 Windows\System32, 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. Hwever 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. OverviewOAUTH2 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.
Google describes the process like this ImplementationThere 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. UsageIn 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 usageIn 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.
The simple way to get startedI 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 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 argumentsYou 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 scopeIn 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
|
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation >