Получение онлайн-данных из ADVANTA в таблице Excel

Инструкция подходит только для MS EXCEL 2021 и новее. Описывает прямой доступ к данным из LINQ-источников ADVANTA в EXCEL через PowerQuery.

Предварительные действия

1. Настройте LINQ-запрос в ADVANTA. Запишите его UID.

2. В настройках PowerQuery потребуется указать количество строк, возвращаемых запросом. Для первого, тестового запуска можно принять, например, 20. Для полной выгрузки потребуется указать значение чуть больше числа строк, возвращаемых запросом. Например, если LINQ-запрос возвращает 878 строк, можно взять количество с запасом – 1000 или более.

3. Определите логин и пароль пользователя, от имени которого будет выполняться LINQ-запрос. В результат запроса попадут только те объекты, к которым имеет доступ данный пользователь.

Действия в EXCEL

1. Создать новый файл Excel. На закладке «Данные» выполнить команду: «Получить данные / Из других источников / Пустой запрос»:

2. Появится окно PowerQuery. В нём нажать кнопку «Расширенный редактор»:

3. Появится окно редактора запроса:

4. В нём вставить код (текст кода ниже):

let
    // Переменные
    servername = "your_sitename.ru",
    login = "your_login",
    password = "your_password",
    dataSourceId = "your_linq_uid",
    pageSize = 20,
 
    // Этап 1: Авторизация и сохранение токена 
    loginUrl = "https://" & servername & "/api/auth/login",
    loginData = "{""Login"": """ & login & """, ""Password"": """ & password & """}",
 
    // Отправка запроса на авторизацию и получение токена
    authResponse = Web.Contents(
        loginUrl,
        [
            Headers = [
                #"Content-Type" = "application/json",
                #"Accept" = "application/json"
            ],
            Content = Text.ToBinary(loginData)
        ]
    ),
    token = Json.Document(authResponse),
 
    // Сохранение токена в переменную для использования в следующем запросе
    tokenCookie = "token=" & token & "; expires=Thu, 31 Dec 2099 23:59:59 UTC; path=/",
 
    // Этап 2: Получение данных с использованием сохраненного токена
    getDataUrl = "https://" & servername & "/api/queries/get",
    queryData = "{""DataSourceId"": """ & dataSourceId & """, ""PageSize"": " & Number.ToText(pageSize) & "}",
 
    // Отправка запроса на получение данных из LINQ    
    answer = Web.Contents(
        getDataUrl,
        [
            Headers = [
                #"Content-Type" = "application/json",
                #"Accept" = "application/json",
                #"Authorization" = "Bearer " & token
            ],
            Content = Text.ToBinary(queryData)
        ]
    ),
    dataResponse = Json.Document(answer)
in
    dataResponse

В коде заменить значения переменных:

В переменной «servername» указать адрес сайта системы ADVANTA (он может быть вида «sitename.ru» или «sitename.ru/streamline»).
Логин и пароль пользователя, с правами которого будет выполняться запрос.
DataSourceId – идентификатор linq-запроса (его UID).
PageSize – количество строк, которое будет возвращено с сервера. Посмотрите в панели управления ADVANTA сколько строк возвращает LINQ-запрос и укажите значение PageSize чуть больше. При первом подключении, чтобы проверить правильность работы скрипта, укажите небольшое количество строк, например 20.

Нажать «Готово».

Возможно, появится предупреждение, что необходимо настроить способ подключения. Если появилось, нажать «Изменить учётные данные»:

Проверить, что выбран способ подключения «Анонимно» и нажать «Подключение»:

5. Появится предупреждение, что требуются сведения о конфиденциальности. Нажать «Продолжить»:

6. Появится запрос уровня конфиденциальности. Поставить переключатель в поле «Пропускать проверки…» и нажать «Сохранить»:

7. Появится список записей, загруженных из запроса. Нажать кнопку «В таблицу»:

8. Появится запрос подтверждения создания таблицы. Нажать «ОК»:

9. Появится элемент управления для настройки колонок. Нажать на кнопку справа от списка заголовка колонки:

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

10. Появится перечень элементов JSON-файла, который передаёт linq-запрос. Выбрать нужные и нажать «Ок»:

11. Строки загрузятся в редактор PowerQuery. Нажать «Закрыть и загрузить»:

12. Начнётся загрузка. Продолжительность зависит от объёма данных. Процесс виден справа от таблицы:

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

14. Если данные в ADVANTA изменились, чтобы обновить отчёт в Excel, нажать «Обновить»:

Процесс обновления также виден справа, в окне «Запросы и подключения»: