How to Use Excel VBA and Formulas to Access OAuth 2.0 Protected Resources

OAuth 2.0 is a protocol that allows applications to access protected resources on behalf of a user, without requiring the user to share their credentials with the application. OAuth 2.0 uses tokens to represent the user’s authorization and identity, which can be obtained from an authorization server using different grant types.

In this article, we will focus on the client credentials grant type, which is suitable for applications that need to access their own resources on the server. We will use Excel VBA to make requests to the authorization server and the resource server, and use Excel formulas to parse the JSON responses.

The client credentials grant type follows these steps:

• The client (application) authenticates with the authorization server and requests an access token.
• The authorization server validates the client credentials and issues an access token.
• The client uses the access token to access the protected resources on the resource server.
• The resource server validates the access token and returns the requested resources.

The client credentials grant type requires the client to have a client ID and a client secret, which are obtained from the authorization server when the client registers. The client ID and the client secret are used to authenticate the client with the authorization server.

The access token is a string that represents the user’s authorization and identity. It is usually encoded in JSON Web Token (JWT) format, which consists of three parts: header, payload, and signature. The header and the payload are JSON objects that contain information about the token, such as the issuer, the expiration time, the scope, etc. The signature is a hash of the header and the payload, encrypted with the authorization server’s private key. The signature ensures the integrity and authenticity of the token.

The access token is sent to the resource server in the HTTP request header, using the Authorization header with the Bearer scheme. For example:

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJodHRwczovL2F1dGguZXhhbXBsZS5jb20iLCJzdWIiOiJteWFwcCIsImV4cCI6MTYxMTIzNDU2Niwic2NvcGUiOiJyZWFkL3dyaXRlIn0.5yC9Z2y2n8f7ZqQK4aQ4g1j9X1w0ZyZw0Fw0H0j0K0k

The resource server verifies the access token by decoding the JWT and checking the signature, the issuer, the expiration time, and the scope. If the access token is valid, the resource server returns the requested resources in the HTTP response body, usually in JSON format.

Procedures

To get an access token from an oAuth2 token server in Excel VBA, we need to follow these steps:

• Define the variables and constants for the client ID, the client secret, the token URL, the resource URL, and the scope.
• Create a WinHttpRequest object and set the request method, URL, and headers.
• Send the request and get the response.
• Parse the response and extract the access token.
• Create another WinHttpRequest object and set the request method, URL, and headers, using the access token.
• Send the request and get the response.
• Parse the response and extract the resource data.

To parse the JSON responses in Excel formulas, we need to follow these steps:

• Use the FILTERXML function to convert the JSON string to an XML document.
• Use the XPATH syntax to query the XML document and get the desired value.

Example

Let’s assume we have the following information:

• Client ID: myapp
• Client secret: mysecret
• Token URL: https://auth.example.com/api/v1/oauth/token
• Resource URL: https://api.example.com/v1/items
• Scope: read/write

We want to get an access token from the token server and use it to access the items resource on the resource server. We also want to display the name and the price of the first item in the response.

We can use the following VBA code to make the requests and get the responses:

Sub GetAccessTokenAndResource()
    ' Define the variables and constants
    Dim clientID As String, clientSecret As String, tokenURL As String, resourceURL As String, scope As String
    Dim tokenRequest As WinHttpRequest, tokenResponse As String, accessToken As String
    Dim resourceRequest As WinHttpRequest, resourceResponse As String, resourceData As String
    
    clientID = "myapp"
    clientSecret = "mysecret"
    tokenURL = "https://auth.example.com/api/v1/oauth/token"
    resourceURL = "https://api.example.com/v1/items"
    scope = "read/write"
    
    ' Create a WinHttpRequest object for the token request
    Set tokenRequest = New WinHttpRequest
    
    ' Set the request method, URL, and headers
    tokenRequest.Open "POST", tokenURL, False
    tokenRequest.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    tokenRequest.SetRequestHeader "Authorization", "Basic " & Base64Encode(clientID & ":" & clientSecret)
    
    ' Send the request and get the response
    tokenRequest.Send "grant_type=client_credentials&scope=" & scope
    tokenResponse = tokenRequest.ResponseText
    
    ' Parse the response and extract the access token
    accessToken = ParseJSON(tokenResponse, "access_token")
    
    ' Create another WinHttpRequest object for the resource request
    Set resourceRequest = New WinHttpRequest
    
    ' Set the request method, URL, and headers
    resourceRequest.Open "GET", resourceURL, False
    resourceRequest.SetRequestHeader "Authorization", "Bearer " & accessToken
    
    ' Send the request and get the response
    resourceRequest.Send
    resourceResponse = resourceRequest.ResponseText
    
    ' Parse the response and extract the resource data
    resourceData = ParseJSON(resourceResponse, "data")
    
    ' Write the responses and the data to the worksheet
    Range("A1").Value = "Token Response"
    Range("A2").Value = tokenResponse
    Range("A4").Value = "Resource Response"
    Range("A5").Value = resourceResponse
    Range("A7").Value = "Resource Data"
    Range("A8").Value = resourceData
End Sub

We can use the following Excel formulas to parse the JSON responses and get the desired values:

• To get the access token from the token response, we can use:

=FILTERXML("<t><s>"&SUBSTITUTE(A2,"""","</s><s>")&"</s></t>","//s[.='access_token']/following-sibling::s[1]")

• To get the name of the first item from the resource data, we can use:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"[",""),"]",""),""""","</s><s>")&"</s></t>","//s[.='name']/following-sibling::s[1]")

• To get the price of the first item from the resource data, we can use:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"[",""),"]",""),""""","</s><s>")&"</s></t>","//s[.='price']/following-sibling::s[1]")

Other Approaches

There are other ways to get an access token from an oAuth2 token server in Excel VBA, such as using the authorization code grant type, the implicit grant type, or the resource owner password credentials grant type. However, these grant types require the user to interact with the authorization server and provide their consent or credentials, which may not be suitable for some applications.

There are also other ways to parse the JSON responses in Excel formulas, such as using the JSON functions in Excel 365, or using custom functions created with VBA or JavaScript. However, these methods may not be compatible with older versions of Excel, or may require additional coding or installation.