Работая в 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-запросов. Состоит она из четырех частей:
цикла, который считает количество строк для работы по листу, пробегая по столбцу А с 2 по первую пустую ячейку, чтобы у цикла был конец.
функции, для работы с REST API (используется стандартная, библиотека Msxml2.XMLHTTP.6.0, встроенная в Windows., поэтому сложностей с реализацией не возникает. Для MacOS есть альтернатива)
временной задержки, в случае если нужно отправлять запросы не сразу, после получения ответа, а задав время ожидания
таймером, который показывает время выполнения всего макроса после завершения
Код:
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 13 и Excel 16.83. Функция ниже, в том или ином виде, встречается на различных форумах, однако на текущих версиях софта – не работает. В итоге, после небольших правок получили рабочий вариант:
Была отлажена функция вызова 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 14 Sonoma с использованием Excel 64-bit (версия 16.86).
На просторах интернета я не нашел какого-то сборного и единого описания, только фрагменты кода и подходов, которые в большинстве случаев не работали полностью или частично. Поэтому решил объединить все в рабочее решение и выложить на хабр для истории.
На текущий момент я все еще не встретил иного решения, которое бы позволяло в пару кликов копипастить тысячи идентификаторов и параметров из excel и массово их отправлять на сервер. Надеюсь, кому-то пригодится :)
Если такие сторонние решения есть, а я не в курсе, и все можно было сделать проще, быстрее и изящнее – делитесь опытом в комментариях.
Примечание:
Файл-пример, который можно потыкать, пока жив сервер и "бессрочный" токен: