![А я говорю, возьми Excel и позвони - 1 А я говорю, возьми Excel и позвони - 1](https://www.pvsm.ru/images/2021/04/01/a-ya-govoryu-vozmi-Excel-i-pozvoni.jpg)
Voximplant, как и любая облачная платформа, предоставляет API, с помощью которого можно управлять пользователями, запускать звонки, просматривать их историю и логи и делать немало других полезных вещей. В принципе, практически всё, что можно настроить в панели управления, можно сделать и через API.
Но в современном мире иметь API недостаточно – мало кто хочет формировать HTTP-запросы, передавать параметры, думать про правильную авторизацию. Поэтому мы предлагаем SDK для разных языков программирования: Python, PHP, C# и многих других. И кажется, что этого достаточно, чтобы сделать нашу платформу лёгкой в использовании для очень большой аудитории. Или всё-таки недостаточно?
Обратимся к статистике. По разным данным сейчас в мире насчитывается где-то 15-30 миллионов разработчиков – цифра несомненно впечатляющая. Но, например, пользователей MS Excel в мире не менее 100 миллионов. Почему же они должны страдать? Ведь, будем честны, почти каждый из тех, кто хоть раз открывал Excel, явно ощущал недостаток возможностей по управлению коммуникационными платформами в этом без сомнения очень гибком программном продукте. Практически каждый день мы получаем на наш email сотни запросов, которые сводятся к очень простой просьбе: «Я хочу звонить из Excel!». Однажды у окон нашего офиса даже выстроились люди с такими требованиями (видели фото выше?) Мы просто не могли оставаться в стороне.
Однако звонки – это всё-таки слишком революционно, а главное, потребует установки дополнительных ActiveX-компонентов, что, безусловно, противоречит всем существующим и несуществующим политикам информационной безопасности, поэтому давайте начнём с более простой вещи – SDK для работы с нашим API. Из средств разработки в Excel доступен VBA, для него мы и создадим SDK.
Для того чтобы выполнить API-запрос, необходимо:
-
Сформировать URL и тело POST-запроса.
-
Добавить аутентификационные параметры.
-
Непосредственно выполнить запрос.
-
Распарсить результат (в нашем случае это 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. Просто!
![А я говорю, возьми Excel и позвони - 3 А я говорю, возьми Excel и позвони - 3](https://www.pvsm.ru/images/2021/04/01/a-ya-govoryu-vozmi-Excel-i-pozvoni-3.png)
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
И вызываем её следующим образом:
![А я говорю, возьми Excel и позвони - 4 А я говорю, возьми Excel и позвони - 4](https://www.pvsm.ru/images/2021/04/01/a-ya-govoryu-vozmi-Excel-i-pozvoni-4.png)
А потянув мышкой за правый нижний угол активной ячейки, мы можем применить эту формулу ко всем интересующим нас пользователям и узнать, кто из них звонил двоюродной сестре в Монако больше других.
![А я говорю, возьми Excel и позвони - 5 А я говорю, возьми Excel и позвони - 5](https://www.pvsm.ru/images/2021/04/01/a-ya-govoryu-vozmi-Excel-i-pozvoni-5.png)
Резюме:
При желании можно и для VBA сделать какое-то подобие SDK. При его создании не пострадал ни один разработчик. Ах да, с 1 апреля! :D
Автор: Андрей