And I say, take Excel and call

VoximplantLike any cloud platform, it provides an API with which you can manage users, launch calls, view their history and logs, and do a lot of other useful things. Basically, almost everything that can be configured in the control panel can be done through the API.

But in the modern world, having an API is not enough – few people want to form HTTP requests, pass parameters, think about correct authorization. That’s why we offer SDK for different programming languages: Python, PHP, C # and many others. And it seems that this is enough to make our platform easy to use for a very large audience. Or is it not enough?

Let’s turn to statistics. According to various sources, there are now somewhere around 15-30 million developers in the world – the figure is undoubtedly impressive. But, for example, there are at least 100 million MS Excel users in the world. Why should they suffer? After all, let’s be honest, almost everyone who has opened Excel at least once clearly felt the lack of opportunities for managing communication platforms in this, without a doubt, a very flexible software product. Almost every day we receive hundreds of requests to our email address, which boil down to a very simple request: “I want to call from Excel!”. Once, people with such requirements even lined up at the windows of our office (did you see the photo above?) We simply could not stand aside.

However, calls are still too revolutionary, and most importantly, they will require the installation of additional ActiveX components, which, of course, contradicts all existing and non-existent information security policies, so let’s start with a simpler thing – an SDK for working with our API. VBA is available from the development tools in Excel, for which we will create an SDK.

In order to make an API request, you need to:

  1. Generate the URL and body of the POST request.

  2. Add authentication parameters.

  3. Execute the request directly.

  4. Parse the result (in our case, it is JSON).

Forming the URL and body of the POST request

The first part is seemingly the simplest: you just need to URL-encode the parameters and glue them together. But standard VBA is not URL-encoded (we’ll see why later). It’s okay, there are many different solutions on the Internet, let’s choose one of them.

Public Function URL_Encode(ByRef txt As String) As String
    Dim buffer As String, i As Long, c As Long, n As Long
    buffer = String$(Len(txt) * 12, "%")
 
    For i = 1 To Len(txt)
        c = AscW(Mid$(txt, i, 1)) And 65535
 
        Select Case c
            Case 48 To 57, 65 To 90, 97 To 122, 45, 46, 95  ' Unescaped 0-9A-Za-z-._ '
                n = n + 1
                Mid$(buffer, n) = ChrW(c)
            Case Is <= 127            ' Escaped UTF-8 1 bytes U+0000 to U+007F '
                n = n + 3
                Mid$(buffer, n - 1) = Right$(Hex$(256 + c), 2)
            Case Is <= 2047           ' Escaped UTF-8 2 bytes U+0080 to U+07FF '
                n = n + 6
                Mid$(buffer, n - 4) = Hex$(192 + (c  64))
                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
            Case 55296 To 57343       ' Escaped UTF-8 4 bytes U+010000 to U+10FFFF '
                i = i + 1
                c = 65536 + (c Mod 1024) * 1024 + (AscW(Mid$(txt, i, 1)) And 1023)
                n = n + 12
                Mid$(buffer, n - 10) = Hex$(240 + (c  262144))
                Mid$(buffer, n - 7) = Hex$(128 + ((c  4096) Mod 64))
                Mid$(buffer, n - 4) = Hex$(128 + ((c  64) Mod 64))
                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
            Case Else                 ' Escaped UTF-8 3 bytes U+0800 to U+FFFF '
                n = n + 9
                Mid$(buffer, n - 7) = Hex$(224 + (c  4096))
                Mid$(buffer, n - 4) = Hex$(128 + ((c  64) Mod 64))
                Mid$(buffer, n - 1) = Hex$(128 + (c Mod 64))
        End Select
    Next
    URL_Encode = Left$(buffer, n)
End Function

The next nuance is the transmission of date and time. The Voximplant API accepts timestamps in UTC in the format YYYY-MM-DD hh: mm: ss. In Excel, the date and time are stored without taking into account the time zone (in fact, in the table itself, they are generally stored as a floating point number). Therefore, we will have to accept the date / time from the table, too, UTC. Let’s say that all 100+ million Excel users know what UTC is, and that won’t raise any questions for them.

By the way, VBA has a date formatting function, and it even works, but in a very unusual way. The date format of interest to us is described as follows: “yyyy-mm-dd hh: mm: ss”. That is, “mm” is either a month or minutes, depending on whether it follows “hh” or “yyyy” (this is not a joke, it is even described in MSDN). In general, if someone wants to display the time without a clock, they will have to improvise.

Moving on to authentication

This is where the biggest disappointment awaits us. At Voximplant, we offer our clients to use JWT, which, of course, is quite tricky if you run queries from the console or browser, but when using our SDKs, this does not make life difficult for the developer at all. At the same time, JWT provides an extremely high level of security.

What about VBA? Unfortunately, there is simply no reasonably easy way to generate a JWT signature. The reason is that the .NET framework version 4.x is available in VBA, and the function RSA.ImportPkcs8PrivateKeyrequired to load a private key from PKCS8 appeared only in .NET 5. Anyway, all .NET developers use third-party libraries for such tasks.

Therefore, we will have to limit ourselves to authorization using a static API key. After all, as you know, one of the ways to handle information security risk is to accept this risk. So let’s do it. After all, we really, really need this SDK.

Still from The Big Lebowski (1998), Polygram Filmed Entertainment, Working Title Films
Still from The Big Lebowski (1998), Polygram Filmed Entertainment, Working Title Films

We execute the request

Let’s move on to the third part – the execution of the request itself. VBA does not have built-in tools for working with HTTP (now it is clear why there is no URL-encoding function, and why?).

But, nevertheless, this is a rather trivial manipulation – we connect the necessary MSXML 6.0 framework and Microsoft Scripting Runtime and execute the request, connecting the MSXML itself via COM. Simply!

Function makeRequest(name As String, params As Dictionary, accountId As Integer, apiKey As String) As Object

    Dim objHTTP As New MSXML2.XMLHTTP60
    Dim jsonData As String
    Dim parsedJson As Object
    Dim postString As String

    postString = ""
    
    Dim iterKey As Variant
    
    For Each iterKey In params.Keys
        postString = postString & "&" & iterKey & "=" & URL_Encode(params(iterKey))
    Next

    Url = "https://api.voximplant.com/platform_api/" + name
    objHTTP.Open "POST", Url, False
    objHTTP.send "account_id=" & accountId & "&api_key=" & apiKey & postString
    jsonData = objHTTP.responseText
    Set parsedJson = JsonConverter.ParseJson(jsonData)
    Set makeRequest = parsedJson

End Function

Parse JSON

And finally, JSON. Like everything else, the JSON parser must be looked for somewhere outside the VBA ecosystem. Fortunately, it’s 2021, there is GitHub, and someone is already puzzled about creating a JSON parser for VBA. We took like this

It connects as a separate module and turns the JSON string into a Dictionary. Exactly what is needed!

Next, we take the generator of one of our SDKs (we took the Python one), replace the templates and force it to generate VBA code. As a result, we get a ready-made SDK that can be downloaded on our Github

The SDK is a Class Module that you can plug into your favorite workbook in Excel and do all sorts of weird things with it. In principle, you can even launch calls, the SDK supports all the API functions necessary for this c). But this would not be a good idea, given that it is not completely known when Excel will decide to recalculate all the formulas (it is at this moment that the API function will be called).

But for some statistical tasks, the SDK, despite the absurdity of what is happening, may be suitable. For example, you can calculate which user spent the most money on calls over a certain period.

To do this, we write the following function:

Function getTotalCallCost(FromDate, ToDate, Username) As Double

    Dim totalCost As Double
    Dim lastCount As Integer
    Dim offset As Integer
    Dim res As Dictionary
    Dim RecordsPerRequest As Integer
    Dim api As New VoximplantAPI
    totalCost = 0
    lastCount = 1
    offset = 0
    RecordsPerRequest = 100
    
    'Pass Voximplant account id and API key
    api.SetCredentials 100, "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
    
    Do While lastCount > 0
        Set res = api.GetCallHistory(FromDate, ToDate, remote_number:=Username, with_calls:=True, with_records:=True, with_other_resources:=True, offset:=offset, count:=RecordsPerRequest)
        
        Dim session As Variant
        Dim item As Variant
        
        For Each session In res("result")
            For Each item In session("calls")
                totalCost = totalCost + item("cost")
            Next
            For Each item In session("records")
                totalCost = totalCost + item("cost")
            Next
            For Each item In session("other_resource_usage")
                totalCost = totalCost + item("cost")
            Next
        Next
        
        lastCount = res("count")
        offset = offset + RecordsPerRequest
    Loop
    
    getTotalCallCost = totalCost
End Function

And we call it as follows:

And by dragging the mouse over the lower right corner of the active cell, we can apply this formula to all users of interest to us and find out which of them called their cousin in Monaco more than others.

Summary:

If you wish, you can make some kind of SDK for VBA. Not a single developer was harmed during its creation. Oh yes, from April 1! : D

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *