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.
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 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.
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.
Google describes the process like this
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.
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.
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
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.
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.
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.
You then use it like this
You can also pass these as arguments the first ever authentication. In this case, the end points will be the usual Google Endpoints.
In this case, we'll be authenticating the drive scope for the first time, using the already authenticated viz scope to provide the credentials
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.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation >