Excel-REST: Excel REST Client

It seems like everything has a REST webservice these days and there is no reason to leave Excel out of the fun. Also, as of V0.2 there's async support!

Getting started

In a new or existing workbook, open VBA (Alt+F11) and import all files from the src/ directory into the workbook (RestClientBase.bas is optional)
Add a reference to Microsoft Scripting Runtime: VBA Window > Tools > References > Select Microsoft Scripting Runtime
In a new module or class, create a new RestClient for the service, create a new RestRequest to request something specific from the service, and then use the client to execute the request (See below for a simple example)
That's it! There are many advanced uses for Excel-REST, including asynchronous requests so that Excel isn't locked up, Authenticators for accessing services with Basic, OAuth1, and OAuth2 authentication, and detailed requests for complex APIs. Find out more in the Wiki

The first step can be tedious, so you may want to use the blank workbook provided with the project.

Example

The following is a simple of example of calling the Google Maps API to get directions between two locations (including the travel time) and processing the results

Simple Example: Get directions

Function GetDirections(Origin As String, Destination As String) As String
    ' Create a RestClient for executing requests
    ' and set a base url that all requests will be appended to
    Dim MapsClient As New RestClient
    MapsClient.BaseUrl = "https://maps.googleapis.com/maps/api/"
    
    ' Create a RestRequest for getting directions
    Dim DirectionsRequest As New RestRequest
    DirectionsRequest.Resource = "directions/{format}"
    DirectionsRequest.Method = httpGET
    
    ' Set the request format -> Sets {format} segment, content-types, and parses the response
    DirectionsRequest.Format = json
    
    ' (Alternatively, replace {format} segment directly)
    DirectionsRequest.AddUrlSegment "format", "json"
    
    ' Add parameters to the request (as querystring for GET calls and body otherwise)
    DirectionsRequest.AddParameter "origin", Origin
    DirectionsRequest.AddParameter "destination", Destination
    
    ' Force parameter as querystring for all requests
    DirectionsRequest.AddQuerystringParam "sensor", "false"
    
    ' => GET https://maps.../api/directions/json?origin=...&destination=...&sensor=false
    
    ' Execute the request and work with the response
    Dim Response As RestResponse
    Set Response = MapsClient.Execute(DirectionsRequest)
    
    If Response.StatusCode = 200 Then
        ' Work directly with parsed json data
        Dim Route As Object
        Set Route = Response.Data("routes")(1)("legs")(1)
        
        GetDirections = "It will take " & Route("duration")("text") & _
            " to travel " & Route("distance")("text") & _
            " from " & Route("start_address") & _
            " to " & Route("end_address")
    Else
        GetDirections = "Error: " & Response.Content
    End If
End Function

Async Example: Add async to getting directions

Function GetDirections(Origin As String, Destination As String) As String
    Dim MapsClient As New RestClient
    Dim DirectionsRequest As New RestRequest
    ' ... Create client and request using Simple Example
    
    ' Execute the request asynchronously with RestResponse being passed into callback
    MapsClient.ExecuteAsync DirectionsRequest, "ProcessDirections"
    
    ' Keep working, handling response later
End Function

Public Sub ProcessDirections(Response As RestResponse)
    ' Handle response once the request has returned
    If Response.StatusCode = Ok Then
        Dim Route As Dictionary
        Set Route = Response.Data("routes")(1)("legs")(1)

        Debug.Print "It will take " & Route("duration")("text") & _
            " to travel " & Route("distance")("text") & _
            " from " & Route("start_address") & _
            " to " & Route("end_address")
    Else
        Debug.Print "Error: " & Response.Content
    End If
End Sub

For more details, check out the Wiki

Release Notes

2.3.0

Add form-urlencoded format and helpers
Combine Body + Parameters and Querystring + Parameters with priority given to Body or Querystring, respectively

2.2.0

Add cookies support with Request.AddCookie(key, value) and Response.Cookies
2.2.1 Add Response.Headers collection of response headers

2.1.0

Add Microsoft Scripting Runtime dependency (for Dictionary support)
Add RestClient.SetProxy for use in proxy environments
2.1.1 Use Val for number parsing in locale-dependent settings
2.1.2 Add raw binary Body to RestResponse for handling files (thanks @berkus)
2.1.3 Bugfixes and refactor

2.0.0

Remove JSONLib dependency (merged with RestHelpers)
Add RestClientBase for future use with extension for single-client applications
Add build scripts for import/export
New specs and bugfixes
2.0.1 Handle duplicate keys when parsing json
2.0.2 Add Content-Length header and 408 status code for timeout

1.1.0

Major Changes:

Integrate Excel-TDD to fully test Excel-REST library
Handle timeouts for sync and async requests
Remove reference dependencies and use CreateObject instead

Bugfixes:

Add cachebreaker as querystring param only
Add Join helpers to resolve double-slash issue between base and resource url
Only add "?" for querystring if querystring will be created and "?" isn't present
Only put parameters in body if there are parameters

0.2

Add async support

About

Design based heavily on the awesome RestSharp
Author: Tim Hall
License: MIT

cduv/Excel-REST