Оптимизация рекламы в Яндекс Директ с помощью умных Google таблиц

Google Apps Script — это облачная платформа это облачная платформа на базе JavaScript, которая позволяет автоматизировать задачи и расширять функциональность продуктов Google

function updateGoals() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheet = ss.getSheetByName("Главная"); if (!sheet) throw new Error("Не найден лист 'Главная'"); const data = sheet.getDataRange().getValues(); const header = data[0]; const projectCol = header.indexOf("📂 Проект"); const adveronixCol = header.indexOf("📊 Название цели Adveronix"); const cabinetCol = header.indexOf("🎯 Название цели кабинет"); if (projectCol === -1 || adveronixCol === -1 || cabinetCol === -1) throw new Error("Не найдены нужные заголовки столбцов"); let currentProject = null; const projectMap = {}; // project → { oldGoal: newGoal, ... } // собираем все цели по проектам for (let i = 1; i < data.length; i++) { if (data[i][projectCol]) currentProject = data[i][projectCol]; const oldGoal = data[i][adveronixCol]; const newGoal = data[i][cabinetCol]; if (!currentProject || !oldGoal || !newGoal) continue; if (!projectMap[currentProject]) projectMap[currentProject] = {}; projectMap[currentProject][oldGoal] = newGoal; } // заменяем только в первой строке каждой вкладки for (const projectName in projectMap) { const projectSheet = ss.getSheetByName(projectName); if (!projectSheet) { Logger.log("❌ Вкладка не найдена: " + projectName); continue; } const firstRowRange = projectSheet.getRange(1, 1, 1, projectSheet.getLastColumn()); const values = firstRowRange.getValues()[0]; let changes = 0; for (let c = 0; c < values.length; c++) { const cell = values[c]; if (cell && projectMap[projectName][cell]) { values[c] = projectMap[projectName][cell]; changes++; } } if (changes > 0) { firstRowRange.setValues([values]); Logger.log(`✅ Внесено ${changes} замен в листе: ${projectName}`); } else { Logger.log(`ℹ️ Изменений нет в листе: ${projectName}`); } } }

function addConversionMetricsFastFinal() { const CONFIG = { MAIN_SHEET_NAME: "Главная", ADVERONIX_COL_NAME: "📊 Название цели Adveronix", CABINET_COL_NAME: "🎯 Название цели кабинет", METRICS: { TOTAL_CONVERSIONS: "Total conversions", CPA: "CPA" }, COST_COL_NAMES: ["cost", "стоимость", "затраты"], SKIP_SHEETS: ["__CACHE__", "📊 Сводные отчеты"] }; const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); const mainSheet = spreadsheet.getSheetByName(CONFIG.MAIN_SHEET_NAME); if (!mainSheet) { console.log("❌ Главный лист не найден"); return; } // ------------------- // 1. Получаем цели проектов // ------------------- const mainData = mainSheet.getDataRange().getValues(); const headers = mainData[0]; const projectCol = headers.indexOf("📂 Проект"); const adveronixCol = headers.indexOf(CONFIG.ADVERONIX_COL_NAME); const cabinetCol = headers.indexOf(CONFIG.CABINET_COL_NAME); const projectGoals = {}; let currentProject = ""; for (let i = 1; i < mainData.length; i++) { const row = mainData[i]; if (row[projectCol]) currentProject = row[projectCol].toString().trim(); if (!currentProject) continue; if (!projectGoals[currentProject]) projectGoals[currentProject] = new Set(); if (adveronixCol !== -1 && row[adveronixCol]) { row[adveronixCol].toString().split(',').forEach(g => { const goal = g.trim(); if (goal) projectGoals[currentProject].add(goal); }); } if (cabinetCol !== -1 && row[cabinetCol]) { row[cabinetCol].toString().split(',').forEach(g => { const goal = g.trim(); if (goal) projectGoals[currentProject].add(goal); }); } } Object.keys(projectGoals).forEach(p => { projectGoals[p] = Array.from(projectGoals[p]); }); // ------------------- // 2. Обрабатываем листы // ------------------- spreadsheet.getSheets().forEach(sheet => { const sheetName = sheet.getName(); if (CONFIG.SKIP_SHEETS.includes(sheetName) || sheetName === CONFIG.MAIN_SHEET_NAME) return; const goals = projectGoals[sheetName]; if (!goals || goals.length === 0) { console.log(`⏭️ Пропускаем "${sheetName}" - цели не найдены`); return; } console.log(`🔍 Обрабатываем лист: "${sheetName}"`); const lastRow = sheet.getLastRow(); const lastCol = sheet.getLastColumn(); if (lastRow < 2) { console.log(` ⏭️ Пропускаем "${sheetName}" - нет данных`); return; } // Получаем все данные const data = sheet.getRange(1, 1, lastRow, lastCol).getValues(); let headersRow = data[0].map(h => h ? h.toString().trim() : ''); // ------------------- // 3. Удаляем старые колонки метрик (если есть) // ------------------- let totalConvCol = headersRow.indexOf(CONFIG.METRICS.TOTAL_CONVERSIONS); let cpaCol = headersRow.indexOf(CONFIG.METRICS.CPA); if (cpaCol !== -1) { sheet.deleteColumn(cpaCol + 1); if (totalConvCol > cpaCol) totalConvCol--; // сдвиг после удаления } if (totalConvCol !== -1) sheet.deleteColumn(totalConvCol + 1); // Перечитываем заголовки после удаления headersRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(h => h ? h.toString().trim() : ''); // ------------------- // 4. Находим последний «настоящий» столбец заголовка // ------------------- let lastFilledCol = headersRow.length - 1; while ( lastFilledCol >= 0 && (headersRow[lastFilledCol] === '' || headersRow[lastFilledCol] === CONFIG.METRICS.TOTAL_CONVERSIONS || headersRow[lastFilledCol] === CONFIG.METRICS.CPA) ) { lastFilledCol--; } // ------------------- // 5. Находим индексы колонок с целями // ------------------- const goalIndices = goals.map(g => headersRow.indexOf(g)).filter(i => i !== -1); if (goalIndices.length === 0) { console.log(` ❌ Цели не найдены на листе`); return; } // ------------------- // 6. Находим колонку затрат // ------------------- const costCol = headersRow.findIndex(h => CONFIG.COST_COL_NAMES.includes(h.toLowerCase())); if (costCol === -1) { console.log(" ❌ Столбец затрат не найден"); return; } // ------------------- // 7. Считаем Total conversions и CPA для всех строк **одним массивом** // ------------------- const dataRows = data.slice(1); // все строки кроме заголовка const totalConversionsArr = []; const cpaArr = []; dataRows.forEach(row => { const cost = parseFloat(row[costCol]) || 0; let totalConversions = 0; goalIndices.forEach(idx => { totalConversions += parseFloat(row[idx]) || 0; }); totalConversionsArr.push([totalConversions]); cpaArr.push([totalConversions > 0 ? cost / totalConversions : 0]); }); // ------------------- // 8. Добавляем новые колонки метрик рядом с последним заполненным столбцом // ------------------- sheet.insertColumnAfter(lastFilledCol + 1); sheet.getRange(1, lastFilledCol + 2).setValue(CONFIG.METRICS.TOTAL_CONVERSIONS); sheet.insertColumnAfter(lastFilledCol + 2); sheet.getRange(1, lastFilledCol + 3).setValue(CONFIG.METRICS.CPA); // ------------------- // 9. Записываем значения **одним блоком** // ------------------- if (totalConversionsArr.length > 0) { sheet.getRange(2, lastFilledCol + 2, totalConversionsArr.length, 1).setValues(totalConversionsArr); sheet.getRange(2, lastFilledCol + 3, cpaArr.length, 1).setValues(cpaArr); } console.log(` ✅ Лист "${sheetName}" обработан`); }); console.log("✅ Скрипт успешно завершен!"); }

/** * @fileoverview Скрипт для автоматического сведения отчетов в Google Sheets. */ // === 1. Глобальные настройки и константы === const DEBUG_MODE = false; // 🔹 переключатель для логов и верификации const MASTERTABLE_SHEET_NAME = 'Главная'; const RULES_SHEET_NAME = 'Сводные отчеты'; const MASTERTABLE_COLS = { PROJECT_NAME: '📂 Проект', ADVERONIX_TARGET: '📊 Название цели Adveronix', CABINET_TARGET: '🎯 Название цели кабинет', KPI: '📈 KPI', LAST_UPDATE: '🗓️ Дата последнего обновления', ERRORS: '⚠️ Ошибки', REPORT_LINK: '🔗 Ссылка на отчет' }; const RULES_COLS = { REPORT_NAME: '📊 Сводные отчеты', GROUP_BY_FIELD: '🎯 Объект сведения', REQUIRED_COLUMNS: '❗️ Обязательные столбцы', DATE_FILTER: '📆 Дата' }; const DATE_FILTERS = { FULL_PERIOD: 'Нет', CURRENT_MONTH: 'Текущий месяц' }; const AGGREGATION_TYPES = { SUM: ['Cost', 'Clicks', 'Total conversions'], AVG: ['Bounce Rate'], CALCULATED: ['CPC', 'CPA'] }; const FORBIDDEN_COLUMNS = ['Day', 'Campaign Name', 'Campaign ID', 'Ad Group Id', 'Ad Id', 'Criterion', 'Criterion Type', 'Placement', 'Device', 'Mobile Platform', 'Gender', 'Age']; // === 2. Модуль логирования === function logInfo(message) { if (DEBUG_MODE) console.log(`[INFO] ${message}`); } function logError(message, error) { if (DEBUG_MODE) console.error(`[ERROR] ${message}. Stacktrace: ${error ? error.stack : 'N/A'}`); } // === 3. Модуль чтения данных === function readSheetData(sheet) { if (!sheet) throw new Error('Лист не найден.'); try { const data = sheet.getDataRange().getValues(); if (!data || !Array.isArray(data) || data.length === 0) throw new Error('Данные на листе пусты или невалидны.'); logInfo(`Прочитано ${data.length} строк данных с листа "${sheet.getName()}".`); return data; } catch (e) { throw new Error(`Ошибка чтения данных с листа "${sheet.getName()}": ${e.message}`); } } function readRules(rulesSheet) { const data = readSheetData(rulesSheet); const headers = data.shift(); const rules = []; data.forEach(row => { const rule = {}; headers.forEach((header, i) => { const value = row[i]; if (header === RULES_COLS.REQUIRED_COLUMNS) { rule[header] = value ? value.split(',').map(s => s.trim()) : []; } else { rule[header] = value; } }); if (rule[RULES_COLS.REPORT_NAME]) rules.push(rule); }); return rules; } // === 4. Модуль агрегации данных === function parseNumber(value) { if (value === null || value === undefined || value === '') return 0; if (typeof value === 'number') return value; return parseFloat(value.toString().replace(/\s/g, '').replace(',', '.')) || 0; } function formatNumber(value) { return value.toFixed(2).replace('.', ','); } function aggregateData(data, rule, targetMap) { if (!data || !Array.isArray(data) || data.length < 2) { logError('Входные данные невалидны или содержат менее 2 строк.', null); return []; } const headers = data[0]; logInfo(`Заголовки листа: ${headers.join(', ')}`); const dataRows = data.slice(1); const groupByCol = headers.indexOf(rule[RULES_COLS.GROUP_BY_FIELD]); const dateCol = headers.indexOf('Day'); if (groupByCol === -1) throw new Error(`Столбец группировки "${rule[RULES_COLS.GROUP_BY_FIELD]}" не найден.`); const requiredCols = rule[RULES_COLS.REQUIRED_COLUMNS].filter(col => col !== rule[RULES_COLS.GROUP_BY_FIELD]); logInfo(`Обязательные столбцы для агрегации: ${requiredCols.join(', ')}`); const goalIndices = {}; let hasValidGoal = false; Object.entries(targetMap).forEach(([adveronixName, cabinetName]) => { let colIndex = headers.indexOf(adveronixName); if (colIndex === -1) colIndex = headers.indexOf(cabinetName); if (colIndex !== -1 && !goalIndices[cabinetName]) { goalIndices[cabinetName] = colIndex; hasValidGoal = true; logInfo(`Цель "${cabinetName}" найдена в столбце ${colIndex}`); } }); if (!hasValidGoal && requiredCols.includes('"ЦЕЛЬ"')) { logError('Ни одна цель из targetMap не найдена в заголовках.', null); return []; } const stringColumns = ['Day','Campaign Name','Campaign ID','Ad Group Name','Ad Group Id','Ad Id','Criterion','Criterion Type','Placement','Device','Mobile Platform','Gender','Age']; const colIndices = {}; requiredCols.forEach(col => colIndices[col] = headers.indexOf(col)); let filteredRows = dataRows.filter(row => row[groupByCol] && row[groupByCol].toString().trim() !== ''); if (rule[RULES_COLS.DATE_FILTER] === DATE_FILTERS.CURRENT_MONTH && dateCol !== -1) { const currentMonth = new Date().getMonth(); const currentYear = new Date().getFullYear(); filteredRows = filteredRows.filter(row => { const rowDate = new Date(row[dateCol]); return rowDate.getMonth() === currentMonth && rowDate.getFullYear() === currentYear; }); } const aggregatedData = {}; filteredRows.forEach(row => { const campaignCol = headers.indexOf('Campaign Name'); const key = (campaignCol !== -1 ? row[campaignCol] + ' | ' : '') + row[groupByCol]; if (!aggregatedData[key]) { aggregatedData[key] = {}; // строки stringColumns.forEach(col => { if (requiredCols.includes(col) || col === rule[RULES_COLS.GROUP_BY_FIELD]) { const idx = headers.indexOf(col); aggregatedData[key][col] = idx !== -1 ? row[idx] : 0; } }); // цели if (requiredCols.includes('"ЦЕЛЬ"')) { Object.keys(goalIndices).forEach(cabinetName => aggregatedData[key][cabinetName] = 0); } // числовые метрики requiredCols.forEach(col => { if (!aggregatedData[key][col] && col !== '"ЦЕЛЬ"') aggregatedData[key][col] = 0; }); // служебные поля для Bounce Rate aggregatedData[key].__brSum = 0; aggregatedData[key].__brClicksSum = 0; } // наполнение requiredCols.forEach(col => { if (col === '"ЦЕЛЬ"') { Object.entries(goalIndices).forEach(([cabinetName, idx]) => { aggregatedData[key][cabinetName] += parseNumber(row[idx]); }); } else if (!stringColumns.includes(col)) { const idx = colIndices[col]; if (idx !== -1) { if (col === 'Bounce Rate') { const br = parseNumber(row[idx]); // % из отчета const clicksIdx = colIndices['Clicks']; const clicks = clicksIdx !== -1 ? parseNumber(row[clicksIdx]) : 0; aggregatedData[key].__brSum += br * clicks; aggregatedData[key].__brClicksSum += clicks; } else { aggregatedData[key][col] += parseNumber(row[idx]); } } } }); }); // вычисляем CPC, CPA, Total conversions и Bounce Rate Object.values(aggregatedData).forEach(item => { const cost = item['Cost'] || 0; const clicks = item['Clicks'] || 0; if ('CPC' in item) item['CPC'] = clicks > 0 ? cost / clicks : 0; let totalConversions = 0; Object.keys(goalIndices).forEach(cabinetName => totalConversions += item[cabinetName] || 0); item['Total conversions'] = totalConversions; if ('CPA' in item) item['CPA'] = totalConversions > 0 ? cost / totalConversions : 0; // считаем Bounce Rate как средневзвешенный по кликам if (item.__brClicksSum > 0) { item['Bounce Rate'] = Math.round(item.__brSum / item.__brClicksSum); } else { item['Bounce Rate'] = 0; } delete item.__brSum; delete item.__brClicksSum; }); return formatAggregatedData(aggregatedData, rule, targetMap); } function formatAggregatedData(aggregatedData, rule, targetMap) { const requiredCols = rule[RULES_COLS.REQUIRED_COLUMNS]; const groupByField = rule[RULES_COLS.GROUP_BY_FIELD]; // 1️⃣ Строковые колонки const stringOrder = ['Day', 'Campaign Name', 'Campaign ID', 'Ad Group Id', 'Ad Id', 'Criterion', 'Criterion Type', 'Placement', 'Device', 'Mobile Platform', 'Gender', 'Age']; // 2️⃣ Числовые метрики const numericOrder = ['Cost', 'Clicks', 'CPC', 'Bounce Rate']; // 3️⃣ Итоговые конверсии const totalsOrder = ['Total conversions', 'CPA']; const finalHeaders = []; // Добавляем строки stringOrder.forEach(col => { if (requiredCols.includes(col) || col === groupByField) finalHeaders.push(col); }); // Добавляем числовые метрики numericOrder.forEach(col => { if (requiredCols.includes(col)) finalHeaders.push(col); }); // Добавляем цели if (requiredCols.includes('"ЦЕЛЬ"')) { Object.values(targetMap).forEach(cabinetName => finalHeaders.push(cabinetName)); } // Добавляем итоговые конверсии totalsOrder.forEach(col => { if (requiredCols.includes(col)) finalHeaders.push(col); }); // Формируем строки const resultRows = [finalHeaders]; Object.keys(aggregatedData).forEach(key => { const row = []; finalHeaders.forEach(header => { const value = aggregatedData[key][header]; if (value === undefined || value === null || value === '') row.push(0); else if (!isNaN(value) && AGGREGATION_TYPES.CALCULATED.includes(header)) row.push(formatNumber(value)); else if (!isNaN(value)) row.push(Number(value)); else row.push(value); }); resultRows.push(row); }); return resultRows; } // === 6. Верификация данных === function verifyAggregation(rawData, aggregatedData, rule, targetMap) { logInfo('--- Начало проверки правильности сведения данных ---'); if (!rawData || !Array.isArray(rawData) || !aggregatedData || !Array.isArray(aggregatedData)) { logError('Исходные или агрегированные данные невалидны.', null); return; } const headers = rawData[0]; const dataRows = rawData.slice(1); const groupByCol = headers.indexOf(rule[RULES_COLS.GROUP_BY_FIELD]); const requiredCols = rule[RULES_COLS.REQUIRED_COLUMNS].filter(col => col !== rule[RULES_COLS.GROUP_BY_FIELD]); const stringColumns = ['Day', 'Campaign Name', 'Campaign ID', 'Ad Group Id', 'Ad Id', 'Criterion', 'Criterion Type', 'Placement', 'Device', 'Mobile Platform', 'Gender', 'Age']; FORBIDDEN_COLUMNS.forEach(col => { if (requiredCols.includes(col) && !stringColumns.includes(col)) { logError(`Столбец "${col}" включен в агрегацию, хотя не должен суммироваться.`, null); } }); // Кэш целей const goalIndices = {}; const usedColumns = new Set(); Object.entries(targetMap).forEach(([adveronixName, cabinetName]) => { let colIndex = headers.indexOf(adveronixName); if (colIndex !== -1 && !usedColumns.has(colIndex)) { goalIndices[cabinetName] = colIndex; usedColumns.add(colIndex); } else { colIndex = headers.indexOf(cabinetName); if (colIndex !== -1 && !usedColumns.has(colIndex)) { goalIndices[cabinetName] = colIndex; usedColumns.add(colIndex); } } }); } // === 7. Запись и обновление данных === function writeData(sheet, data) { if (!sheet || !data || data.length === 0) return; sheet.clearContents(); sheet.getRange(1, 1, data.length, data[0].length).setValues(data); } function updateMasterTable(masterSheet, rowIndex, status, errorMessage) { const headers = masterSheet.getRange('1:1').getValues()[0]; const lastUpdateColIndex = headers.indexOf(MASTERTABLE_COLS.LAST_UPDATE) + 1; const errorsColIndex = headers.indexOf(MASTERTABLE_COLS.ERRORS) + 1; if (status === 'success') { masterSheet.getRange(rowIndex, lastUpdateColIndex).setValue(new Date()); masterSheet.getRange(rowIndex, errorsColIndex).setValue(''); } else { masterSheet.getRange(rowIndex, errorsColIndex).setValue(errorMessage); masterSheet.getRange(rowIndex, lastUpdateColIndex).setValue(new Date()); } } // === 8. Главная функция === function runReportAggregation() { logInfo('--- Начинаем процесс сведения отчетов ---'); const mainSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); const masterSheet = mainSpreadsheet.getSheetByName(MASTERTABLE_SHEET_NAME); const rulesSheet = mainSpreadsheet.getSheetByName(RULES_SHEET_NAME); if (!masterSheet || !rulesSheet) return; const masterData = masterSheet.getDataRange().getValues(); const masterHeaders = masterData.shift(); const rules = readRules(rulesSheet); const reportLinkIndex = masterHeaders.indexOf(MASTERTABLE_COLS.REPORT_LINK); const projectNameIndex = masterHeaders.indexOf(MASTERTABLE_COLS.PROJECT_NAME); const adveronixTargetIndex = masterHeaders.indexOf(MASTERTABLE_COLS.ADVERONIX_TARGET); const cabinetTargetIndex = masterHeaders.indexOf(MASTERTABLE_COLS.CABINET_TARGET); const projects = {}; let currentProject = null; masterData.forEach((row, i) => { const projectName = row[projectNameIndex]; if (projectName) { currentProject = projectName; projects[currentProject] = { mainRow: row, rows: [row], rowIndex: i + 2 }; } else if (currentProject) { projects[currentProject].rows.push(row); } }); const spreadsheetCache = {}; Object.keys(projects).forEach(projectName => { const project = projects[projectName]; const masterTableRowIndex = project.rowIndex; const reportUrl = project.rows[0][reportLinkIndex]; const targetMap = {}; project.rows.forEach(row => { const adveronixName = row[adveronixTargetIndex]; const cabinetName = row[cabinetTargetIndex]; if (adveronixName && cabinetName) targetMap[adveronixName] = cabinetName; }); try { if (!reportUrl) throw new Error('Отсутствует ссылка на отчет.'); const sourceSheet = mainSpreadsheet.getSheetByName(projectName); if (!sourceSheet) throw new Error(`Не найдена исходная вкладка "${projectName}".`); const rawData = readSheetData(sourceSheet); rules.forEach(rule => { const targetSheetName = rule[RULES_COLS.REPORT_NAME]; if (!spreadsheetCache[reportUrl]) spreadsheetCache[reportUrl] = SpreadsheetApp.openByUrl(reportUrl); const targetSpreadsheet = spreadsheetCache[reportUrl]; const targetSheet = targetSpreadsheet.getSheetByName(targetSheetName); if (!targetSheet) throw new Error(`Не найдена целевая вкладка "${targetSheetName}".`); const aggregatedData = aggregateData(rawData, rule, targetMap); if (aggregatedData.length) writeData(targetSheet, aggregatedData); }); updateMasterTable(masterSheet, masterTableRowIndex, 'success', ''); } catch (e) { updateMasterTable(masterSheet, masterTableRowIndex, 'error', e.message); } }); logInfo('--- Процесс завершен ---'); }

function getProjectsAndReports(enableLogs = true) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const mainSheet = ss.getSheetByName("Главная"); if (!mainSheet) return []; const data = mainSheet.getDataRange().getValues(); const projects = []; for (let i = 1; i < data.length; i++) { // пропускаем заголовок const projectName = data[i][0]; // 📂 Проект const reportUrl = data[i][6]; // 🔗 Ссылка на отчет if (reportUrl && reportUrl.toString().trim() !== "") { projects.push({ projectName, reportUrl }); if (enableLogs) Logger.log(`[INFO] Найден проект: ${projectName}`); } } if (enableLogs) Logger.log(`[INFO] Всего проектов с отчетами: ${projects.length}`); return projects; } function getAutoRules(enableLogs = true) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const rulesSheet = ss.getSheetByName("Автоправила"); if (!rulesSheet || rulesSheet.getLastRow() < 2) return []; const data = rulesSheet.getDataRange().getValues(); const headers = data[0]; const idx = { name: headers.indexOf("Правило"), logic: headers.indexOf("Логика"), color: headers.indexOf("Цвет фона"), targetSheets: headers.indexOf("Целевые листы"), displayObjects: headers.indexOf("Объекты для вывода"), project: headers.indexOf("Проект") // новый столбец }; const rules = []; for (let i = 1; i < data.length; i++) { const row = data[i]; if (!row[idx.name]) continue; const logic = (row[idx.logic] || "AND").toString().toUpperCase(); const color = row[idx.color] ? row[idx.color].toString().trim() : ""; const displayObjects = (row[idx.displayObjects] || "").toString().split(",").map(s => s.trim()).filter(s => s); const targetSheets = (row[idx.targetSheets] || "").toString().split(",").map(s => s.trim()).filter(s => s); const project = row[idx.project] ? row[idx.project].toString().trim() : ""; // читаем проект const conditions = []; for (let n = 1; n <= 3; n++) { const colIdx = headers.indexOf(`Столбец ${n}`); const opIdx = headers.indexOf(`Оператор ${n}`); const valIdx = headers.indexOf(`Значение ${n}`); if (row[colIdx] && row[opIdx] && row[valIdx] !== undefined && row[valIdx] !== "") { conditions.push({ column: row[colIdx].toString().trim(), operator: row[opIdx].toString().trim(), value: row[valIdx] }); } } if (conditions.length > 0) { rules.push({ name: row[idx.name].toString().trim(), conditions, logic, color, targetSheets, displayObjects, project }); if (enableLogs) Logger.log(`[INFO] Правило загружено: ${row[idx.name]} (Проект: ${project || "Все"})`); } } if (enableLogs) Logger.log(`[INFO] Всего правил загружено: ${rules.length}`); return rules; } function compareValues(value1, operator, value2) { // Для корректного сравнения числовых значений преобразуем их const num1 = Number(value1); const num2 = Number(value2); const isNumberComparison = !isNaN(num1) && !isNaN(num2) && !isNaN(value1) && !isNaN(value2); switch (operator.trim()) { case '==': // Специальная обработка для дат, чтобы сравнивать их как числа (timestamp) if (value1 instanceof Date && value2 instanceof Date) { return value1.getTime() == value2.getTime(); } // Если оба значения - числа, сравниваем их как числа if (isNumberComparison) { return num1 == num2; } return value1 == value2; case '!=': if (isNumberComparison) { return num1 != num2; } return value1 != value2; case '>': if (isNumberComparison) { return num1 > num2; } return value1 > value2; case '<': if (isNumberComparison) { return num1 < num2; } return value1 < value2; case '>=': if (isNumberComparison) { return num1 >= num2; } return value1 >= value2; case '<=': if (isNumberComparison) { return num1 <= num2; } return value1 <= value2; default: Logger.log(`[ERROR] Неизвестный оператор: ${operator}`); return false; } } /** * Writes the grouped optimization data to the specified sheet. * @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The target sheet. * @param {object} data The grouped data object. */ function writeGroupedOptimizationData(sheet, groupedData) { sheet.clear(); let currentRow = 1; for (const sheetName in groupedData) { for (const ruleName in groupedData[sheetName]) { const group = groupedData[sheetName][ruleName]; const headers = Object.keys(group[0]); const displayHeaders = ["Проект", "Правило"].concat(headers); // 🔹 Заголовок блока sheet.getRange(currentRow, 1, 1, displayHeaders.length) .setValues([displayHeaders]) .setFontWeight("bold") .setBackground("#d9dff0"); currentRow++; const startDataRow = currentRow; // 🔹 Данные с визуальными подсказками group.forEach(dataRow => { const values = [sheetName, ruleName]; headers.forEach(header => { let value = dataRow[header]; if (value === null || value === "") value = 0; // Добавляем emoji для метрик const h = header.toString().toLowerCase(); if (h.includes("cpa") && value > 8000) value += " 🔥"; if (h.includes("ctr") && value < 4) value += " 📉"; if ((h.includes("bounce") || h.includes("отказы")) && value > 30) value += " ⚠️"; values.push(value); }); sheet.getRange(currentRow, 1, 1, values.length).setValues([values]); currentRow++; }); const endDataRow = currentRow - 1; // 🔹 Сетка: рамка вокруг блока sheet.getRange(startDataRow - 1, 1, endDataRow - startDataRow + 2, displayHeaders.length) .setBorder(true, true, true, true, true, true, "#cccccc", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // 🔹 Пустая строка после блока currentRow++; } } // 🔹 Авторазмер колонок и фиксированные первые 2 колонки const lastColumn = sheet.getLastColumn(); if (lastColumn > 0) { sheet.autoResizeColumns(1, lastColumn); sheet.setFrozenColumns(2); // Проект + Правило всегда видны } } function runAutoRules(enableLogs = true) { const ss = SpreadsheetApp.getActiveSpreadsheet(); const optimizationSheetName = "Оптимизация"; const optimizationSheet = ss.getSheetByName(optimizationSheetName) || ss.insertSheet(optimizationSheetName); optimizationSheet.clear(); if (enableLogs) Logger.log("[START] Скрипт запущен"); const projects = getProjectsAndReports(enableLogs); const rules = getAutoRules(enableLogs); if (projects.length === 0 || rules.length === 0) { optimizationSheet.getRange("A1").setValue("Нет проектов или правил для обработки"); if (enableLogs) Logger.log("[STOP] Нет проектов или правил"); return; } let optimizationData = {}; projects.forEach(project => { if (enableLogs) Logger.log(`\n[PROJECT] Начинаем обработку проекта: '${project.projectName}'`); let reportSpreadsheet; try { reportSpreadsheet = SpreadsheetApp.openByUrl(project.reportUrl); if (enableLogs) Logger.log(` [INFO] Открыт отчет по ссылке: ${project.reportUrl}`); } catch (e) { if (enableLogs) Logger.log(` [ERROR] Не удалось открыть отчет по ссылке: ${project.reportUrl}`); return; } reportSpreadsheet.getSheets().forEach(reportSheet => { const sheetName = reportSheet.getName(); if (enableLogs) Logger.log(` [INFO] Обрабатываем лист: '${sheetName}'`); const lastRow = reportSheet.getLastRow(); const lastCol = reportSheet.getLastColumn(); if (lastRow < 2) { if (enableLogs) Logger.log(` - Лист '${sheetName}' пустой или содержит только заголовок`); return; } const sheetHeaders = reportSheet.getRange(1, 1, 1, lastCol).getValues()[0]; const sheetData = reportSheet.getRange(2, 1, lastRow - 1, lastCol).getValues(); const activeRules = rules.filter(rule => (rule.targetSheets.length === 0 || rule.targetSheets.includes(sheetName)) && (!rule.project || rule.project === project.projectName) // проверка проекта ); if (enableLogs) Logger.log(` [INFO] Найдено правил для листа '${sheetName}': ${activeRules.length}`); activeRules.forEach(rule => { if (enableLogs) Logger.log(` > Применяем правило '${rule.name}' (логика: ${rule.logic})`); const displayColumns = new Set([...rule.displayObjects, ...rule.conditions.map(c => c.column)]); sheetData.forEach((row, i) => { const conditionResults = rule.conditions.map(cond => { const colIndex = sheetHeaders.findIndex(h => h.toString().trim().toLowerCase() === cond.column.toLowerCase()); if (colIndex === -1) { if (enableLogs) Logger.log(` - Столбец '${cond.column}' не найден в листе '${sheetName}'`); return false; } const result = compareValues(row[colIndex], cond.operator, cond.value); if (enableLogs) Logger.log(` - Проверка строки ${i + 2}, столбец '${cond.column}': ${row[colIndex]} ${cond.operator} ${cond.value} => ${result}`); return result; }); const isMatch = rule.logic === "OR" ? conditionResults.some(Boolean) : conditionResults.every(Boolean); if (isMatch) { const matchedValues = {}; displayColumns.forEach(header => { const colIndex = sheetHeaders.findIndex(h => h.toString().trim().toLowerCase() === header.toLowerCase()); matchedValues[header] = colIndex !== -1 ? row[colIndex] : ""; }); if (!optimizationData[project.projectName]) optimizationData[project.projectName] = {}; if (!optimizationData[project.projectName][rule.name]) optimizationData[project.projectName][rule.name] = []; optimizationData[project.projectName][rule.name].push(matchedValues); if (enableLogs) Logger.log(` - Совпадение найдено! Строка ${i + 2}, данные: ${JSON.stringify(matchedValues)}`); } }); }); }); }); // ✅ Исправленный вызов функции writeGroupedOptimizationData(optimizationSheet, optimizationData); if (enableLogs) Logger.log("[END] Скрипт завершен"); }

// Данные для Telegram const TELEGRAM_BOT_TOKEN = 'ваш токен'; const CHAT_ID = 'Ваш чатID'; // Название листа, из которого берутся данные const SHEET_NAME = 'Оптимизация'; function sendUpdatesToTelegram() { Logger.log('--- Скрипт запущен ---'); const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); const sheet = spreadsheet.getSheetByName(SHEET_NAME); if (!sheet) { Logger.log(`🚫 Лист с названием "${SHEET_NAME}" не найден.`); return; } const allValues = sheet.getDataRange().getValues(); const HEADER_KEYWORDS = [ 'правило', 'лист', 'campaign', 'ad group', 'placement', 'проект', 'cost', 'conversions', 'clicks', 'bounce rate' ]; const blocks = parseBlocks(allValues, HEADER_KEYWORDS); if (blocks.length === 0) { Logger.log('⚠️ Не найдено ни одного блока данных.'); return; } Logger.log(`✅ Найдено ${blocks.length} блоков.`); const properties = PropertiesService.getScriptProperties(); const previousDataJson = properties.getProperty('previousOptimizationData'); const previousData = previousDataJson ? JSON.parse(previousDataJson) : []; // 🔹 Оптимизация: используем Set для быстрого поиска const previousSet = new Set(previousData.map(row => row.join('|'))); const allNewRows = []; // Группировка сообщений по проекту и правилу const groupedMessages = {}; blocks.forEach(block => { const { headers, rows } = block; const newRows = rows.filter(row => !previousSet.has(row.join('|'))); newRows.forEach(row => { const project = extractProject(row, headers); const rule = extractRule(row, headers); const message = formatMessage(row, headers); if (!groupedMessages[project]) groupedMessages[project] = {}; if (!groupedMessages[project][rule]) groupedMessages[project][rule] = []; groupedMessages[project][rule].push(message); allNewRows.push(row); }); }); // Отправка сообщений группами по проекту и правилу for (const [project, rules] of Object.entries(groupedMessages)) { for (const [rule, messages] of Object.entries(rules)) { let combined = `📊 **Отчет по оптимизации**\n\n👾 **Проект**: ${project}\n💡 **Правило**: ${rule}\n\n`; messages.forEach(msg => { const body = msg.split('\n').slice(3).join('\n'); // убираем дублирующий заголовок combined += body + '\n\n'; }); // Делим сообщение по лимиту Telegram (4096 символов) const chunks = splitMessage(combined, 4000); chunks.forEach(chunk => sendMessage(chunk)); } } // Сохраняем историю if (allNewRows.length > 0) { const updatedData = previousData.concat(allNewRows); properties.setProperty('previousOptimizationData', JSON.stringify(updatedData)); Logger.log(`💾 История обновлена (${updatedData.length} строк).`); } Logger.log('--- Скрипт завершен ---'); } /** * Вытаскивает значение "Правило" из строки. */ function extractRule(row, headers) { for (let i = 0; i < headers.length; i++) { if (headers[i].toLowerCase().includes('правило')) { return row[i] ? row[i].toString() : 'Без правила'; } } return 'Без правила'; } /** * Вытаскивает значение "Проект" из строки. */ function extractProject(row, headers) { for (let i = 0; i < headers.length; i++) { if (headers[i].toLowerCase().includes('проект')) { return row[i] ? row[i].toString() : 'Без проекта'; } } return 'Без проекта'; } /** * Разбивает таблицу на блоки (каждая мини-таблица = headers + rows). */ function parseBlocks(allValues, HEADER_KEYWORDS) { const blocks = []; let headers = null; let rows = []; for (let i = 0; i < allValues.length; i++) { const row = allValues[i]; const isHeaderCandidate = row.some(cell => typeof cell === 'string' && HEADER_KEYWORDS.some(keyword => cell.toLowerCase().includes(keyword)) ); if (isHeaderCandidate) { if (headers && rows.length > 0) { blocks.push({ headers, rows }); } headers = row.map(h => h.toString().trim()); rows = []; } else if (headers && row.some(cell => cell)) { rows.push(row); } } if (headers && rows.length > 0) { blocks.push({ headers, rows }); } return blocks; } /** * Форматирует строку данных в сообщение для Telegram. */ function formatMessage(row, headers) { const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); const tableName = spreadsheet.getName(); let message = `📊 **Отчет по оптимизации** (${tableName})\n\n`; let rule = ''; let campaignMessage = ''; let metricsMessage = 'Метрики:\n'; function escapeMarkdown(text) { if (text === null || text === undefined) return ''; return String(text).replace(/([_*`])/g, '\\$1'); } for (let i = 0; i < headers.length; i++) { const header = headers[i]; const value = row[i]; const escapedValue = escapeMarkdown(value); if (escapedValue === '') continue; const lowerHeader = header.toLowerCase(); if (lowerHeader.includes('правило')) { rule = escapedValue; } else if ( lowerHeader.includes('campaign') || lowerHeader.includes('ad group') || lowerHeader.includes('placement') ) { campaignMessage += `├─ **${escapeMarkdown(header)}**: ${escapedValue}\n`; } else { if (header.trim() !== '') { metricsMessage += `├─ **${escapeMarkdown(header)}**: ${escapedValue}\n`; } } } message += `💡 **Правило**: ${rule}\n\n`; if (campaignMessage.trim() !== '') { message += 'Кампания:\n' + campaignMessage + '\n'; } if (metricsMessage !== 'Метрики:\n') { message += metricsMessage; } return message; } /** * Делим длинные сообщения на части, не обрывая строки. */ function splitMessage(message, maxLength) { if (message.length <= maxLength) return [message]; const parts = []; let current = ''; const lines = message.split('\n'); lines.forEach(line => { if ((current + line + '\n').length > maxLength) { parts.push(current); current = line + '\n'; } else { current += line + '\n'; } }); if (current.trim() !== '') { parts.push(current); } return parts; } /** * Отправка сообщения в Telegram. */ function sendMessage(message) { const url = `https://api.telegram.org/bot${TELEGRAM_BOT_TOKEN}/sendMessage`; const options = { 'method': 'post', 'payload': { 'chat_id': CHAT_ID, 'text': message, 'parse_mode': 'Markdown' } }; try { const response = UrlFetchApp.fetch(url, options); Logger.log(response.getContentText()); } catch (e) { Logger.log('🚫 Ошибка при отправке сообщения в Telegram: ' + e.toString()); } }

// Данные для Telegram const TELEGRAM_BOT_TOKEN = ''; const CHAT_ID = '';