[Сквозная аналитика] Power Query. Получаем из Битрикс24 более 2500 записей. Скрипт для выгрузки данных из Битрикс24 в Power Bi
В прошлой статье, посвященной получению записей из Битрикс24 в Excel с помощью надстройки Power Query, был приведен код на языке M для получения сделок. С помощью данного скрипта можно получать данных не только в Excel, используя надстройку PowerQuery, но и получать данные из Битрикс24 в PowerBi - систему анализа данных. В качестве ограничения мы отметили тот факт, что скрипт позволяет получит не более 2500 записей. Сегодня усовершенствуем наш скрипт, чтобы обойти данное ограничение.
Получаем сделки
Немного изменим предыдущий код, для получения неограниченного количества записей.
let
GetString=(k,x)=>
let
QueryCmd="cmd[get_"&Number.ToText(x)&"]",
QueryFilter=Number.ToText((k-1)*2500+(x-1)*50),
QueryString = "crm.deal.list?start="&QueryFilter,
QueryStringURI=Uri.EscapeDataString(QueryCmd)&"="&Uri.EscapeDataString(QueryString)
in
QueryStringURI,
GetRecord=(k,NumberOfRecord,b24Site, b24WebhookKey)=>
let
URLStringEach=List.Generate(
()=>1,
(x)=>x<=Number.RoundUp(NumberOfRecord/50),
(x)=>x+1,
(x)=>GetString(k,x)
),
URLString=Text.Combine(URLStringEach,"&"),
B24Json = Json.Document(Web.Contents("https://"&b24Site&".bitrix24.ru/rest/1/"&b24WebhookKey&"/batch.json?"&URLString)),
DealList = B24Json[result][result],
RecordToTable = Record.ToTable(DealList),
TableExpandListColumn = Table.ExpandListColumn(RecordToTable,"Value"),
TableExpandRecordColumn = Table.ExpandRecordColumn(TableExpandListColumn, "Value", {"ID", "TITLE", "TYPE_ID", "STAGE_ID"},{"ID", "TITLE", "TYPE_ID", "STAGE_ID"}),
TableRemoveColumns = Table.RemoveColumns(TableExpandRecordColumn,{"Name"})
in
TableRemoveColumns,
b24Site="Ваш Битрикс24",
b24WebhookKey="Ваш ключ",
B24Json1 = Json.Document(Web.Contents("https://"&b24Site&".bitrix24.ru/rest/1/"&b24WebhookKey&"/batch.json?"&GetString(1,1))),
NumberOfRecord = B24Json1[result][result_total][get_1],
DealListEach=List.Generate(
()=>1,
(k)=>k<=Number.RoundUp(NumberOfRecord/2500),
(k)=>k+1,
(k)=>GetRecord(k,if (NumberOfRecord-(k-1)*2500)>2500 then 2500 else NumberOfRecord-(k-1)*2500,b24Site,b24WebhookKey)
),
TableDeals=Table.Combine(DealListEach)
in
TableDeals
Что изменилось в коде?
Во-первых, добавили еще один цикл, в котором метод batch вызывается необходимое количество раз. Как и в прошлом скрипте, в первый раз мы «дергаем» метод Batch в первый раз, чтобы понять, какое количества записей нужного нам типа есть в Битрикс24. Далее в первом цикле мы формируем нужное количество вызовов метода batch, а во втором, вложенном, нужное количество вызовов метода crm.deal.list.
Во-вторых, мы изменили состав столбцов, которые получаем в таблице PowerQuery.
В-третьих, адрес нашего портала Битрикс24 и ключ API мы размещаем в переменную
Получаем Лиды
А что, если нужно получить не лиды, а сделки. Код скрипта будет не сильно отличаться, за исключением того, что нужно будет использовать метод crm.lead.list, и названием столбцов в полученной таблице.
let
GetString=(k,x)=>
let
QueryCmd="cmd[get_"&Number.ToText(x)&"]",
QueryFilter=Number.ToText((k-1)*2500+(x-1)*50),
QueryString = "crm.lead.list?start="&QueryFilter,
QueryStringURI=Uri.EscapeDataString(QueryCmd)&"="&Uri.EscapeDataString(QueryString)
in
QueryStringURI,
GetRecord=(k,NumberOfRecord,b24Site, b24WebhookKey)=>
let
URLStringEach=List.Generate(
()=>1,
(x)=>x<=Number.RoundUp(NumberOfRecord/50),
(x)=>x+1,
(x)=>GetString(k,x)
),
URLString=Text.Combine(URLStringEach,"&"),
B24Json = Json.Document(Web.Contents("https://"&b24Site&".bitrix24.ru/rest/1/"&b24WebhookKey&"/batch.json?"&URLString)),
LeadList = B24Json[result][result],
RecordToTable = Record.ToTable(LeadList),
TableExpandListColumn = Table.ExpandListColumn(RecordToTable,"Value"),
TableExpandRecordColumn = Table.ExpandRecordColumn(TableExpandListColumn, "Value", {ID", "TITLE", "NAME", "SECOND_NAME"},{ID", "TITLE", "NAME", "SECOND_NAME"}),
TableRemoveColumns = Table.RemoveColumns(TableExpandRecordColumn,{"Name"})
in
TableRemoveColumns,
b24Site="Ваш Битрикс24",
b24WebhookKey="Ваш API ключ",
B24Json1 = Json.Document(Web.Contents("https://"&b24Site&".bitrix24.ru/rest/1/"&b24WebhookKey&"/batch.json?"&GetString(1,1))),
NumberOfRecord = B24Json1[result][result_total][get_1],
DealListEach=List.Generate(
()=>1,
(k)=>k<=Number.RoundUp(NumberOfRecord/2500),
(k)=>k+1,
(k)=>GetRecord(k,if (NumberOfRecord-(k-1)*2500)>2500 then 2500 else NumberOfRecord-(k-1)*2500,b24Site,b24WebhookKey)
),
TableDeals=Table.Combine(DealListEach)
in
TableDeals
Выводы
Какие выводы можно сделать на основании полученной информации?
Во-первых, на языке M можно относительно просто получать данные из Битрикс24, независимо от объема этих данных. Но нужно понимать, что получение такого объема данных может занять достаточно большой промежуток времени и потребовать значительных вычислительных ресурсов. Это связано с тем, что при работе с большим количеством запросов Битрикс24 не «отдает» все данные не сразу, а с определенной задержкой. Поэтому, по возможности, необходимо фильтровать получаемые данные на этапе запросов к API Битрикс24.
Во-вторых, как уже было упомянуто, самой надстройке PowerQuery необходимы достаточно мощные аппаратные ресурсы для обработки большого количества информации.
В целях оптимизации при работе с большими объемами данных можно разделить работу с данными не несколько этапов . На первом этапе данные из Битрикс24 загружаем в промежуточную базу данных. При этом обновлять нужно те данные, которые были изменены с момента последнего запроса. А из PowerQuery обращаться уже непосредственно к этой промежуточной базе данных.
Заказать услугу
Если вам кажется, что все написанное сложно, и этот инструмент не для вас - доверьте работу профессионалам. Заполните форму ниже, и получите персональное предложение.