Как мы в TURKOV за 10 часов интегрировали 1С с BI-системой Yandex Datalens

В жизни каждой быстрорастущей компании рано или поздно наступает момент, когда привычного Excel перестаёт хватать для сбора и анализа рабочих данных. Не подумайте, мы нежно любим эту программу и преклоняемся перед её возможностями. Наша компания TURKOV – ведущий производитель вентиляции – последние несколько лет бурно развивается, количество данных постоянно растет и

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

Таким образом, необходимость внедрения BI-системы была продиктована несколькими факторами:

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

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

Определившись с необходимостью внедрения BI-системы, мы задались следующим вопросом: какую систему выбрать из существующего многообразия? В голову сразу приходят такие мастодонты индустрии, как Microsoft Power BI, Tableau или набирающий в последнее время популярность Apache Superset. Однако основным критерием при выборе было желание уйти от инфраструктурных рисков: наученные горьким опытом мы хотели работать с отечественным ПО.

Изучив рынок, мы остановились на BI-системе от Яндекса – Yandex Datalens. Это решение было обусловлено рядом преимуществ:

Отечественный разработчик – минимум инфрастуктурных рисков
Простота и доступность – технически проще конкурентов.
Интеграция с экосистемой Яндекса – Metrica, Direct и др.
Стоимость – есть бесплатный доступ, для развёртывания не нужны разработчики

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

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

Принципиально выгрузка данных из 1С возможна несколькими способами:

Выгрузка по Open data protocol (OData) – сработало бы с Power BI, но, к сожалению, не с Datalens
Использование СОМ-соединения – требует усилий программистов и большого числа недешёвых лицензий 1С
Использование шины 1С – также сопряжено с определёнными рисками (может не быть нужного коннектора, сыровато для BI, очень дорого)
Рассылка отчётов по FTP / в сетевой каталог / в папку на сервере 1С – небезопасна, требует участия программиста, в случая использования сетевого каталога не происходит синхронизации файла с дашбордом
Использование готового коннектора (Экстрактора 1С) – является отличным вариантом, однако стоит внушительных для теста денег и подразумевает зависимость от внешнего разработчика

В общем, имеющиеся решения либо технически несовместимы с выбранной (или, в принципе, с любой BI системой), либо занимают несоизмеримо много ресурсов для нашей задачи – мы всего лишь хотели попробовать Datalens (спойлер: мы попробовали, нам понравилось, и сейчас ищем способ сбора отлаженных метрик более автономным и отказоустойчивым способом).

Тогда мы решили пойти от обратного: посмотрели, какие подключения поддерживает Datalens и какие из них являются наиболее простыми с технической точки зрения, чтобы можно было обойтись без привлечения программистов. Параллельно изучали методы выгрузки из 1С и наткнулись на модуль почтовой рассылки отчетов.

Как многие уже догадались, в качестве подключения наш выбор пал на старые добрые Google Sheets:

в 1С можно настроить почтовую рассылку нужных отчётов
таблицы из вложений можно парсить с помощью скрипта
данные складывать в Google Sheets, откуда их автоматически будет забирать Datalens

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

Для того, чтобы создать рассылку отчётов на почту, необходимо зайти в существующий отчёт, открыть меню почтовых рассылок и нажать «Создать рассылку отчётов».

задать отчёту наименование (в дальнейшем оно будет отображаться в теме письма, по ней скрипт будет искать нужные данные)
настроить рассылку по расписанию
указать получателя (почтовый адрес, принадлежащий аккаунту, в котором будет разрабатываться скрипт)

Важно убрать галочку из пункта «Прикреплять отчёты во вложения».

Продолжаем настраивать рассылку отчета из 1С на почту, не забываем отказаться от вложения в письме

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

Далее создаем новую гугл-таблицу. Она будет выступать заменителем полноценной базе данных, внутри неё также будет написан скрипт для парсинга.

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

Для этого переходим в меню «Инструменты» -> «Apps Script» (может называться Редактором скриптов)

Готовимся писать скрипт для парсинга таблицы из письма от 1С

Добавляем функцию, которая позволяет вызвать скрипт прямо из привычного меню, расположенного на верхней панели

function OpenScript() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Import') .addItem('Импортировать', 'importMail') .addToUi(); }

Функция OpenScript добавляет в интерфейс пункт Import, при нажатии на который вызывается функция importMail, выгружающая нужные данные с почты. Осталось написать эту самую функцию и можно с пользой тестировать функционал Datalens.

// Используем сегодняшнюю дату в теме письма var subjectQuery = `Ваше название отчета от ${day}.${month}.${year}`; var threads = GmailApp.search(`subject:"${subjectQuery}"`); if (threads.length === 0) { Logger.log(`Письма с темой "Ваше название отчета от ${day}.${month}.${year}" не найдены.`); } else { [threads[0]].forEach(thread => { let msg = thread.getMessages()[0]; const subject = msg.getSubject(); Logger.log("Тема письма, из которого извлекаются данные: " + subject); const dateInSubject = subject.match(/от\s+(\d{2}\.\d{2}\.\d{4})/); let extractedDate = dateInSubject ? dateInSubject[1] : "Дата не найдена"; const table = msg.getBody().match(/<table[\s\S]*?>*?<\/table>/gi); let newTable; for (let i = 0; i < table.length; i++) { if (table[i].indexOf('Денежный поток') !== -1 && table[i].indexOf('Поступления') !== -1 && table[i].indexOf('Итого') !== -1) { newTable = table[i]; i = table.length; } }

Полная версия кода будет доступна в файле по ссылке. Можете написать в личку – скину весь блокнот. Либо можете найти полную версию кода в прикрепленном файле.

Скрипт ищет в вашей таблице лист с названием «Datalist», определяет текущую дату и формирует тему письма «Ваше название отчета от ДД.ММ.ГГГГ»
Затем происходит поиск соответствующего письма на почте
Если письмо найдено, то скрипт извлекает дату из письма в отдельный столбец
Далее он ищет HTML-таблицу с заданными триггерными словами («Денежный поток», «Поступления», «Итого»), парсит найденную таблицу и перезаписывает данные в гугл-файл

if (table[i].indexOf('Денежный поток') !== -1 && table[i].indexOf('Поступления') !== -1 && table[i].indexOf('Итого') !== -1) { newTable = table[i]; i = table.length; }

Такая схема подойдёт для сбора и хранения «оперативных» данных, которые обновляются раз в какой-то период (например, ежедневно) и перезаписываются в хранилище. Для накопления временных рядов скрипт требует небольшой корректировки, легко реализуемой с помощью Chat GPT.

Для запуска скрипта необходимо предоставить редактору доступ к вашему почтовому ящику. Окно с запросом появится после нажатия кнопки «Выполнить» в разделе редактора кода.

После запуска функции ImportMail в окошке редактора выведутся логи, а на листе Datalist должны появиться данные из 1С.

Результат запуска скрипта, появились данные из отчета 1С

После тестирования работоспособности скрипта можно настроить его автоматическое исполнение по времени. Это делается в разделе «Триггеры» внутри App Script. В противном случае придётся каждый раз, когда хотите получить свежие данные, заходить внутрь таблицы и активировать кнопку «import», созданную нами ранее.

Настраиваем триггер, для автоматического запуска нашей функции по расписанию

Таблицу с листа Datalist можно при желании подвергнуть формульной обработке внутри Google Sheets, очистив данные от ненужных символов, или, например, выполнив арифметические операции.

Таким образом, на соседнем листе будут готовые для передачи в Datalens данные из 1С. Они будут автоматически обновляться при поступлении новых отчётов из 1С, самостоятельно обрабатываться заданным образом и превращаться в красивые дашборды внутри системы. И всё это без вашего участия!

Файл доступен по ссылке. Для использования нужно скопировать файл себе и запустить скрипт, предоставив ему разрешение на выполнение.

docs.google.com

Новая таблица

Нам удалось в крайне сжатые сроки настроить систему автоматической выгрузки данных из 1С:Предпрития в BI-систему Yandex Datalens. На выходе мы получили автономную систему, которая забирает, преобразовывает и загружает данные, важные для бизнеса.

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

На этом всё, жду обратной связи. С удовольствием отвечу на вопросы в комментариях или по адресу rykovtihon@gmail.com. Если интересны типовые дашборды, разработанные на полученных данных, или вариации метода с накоплением временных рядов – дайте знать – выпущу 2 часть.

Как мы в TURKOV за 10 часов интегрировали 1С с BI-системой Yandex Datalens

Необходимость BI

Способы выгрузки из 1С

Рассылка отчётов на почту

Выгрузка на диск

Заключение