Skrypt Google Ads – kalkulacja budżetów miesiąc kalendarzowy – poziom MCK

Poniżej znajdziecie wynik „rozmów” z ChatGPT trwających ponad 3 miesiące. Dlaczego 3 miesiące? Prosty skrypt mający przeliczać budżet dzienny rozbudowywał się, a wraz z rozbudową testowałem, czy działa poprawnie.

Nie ufając ChatGPT – pierwsze uruchomienie pierwszego wygenerowanego skryptu… Ależ to były emocje. Co mi wyłączy na kontach? Co wybuchnie?

Wracając do skryptu.

Skrypt do wdrożenia na poziomie MCK. Możecie śledzić wydatki na x kontach Google Ads w hierarchii tego konta MCK.

Główne zadanie skryptu, to przeliczenie jaki ma być obecny budżet dzienny biorąc pod uwagę dotychczasowe wydatki w danym miesiącu.

Problemy po drodze tworzenia skryptu? Pierwszy i ostatni dzień miesiąca. Skrypt nie liczył poprawnie tych dni. Teraz jest ok, ale, gdy skrypt uruchamia się pierwszego dnia, by ustalić budżet dzienny na dany miesiąc, sugerujcie się kolumną Budżet dzienny (szacowany), ponieważ kolumna Budżet dzienny (pozostałe dni) przeliczy budżet, jakby minął już jeden pełny dzień.

Koszty w pierwszym dniu danego miesiąca, to koszty uzyskane w ten pierwszy dzień miesiąca do momentu uruchomienia skryptu – to jedyny wyjątek liczenia kosztów. Każdy następny dzień liczony jest od pierwszego dnia miesiąca, do wczoraj.

Tak wygląda przykładowy arkusz z przeliczeniami.

Poniżej znajdziecie szczegółową instrukcję.

UWAGA! Nie napisałem ani jednej linijki tego kodu. Kod powstał z użyciem ChatGPT. Skryptu używasz na własną odpowiedzialność.

// Copyright 2025. All Rights Reserved. Skrypt wygenerowany za pomocą ChatGPT.
// Kalkulacja budżetów w miesiącu kalendarzowym.
// Masz pytania? Napisz dominik@myslak.pl
//
//
// O skrypcie
// 
// Skrypt do wdrożenia na poziomie MCK (Moje Centrum Klienta).
// 
// Skrypt analizuje wydatki na wskazanych kontach Google Ads. 
// Bierze pod uwagę liczbę dni jakie minęły w danym miesiącu i zadany budżet miesięczny. 
// 
// GŁÓWNE DZIAŁANIE SKRYPTU: Skrypt wyliczy szacowany budżet dzienny danego konta Google Ads na pozostałe dni obecnego miesiąca.
// 
// Skrypt dodatkowo wysyła alert na Twojego maila, gdy wartość w kolumnie Budżet dzienny (pozostałe dni) jest mniejsza od wartości w kolumnie Budżet dzienny (szacowany) (o zadany procent w skrypcie) oraz, gdy wydatki z wczoraj były wyższe od wartości w kolumnie Budżet dzienny (pozostałe dni) (o zadany procent w skrypcie). 
// 
// Instrukcja
// Skopiuj cały skrypt i wklej w odpowiednie pole podczas dodawania skryptu na koncie Google Ads.
// Skopiuj adres z pola SPREADSHEET_URL i wklej w okno przeglądarki. Utwórz Swój arkusz.
// Na karcie Budżety wypełnij kolumnę Account ID oraz Monthly Budget. Skopiuj adres url Swojego szablonu pliku i wklej z powrotem do pola SPREADSHEET_URL pomiędzy ''.
// W polu ALERT_EMAIL uzupełnij email do alertów, a w polu THRESHOLD_PERCENT i YESTERDAY_COST_THRESHOLD_PERCENT wartości procentowe, po przekroczeniu, których skrypt wyśle maila z alertami.
//
// Wartość procentowa w polu THRESHOLD_PERCENT to różnica pomiędzy kolumnami Budżet dzienny (szacowany) a Budżet dzienny (pozostałe dni).
//
// Wartość procentowa w polu YESTERDAY_COST_THRESHOLD_PERCENT to różnica pomiędzy kolumnami kosztów z dnia wczorajszego a Budżet dzienny (pozostałe dni).
//
// W dolnej części skryptu możesz zmienić tytuł otrzymywanego maila z alertami.
//
// Zapisz skrypt, autoryzuj i uruchom. Nie zapomnij ustawić harmonogramu. Zalecam codziennie, ale to od Ciebie zależy częstotliwość jego uruchamiania.
// 
// Wyniki przeliczeń pojawią się w Twoim Dokumencie Google na karcie Analiza Budżetu.
// Skrypt będzie nadpisywał poprzednie wyniki z poprzednich uruchomień skryptu.
//
// Objaśnienie kilku kolumn. 
// % budżetu (szacowany) - tyle procentowo powinniśmy wydać dziennie do tej pory, licząc Budżet miesięczny/liczbę dni w miesiącu.
// % budżetu (faktyczny) - tyle procentowo faktycznie wydaliśmy uwzględniając dotychczasowe wydatki. Tło komórek wypełnia się odcieniami czerwonego i zielonego w zależności od tego, czy wydatki są pod, czy nad normą % budżetu (szacowany).
// Budżet dzienny (szacowany) - tyle powinniśmy wydawać dziennie, licząc Budżet miesięczny/liczbę dni w miesiącu.
// Budżet dzienny (pozostałe dni) - tyle powinniśmy wydawać dziennie po uwzględnieniu dotychczasowych wydatków. Tło komórek wypełnia się kolorem czerwonym lub zielonym w zależności od tego, czy musimy zmniejszyć lub zwiększyć budżet dzienny.
// Kolumna z kosztem z dnia wczorajszego wypełnia się odcieniami czerwonego i zielonego w zależności od tego, czy wydatki są pod, czy nad normą w zależności od Budżet dzienny (pozostałe dni).
//
// Dwie ostatnie kolumny w arkuszu, to kolumny do zweryfikowania, czy system pobrał dane z odpowiedniego zakresu i czy dobrze odczytuje liczbę dni w danym miesiącu.
//
// UWAGA! Nie napisałem ani jednej linijki tego skryptu. Skrypt powstał z użyciem ChatGPT. Skryptu używasz na własną odpowiedzialność.
//
////////////////////////////////////////////////////////////////////


const SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1nkvbHv4pb7MMErXslLtEw_SyUbXR3nu-ZehsQJ75LHI/copy'; // <-- Skopiuj ten link. Utwórz Swój arkusz i wklej adres do niego.
const INPUT_SHEET_NAME = 'Budżety';
const OUTPUT_SHEET_NAME = 'Analiza Budżetu';
const ALERT_EMAIL = 'twój@email.pl'; // <-- Tutaj wpisz adres e-mail do alertów

const THRESHOLD_PERCENT = 10; // <-- Tutaj wpisz próg procentowy różnicy kolumn Budżet dzienny (pozostałe dni) a Budżet dzienny (szacowany) (np. 10%)
const YESTERDAY_COST_THRESHOLD_PERCENT = 10; // <-- Tutaj wpisz próg procentowy różnicy kolumn kosztów z dnia wczorajszego a Budżet dzienny (pozostałe dni) (np. 10%)


////// Koniec konfiguracji w skrypcie. Poniżej w kodzie znajdziesz kilka sformułowań, które możesz zmienić na Swoje potrzeby, jak np. komunikaty w Alertach email - na własną odpowiedzialność.

function main() {
  const spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  const inputSheet = spreadsheet.getSheetByName(INPUT_SHEET_NAME);
  const outputSheet = getOrCreateOutputSheet(spreadsheet, OUTPUT_SHEET_NAME);
  outputSheet.clearContents();

  const today = new Date();
  const firstDay = new Date(today.getFullYear(), today.getMonth(), 1);
  const lastDay = new Date(today.getFullYear(), today.getMonth() + 1, 0);

  // Nowa logika dat
  const yesterday = new Date(today);
  if (today.getDate() === 1) {
    yesterday.setTime(firstDay.getTime());
  } else {
    yesterday.setDate(today.getDate() - 1);
  }

  const timeZone = AdsApp.currentAccount().getTimeZone();
  const dateStart = Utilities.formatDate(firstDay, timeZone, 'yyyyMMdd');
  const dateEnd = Utilities.formatDate(yesterday, timeZone, 'yyyyMMdd');
  const displayDateStart = Utilities.formatDate(firstDay, timeZone, 'yyyy-MM-dd');
  const displayDateEnd = Utilities.formatDate(yesterday, timeZone, 'yyyy-MM-dd');

  const totalDaysInMonth = lastDay.getDate();
  const elapsedDays = Math.floor((yesterday - firstDay) / (1000 * 60 * 60 * 24)) + 1;
  const remainingDays = totalDaysInMonth - elapsedDays;

  const headers = [
    'Data',
    'ID konta',
    'Nazwa konta',
    'Budżet miesięczny',
    'Wydano dotychczas (1 – wczoraj)',
    '% budżetu (szacowany)',
    '% budżetu (faktyczny)',
    'Budżet dzienny (szacowany)',
    'Budżet dzienny (pozostałe dni)'
  ];

  const dateColumns = [];
  for (let d = new Date(yesterday); d >= firstDay; d.setDate(d.getDate() - 1)) {
    dateColumns.push(Utilities.formatDate(new Date(d), timeZone, 'yyyy-MM-dd'));
  }

  outputSheet.appendRow(headers.concat(dateColumns, ['Zakres wydatków', 'Liczba dni w miesiącu']));

  const rows = inputSheet.getDataRange().getValues();
  const header = rows[0];
  const accountIdIndex = header.indexOf('Account ID');
  const budgetIndex = header.indexOf('Monthly Budget');

  let alertRows = [];
  let rowIndex = 2;

  for (let i = 1; i < rows.length; i++) {
    const accountId = rows[i][accountIdIndex];
    const monthlyBudget = parseFloat(rows[i][budgetIndex]);

    if (!accountId || isNaN(monthlyBudget)) continue;

    const accountIterator = AdsManagerApp.accounts().withIds([accountId]).get();
    if (!accountIterator.hasNext()) continue;

    const account = accountIterator.next();
    MccApp.select(account);

    const stats = AdsApp.currentAccount().getStatsFor(dateStart, dateEnd);
    const spent = stats.getCost();

    const estimatedDaily = monthlyBudget / totalDaysInMonth;
    const adjustedDaily = remainingDays > 0 ? (monthlyBudget - spent) / remainingDays : 0;
    const accountName = account.getName();

    const percentBudgetExpected = ((estimatedDaily * elapsedDays) / monthlyBudget) * 100;
    const percentBudgetActual = (spent / monthlyBudget) * 100;

    const dailyCosts = [];
    let costYesterday = 0;

    for (let d = new Date(yesterday); d >= firstDay; d.setDate(d.getDate() - 1)) {
      const singleDay = Utilities.formatDate(new Date(d), timeZone, 'yyyyMMdd');
      const cost = AdsApp.currentAccount().getStatsFor(singleDay, singleDay).getCost();
      if (singleDay === dateEnd) {
        costYesterday = cost;
      }
      dailyCosts.push(cost.toFixed(2));
    }

    const dataRow = [
      Utilities.formatDate(today, timeZone, 'yyyy-MM-dd'),
      accountId,
      accountName,
      monthlyBudget,
      spent.toFixed(2),
      percentBudgetExpected.toFixed(2) + '%',
      percentBudgetActual.toFixed(2) + '%',
      estimatedDaily.toFixed(2),
      adjustedDaily.toFixed(2)
    ].concat(dailyCosts, [`${displayDateStart} – ${displayDateEnd}`, totalDaysInMonth]);

    outputSheet.appendRow(dataRow);

    const adjustedDailyCell = outputSheet.getRange(rowIndex, 9);
    const estDailyCell = outputSheet.getRange(rowIndex, 8);
    const adjustedDailyVal = parseFloat(adjustedDailyCell.getValue());
    const estDailyVal = parseFloat(estDailyCell.getValue());

    if (adjustedDailyVal < estDailyVal) {
      adjustedDailyCell.setBackground('#f8d7da');
      const percentDiff = ((estDailyVal - adjustedDailyVal) / estDailyVal) * 100;
      if (percentDiff >= THRESHOLD_PERCENT) {
        alertRows.push({
          id: accountId,
          name: accountName,
          monthly: monthlyBudget.toFixed(2),
          spent: spent.toFixed(2),
          estimated: estDailyVal.toFixed(2),
          remaining: adjustedDailyVal.toFixed(2),
          yesterday: costYesterday.toFixed(2),
          overSpent: false
        });
      }
    } else {
      adjustedDailyCell.setBackground('#d4edda');
    }

    const percentExpectedCell = outputSheet.getRange(rowIndex, 6);
    const percentActualCell = outputSheet.getRange(rowIndex, 7);
    const valExpected = parseFloat(percentExpectedCell.getValue());
    const valActual = parseFloat(percentActualCell.getValue());

    let color = '';
    const diffPercent = valActual - valExpected;

    if (diffPercent > 0) {
      if (diffPercent >= 30) color = '#a94442';
      else if (diffPercent >= 20) color = '#c9302c';
      else color = '#f2dede';
    } else {
      const diff = Math.abs(diffPercent);
      if (diff >= 30) color = '#2b542c';
      else if (diff >= 20) color = '#449d44';
      else color = '#dff0d8';
    }

    percentActualCell.setBackground(color);

    const yesterdayCol = headers.length + 1;
    const yesterdayCostCell = outputSheet.getRange(rowIndex, yesterdayCol);
    const yesterdayValue = parseFloat(yesterdayCostCell.getValue());

    if (yesterdayValue > adjustedDailyVal) {
      const diff = yesterdayValue - adjustedDailyVal;
      const level = Math.min(Math.floor(diff / 10), 5);
      const redShades = ['#f8d7da', '#f5b7b1', '#ec7063', '#e74c3c', '#c0392b', '#922b21'];
      yesterdayCostCell.setBackground(redShades[level]);

      const percentDiff = (diff / adjustedDailyVal) * 100;
      if (percentDiff >= YESTERDAY_COST_THRESHOLD_PERCENT) {
        alertRows.push({
          id: accountId,
          name: accountName,
          monthly: monthlyBudget.toFixed(2),
          spent: spent.toFixed(2),
          estimated: estDailyVal.toFixed(2),
          remaining: adjustedDailyVal.toFixed(2),
          yesterday: costYesterday.toFixed(2),
          overSpent: true
        });
      }
    } else {
      const diff = adjustedDailyVal - yesterdayValue;
      const level = Math.min(Math.floor(diff / 10), 5);
      const greenShades = ['#d4edda', '#a9dfbf', '#7dcea0', '#52be80', '#27ae60', '#1e8449'];
      yesterdayCostCell.setBackground(greenShades[level]);
    }

    rowIndex++;
  }

  if (alertRows.length > 0) {
    sendEmailAlert(alertRows);
  }
}

function getOrCreateOutputSheet(spreadsheet, name) {
  let sheet = spreadsheet.getSheetByName(name);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(name);
  }
  return sheet;
}

function sendEmailAlert(rows) {
  let html = `
    <p><strong>Wykryto przekroczenia budżetów dziennych.</strong></p>
    <table border="1" cellpadding="5" cellspacing="0" style="border-collapse: collapse;">
      <tr>
        <th>ID konta</th>
        <th>Nazwa konta</th>
        <th>Budżet miesięczny</th>
        <th>Wydano dotychczas</th>
        <th>Budżet dzienny (szacowany)</th>
        <th>Budżet dzienny (pozostałe dni)</th>
        <th>Koszt z dnia wczorajszego</th>
        <th>Typ alertu</th>
      </tr>
  `;

  rows.forEach(r => {
    html += `
      <tr>
        <td>${r.id}</td>
        <td>${r.name}</td>
        <td>${r.monthly} zł</td>
        <td>${r.spent} zł</td>
        <td>${r.estimated} zł</td>
        <td>${r.remaining} zł</td>
        <td>${r.yesterday} zł</td>
        <td>${r.overSpent ? 'Koszt z wczoraj przekroczył limit' : 'Pozostały budżet zbyt niski'}</td>
      </tr>
    `;
  });

  html += `</table>
    <p>Sprawdź szczegóły w arkuszu: <a href="${SPREADSHEET_URL}" target="_blank">Otwórz Arkusz Google</a></p>
  `;

  MailApp.sendEmail({
    to: ALERT_EMAIL,
    subject: 'Alert Budżetowy!',
    htmlBody: html
  });
}