Power Query прокси настройка: системные параметры, Web.Contents и обновление книги по расписанию

Раздел 3.4 · Рабочие программы

Разбор идёт по порядку. Откуда механизм запросов берёт параметры посредника и почему собственного поля у него нет, как записать адрес и заголовки внутри функции Web.Contents, что делает брандмауэр формул с уровнями конфиденциальности, как выглядит обновление по расписанию и что ломается при переносе книги на другую машину, сколько обращений уходит при разворачивании столбца с таблицами, и как та же книга работает после публикации отчёта через шлюз.

1. Что понадобится

Понадобится Excel для Windows с вкладкой Данные и кнопкой Получить данные. Механизм запросов встроен в настольный выпуск, отдельная установка надстройки давно не нужна. Все пути к пунктам меню ниже я привожу по русской локализации, английские названия дублирую там, где они попадают в текст ошибки.

Понадобится строка подключения. В кабинете сервиса список выдаётся в двух форматах: IP:PORT, когда адрес рабочей машины уже привязан к пакету, и IP:PORT:LOGIN:PASS для входа по паре логин и пароль. Список забирается ссылкой или файлом, я держу его отдельным txt рядом с книгой. Пакет включается примерно за 5 минут, бесплатный тест до 2 часов позволяет прогнать полное обновление книги ещё до покупки.

Понадобится понимание, каким способом авторизуется посредник. Механизм запросов Excel общается с посредником через сетевой стек Windows, и вход по паре логин и пароль он умеет отдавать только в формате базовой авторизации. Когда адрес машины привязан в кабинете, поля логина не нужны совсем: посредник узнаёт источник по адресу и пропускает соединение молча. Я работаю именно так, потому что привязка снимает половину возни с хранением пароля в конфигурационных файлах. Для запросов из книги я подключаю HTTP-прокси для обращений из Excel: механизм понимает схемы http и https, туннель до чужого узла открывается методом CONNECT.

Понадобится тестовый эндпоинт. Я держу под рукой два: сервис эха, который возвращает адрес источника обычным текстом, и любой отдающий JSON адрес того API, с которым предстоит работать.

2. Откуда Power Query берёт параметры посредника

Собственного поля посредника у механизма запросов нет, и это осознанное устройство. Запросы выполняются в отдельном процессе, который называется контейнером вычислений, Microsoft.Mashup.Container. Процесс собран на платформе .NET и за сетевыми параметрами обращается к операционной системе: он читает те же настройки, что и любая другая программа Windows, минуя интерфейс Excel целиком.

Значит, адрес посредника задаётся снаружи книги. Мест, откуда он подхватывается, ровно три, и они выстроены по приоритету.

Первые два пункта это одно и то же место с двумя окнами. Ветка реестра HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings хранит три значения: ProxyEnable со единицей, ProxyServer со строкой адреса и порта, ProxyOverride со списком исключений. Проверить их быстрее всего консолью:

reg query "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings" /v ProxyServer
reg query "HKCU\Software\Microsoft\Windows\CurrentVersion\Internet Settings" /v ProxyEnable
netsh winhttp show proxy

Третий пункт я считаю самым удобным для рабочей книги. Файл конфигурации контейнера лежит рядом с исполняемыми файлами Office, обычно по пути C:\Program Files\Microsoft Office\root\Office16\. Там несколько файлов с похожими именами под разные версии платформы, и правку я вношу во все сразу, потому что версию контейнера Excel выбирает сам. Внутрь секции configuration добавляется блок:

<system.net>
  <defaultProxy enabled="true" useDefaultCredentials="true">
    <proxy usesystemdefault="false"
           proxyaddress="http://203.0.113.14:8000"
           bypassonlocal="true" />
    <bypasslist>
      <add address="localhost" />
      <add address="127\.0\.0\.1" />
    </bypasslist>
  </defaultProxy>
</system.net>

Обратите внимание на список исключений: адреса там записываются регулярными выражениями, поэтому точки экранируются обратной косой чертой. Ошибка в этом месте стоила мне часа: книга упорно ходила через посредник даже к локальному файлу на сетевой шайбе.

После правки конфигурации Excel закрывается полностью. Контейнер живёт своей жизнью и после закрытия книги висит в памяти ещё несколько минут, так что я добиваю его руками и только потом открываю книгу заново:

taskkill /F /IM Microsoft.Mashup.Container.NetFX45.exe
taskkill /F /IM Microsoft.Mashup.Container.exe

Четвёртый пункт отвечает за обновление из-под службы. Задание планировщика, запущенное от системной учётной записи, ветку текущего пользователя не видит вовсе, и параметры ему нужно положить в хранилище WinHTTP:

netsh winhttp set proxy proxy-server="http=203.0.113.14:8000;https=203.0.113.14:8000" bypass-list="localhost;127.0.0.1;<local>"
netsh winhttp show proxy
netsh winhttp reset proxy

Автоматическое определение параметров через скрипт PAC контейнер поддерживает, только я его снимаю. Скрипт вычисляется на каждое обращение, и при разворачивании таблицы на несколько сотен строк это заметно растягивает обновление. Прямой адрес отрабатывает предсказуемо.

3. Web.Contents: адрес, заголовки и тайм-аут

Функция Web.Contents это то место, где запрос собирается руками. Первый параметр это базовый адрес, второй это запись параметров. Мастер импорта пишет только первый, всё остальное я дописываю в расширенном редакторе: Данные, Получить данные, Запустить редактор Power Query, дальше Расширенный редактор.

Рабочий запрос выглядит так:

let
    База    = "https://api.example.net",
    Ключ    = "b7Rk2Qm9x4",
    Ответ   = Web.Contents(
        База,
        [
            RelativePath = "v1/catalog",
            Query        = [ page = "1", per_page = "100" ],
            Headers      = [
                #"Accept"        = "application/json",
                #"User-Agent"    = "Excel Power Query",
                #"Authorization" = "Bearer " & Ключ
            ],
            Timeout      = #duration(0,0,1,30)
        ]
    ),
    Разбор  = Json.Document(Ответ),
    Таблица = Table.FromRecords(Разбор[items])
in
    Таблица

Ключевая деталь сидит в разделении адреса. Когда весь путь склеен в одну строку через оператор &, механизм считает источник динамическим и отказывается обновлять книгу на стороне сервиса. Базовый адрес строкой без склейки плюс RelativePath и Query дают тот же итоговый запрос при статическом источнике.

Заголовок Authorization механизм передаёт, когда учётные данные источника выставлены как анонимные. Смешивать встроенную авторизацию источника с собственным заголовком нельзя: механизм отдаст ошибку доступа ещё до отправки запроса. Я всегда выбираю анонимный вход и кладу ключ в заголовок руками, так поведение одинаковое и на машине, и после публикации.

Отдельно про коды ответа. По умолчанию любой код за пределами двухсотых обрывает шаг с ошибкой, и вся книга остаётся необновлённой из-за одной строки. Список в ManualStatusHandling возвращает управление в шаги:

let
    База  = "https://api.example.net",
    Ответ = Web.Contents(База, [
        RelativePath         = "v1/catalog",
        ManualStatusHandling = {400, 404, 407, 429, 500}
    ]),
    Код   = Value.Metadata(Ответ)[Response.Status],
    Итог  = if Код = 200 then Json.Document(Ответ)
            else if Код = 429 then Function.InvokeAfter(
                     () => Json.Document(Web.Contents(База, [RelativePath="v1/catalog", IsRetry=true])),
                     #duration(0,0,0,5))
            else error Error.Record("HTTP", "Код ответа " & Text.From(Код))
in
    Итог

Код 407 в этом списке стоит намеренно. Так отказ авторизации у посредника приходит понятной записью в столбце ошибок, и его видно сразу, без раскрытия каждой строки поодиночке.

4. Уровни конфиденциальности и отказ брандмауэра формул

Механизм запросов помечает каждый источник уровнем конфиденциальности и следит, чтобы содержимое одного источника не утекло в запрос к другому. Проверку выполняет брандмауэр формул, и он срабатывает раньше, чем запрос уйдёт в сеть.

Отказ выглядит одним из двух сообщений. Первое: Formula.Firewall: Query 'Каталог' (step 'Добавлен пользовательский объект') references other queries or steps, so it may not directly access a data source. Второе: Formula.Firewall: Query 'Каталог' is accessing data sources that have privacy levels which cannot be used together.

Причина у обоих одна. Запрос читает значение из книги или из другого запроса и подставляет его в адрес обращения к сети. Лист с ключами помечен как конфиденциальный, чужой API как общедоступный, передача между ними запрещена. У меня это выстреливает каждый раз, когда список артикулов лежит на листе, а обращение уходит наружу по каждому артикулу.

Разбираю двумя ходами. Первый ход: разложить логику на два запроса так, чтобы источник читался отдельным запросом с явно назначенным уровнем. Второй ход быстрее и подходит для книги, которую я держу у себя: Данные, Получить данные, Параметры запроса, раздел Конфиденциальность, переключатель Всегда пропускать уровни конфиденциальности. Тот же переключатель есть отдельно для текущей книги и глобально для всех книг пользователя.

Уровень источника переназначается там же, где хранятся учётные данные: Данные, Получить данные, Параметры источника данных, кнопка Изменить разрешения. Внутри поле Уровень конфиденциальности со списком из четырёх значений. Посредник в эту проверку не входит вовсе: механизм считает источником конечный адрес, куда идёт обращение, и адрес посредника в правилах не участвует. Знание этого экономит время, потому что при отказе брандмауэра сеть трогать бесполезно.

5. Обновление по расписанию и переезд книги на другую машину

Обновление внутри Excel настраивается через Данные, Запросы и подключения, правая кнопка по запросу, Свойства. Там три галочки: обновление при открытии файла, обновление с заданным интервалом в минутах, разрешение фонового обновления. Интервал я ставлю от 30 минут и выше, потому что каждое срабатывание перезапускает всю цепочку запросов книги целиком.

Обновление по расписанию без открытого окна собирается через планировщик заданий и короткий скрипт PowerShell:

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$book = $excel.Workbooks.Open("D:\otchety\catalog.xlsx")
$book.RefreshAll()
$excel.CalculateUntilAsyncQueriesDone()
$book.Save()
$book.Close($false)
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null

Строка CalculateUntilAsyncQueriesDone обязательна. Без неё скрипт закрывает книгу до того, как запросы вернут содержимое, и на листе остаётся вчерашняя таблица. Задание в планировщике я запускаю от той же учётной записи, под которой книга настраивалась вручную, с галочкой Выполнять только для вошедших в систему пользователей. Запуск от системной учётной записи требует переносить и параметры посредника в WinHTTP, и учётные данные источника, поэтому обычный пользовательский запуск получается короче.

Переезд книги на другую машину даёт три сюрприза подряд, и все три предсказуемы.

Учётные данные источника внутри файла не хранятся. Они лежат в профиле пользователя той машины, где их вводили, и при первом открытии книги на новом месте механизм выдаст запрос на вход. Уровень конфиденциальности источника тоже локальный и назначается заново.

Параметры посредника на новой машине свои. Книга их с собой не везёт, и до правки системных параметров или файла конфигурации контейнера запросы уйдут с адреса этой машины. Я вожу вместе с книгой короткий cmd-файл с командами reg add и netsh, чтобы приведение новой машины в рабочее состояние занимало минуту.

Привязка адреса тоже переносится. В пакет входит 2 адреса, менять привязку разрешено без ограничений, так что рабочий сервер и настольная машина живут внутри одного пакета одновременно. Стоит помнить, что при двух привязках лимит потоков делится пополам: на обычном пакете это 500 потоков на каждую сторону, для книги с несколькими запросами запас всё равно избыточный. Под регулярные ночные обновления я развожу задания по времени, чтобы планировщик не стартовал их пачкой. Тот же расчёт для прогонов по готовым шаблонам разобран на странице про адреса под прогоны в ZennoPoster.

6. Число обращений при разворачивании таблицы

Самое дорогое место всей схемы это столбец с вызовом функции по каждой строке. Список артикулов на 800 позиций превращается в 800 обращений к чужому API, и уходят они подряд с интервалом в десятки миллисекунд.

Устроено так. Функция вызывается в добавленном столбце, результат каждого вызова это таблица, дальше Table.ExpandTableColumn раскрывает вложенные таблицы в плоский список.

let
    Артикулы = Excel.CurrentWorkbook(){[Name="Артикулы"]}[Content],
    Опрос    = (код as text) as table =>
        Function.InvokeAfter(
            () =>
                let
                    Сырое = Web.Contents(
                        "https://api.example.net",
                        [ RelativePath = "v1/item",
                          Query        = [ sku = код ],
                          ManualStatusHandling = {404, 429} ]),
                    Разбор = Json.Document(Сырое)
                in
                    Table.FromRecords(Разбор[variants]),
            #duration(0,0,0,0.7)
        ),
    Сцепка   = Table.AddColumn(Артикулы, "Ответ", each Опрос([Артикул])),
    Плоско   = Table.ExpandTableColumn(Сцепка, "Ответ", {"id", "name", "stock"}),
    Готово   = Table.Buffer(Плоско)
in
    Готово

Функция Function.InvokeAfter ставит паузу перед каждым вызовом. Значение #duration(0,0,0,0.7) даёт семьсот миллисекунд между обращениями, и на восьмистах артикулах обновление растягивается примерно на 10 минут. Пауза подбирается под лимит чужой стороны: я начинаю с секунды, смотрю на долю ответов 429 и опускаю до момента, когда отказы исчезают.

Второе, что важно знать про число обращений. Механизм вычисляет один и тот же шаг несколько раз: отдельно для предварительного просмотра в редакторе, отдельно для загрузки на лист, отдельно для каждого запроса, который ссылается на этот. Реальное количество запросов к API легко выходит в два и три раза больше ожидаемого. Спасает Table.Buffer и Binary.Buffer: результат фиксируется в памяти и повторно из сети не тянется.

Ещё один рычаг это число одновременных вычислений. Оно задаётся в Параметры запроса, раздел Загрузка данных, поле с максимальным числом одновременных вычислений. По умолчанию механизм берёт значение по числу ядер, и на восьмиядерной машине к чужому API уходит восемь параллельных потоков. Я ставлю двойку, когда сторона отвечает лимитом, и снимаю ограничение обратно на спокойных источниках. Для прогонов по большим спискам я подключаю адреса под сбор данных и развожу артикулы по нескольким книгам: пул сервиса насчитывает около 12 000 активных адресов с автоматической ротацией внутри пула, так что параллельные обновления не сталкиваются на одном адресе.

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

7. Публикация отчёта и работа через шлюз

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

Шлюз это отдельная служба Windows, и параметры посредника у неё свои. Задаются они в файле Microsoft.PowerBI.EnterpriseGateway.exe.config по пути C:\Program Files\On-premises data gateway\, той же секцией system.net, что и у контейнера вычислений. Рядом лежит Microsoft.Mashup.Container.exe.config, его правлю тем же блоком: шлюз поднимает контейнеры вычислений внутри себя, и они читают собственный файл конфигурации.

<system.net>
  <defaultProxy enabled="true" useDefaultCredentials="true">
    <proxy proxyaddress="http://203.0.113.14:8000" bypassonlocal="true" />
  </defaultProxy>
</system.net>

После правки служба перезапускается из окна Службы либо командой:

net stop PBIEgwService && net start PBIEgwService

Учётная запись, под которой работает служба, по умолчанию системная. Если посредник требует вход по паре логин и пароль, я перевожу службу на доменную учётную запись через вкладку Вход в систему в свойствах службы, потому что параметр useDefaultCredentials отдаёт наружу учётные данные того пользователя, от чьего имени идёт процесс. Привязка адреса машины в кабинете избавляет от этой возни целиком: шлюз ходит через посредник молча.

Публикация ставит ещё одно условие. Источник должен быть статическим, иначе служба откажется от обновления по расписанию с сообщением про динамический источник данных. Разложенный на RelativePath и Query запрос это условие выполняет, склеенный оператором & адрес нет.

Проверить связку целиком проще всего одним обновлением из веб-интерфейса с включённой историей обновлений: там видно и время выполнения каждого запроса, и текст отказа при неудаче. Для регулярных обращений к внешним интерфейсам через шлюз я держу прокси для массовых обращений к API отдельным набором от того, что стоит на настольных машинах.

8. Проверка после настройки

Проверку я делаю в три захода, и занимает она пять минут.

Первый заход это запрос к сервису эха прямо из книги. Пустой запрос создаётся через Данные, Получить данные, Из других источников, Пустой запрос, дальше расширенный редактор и три строки:

let
    Сырое  = Text.FromBinary(Web.Contents("https://api.ipify.org")),
    Итог   = #table({"Адрес источника"}, {{Сырое}})
in
    Итог

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

Второй заход это сверка с консолью с той же машины. Строка проверяется до внесения в систему, ответ приходит за доли секунды:

curl -x http://203.0.113.14:8000 -U u4471:Kq8Rm2Vt -s https://api.ipify.org
curl -x http://203.0.113.14:8000 -U u4471:Kq8Rm2Vt -sv https://api.example.net/v1/ping

Адрес посредника в ответе первой команды подтверждает рабочую строку и проходящий вход. Строка CONNECT api.example.net:443 с кодом 200 во второй команде подтверждает, что туннель по TLS открывается. Расхождение между консолью и книгой означает, что дело в контейнере вычислений, сеть тут ни при чём.

Третий заход это сверка заголовков. Тот же пустой запрос с адресом https://httpbin.org/headers вернёт JSON с тем набором полей, который дошёл до конечного узла. Смотрю два момента: подставился ли мой User-Agent и нет ли служебных полей с адресом источника. Для работы с чужими интерфейсами я держу отдельный набор строк, и лимит чужой стороны расходуется только моими запросами. Как такой набор раскладывается по потокам на массовом сборе, я разбирал на странице про адреса под многопоточные прогоны A-Parser.

Отдельно проверяю поведение под планировщиком. Задание запускается руками кнопкой Выполнить, дальше я открываю обновлённую книгу и смотрю на столбец с меткой времени, который добавляю в каждый запрос отдельным шагом DateTime.LocalNow(). Свежая метка подтверждает, что обновление прошло целиком.

9. Разбор ошибок

Ниже сообщения, которые встречались мне при работе книги через посредник чаще прочих.

Две ситуации разберу подробнее.

Первая: адрес источника в проверочном запросе меняется от обновления к обновлению. Такое поведение нормальное, ротация внутри пула работает автоматически, и каждое новое соединение получает свою точку выхода. Для сбора данных это плюс: лимит чужой стороны считается по адресу, и разнесение обращений по пулу растягивает доступную квоту.

Вторая: часть запросов книги уходит через посредник, часть напрямую. Смотрю список исключений. В ProxyOverride или в блоке bypasslist попал шаблон, который совпадает с доменом целевого API по хвосту имени. Запись example.net уводит напрямую и api.example.net, и stage.example.net, потому что сравнение идёт по окончанию строки. Убираю лишний шаблон, оставляю только локальные адреса.

Соседние страницы справочника закрывают смежные случаи: перехват трафика программ в Proxifier пригодится, когда программа полей посредника не имеет вовсе, работа со списком адресов в KeyAssort показывает, как раздавать пул по потокам при разборе выдачи, настройка клиентов Postman и Insomnia разбирает те же заголовки и сертификаты со стороны отладки запроса. Когда таблица переезжает из книги в облако, смотрите обращения к внешним API из Google Таблиц: там разобрана передача адреса посредника через Apps Script и работа с квотами на число вызовов.