Работая в IoT-сфере и плотно взаимодействуя с одним из основных элементов данной концепции технологий – сетевым сервером, столкнулся вот с какой проблемой (задачей): необходимо отправлять много запросов для работы с умными устройствами на сетевой сервер. На сервере был реализован REST API с оболочкой Swagger UI, где из графической оболочки можно было отправлять только разовые запросы. Анализ сторонних клиентов, типа Postman или Insomnia показал, что простого визуального способа поместить в скрипт массив из необходимого перечня идентификаторов устройств (или любых других элементов сервера), для обращения к ним – не нашлось.

Так как большая часть работы с выгрузками и данными была в Excel, то решено было вспомнить навыки, полученные на учебе в университете, и написать скрипт на VBA, который бы мою задачку решал.

Необходимо было:

• получать информацию по устройствам с различными параметрами фильтрации (GET);

• применять изменения в конфигурации по устройствам: имя, профиль устройства, сетевые лицензии и пр. (PUT);

• отправлять данные для конфигурации и взаимодействия с устройствами (POST).

И сегодня я расскажу вам про то, как с помощью Excel, пары формул и самописных функций на VBA можно реализовать алгоритм, отправляющий любое необходимое количество REST-API запросов с использованием авторизации Bearer Token.

Данная статья будет полезная тем, кто воспользуется данным решением под Windows, но еще больше она будет полезна тем людям, которые хотят использовать данное решение на MacOS (с Excel x64). Как вы уже догадались, ниже будут рассмотрены два варианта реализации под разные системы, так как с MacOS есть нюанс.

Часть 1. Реализация решения под Windows

GET

Начнем с самого простого: GET – запросов. В данном примере необходимо получить ответ (информацию) от сервера по заданному списку устройств.

Для реализации GET – запросов нам дано:

1)   Ссылка, в которой указываются параметры запроса. 

https://dx-api.thingpark.io/core/latest/api/devices?deviceEUI=

2)   Заголовки запроса + Токен авторизации (Bearer Token)

--header 'Accept: application/json' --header 'Authorization: Bearer

eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJzY29wZSI6WyJTVUJTQ1JJQkVSOjY3MDAiXSwiZXhwIjozNzc0MTY0MzE4LCJqdGkiOiI5OTNiOTk1Ny03NGY1LTQ5MDgtYjg4Ni0xYjk5NTVkZDQwZTEiLCJjbGllbnRfaWQiOiJkZXYxLWFwaS9lcnRoLnRlY2guZGVzayt2bGFkaXNsYXYuZ2F0Y2Vua29AZ21haWwuY29tIn0.dqybsMdVXXpQV8_ykufNZoQpSPZrVA67uieOJan-qs8W7rAImyy0552buniHXPWy6ilvdwJKPCdIKE__LghP6A

3)   Параметр, указываемый в ссылке (в данном примере это идентификаторы устройств – DevEUI):

1ABCDEFF00AABBCC

0016ACC4DCF15A23

D88039FFFE954DF4

0000000000001103

0000000000001104

Имея такие данные на входе, делаем в Excel лист-шаблон, который заполняем в соответствии с тем, что имеем:

• столбец А уходит вот значения параметров

• столбец F уходит под ссылку-родителя

• столбец H уходит под заголовки, где в ячейке H1 единоразово для текущего листа указывается токен:

=СЦЕП("--header 'Accept: application/json' --header 'Authorization: Bearer ";$H$1;"'")

• столбец I уходит под URL (ссылки-дети, на основе ссылки-родителя)

=СЦЕПИТЬ($F$1;A2)

• столбец J уходит под результат (ответ от сервера)

Далее, нам необходимо реализовать подпрограмму(макрос) отправки GET-запросов. Состоит она из четырех частей:

1. цикла, который считает количество строк для работы по листу, пробегая по столбцу А с 2 по первую пустую ячейку, чтобы у цикла был конец.

2. функции, для работы с REST API (используется стандартная, библиотека Msxml2.XMLHTTP.6.0, встроенная в Windows., поэтому сложностей с реализацией не возникает. Для MacOS есть альтернатива)

3. временной задержки, в случае если нужно отправлять запросы не сразу, после получения ответа, а задав время ожидания

4. таймером, который показывает время выполнения всего макроса после завершения

   Код:

   Sub GET_Request()
   
   Dim i As Integer
   Dim j As Integer
   Dim objHTTP As Object
   Dim Json As String
   Dim result As String
   Dim URL As String
   Dim Token As String
   a = Timer
   
       i = 1
           Do While Not IsEmpty(Cells(i, 1))
           i = i + 1
           Loop
       i = i - 1
       'MsgBox i
   
       For j = 2 To i
           Json = Range("D" & j)
           URL = Range("I" & j)
           Token = Range("H1")
   
           Set objHTTP = CreateObject("Msxml2.XMLHTTP.6.0")
               objHTTP.Open "GET", URL, False
               objHTTP.setRequestHeader "Content-type", "application/json"
               objHTTP.setRequestHeader "Accept", "application/json"
               objHTTP.setRequestHeader "Authorization", "Bearer " + Token
               objHTTP.Send (Json)
               result = objHTTP.responseText
               Range("J" & j).Value = result
           Set objHTTP = Nothing
           'Application.Wait (Now + TimeValue("0:00:01"))
       Next j
   
   MsgBox Timer - a
   
   End Sub
   

Привязываем подпрограмму к кнопкам для удобства и выполним скрипт. Получается: 

Таким образом, скрипт проходит по столбцу I, забирая из значения каждой ячейки URL, для тех строк, где в столбце А есть значения (которые и подставляются в URL). Для удобства также сделаны кнопки очистки полей и подсветка запросов условным форматированием, в случае успешного ответа на запрос.

PUT

Чуть-чуть усложним задачу и перейдем к PUT-запросам. В данном примере необходимо изменить профиль устройства, чтобы сервер по-другому с ним взаимодействовал.

К исходным данным для GET – запроса добавляется тело запроса с ключем-значением (п4). Итого дано:

1)   Ссылка, в которой указываются параметры запроса.

https://dx-api.thingpark.io/core/latest/api/devices/

2)   Заголовки запроса + Токен авторизации (Bearer Token)

--header 'Content-Type: application/json' --header 'Accept: application/json' --header 'Authorization: Bearer

eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9.eyJzY29wZSI6WyJTVUJTQ1JJQkVSOjY3MDAiXSwiZXhwIjozNzc0MTY0MzE4LCJqdGkiOiI5OTNiOTk1Ny03NGY1LTQ5MDgtYjg4Ni0xYjk5NTVkZDQwZTEiLCJjbGllbnRfaWQiOiJkZXYxLWFwaS9lcnRoLnRlY2guZGVzayt2bGFkaXNsYXYuZ2F0Y2Vua29AZ21haWwuY29tIn0.dqybsMdVXXpQV8_ykufNZoQpSPZrVA67uieOJan-qs8W7rAImyy0552buniHXPWy6ilvdwJKPCdIKE__LghP6A

3)   Параметр, указываемый в ссылке (в данном примере это внутренние идентификаторы устройств – hRef):

17272

18199

17242

17245

17248

4)   Тело запроса, с ключом и значением:

{"deviceProfileId":"LORA/GenericA.1.0.3a_ETSI"}

Немного дополняем новый PUT-лист в Excel по сравнению с GET (остальное без изменений):

• новый столбец B теперь отвечает за ключ deviceProfileId (ячейка B1), а все значения ниже за его возможные значения)

• столбец D отвечает за формирование итогового тела сообщения в формате JSON.

=СЦЕПИТЬ("'{""";$B$1;""":""";B2;"""";"}'")

Немного поменяем макрос и вынесем его в отдельную подпрограмму: 

Sub PUT_Request()

Dim i As Integer
Dim j As Integer
Dim objHTTP As Object
Dim Json As String
Dim result As String
Dim URL As String
Dim Token As String
a = Timer

    i = 1
        Do While Not IsEmpty(Cells(i, 1))
        i = i + 1
        Loop
    i = i - 1
    'MsgBox i

    For j = 2 To i
        Json = Range("D" & j)
        URL = Range("I" & j)
        Token = Range("H1")

        Set objHTTP = CreateObject("Msxml2.XMLHTTP.6.0")
            objHTTP.Open "PUT", URL, False
            objHTTP.setRequestHeader "Content-type", "application/json"
            objHTTP.setRequestHeader "Accept", "application/json"
            objHTTP.setRequestHeader "Authorization", "Bearer " + Token
            objHTTP.Send (Json)
            result = objHTTP.responseText
            Range("J" & j).Value = result
        Set objHTTP = Nothing
        'Application.Wait (Now + TimeValue("0:00:01"))
    Next j

MsgBox Timer - a

End Sub

Привяжем макрос к кнопке и выполним. 

Логика абсолютно аналогична GET запросу.

POST

Для POST запросов все аналогично PUT. Только немного меняется код в части типа запроса. В данном примере на устройство отправляется команда-конфигурация с указанием тела посылки (payload_hex) и порта (fport) для конкретного устройства. 

Sub PUT_Request()

Dim i As Integer
Dim j As Integer
Dim objHTTP As Object
Dim Json As String
Dim result As String
Dim URL As String
Dim Token As String
a = Timer

    i = 1
        Do While Not IsEmpty(Cells(i, 1))
        i = i + 1
        Loop
    i = i - 1
    'MsgBox i

    For j = 2 To i
        Json = Range("D" & j)
        URL = Range("I" & j)
        Token = Range("H1")

        Set objHTTP = CreateObject("Msxml2.XMLHTTP.6.0")
            objHTTP.Open "PUT", URL, False
            objHTTP.setRequestHeader "Content-type", "application/json"
            objHTTP.setRequestHeader "Accept", "application/json"
            objHTTP.setRequestHeader "Authorization", "Bearer " + Token
            objHTTP.Send (Json)
            result = objHTTP.responseText
            Range("J" & j).Value = result
        Set objHTTP = Nothing
        'Application.Wait (Now + TimeValue("0:00:01"))
    Next j

MsgBox Timer - a

End Sub

Получившаяся таблица выглядит следующим образом:

 На этом часть для Windows заканчивается. Здесь все оказалось довольно просто: стандартная библиотека, простенький алгоритм перебора значений в цикле. 

Часть 2. Реализация решения под MacOS и Excel 64-bit

В виду того, что работал я на двух машинах под управлением разных ОС, хотелось, чтобы решение было универсальным. В итоге, собрав по крупицам информацию по интернет-форумам с данной тематикой у меня вышло следующее решение. Принцип работы его остается схожим, а изменения были внесены в часть, где использовалась стандартная библиотека WindowsMsxml2.XMLHTTP.6.0, которой в MacOS не было по понятным причинам. 

Чтобы обойти данное ограничение, был выбран единственный рабочий подход через cUrl, exec и функции. Данное решение точно работает под версией MacOS 10.14 и Excel 16.51. Функция ниже, в том или ином виде, встречается на различных форумах, однако на текущих версиях софта – не работает. В итоге, после небольших правок получили рабочий вариант:

 Была отлажена функция вызова ExecShell:

Option Explicit
Private Declare PtrSafe Function popen Lib "/usr/lib/libc.dylib" (ByVal Command As String, ByVal Mode As String) As LongPtr
Private Declare PtrSafe Function pclose Lib "/usr/lib/libc.dylib" (ByVal file As LongPtr) As Long
Private Declare PtrSafe Function fread Lib "/usr/lib/libc.dylib" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As Long
Private Declare PtrSafe Function feof Lib "/usr/lib/libc.dylib" (ByVal file As LongPtr) As LongPtr

Function execShell(Command As String, Optional ByRef exitCode As Long) As String
    Dim file As LongPtr
    file = popen(Command, "r")

    If file = 0 Then
        Exit Function
    End If

    While feof(file) = 0
        Dim chunk As String
        Dim read As Long
        chunk = Space(500)
        read = fread(chunk, 1, Len(chunk) - 1, file)
        If read > 0 Then
            chunk = Left$(chunk, read)
            execShell = execShell & chunk
        End If
    Wend

    exitCode = pclose(file)
    
End Function

И написаны отдельные функции для работы с различным методами GET / PUT / POST, которые на входе принимают URL и параметры):

Function HTTPGet(sUrl As String, sQuery As String) As String

    Dim sCmd As String
    Dim sResult As String
    Dim lExitCode As Long

    sCmd = "curl -X GET " & sQuery & "" & " " & sUrl
    sResult = execShell(sCmd, lExitCode)
    HTTPGet = sResult

End Function

Function HTTPPost(sUrl As String, sQuery1 As String, sQuery2 As String) As String

    Dim sCmd As String
    Dim sResult As String
    Dim lExitCode As Long

    sCmd = "curl -X POST " & sQuery1 & "" & " -d " & sQuery2 & "" & " " & sUrl
    sResult = execShell(sCmd, lExitCode)
    HTTPPost = sResult

End Function

Function HTTPPut(sUrl As String, sQuery1 As String, sQuery2 As String) As String

    Dim sCmd As String
    Dim sResult As String
    Dim lExitCode As Long

    sCmd = "curl -X PUT " & sQuery1 & "" & " -d " & sQuery2 & "" & " " & sUrl
    sResult = execShell(sCmd, lExitCode)
    HTTPPut = sResult

End Function

Так как мы заменяем библиотеку Msxml2.XMLHTTP.6.0 – поменялась реализация макросов в этой части: мы заменили Msxml2 на написанные выше функции и получили следующее:

'GET-запросы
Sub SendGETRequest()

Dim i As Integer
Dim j As Integer
Dim result As String
Dim URL As String
Dim Auth As String

a = Timer

    'Подсчет заполненных ячеек первого столбца
    i = 1
        Do While Not IsEmpty(Cells(i, 1))
        i = i + 1
        Loop
    i = i - 1

    'Цикл, который отправляет запрос от 2 до последнего элемента
    For j = 2 To i
        URL = Range("I" & j)
        Auth = Range("H" & j)
        result = HTTPGet(URL, Auth)
        Range("J" & j).Value = result
        'Application.Wait (Now + TimeValue("0:00:01"))
    Next j

MsgBox Timer - a

End Sub


'PUT-запросы
Sub SendPUTRequest()

Dim i As Integer
Dim j As Integer
Dim result As String
Dim URL As String
Dim Auth As String
Dim Message As String


a = Timer

    'Подсчет заполненных ячеек первого столбца
    i = 1
        Do While Not IsEmpty(Cells(i, 1))
        i = i + 1
        Loop
    i = i - 1

    'Цикл, который отправляет запрос от 2 до последнего элемента
    For j = 2 To i
        Message = Range("D" & j)
        URL = Range("I" & j)
        Auth = Range("H" & j)
        result = HTTPPut(URL, Auth, Message)
        Range("J" & j).Value = result
        'Application.Wait (Now + TimeValue("0:00:01"))
    Next j

MsgBox Timer - a

End Sub


'POST-запросы
Sub SendPOSTRequest()

Dim i As Integer
Dim j As Integer
Dim result As String
Dim URL As String
Dim Auth As String
Dim Message As String

a = Timer

    'Подсчет заполненных ячеек первого столбца
    i = 1
        Do While Not IsEmpty(Cells(i, 1))
        i = i + 1
        Loop
    i = i - 1

    'Цикл, который отправляет запрос от 2 до последнего элемента
    For j = 2 To i
        Message = Range("D" & j)
        URL = Range("I" & j)
        Auth = Range("H" & j)
        result = HTTPPost(URL, Auth, Message)
        Range("J" & j).Value = result
        'Application.Wait (Now + TimeValue("0:00:01"))
    Next j

MsgBox Timer – a

Итог

В итоге, у меня получилось аналогичное windows по работе и функционалу решение для MacOS c использованием Excel 64-bit. 

На просторах интернета я не нашел какого-то сборного и единого описания, только фрагменты кода и подходов, которые в большинстве случаев не работали полностью или частично. Поэтому решил объединить все в рабочее решение и выложить на хабр для истории. 

На текущий момент я все еще не встретил иного решения, которое бы позволяло в пару кликов копипастить тысячи идентификаторов и параметров из excel и массово их отправлять на сервер. Надеюсь, кому-то пригодится :)

Если такие сторонние решения есть, а я не в курсе, и все можно было сделать проще, быстрее и изящнее – делитесь опытом в комментариях.

Примечание:

Файл-пример, который можно потыкать, пока жив сервер и "бессрочный" токен: 

https://disk.yandex.ru/d/y7EVtn8afM4QPg

Открытое описание API, если кому-то будет любопытно