Автоматизация отчётности с маркетплейсов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Самое оптимальное решение - сделать с помощью 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" и установите триггер по времени.

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

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

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

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

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

// Функция обновления фактов в планах
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(`Факты обновлены для всех листов.`);
}

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

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

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

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

Для начала нам нужно создать Телеграм бота и получить 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 темы.

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

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

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

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

// Функция для отправки сообщений в 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;
}

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

function sendReportsToTopics() {
  const spreadsheet = SpreadsheetApp.openById(MANAGER_SHEET_ID);

  // Сначала обновляем планы
  updatePlanFacts();

  const today = new Date();
  const previousDay = new Date(today.setDate(today.getDate() - 1));
  const previousDayStr = Utilities.formatDate(previousDay, Session.getScriptTimeZone(), 'dd.MM.yyyy');

  // Русские названия месяцев
  const months = [
    'январь', 'февраль', 'март', 'апрель', 'май', 'июнь',
    'июль', 'август', 'сентябрь', 'октябрь', 'ноябрь', 'декабрь'
  ];

  // Формируем название текущего месяца на русском
  const currentMonth = months[today.getMonth()];
  const currentYear = today.getFullYear();
  const planSheetName = `План ${currentMonth} ${currentYear}`; // Пример: "План ноябрь 2024"

  const sheetsToProcess = {
    'Бартер': 15,
    'Коммерция': 13,
    'Раздачи': 16
  };

  for (let sheetName in sheetsToProcess) {
    const threadId = sheetsToProcess[sheetName];
    const sheet = spreadsheet.getSheetByName(sheetName);

    if (!sheet) {
      console.log(`Лист ${sheetName} не найден.`);
      continue;
    }

    const data = getPreviousDayData(sheet);

    if (Object.keys(data).length === 0) {
      const noDataMessage = `*Нет данных за предыдущий день для вкладки ${sheetName}.*`;
      sendToTelegram(noDataMessage, threadId);
      console.log(noDataMessage);
      continue;
    }

    // Формируем сообщение с отчетом за предыдущий день
    let report = `*Отчет по ${sheetName} за ${previousDayStr}:*\n\n`;

    for (let key in data) {
      const { itemName, totalQuantity, totalPrice } = data[key];
      report += `*Артикул:* ${itemName}\n*Кол-во:* ${totalQuantity}\n*Общая стоимость:* ${totalPrice.toFixed(2)}\n\n`;
    }

    // Добавляем данные из "План [месяц год]"
    const planSheet = spreadsheet.getSheetByName(planSheetName);

    if (!planSheet) {
      console.log(`Лист "${planSheetName}" не найден.`);
      sendToTelegram(report, threadId);
      continue;
    }

    const planData = planSheet.getDataRange().getValues();
    let planReport = `*Общее за текущий месяц:*\n\n`;

    for (let i = 1; i < planData.length; i++) { // Пропускаем заголовок
      const itemName = planData[i][1]; // Наименование товара (столбец B)
      const integrationType = planData[i][2]; // Тип интеграции (столбец C)
      const plan = Number(planData[i][3]) || 0; // План (столбец D)
      const fact = Number(planData[i][4]) || 0; // Факт (столбец E)
      const percent = Number(planData[i][5]) || 0; // % выполнения плана (столбец F)

      if (integrationType === sheetName) { // Сравниваем с текущим типом интеграции
        planReport += `*Артикул:* ${itemName}\n*План:* ${plan}\n*Факт:* ${fact}\n*% выполнения плана:* ${percent.toFixed(2)}%\n\n`;
      }
    }

    // Отправляем сообщение
    sendToTelegram(report + planReport, threadId);
  }
}

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

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

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

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

1. Хранить все данные в одной таблице и не теряться между таблицами сотрудников
2. Автоматически подсчитывать факт в отношении к плану, без вашего участия
3. Получать уведомления в Телеграм о работе сотрудников и выполнении плана ежедневно

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

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