Google Sheets - автоматизируем отчетность для маркетплейсов: бартер, раздачи, коммерция

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

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

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

Дальше расскажу как делал, что получилось и что это дало.

Меня зовут Никита - я директор по маркетингу на удаленке: выстраиваю аналитику, стратегии, команды и привожу их к результату, заходите в мой телеграм, чтобы быть на связи: t.me/svestudio

1. Для начала я пошел смотреть таблицы которые ведут мои подчиненные: выделил все действительно необходимые данные, убрав ненужные.

2. Создал универсальную таблицу отчетности для всех сотрудников, далее в зависимости от подразделения - скрыл ненужные столбцы, чтобы не мешали

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

4. Создал базу данных для раскрывающихся списков на отдельной вкладке

5. Создал таблицу руководителя, добавив туда листы с подразделениями которые полностью соответствовали листам сотрудников.

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

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

Самое оптимальное решение - сделать с помощью Google apps script (откройте таблицу и выберите из пунктов меню: Расширения, далее перейдите в Apps Script).

Да там все на JavaScript надо делать, навыки в разработке отсутствуют, но нейроночки нас выручат, идем в ChatGPT - описываем задачу и получаем первую часть кода:

// Массивы ID таблиц для каждого листа const BARTER_FILE_IDS = ['ID таблицы из адресной строки', 'ID таблицы из адресной строки']; const COMMERCIAL_FILE_IDS = ['ID таблицы из адресной строки']; const DISTRIBUTION_FILE_IDS = ['ID таблицы из адресной строки', 'ID таблицы из адресной строки', 'ID таблицы из адресной строки']; // ID вашей таблицы руководителя const MANAGER_SHEET_ID = 'ID таблицы из адресной строки'; // Карта листов с соответствующими ID таблиц const SHEETS_MAP = { 'Бартер': BARTER_FILE_IDS, 'Коммерция': COMMERCIAL_FILE_IDS, 'Раздачи': DISTRIBUTION_FILE_IDS };

Это просто дорожная карта для скрипта, в ней указываются ID таблицы руководителя и ID таблиц сотрудников ответственных за направление.

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

// Функция для обновления данных function updateManagerSheet() { const managerSpreadsheet = SpreadsheetApp.openById(MANAGER_SHEET_ID); for (let sheetName in SHEETS_MAP) { const sourceFileIds = SHEETS_MAP[sheetName]; const targetSheet = managerSpreadsheet.getSheetByName(sheetName); if (!targetSheet) { console.log('Лист ${sheetName} не найден в таблице руководителя'); continue; } const targetData = targetSheet.getDataRange().getValues(); const targetDict = {}; // Создаем словарь для существующих записей на основе уникального ключа в первом столбце targetData.forEach((row, index) => { const uniqueKey = row[0]; // Первый столбец — уникальный ключ if (uniqueKey) { targetDict[uniqueKey] = index + 1; // rowIndex для обновления данных } }); sourceFileIds.forEach(fileId => { const sourceSpreadsheet = SpreadsheetApp.openById(fileId); const sourceSheet = sourceSpreadsheet.getSheets()[0]; const sourceData = sourceSheet.getDataRange().getValues(); for (let i = 1; i < sourceData.length; i++) { // Пропускаем заголовок const sourceRow = sourceData[i]; // Проверяем, что в 40-м столбце (AN) есть значение if (sourceRow[39] !== "" && sourceRow[39] !== null) { const uniqueKey = `${fileId}_${sourceRow[0]}_${sourceRow[3]}`; // Уникальный ключ на основе файла и A, D столбцов if (targetDict[uniqueKey]) { try { const rowIndex = targetDict[uniqueKey]; // Проверяем существование строки if (rowIndex > targetSheet.getLastRow()) { Logger.log(`Ошибка: строки с индексом ${rowIndex} нет в таблице.`); continue; } // Очистка данных перед записью const cleanedRow = sourceRow.map(value => { if (value instanceof Date) { // Форматируем дату в дд.мм.гггг return Utilities.formatDate(value, Session.getScriptTimeZone(), 'dd.MM.yyyy'); } return value !== null && value !== undefined ? value.toString() : ''; }); // Проверяем, хватает ли столбцов const currentCols = targetSheet.getLastColumn(); const neededCols = cleanedRow.length; if (currentCols < neededCols + 1) { // +1 для учета уникального ключа targetSheet.insertColumnsAfter(currentCols, neededCols - currentCols); } // Обновляем строку Logger.log(`Обновляем строку: ключ=${uniqueKey}, строка=${rowIndex}, столбцы=${cleanedRow.length}`); targetSheet.getRange(rowIndex, 2, 1, cleanedRow.length).setValues([cleanedRow]); } catch (e) { Logger.log(`Ошибка при обновлении строки с ключом ${uniqueKey}: ${e.message}`); } } else { try { // Очистка данных перед добавлением const cleanedRow = sourceRow.map(value => { if (value instanceof Date) { // Форматируем дату в дд.мм.гггг return Utilities.formatDate(value, Session.getScriptTimeZone(), 'dd.MM.yyyy'); } return value !== null && value !== undefined ? value.toString() : ''; }); // Логируем добавление новой строки Logger.log(`Добавляем новую строку для ключа: ${uniqueKey}`); // Добавляем новую строку targetSheet.appendRow([uniqueKey, ...cleanedRow]); // Уникальный ключ в первый столбец } catch (e) { Logger.log(`Ошибка при добавлении строки с ключом ${uniqueKey}: ${e.message}`); } } } } }); } }

Да, это все мой нейро - помощник написал. Что она делает, проходиться по таблицам сотрудников, если в 40 столбце "AN" - указан статус, то это запись переносит в таблицу руководителя и распределяется по нужным листам - которые я указал в самом начале, дополнительно создавая уникальный ключ для этой записи, чтобы уже существующие записи не дублировались.

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

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

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

Вот мы создали табличку с планом и установили его, что делать дальше? А тут мы настроим проверку по Листам, суммирования показателей на основе типа интеграции и артикула.

Снова идем за помощью к нейро - помощнику вот что получаем:

// Функция обновления фактов в планах function updatePlanFacts() { const spreadsheet = SpreadsheetApp.openById(MANAGER_SHEET_ID); // Русские названия месяцев const months = [ 'январь', 'февраль', 'март', 'апрель', 'май', 'июнь', 'июль', 'август', 'сентябрь', 'октябрь', 'ноябрь', 'декабрь' ]; const integrationSheets = { 'Бартер': spreadsheet.getSheetByName('Бартер'), 'Коммерция': spreadsheet.getSheetByName('Коммерция'), 'Раздачи': spreadsheet.getSheetByName('Раздачи') }; // Проверяем наличие листов for (let type in integrationSheets) { if (!integrationSheets[type]) { console.log(`Лист "${type}" для интеграций не найден.`); return; } } // Кэш для планов, чтобы собирать суммы перед записью const planUpdates = {}; // Читаем данные из каждого листа интеграции for (let type in integrationSheets) { const data = integrationSheets[type].getDataRange().getValues(); for (let i = 1; i < data.length; i++) { // Пропускаем заголовок const purchaseDateRaw = data[i][2]; // Дата закупки (столбец C) const releaseDateRaw = data[i][3]; // Дата выхода (столбец D) const article = data[i][5]; // Рекламируемый артикул (столбец F) const itemName = data[i][6]; // Наименование товара (столбец G) const quantity = Number(data[i][8]) || 0; // Количество (столбец I) if (!article || !itemName || quantity <= 0) continue; // Определяем целевую дату на основе типа листа let targetDate; if (type === 'Бартер' || type === 'Раздачи') { // Для "Бартер" и "Раздачи" всегда используем столбец C targetDate = new Date(purchaseDateRaw); } else { // Для "Коммерция" оставляем прежнюю логику targetDate = releaseDateRaw ? new Date(releaseDateRaw) : new Date(purchaseDateRaw); } // Проверяем корректность даты if (!targetDate || isNaN(targetDate.getTime())) { console.log(`Некорректная дата для строки ${i + 1} на листе "${type}": Дата закупки=${purchaseDateRaw}, Дата выхода=${releaseDateRaw}`); continue; } const targetMonth = targetDate.getMonth(); const targetYear = targetDate.getFullYear(); const planSheetName = `План ${months[targetMonth]} ${targetYear}`; if (!planUpdates[planSheetName]) { planUpdates[planSheetName] = {}; } // Суммируем значения в кэше const key = `${article}_${itemName}_${type}`; if (!planUpdates[planSheetName][key]) { planUpdates[planSheetName][key] = 0; } planUpdates[planSheetName][key] += quantity; } } // Записываем суммы из кэша в соответствующие листы "План" for (let planSheetName in planUpdates) { const planSheet = spreadsheet.getSheetByName(planSheetName); if (!planSheet) { console.log(`Лист "${planSheetName}" не найден, пропускаем.`); continue; } const planData = planSheet.getDataRange().getValues(); const updates = planUpdates[planSheetName]; for (let key in updates) { const [article, itemName, type] = key.split('_'); const totalQuantity = updates[key]; for (let i = 1; i < planData.length; i++) { // Пропускаем заголовок const planArticle = planData[i][0]; // Рекламируемый артикул (столбец A) const planItemName = planData[i][1]; // Наименование товара (столбец B) const planIntegrationType = planData[i][2]; // Тип интеграции (столбец C) if (planIntegrationType === type && (planArticle === article || planItemName === itemName)) { planSheet.getRange(i + 1, 5).setValue(totalQuantity); // Записываем итоговое значение console.log(`Обновлено: Лист "${planSheetName}", Артикул "${article}", Тип "${type}", Факт "${totalQuantity}".`); } } } } console.log(`Факты обновлены для всех листов.`); }

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

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

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

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

Как создать бота - есть куча инструкций в интернете, вот даже нашел для вас:

Для начала создадим бота через @BotFather:

Откройте Телеграм и найдите @BotFather.
Нажмите /start и затем команду /newbot.
Придумайте имя для вашего бота (например, ShopBot) и уникальный юзернейм (например, MyShopBot).
Сохраните API токен, который будет использован для дальнейшей настройки.

Ну как группу создать думаю разберетесь, я выбрал для себя группу с темами, то есть в группе у меня еще разделы были: раздачи/бартер/коммерция.

Добавьте вашего бота туда и дайте права администратора.

Теперь в наш код добавляем сведения о боте и группе:

// Telegram Bot API const TOKEN = 'API токен телеграм бота'; // Токен вашего бота const CHAT_ID = 'ID группы телеграм - узнается путем API запроса'; // ID группы или чата const TOPIC_MAP = { // Соответствие ID тем и вкладок таблицы, узнаем так же темы чата, указываем актуальные вместо '15'/'13'/'16'. '15': 'Бартер', '13': 'Коммерция', '16': 'Раздачи' };

1. Где взять Thread ID и Group ID (ID темы и ID группы):

ID темы и группы можно получить через Telegram Bot API. Вот как это сделать:

1. Отправьте сообщение в тему группы, куда бот будет отправлять данные.

• Перейдите в вашу группу с темами.

• Выберите тему.

• Напишите сообщение.

2. Запросите обновления через Bot API:

• Откройте браузер и перейдите по ссылке, где вместо TOKEN - подставьте ваш API ключ, который сгенерировали при создании бота:

• Вы увидите ответ в формате JSON, например:

{ "ok": true, "result": [ { "update_id": 123456789, "message": { "message_thread_id": 12345, "chat": { "id": -987654321, "type": "supergroup" }, "text": "Test message" } } ] }

"id": -987654321: Это ваш ID группы

"message_thread_id": 12345: это ваш ID темы.

Если тем несколько, как у меня, просто проделайте это несколько раз.

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

Вот функция которая базово просто позволяет отправить данные в тележеньку:

// Функция для отправки сообщений в Telegram function sendToTelegram(message, threadId) { const url = `https://api.telegram.org/bot${TOKEN}/sendMessage`; const payload = { chat_id: CHAT_ID, text: message, parse_mode: 'Markdown', message_thread_id: threadId // Если нет тем, удалите эту строку }; const options = { method: 'post', contentType: 'application/json', payload: JSON.stringify(payload), }; UrlFetchApp.fetch(url, options); // Отправка запроса }

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

Давайте лучше данные будем собирать.

Мне нужно чтобы я видел все записи сделанные сотрудниками в подразделениях за вчера. Этот скрипт проходит и в каждой вкладке анализирует дату: убеждается что запись была сделана вчера. Суммирует данные по артикулу. Данные которые он собирает: Артикул, кол-во интеграций, стоимость этих интеграций.

// Функция для фильтрации данных за предыдущий день function getPreviousDayData(sheet) { if (!sheet) { console.log(`Ошибка: Лист не передан или не существует.`); return {}; } if (sheet.getLastRow() === 0) { console.log(`Лист ${sheet.getName()} пуст.`); return {}; } const data = sheet.getDataRange().getValues(); // Чтение всех данных const today = new Date(); const previousDay = new Date(today.setDate(today.getDate() - 1)); const previousDayStr = Utilities.formatDate(previousDay, Session.getScriptTimeZone(), 'dd.MM.yyyy'); const result = {}; for (let i = 1; i < data.length; i++) { // Пропускаем заголовок const rawDate = data[i][2]; // "Дата закупа" const dateStr = Utilities.formatDate(new Date(rawDate), Session.getScriptTimeZone(), 'dd.MM.yyyy'); // Преобразуем в строку const itemName = data[i][6]; // "Наименование товара" const quantity = Number(data[i][8]) || 0; // "Кол-во" const price = Number(data[i][14]) || 0; // "Стоимость" if (dateStr === previousDayStr) { // Сравниваем строки const key = `${itemName}`; // Уникальный ключ только по артикулу if (!result[key]) { result[key] = { itemName, totalQuantity: 0, totalPrice: 0 }; } result[key].totalQuantity += quantity; // Суммируем количество result[key].totalPrice += price; // Суммируем стоимость } } return result; }

Дальше данные необходимо отправить в телеграм, для этого нам поможет вот эта часть кода:

Сначала он обновляет факты, затем отправляет данные за предидущий день, которые собрал предидущий скрипт и добавляет данные с листа "План [месяц] [год]"

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

Google Sheets - автоматизируем отчетность для маркетплейсов: бартер, раздачи, коммерция

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

Это позволит вам:

1. Хранить все данные в одной таблице и не теряться между таблицами сотрудников

2. Автоматически подсчитывать факт в отношении к плану, без вашего участия

3. Получать уведомления в телеграм о работе сотрудников и выполнении плана ежедневно

Ну и при наличии желания, можно все это сделать самому и даже подписок никаких платить не прийдется! Все абсолютно бесплатно.

Если у вас остались вопросы, или хотите что-то подобное для своей компании - пишите мне, договоримся: t.me/svestudio

Google Sheets - автоматизируем отчетность для маркетплейсов: бартер, раздачи, коммерция

Шаг 1 - подготовка таблиц

Шаг 2 - перенос данных с таблиц подчиненных.

Шаг 3 - создание плана для сотрудников

Шаг 4 - указываем куда передавать данные, настраиваем бота.

Создание и настройка бота

Шаг 5 - чтение данных, их группировка и отправка.