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

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

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

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

// Массивы 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
};

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

Шаг 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 - указываем куда передавать данные, настраиваем бота

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

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

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

Шаг 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);
  }
}