Excel post запрос json
Обратите внимание, что настраиваемые функции доступны в Excel на следующих платформах.
- Office для Windows
- Office для Mac
- Office в Интернете
Сейчас настраиваемые функции Excel не поддерживаются на iPad или в версиях Office 2019 (или более ранних), предлагаемых в виде единовременных покупок.
Функции, которые возвращают данные из внешних источников
Если пользовательская функция извлекает данные из внешнего источника, например, сайта, она должна:
- Возвращает код JavaScript Promise для Excel.
- Разрешите значение Promise с окончательным значением с помощью функции обратного вызова.
Пример получения данных
В следующем примере кода webRequest функция достигает гипотетического внешнего API, который отслеживает количество людей на международной станции. Функция возвращает JavaScript Promise и использует fetch для запроса информации из гипотетического API. Полученные данные преобразуются в JSON names , а свойство преобразуется в строку, которая используется для разрешения обещания.
При разработке собственных функций может потребоваться выполнение действия, если веб-запрос не завершается своевременно. Также можно рассмотреть совмещение нескольких запросов API.
При использовании метода fetch не создаются вложенные обратные вызовы, что в некоторых случаях может быть предпочтительнее, чем использование метода XHR.
Пример XHR
В следующем примере кода getStarCount функция вызывает API Github для обнаружения количества звезд, присвоенных репозиторию определенного пользователя. Это асинхронная функция, которая возвращает JavaScript Promise . При получении данных из веб-вызова обещание разрешается, что возвращает данные в ячейку.
Создание функции потоковой передачи
Пользовательские функции потоковой передачи позволяют выводить данные в ячейки, которые повторно обновляются, не требуя от пользователя явно что-либо обновлять. Такие функции (например, функция из руководства по пользовательским функциям) могут быть полезны для проверки данных, обновляемых в реальном времени, из веб-службы.
Чтобы объявить функцию потоковой передачи, можно использовать один из следующих двух вариантов.
- Тег @streaming .
- Параметр CustomFunctions.StreamingInvocation вызова.
Следующий пример кода — это пользовательская функция, которая добавляет число к результату каждую секунду. Обратите внимание на указанные ниже аспекты этого кода.
- Excel отображает каждое новое значение автоматически с помощью метода setResult .
- Второй параметр ввода, invocation , не отображается для конечных пользователей в Excel, когда они выбирают функцию в меню "Автозаполнение".
- Обратный onCanceled вызов определяет функцию, которая выполняется при отмене функции.
- Потоковая передача не обязательно связана с выполнением веб-запроса. В этом случае функция не выполняет веб-запрос, но по-прежнему получает данные с заданным интервалом, поэтому она требует использования параметра потоковой invocation передачи.
Отмена функции
Excel отменяет выполнение функции в следующих ситуациях.
- Когда пользователь редактирует или удаляет ячейку, ссылающуюся на функцию.
- Когда изменяется один из аргументов (входных параметров) функции. В этом случае после отмены выполняется новый вызов функции.
- Когда пользователь вручную вызывает пересчет. В этом случае после отмены выполняется новый вызов функции.
Также можно настроить стандартное значение потоковой передачи, чтобы обрабатывать случаи выполнения запроса, когда вы находитесь в автономном режиме.
Существует также категория функций, называемых отменяемыми функциями, и они не связаны с функциями потоковой передачи. Отменяются только асинхронные пользовательские функции, возвращаемые одним значением. Отменяемые функции позволяют прервать выполнение веб-запроса, используя CancelableInvocation , чтобы решить, что делать после отмены. Для объявления отменяемых функций используется тег @cancelable .
Использование параметра вызова
Параметр invocation является по умолчанию последним в любой пользовательской функции. Параметр invocation предоставляет контекст ячейки (например, ее адрес и содержимое) и позволяет использовать setResult и методы onCanceled . Эти методы определяют, что делает функция во время ее потоковой передачи ( setResult ) или отмены ( onCanceled ).
Если вы используете TypeScript, обработчик вызова должен иметь тип или CustomFunctions.StreamingInvocation CancelableInvocation .
Получение данных через WebSockets
Пример WebSockets
Работая в 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) Ссылка, в которой указываются параметры запроса.
2) Заголовки запроса + Токен авторизации (Bearer Token)
--header 'Accept: application/json' --header 'Authorization: Bearer
3) Параметр, указываемый в ссылке (в данном примере это идентификаторы устройств – DevEUI):
Имея такие данные на входе, делаем в Excel лист-шаблон, который заполняем в соответствии с тем, что имеем:
столбец А уходит вот значения параметров
столбец F уходит под ссылку-родителя
столбец H уходит под заголовки, где в ячейке H1 единоразово для текущего листа указывается токен:
=СЦЕП("--header 'Accept: application/json' --header 'Authorization: Bearer ";$H$1;"'")
столбец I уходит под URL (ссылки-дети, на основе ссылки-родителя)
столбец J уходит под результат (ответ от сервера)
Далее, нам необходимо реализовать подпрограмму(макрос) отправки GET-запросов. Состоит она из четырех частей:
цикла, который считает количество строк для работы по листу, пробегая по столбцу А с 2 по первую пустую ячейку, чтобы у цикла был конец.
временной задержки, в случае если нужно отправлять запросы не сразу, после получения ответа, а задав время ожидания
таймером, который показывает время выполнения всего макроса после завершения
Привязываем подпрограмму к кнопкам для удобства и выполним скрипт. Получается:
Таким образом, скрипт проходит по столбцу I, забирая из значения каждой ячейки URL, для тех строк, где в столбце А есть значения (которые и подставляются в URL). Для удобства также сделаны кнопки очистки полей и подсветка запросов условным форматированием, в случае успешного ответа на запрос.
PUT
Чуть-чуть усложним задачу и перейдем к PUT-запросам. В данном примере необходимо изменить профиль устройства, чтобы сервер по-другому с ним взаимодействовал.
К исходным данным для GET – запроса добавляется тело запроса с ключем-значением (п4). Итого дано:
1) Ссылка, в которой указываются параметры запроса.
2) Заголовки запроса + Токен авторизации (Bearer Token)
--header 'Content-Type: application/json' --header 'Accept: application/json' --header 'Authorization: Bearer
3) Параметр, указываемый в ссылке (в данном примере это внутренние идентификаторы устройств – hRef):
4) Тело запроса, с ключом и значением:
Немного дополняем новый PUT-лист в Excel по сравнению с GET (остальное без изменений):
новый столбец B теперь отвечает за ключ deviceProfileId (ячейка B1), а все значения ниже за его возможные значения)
Немного поменяем макрос и вынесем его в отдельную подпрограмму:
Привяжем макрос к кнопке и выполним.
Логика абсолютно аналогична GET запросу.
POST
Для POST запросов все аналогично PUT. Только немного меняется код в части типа запроса. В данном примере на устройство отправляется команда-конфигурация с указанием тела посылки (payload_hex) и порта (fport) для конкретного устройства.
Получившаяся таблица выглядит следующим образом:
На этом часть для Windows заканчивается. Здесь все оказалось довольно просто: стандартная библиотека, простенький алгоритм перебора значений в цикле.
Часть 2. Реализация решения под MacOS и Excel 64-bit
Чтобы обойти данное ограничение, был выбран единственный рабочий подход через cUrl, exec и функции. Данное решение точно работает под версией MacOS 10.14 и Excel 16.51. Функция ниже, в том или ином виде, встречается на различных форумах, однако на текущих версиях софта – не работает. В итоге, после небольших правок получили рабочий вариант:
Была отлажена функция вызова ExecShell:
И написаны отдельные функции для работы с различным методами GET / PUT / POST, которые на входе принимают URL и параметры):
В итоге, у меня получилось аналогичное windows по работе и функционалу решение для MacOS c использованием Excel 64-bit.
На просторах интернета я не нашел какого-то сборного и единого описания, только фрагменты кода и подходов, которые в большинстве случаев не работали полностью или частично. Поэтому решил объединить все в рабочее решение и выложить на хабр для истории.
На текущий момент я все еще не встретил иного решения, которое бы позволяло в пару кликов копипастить тысячи идентификаторов и параметров из excel и массово их отправлять на сервер. Надеюсь, кому-то пригодится :)
Если такие сторонние решения есть, а я не в курсе, и все можно было сделать проще, быстрее и изящнее – делитесь опытом в комментариях.
Файл-пример, который можно потыкать, пока жив сервер и "бессрочный" токен:
Недавно нужно было написать API автотесты - запросы Post с большим количеством параметров в теле, в том числе вложенные JSON объекты, массивы , массивы JSON объектов. Многие параметры не обязательные, а значит - большое количество наборов тестовых данных.
Дано: Датапровайдер - для многократного запуска тестов, тестовые данные в таблице Excel.
Что бы сформировать тело первого запроса пришлось создать несколько классов, через сеттеры присваивать значения переменным в классе и из базового класса формировать JSON, который и использовался как тело запроса. И вдруг оказалось, что все написанное никак не получится переиспользовать для других эндпоинтов. Тогда и решил написать метод, который будет превращать таблицу Excel в JSON объект без всяких там классов и правок в коде. Нужно только придерживаться некоторых правил при составлении таблицы.
Итак! Для получения данных из таблицы использовал, как обычно, Fillo. Название столбца будет ключом, значения в столбце, собственно, значениями ключа в запросе. Строка таблицы - один набор тестовых данных.
Должно быть как-то так на входе:
Для начала, метод для получения ВСЕХ данных из Excel листа в виде массива мапов. Раньше использовал для похожих задач для вебтестов. Метод принимает две строки: путь к Excel файлу и название листа. Это единственные константы, которые вам придется захардкодить, все остальное управление формированием тела запроса выполняется из Excel файла. Я создаю для этого класс констант и там, если необходимо, меняю значение, которое потом используется во всех тестах.
Превратить мапу в JSON с помощью GSON можно за одно действие, но тут оказалось, что сервер не принимает пустые ключи, поэтому их нужно выпилить в процессе. Следующий метод принимает на вход массив из метода fromExcelToListOfMaps , удаляет ключи без значений и возвращает массив JSON строк.
После первого метода:
В таком виде уже можно отправлять результат в датапровайдер, но у меня значения не только типа String. А как объяснить методу, что вот это вот boolean, а вот это Integer и кавычки тут не нужны, а там вообще массив JSON объектов. И в коде указывать нельзя, получится не универсально. Решил в название столбцов добавить ключевые слова:
string - нет ключевого слова
Ключевое слово отделяется от имени ключа двумя звездочками (**). Теперь, после проверки на пустые значения, выполняется проверка на наличия ключевого слова в названии столбца, и, если это слово есть и соответствует одному из приведенных выше, создается новый ключ (отбрасывается ключевое слово и звездочки) и ему присваивается значение с приведением к нужному типу данных. Выглядит примерно так:
после чего из мапы выпиливаются все пары в ключе которых присутствуют ** . Т.е. если написать в таблице ключевое слово с ошибкой, то эта колонка в запрос не попадет.
Конечно работать с разными типами данных в Map не получится, поэтому все результаты пересобираются в переменную типа JsonObject.
Дальше нужно было добавить ключи со значениями класса JsonObject. Конечно можно просто в Excel ячейку записать что-то типа , но ведь у меня уже есть метод, который который соберет JSON из листа Excel. Нужно только указать лист и строку которая будет использоваться. Получилось чуть-чуть рекурсии. Ключевое слово:
JsonObject - Если в качестве значения ключа нужен JsonObject, то ключевое слово должно совпадать с названием Excel листа, из которого берутся данные (добавил метод получающий все имена листов Excel файла в массив). Значение в ячейке (тип int) - номер строки в этом листе (нумерация с нуля).
В результате на входе:
Остались массивы. Наверное, стоило написать логику с нуля, но я поленился и решил из JsonObject и выбранной строки просто все значения перенести в List . Написал отдельный метод. На вход принимает путь к Excel файлу, имя листа и номер строки (нумерация с нуля)
List - arr. Имя столбца должно совпадать с именем Excel листа, из которого будут браться значения для массива. Значение в ячейке (тип int) - номер строки. Имя столбцов в Excel листе из которого будут набираться данные в массив значения не имеют, но они не должны повторяться (Fillo не обработает) и для всех столбцов должно быть одинаковое ключевое слово. (для данных типа String ключевое слово не нужно).
В результате как-то так на входе:
Вот основной класс с методами:
В общем по функционалу на этом все, осталось заполнить таблицу. Оказалось, что указывать номера строк, находящихся на другом листе, не очень-то и удобно, постоянно какая-то путаница. Тут мне на помощь пришли неправильные ключевые слова. Если в имени столбца присутствует две звездочки (**), но текст перед ** не соответствует одному из ключей, то этот столбец в запросе игнорируется, и в него можно писать комментарии, добавлять нумерацию, или выводить результаты теста. Я ставил четыре звездочки, чтобы сходу видеть, что в запрос не попадет. Таблицы стали выглядеть как-то так:
Осталось передать все это в датапровайдер и превратить в классический двухмерный массив строк.
И дальше в тест:
Тело первого теста:
Все это писалось в отдельном проекте, клонировать можно здесь.
Если нужно применить в существующем проекте, но совсем не хочется разбираться как это устроено, то скопируйте пакеты api и файл Data.xlsx в соответствующие разделы вашего проекта на Maven + TestNG, при необходимости добавьте нужные зависимости и пишите тесты в классе ExampleTests.
Собираюсь потихоньку перетянуть сюда все универсальные методы, которые использовал - пусть все лежит в одном месте! Надеюсь, что информация поможет другим начинающим автоматизаторам).
Всем привет, я руководитель отдела тестирования, и недавно по работе появилась задача на тестирование API. Для ее решения освоил новый для меня инструмент Postman и JavaScript.
Первоначально на каждый API я писал свои коллекции и готовил тестовые данные в JSON формате. Это достаточно удобно, но при большом количестве тестов и коллекций поддерживать это становится накладно. Да и проводить валидацию данных в JSON не удобно.
Для решения этих проблем я написал макрос для Excel и коллекцию в Postman. Теперь в Postman у меня одна коллекция на все API и стандартный набор функций для обработки входящих данных и валидации возвращаемых результатов. Мне удалось перенести управление тестовыми данными и последовательность выполнения запросов в Excel.
Что было
1. JSON с данными
Раньше тестовый набор хранился в таком виде
2. 2. Последовательность выполнения запросов с обработчиками на JS хранилась в коллекциях Postman.
Что стало
1. Тестовый набор переехал в Excel
Все данные вносятся в Excel (управляющие ключевые символы: R, H, I и т.д. распишу ниже) и дальше при помощи макроса перегоняются в json формат:
Эксперимент проводил на стандартном наборе CRUD операций, который в дальнейшем можно расширять.
Так как в Postman все операций выполняются только в рамках запроса, пришлось ввести пустой get запрос в конце выполнения которого определяется следующий запрос из последовательности. Выполнить JS код до запроса и определить первый запрос без пустого не получилось.
Во всех запросах Pre-request script и Test секции пустые, весь код унифицирован для запросов и хранится в общих секциях Pre-request script и Test папки API Collection.
Во всех запросах важно обратить внимание на url и на секцию Body в запросах POST и PUT, их значения определяются переменными, значения в которые вносятся из JSON с данными.
Теперь о самих тестах
Как читать Excel. Первой не пустой строкой идет номер тест кейса, то есть тест кейс хранится вертикально и на этой странице 9 тест кейсов. В текущем наборе в каждом тест кейсе будут выполнены сначала POST запрос, потом Delete.
Как запустить генерацию Json из Excel. В Excel нажать F11 и перейти на «ЭтаКнига», там запустить макрос.
Ключевые слова
R – request, означает начало нового запроса, во второй ячейке строки хранится тип запроса, в третьей адрес, по которому надо обращаться. Обратите внимание, что в url можно указывать переменные Postman
Значение из переменной подтянется
I, I2 … – Секция входных данных, поддерживает хранение моделей данных любой вложенности, цифра справа от I отвечает за уровень вложенности. Следующий набор данных вот так завернется в JSON. Если переменная, которая хранит данные пустая, то она не добавится. То есть если в переменной inn не будет значения, то она не добавится, а переменная chief добавится, так как она хранит модель. При этом если вся модель пустая, то она также не добавится.
Данные этой секции будут поданы в теле запроса
O, O2 … — Секция выходных параметров, они будут сравнены с теми, что возвращает response. Как и секция Input поддерживает хранение моделей.
При сравнении данные конвертируются в string, еще я дополнительно ограничил уровень рекурсии, это можно убрать
PO – Postman Output, значения из этой секции будут по имени переменной браться из response body запроса и записываться в переменную Postman.
В Excel достаточно поставить любой символ, в переменную запишется значение из response, а не excel
Эта секция нужна, чтобы хранить данные между скриптами, например чтобы удалить объект с id, который был создан в предыдущем реквесте
PC – Postman command, ввел только одну команду “terminate”, она используется для принудительного обрывания после выполнения текущего запроса. Полезно при негативном тесте, чтобы не вызывать шаг с удалением созданного объекта.
Ввод этой команды позволил на одном листе хранить и позитивные и негативные тесты
PI – Postman Input, значения из этой секции будут записаны в переменные Postman перед определением url
Может быть полезным, если надо переопределить переменные, которые указаны в url запроса.
Кстати, в подаваемых данных можно использовать данные из Postman переменных, для этого требуется использовать специальную конструкцию
В кейсе 1 мы внесли полученное значение в переменную, в кейсе 2 его использовали. Можно использовать не только в следующем кейсе, но и в текущем при следующем запросе. Например может потребоваться, если определение объекта для изменения идет не по url, а по значению переменной в запросе.
Подготовка к прогону
А теперь прогон, запускаем Postman runner, в нем выбираем нужную коллекцию и подгружаем файл с тестовыми данными:
Подавать будем следующий набор:
Здесь описано 15 тестов, при этом шаги 1-11, 13, 15 положительные с результатом 200 при POST запросе и шаги 12, 14 негативные с результатом 400. По ним информация в базу не будет внесена и поэтому секция Output пустая, а также указана команда terminate. Эта команда прервет выполнение последовательности и запрос на удаление «Delete» отправлен не будет.
После кейсов 1-11, 13, 15 мы запоминаем id который присвоился новому объекту, чтобы потом его удалить.
Запускаем
Все 15 тестов прошли успешно, на картинке виден тест 14, в котором не вызывается Delete после POST
В тестах 1-11,13,15 после POST вызывается удаление созданного объекта:
Резюме
- Последовательность выполнения запросов для тестирования API вынесена в Excel и обрабатывается в Postman.
- Все тестовые данные также вынесены в Excel ими удобно управлять и валидировать их. По крайней мере удобней чем в JSON формате.
- Коллекция Postman стандартизирована и при тестировании схожих API не требует доработки.
Ссылки
Update от 15.10.2019
1. Доработал возможность генерации json файлов без запуска excel, выложил в гит GenerateAll.cmd, который запускает vb скрипт GenerateJsonFiles.vbs. Вызванный скрипт пройдет по текущей папке и всем вложенным и сгенерирует json файлы
2. RunAll.cmd запускаем для прогона всех сгенерированных json файлов с данными, также пройдет текущую папку и все вложенные папки. Рядом с каждым из них положит output-report.log с результатами прогона.
Теперь прикрутить к CI не должно быть проблемой. Также можно прикрутить генерацию json к git deploy и проводить сравнение данных в excel по изменениях в json файлах.
Open an excel file and open VBA editor (Alt + f11) > new module and start writing code in a sub
If you need VBA’s Intellisense autocomplete then do it this way :
First, Add a reference to MSXML (Tools > references)
Select appropriate version based on your PC :
1. Microsoft XML, v 3.0.
2. Microsoft XML, v 4.0 (if you have installed MSXML 4.0 separately).
3. Microsoft XML, v 5.0 (if you have installed Office 2003 – 2007 which provides MSXML 5.0 for Microsoft Office Applications).
4. Microsoft XML, v 6.0 for latest versions of MS Office.
VBA Intellisense will show you the right one when you start typing.
Make requests
Requests can be made using open and send methods. Open method syntax is as follows :
I’m using requestBin to test requests. Create a bin there and send requests to that URL to test requests.
A simple GET request would be :
Run this code, a message box is displayed with the response of the request.
Request headers
Request headers can be set using setRequestHeader method. Examples :
Simple POST request to send formdata
POST requests are used to send some data, data can be sent in Send method. A simple POST request to send form data :
Basic Authentication in VBA
When we need to access web services with basic authentication, A username and password have to be sent with the Authorization header. Username and password should also be base64 encoded. Example :
Here’s a paste of utility function that helps to encode string to Base64
Practical use cases
If you have and questions or feedback, comment below.
A CA- by education, self taught coder by passion, loves to explore new technologies and believes in learn by doing.
- 5 easy ways to center an element with CSS - April 5, 2022
- How to add participants to a WhatsApp group using whatsapp-web.js - March 31, 2022
- How to send a PDF to multiple contacts/groups using WhatsApp API - March 29, 2022
Related
what is the syntax to open Techopedia resfull service in Excel VBA?
They provided they following info using curl
Hi George, That curl information explains the following : Send a header with name ‘Authorization’ and value ‘apikey ‘ + yourapikey
If you API key is ‘abc123’, then you can do it this way in VBA :
xmlhttp.setRequestHeader "Authorization", "apikey " + "abc123"
Hope that helps.
Hi Can you please provide information for Authorization Oauth 1.0
type.
consumer_key: CONSUMER_KEY
, consumer_secret: CONSUMER_SECRET
, token: token_no
, token_secret: secret_key
do you find anything related to your query? I am also looking for vba code using OAuth1.0 and downloading data.
I can get the data from the API but I am having trouble pasting the data into Excel. My code sample is below.
When I check the responseText with msgBox, I see all the data. But when I try pasting it into Excel, it only pastes the headers for some reason.
I also tried putting the data into a variable first and then paste into Excel, but it’s the same result.
can you tell your API URL used to extract data?
[API KEY] being the actual key
It might be because of the formatting or special characters in the response. Can’t tell without accessing the API with a key. Check the response text for special characters and line breaks.
You are using their CSV API now. You can also try using their XML and JSON API.
It doesn’t seem to support xml.
But here is a sample of the output. It only pastes the first line.
hi scott, can u send me the complete code for oauth1.0 in excel?
thanks in advance
Thank you very much
Hello,
How can I send a post to a specific web page and also open and display this web page at the same time ?
Thanks
Hello, my request in VBA needs login but I don’t manage to write the right code, this is the request (Chrome) :
Query String Parameters
returnUrl:/NEA/
Form Data
__RequestVerificationToken:iFUBQb5dMsakBQAgHdELh0VOb
codeAffilie:XXXXXX
key:X
password:XXXX
Читайте также: