А я говорю, возьми Excel и позвони

Voximplant, как и любая облачная платформа, предоставляет API, с помощью которого можно управлять пользователями, запускать звонки, просматривать их историю и логи и делать немало других полезных вещей. В принципе, практически всё, что можно настроить в панели управления, можно сделать и через API.

Но в современном мире иметь API недостаточно – мало кто хочет формировать HTTP-запросы, передавать параметры, думать про правильную авторизацию. Поэтому мы предлагаем SDK для разных языков программирования: Python, PHP, C# и многих других. И кажется, что этого достаточно, чтобы сделать нашу платформу лёгкой в использовании для очень большой аудитории. Или всё-таки недостаточно?

Обратимся к статистике. По разным данным сейчас в мире насчитывается где-то 15-30 миллионов разработчиков – цифра несомненно впечатляющая. Но, например, пользователей MS Excel в мире не менее 100 миллионов. Почему же они должны страдать? Ведь, будем честны, почти каждый из тех, кто хоть раз открывал Excel, явно ощущал недостаток возможностей по управлению коммуникационными платформами в этом без сомнения очень гибком программном продукте. Практически каждый день мы получаем на наш email сотни запросов, которые сводятся к очень простой просьбе: «Я хочу звонить из Excel!». Однажды у окон нашего офиса даже выстроились люди с такими требованиями (видели фото выше?) Мы просто не могли оставаться в стороне.

Однако звонки – это всё-таки слишком революционно, а главное, потребует установки дополнительных ActiveX-компонентов, что, безусловно, противоречит всем существующим и несуществующим политикам информационной безопасности, поэтому давайте начнём с более простой вещи – SDK для работы с нашим API. Из средств разработки в Excel доступен VBA, для него мы и создадим SDK.

Для того чтобы выполнить API-запрос, необходимо:

1. Сформировать URL и тело POST-запроса.

2. Добавить аутентификационные параметры.

3. Непосредственно выполнить запрос.

4. Распарсить результат (в нашем случае это JSON).

Формируем URL и тело POST-запроса

Первая часть, казалось бы, самая простая: нужно просто закодировать параметры в URL-кодировку и склеить их. Но в стандартном VBA не предусмотрена URL-кодировка (позже мы поймём, почему). Ничего страшного, на просторах Интернета есть множество разных решений, выберем одно из них.

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

Следующий нюанс – передача даты и времени. В API Voximplant временные метки принимаются в UTC в формате YYYY-MM-DD hh:mm:ss. В Excel же дата и время хранятся без учёта часового пояса (на самом деле, в самой таблице они вообще хранятся как число с плавающей точкой). Поэтому нам придётся принимать дату/время из таблицы тоже UTC. Предположим, что все 100+ миллионов пользователей Excel знают, что такое UTC, и это не вызовет у них никаких вопросов.

Кстати, в VBA есть функция форматирования даты, и она даже работает, но весьма необычным образом. Интересующий нас формат даты описывается так: “yyyy-mm-dd hh:mm:ss”. То есть “mm” – это либо месяц, либо минуты в зависимости от того, за чем оно следует: за “hh”или за “yyyy” (это не шутка, это даже в MSDN описано). В общем, если кто-то захочет вывести время без часов, придётся импровизировать.

Переходим к аутентификации

Здесь нас ожидает самое большое разочарование. Мы в Voximplant предлагаем нашим клиентам использовать JWT, что, конечно, весьма мудрёно, если выполнять запросы из консоли или браузера, но при использовании наших SDK это совершенно никак не усложняет жизнь разработчику. В то же время JWT обеспечивает крайне высокий уровень безопасности.

А что же VBA? К сожалению, разумно простого способа сформировать JWT-подпись просто не существует. Причина в том, что в VBA доступен фреймворк .NET версии 4.x, а функция RSA.ImportPkcs8PrivateKey, необходимая для загрузки приватного ключа из PKCS8, появилась только в .NET 5. Да и вообще, все .NET-разработчики используют для таких задач сторонние библиотеки.

Поэтому нам придётся ограничиться авторизацией с помощью статического API-ключа. Ведь, как известно, один из способов обработки риска информационной безопасности - принятие этого риска. Так и поступим. Ведь нам ну очень-очень нужен этот SDK.

Выполняем запрос

Переходим к третьей части  – к выполнению самого запроса. Встроенных средств работы с HTTP в VBA нет (теперь понятно, почему нет и функции URL-кодирования, а зачем?). 

Но, тем не менее, это достаточно тривиальная манипуляция – подключаем необходимый фреймворк MSXML 6.0 и Microsoft Scripting Runtime и выполняем запрос, подключая через COM сам MSXML. Просто!

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

Парсим JSON

Ну и, наконец, JSON. Как и всё остальное, парсер JSON надо искать где-то вовне экосистемы VBA. К счастью, на дворе 2021 год, есть GitHub, и кто-то уже озадачился созданием JSON-парсера для VBA. Мы взяли вот такой.

Он подключается как отдельный модуль и превращает JSON-строку в Dictionary. То, что нужно!

Дальше берём генератор одного из наших SDK (мы взяли питоновский), заменяем шаблоны и заставляем его генерировать код на VBA. В итоге получаем готовый SDK, который можно скачать на нашем GitHub.

SDK представляет собой Class Module, который можно подключить к вашей любимой книге в Excel и делать с его помощью разные странные вещи. В принципе, можно даже звонки запускать, SDK поддерживает все необходимые для этого функции API в). Но это будет не лучшей идеей, учитывая, что не до конца известно, когда Excel решит пересчитать все формулы (именно в этот момент произойдёт вызов функции API). 

А вот для каких-то статистических задач SDK, несмотря на всю абсурдность происходящего, может подойти. Например, можно посчитать, какой из пользователей потратил на звонки больше всех денег за какой-то период.

Для этого пишем вот такую функцию:

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

И вызываем её следующим образом:

А потянув мышкой за правый нижний угол активной ячейки, мы можем применить эту формулу ко всем интересующим нас пользователям и узнать, кто из них звонил двоюродной сестре в Монако больше других.

Резюме:

При желании можно и для VBA сделать какое-то подобие SDK. При его создании не пострадал ни один разработчик. Ах да, с 1 апреля! :D

Автор: Андрей

Источник