Получение JSON из API GET-запросами на Power Query в Excel (Web.Contents)

Получение JSON из API GET-запросами на Power Query в Excel (Web.Contents)

Power Query умеет подключаться не только к web страничкам, но и к API, не требующим авторизации. Если API позволяет анонимным пользователям забирать данные, Power Query подойдёт идеально.  

API компании ПИК - данные по квартирам

Подключимся к API застройщика, чтобы отслеживать актуальную стоимость квартиры. Находим нужную квартиру на сайте застройщика: https://beta.pik.ru/flat/801472.

"https://api.pik.ru/v1/flat?id=" - URL адрес, по которому застройщик предоставляет информацию, а "801472" – идентификатор квартиры.

Информация на сайте показана красиво, но разбросана по разным вкладкам.
Мы же хотим получить лаконичную табличку в Excel:

Адрес
Стоимость
Скидка
Заселение до
Площадь
Комнаты
Этаж
Ссылка
PDF

GET-запрос к API ПИК на Power Query

Для разнообразия воспользуюсь Power Query в Excel. Создаем пустой запрос:

В строке формул пишем: 

Json.Document(Web.Contents("https://api.pik.ru/v1/flat?id=801472"))

В результате получаем строку с вложенными составными элементами, которые также могут содержать вложенные элементы:

Называем запрос «getData_oneFlat» и вставляем пустой шаг после шага Источник (чтобы открыть меню - щелкаем правой кнопкой мышки на шаг Источник):

С помощью функции Record.FromList формируем строку с нужными данными, используя шаг Источник. В строке формул пишем: 

Record.FromList(
{Источник[block][address], Источник[price], Источник[discount], Источник[bulk][date_till], Источник[area], Источник[rooms], Источник[floor], Источник[url], Источник[pdf]}, 
type [ Адрес = text, Стоимость = number, Скидка = number, Заселение до = text, Площадь = number, Комнаты = number, Этаж = number, Ссылка = text, PDF = text])

Давайте разбираться как работает функция Record.FromList:

  1. Первый аргумент функции отвечает за содержимое полей – это список: 
    {Источник[block][address], Источник[price]}
  2. Источник[price] – возвращает содержимое поля price
  3. Источник[block][address] – возвращает содержимое поля address из Record вложенной в поле block
  4. Второй аргумент отвечает за названия и тип полей – это строка: 
    type [Адрес = text, Стоимость = number]

Результат - строка нужных нам значений:

Преобразуем строку в таблицу:

Выгрузим результат на лист Excel и посмотрим что получилось:

Такой формат нас устроит, теперь мы видим всю информацию по квартире в удобной таблице. 

Несколько GET-запросов с помощью функции

Теперь попробуем забрать сразу несколько квартир и разместить их в одной табличке. 

Для этого убираем последний шаг и создаём функцию getData от параметра flat (номер квартиры), заменяя сам номер на "flat":

(flat as text)=>
let
   Источник = Json.Document(Web.Contents("https://api.pik.ru/v1/flat?id="& flat )),
   create_table = Record.FromList(
{Источник[block][address], 
Источник[price], 
Источник[discount], 
Источник[bulk][date_till], 
Источник[area], 
Источник[rooms], 
Источник[floor],
Источник[url],
Источник[pdf]
},
type [
Адрес = text, 
Стоимость = number, 
Скидка = number, 
Заселение до = text, 
Площадь = number, 
Комнаты = number, 
Этаж = number,
Ссылка = text,
PDF = text
])
in
   create_table

Теперь наша функция будет получать данные по той квартире, которую мы укажем в параметре.

Составляем на листе Excel список интересных нам квартир:

Загружаем список квартир в Power Query, выставляем столбцу текстовый тип. Затем добавляем новый столбец с вызовом нашей функции от каждой квартиры в списке, получается такой код:

let
   Источник = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
   #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Получаемые квартиры", type text}}),
   #"Вызвана настраиваемая функция" = Table.AddColumn(#"Измененный тип", "getData", each getData([Получаемые квартиры]))
in
   #"Вызвана настраиваемая функция"

Таблица в Power Query выглядит так:

Нажимаем на стрелочки справа от "getData" и разворачиваем нужные столбцы, а затем выгружаем всё на лист Excel. Теперь у нас есть табличка со стоимостью, скидками, площадью и датами заселения для всех интересных нам объектов:

Заключение

Пример вызова API сайта компании ПИК показывает как Power Query может быть полезен в бытовых задачах. Используйте PQ если вам нужно быть в курсе изменений данных, будь то стоимость квартиры или цена товара в интернет-магазине. 

Если компания предоставляет данные по открытому API, вы всегда можете использовать магию Power Query и вывести эти данные себе в Excel-файл. Можно сравнить несколько товаров/категорий или вообще написать алгоритм, который будет отображать только самые интересные товары. 

Комментарии