Skip to content

/**
 * Google Ads MCC-script — prepaid budgetbewaking per klantaccount in Google Sheets.
 *
 * Tab "Stortingen" (rij 1 = kopjes, vul je zelf aan bij elke aanvulling):
 *   Per account een blok naast elkaar, bv. A t/m E, (F leeg), G t/m K, ...
 *   Account ID (123-456-7890) | Naam | Datum | Bedrag (€) | Opmerking (optioneel)
 *   - Het script herkent elk blok aan het kopje "Account ID" in rij 1.
 *   - Elke aanvulling = een nieuwe regel in het blok van dat account.
 *     Het Account ID hoeft alleen op de eerste regel; lege ID-cellen eronder
 *     tellen voor hetzelfde account.
 *   - Negatief bedrag = correctie.
 *   - Met KOSTEN_VANAF = 'ALTIJD' tellen alle kosten ooit mee; zet dan bij de
 *     stortingen ook alles wat er ooit gestort is. Met 'STORTING' begint de
 *     telling bij de eerste storting.
 *
 * Het script (her)bouwt dagelijks:
 *   - Tab "Saldo":        één regel per account met actueel saldo en status
 *   - Tab "Dagoverzicht": per account per dag: storting, kosten en lopend saldo
 *
 * Alles wordt elke run opnieuw berekend vanaf de eerste storting, dus een
 * nieuwe (of achteraf ingevoerde) storting en kostencorrecties van Google
 * worden automatisch verwerkt.
 */

const SHEET_URL = 'https://docs.google.com/spreadsheets/d/1LPzVfcSvYQ93tb673cdCxzTb01dpP7aQdZoAbWtT3bI/edit';
const TAB_STORTINGEN = 'Stortingen';
const TAB_SALDO = 'Saldo';
const TAB_DAGEN = 'Dagoverzicht';
const WAARSCHUWING_DAGEN = 7; // "BIJNA OP" als het saldo bij huidig tempo korter dan dit meegaat
// E-mail na elke run (meerdere adressen scheiden met een komma). Leeg = geen mail.
const EMAIL = 'ernstvanwijk@gmail.com';
// true = alleen mailen als een account BIJNA OP, OP of NIET GEVONDEN is
const MAIL_ALLEEN_BIJ_WAARSCHUWING = false;
// 'ALTIJD'   = alle kosten sinds het account bestaat (zoals "Onbeperkt" in Google Ads)
// 'STORTING' = alleen kosten vanaf de eerste storting in de sheet
const KOSTEN_VANAF = 'ALTIJD';

function main() {
  const ss = SpreadsheetApp.openByUrl(SHEET_URL);
  const stortingen = leesStortingen(ss);
  const ids = Object.keys(stortingen);
  if (!ids.length) {
    Logger.log('Geen stortingen gevonden in tab "' + TAB_STORTINGEN + '".');
    return;
  }

  const saldoRows = [];
  const dagBlokken = [];
  const gevonden = {};

  const it = AdsManagerApp.accounts().withIds(ids).get();
  while (it.hasNext()) {
    const account = it.next();
    AdsManagerApp.select(account);

    const id = account.getCustomerId();
    gevonden[id] = true;
    const s = stortingen[id];
    const naam = s.naam || account.getName();
    const tz = AdsApp.currentAccount().getTimeZone();
    const gisteren = Utilities.formatDate(new Date(Date.now() - 864e5), tz, 'yyyy-MM-dd');

    const eersteStorting = s.items[0].datum;
    const laatsteStorting = s.items[s.items.length - 1].datum;
    const kostenVan = KOSTEN_VANAF === 'ALTIJD' ? '2000-01-01' : eersteStorting;
    const kosten = kostenVan <= gisteren ? dagKosten(kostenVan, gisteren) : {};

    // Grootboek begint bij de eerste storting, of eerder als er al kosten waren
    let start = eersteStorting;
    const eersteKostenDag = Object.keys(kosten).sort()[0];
    if (eersteKostenDag && eersteKostenDag < start) start = eersteKostenDag;

    const stortPerDag = {};
    s.items.forEach(i => { stortPerDag[i.datum] = (stortPerDag[i.datum] || 0) + i.bedrag; });

    // Dagelijks grootboek opbouwen
    const eind = laatsteStorting > gisteren ? laatsteStorting : gisteren;
    let saldo = 0, gestort = 0, besteed = 0;
    const rows = [];
    for (let d = start; d <= eind; d = volgendeDag(d)) {
      const st = stortPerDag[d] || 0;
      const k = kosten[d] || 0;
      gestort += st;
      besteed += k;
      saldo += st - k;
      rows.push([d, st || '', rond(k), rond(gestort), rond(besteed), rond(saldo)]);
    }
    dagBlokken.push({ id: id, titel: naam + ' (' + id + ')', rows: rows });

    const dagenMetKosten = Object.keys(kosten).length;
    Logger.log(id + ' | KOSTEN_VANAF=' + KOSTEN_VANAF + ' | kosten opgehaald van ' + kostenVan +
      ' t/m ' + gisteren + ' | dagen met kosten: ' + dagenMetKosten +
      ' | eerste kostendag: ' + (eersteKostenDag || '-') + ' | som dagkosten: ' + rond(besteed));

    // Controle met het "alle tijd"-totaal van Google Ads zelf (min de kosten van vandaag)
    if (KOSTEN_VANAF === 'ALTIJD') {
      const acc = AdsApp.currentAccount();
      const totaalAltijd = acc.getStatsFor('ALL_TIME').getCost() - acc.getStatsFor('TODAY').getCost();
      Logger.log(id + ' | ALL_TIME t/m gisteren: ' + rond(totaalAltijd));
      if (Math.abs(totaalAltijd - besteed) > 0.05) {
        Logger.log(id + ' | Verschil met dagkosten, ALL_TIME-totaal wordt gebruikt voor het saldo.');
        saldo += besteed - totaalAltijd;
        besteed = totaalAltijd;
      }
    }

    // Gemiddelde besteding laatste 7 dagen
    let som7 = 0;
    for (let i = 0, d = gisteren; i < 7; i++, d = vorigeDag(d)) som7 += kosten[d] || 0;
    const gem7 = som7 / 7;
    const dagenOver = gem7 > 0 ? Math.max(0, Math.floor(saldo / gem7)) : '';

    let status = 'OK';
    if (saldo <= 0) status = 'OP';
    else if (dagenOver !== '' && dagenOver < WAARSCHUWING_DAGEN) status = 'BIJNA OP';

    saldoRows.push([
      id, naam, rond(gestort), laatsteStorting, rond(besteed),
      rond(kosten[gisteren] || 0), rond(gem7), rond(saldo), dagenOver, status,
      Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd HH:mm')
    ]);
  }

  ids.filter(id => !gevonden[id]).forEach(id => {
    saldoRows.push([id, stortingen[id].naam, '', '', '', '', '', '', '', 'NIET GEVONDEN', '']);
  });

  // Tab Saldo
  const saldoSheet = schrijfTab(ss, TAB_SALDO, [
    'Account ID', 'Naam', 'Totaal gestort', 'Laatste storting', 'Totaal besteed',
    'Besteed gisteren', 'Gem. per dag (7d)', 'Saldo', 'Dagen resterend', 'Status', 'Bijgewerkt'
  ], saldoRows);
  kleurStatus(saldoSheet, saldoRows, 10);
  stuurMail(ss, saldoRows);

  // Tab Dagoverzicht: accounts naast elkaar, in de volgorde van de tab Stortingen
  dagBlokken.sort((a, b) => ids.indexOf(a.id) - ids.indexOf(b.id));
  schrijfBlokken(ss, TAB_DAGEN,
    ['Datum', 'Storting', 'Kosten', 'Totaal gestort', 'Totaal besteed', 'Saldo'], dagBlokken);
}

/**
 * Zet elk account in een eigen blok kolommen naast elkaar:
 * account 1 in A t/m F, account 2 in G t/m L, enz.
 * Rij 1 = accountnaam, rij 2 = kopjes, vanaf rij 3 de dagen.
 */
function schrijfBlokken(ss, naam, kop, blokken) {
  const sh = ss.getSheetByName(naam) || ss.insertSheet(naam);
  sh.getRange(1, 1, sh.getMaxRows(), sh.getMaxColumns()).breakApart();
  sh.clear();

  const breedte = kop.length;
  const maxRijen = blokken.reduce((m, b) => Math.max(m, b.rows.length), 0) + 2;
  if (sh.getMaxRows() < maxRijen) sh.insertRowsAfter(sh.getMaxRows(), maxRijen - sh.getMaxRows());
  const nodigKolommen = blokken.length * breedte;
  if (sh.getMaxColumns() < nodigKolommen) {
    sh.insertColumnsAfter(sh.getMaxColumns(), nodigKolommen - sh.getMaxColumns());
  }

  blokken.forEach((blok, i) => {
    const kol = 1 + i * breedte;
    sh.getRange(1, kol, 1, breedte).merge().setValue(blok.titel)
      .setFontWeight('bold').setHorizontalAlignment('center').setBackground('#cfe2f3');
    sh.getRange(2, kol, 1, breedte).setValues([kop]).setFontWeight('bold').setBackground('#eeeeee');
    if (blok.rows.length) sh.getRange(3, kol, blok.rows.length, breedte).setValues(blok.rows);
    if (i > 0) {
      sh.getRange(1, kol, maxRijen, 1).setBorder(null, true, null, null, null, null,
        '#666666', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    }
  });
  sh.setFrozenRows(2);
}

function leesStortingen(ss) {
  const sh = ss.getSheetByName(TAB_STORTINGEN);
  if (!sh) throw new Error('Tab "' + TAB_STORTINGEN + '" niet gevonden.');
  const tz = ss.getSpreadsheetTimeZone();
  const waarden = sh.getDataRange().getValues();
  const kop = waarden[0];
  const data = {};

  // Elk blok begint bij een kolom met kopje "Account ID" (bv. A, G, M, ...)
  // en bestaat uit: Account ID | Naam | Datum | Bedrag | Opmerking
  const blokStarts = [];
  kop.forEach((v, c) => { if (String(v).trim().toLowerCase() === 'account id') blokStarts.push(c); });

  blokStarts.forEach(c => {
    let blokId = '';
    waarden.slice(1).forEach(r => {
      if (String(r[c]).trim()) blokId = String(r[c]).trim(); // lege ID-cel = zelfde account als erboven
      const id = blokId;
      const datum = r[c + 2];
      const bedrag = Number(r[c + 3]);
      if (!id || !(datum instanceof Date) || !bedrag) return;
      if (!data[id]) data[id] = { naam: '', items: [] };
      if (r[c + 1]) data[id].naam = String(r[c + 1]).trim();
      data[id].items.push({ datum: Utilities.formatDate(datum, tz, 'yyyy-MM-dd'), bedrag: bedrag });
    });
  });
  Object.keys(data).forEach(id => data[id].items.sort((a, b) => (a.datum < b.datum ? -1 : 1)));
  return data;
}

function dagKosten(van, tot) {
  const res = {};
  const rows = AdsApp.search(
    "SELECT segments.date, metrics.cost_micros FROM customer " +
    "WHERE segments.date BETWEEN '" + van + "' AND '" + tot + "'"
  );
  while (rows.hasNext()) {
    const r = rows.next();
    const d = r.segments.date;
    res[d] = (res[d] || 0) + Number(r.metrics.costMicros) / 1e6;
  }
  return res;
}

function schrijfTab(ss, naam, kop, rows) {
  const sh = ss.getSheetByName(naam) || ss.insertSheet(naam);
  sh.clear();
  sh.getRange(1, 1, 1, kop.length).setValues([kop]).setFontWeight('bold');
  if (rows.length) sh.getRange(2, 1, rows.length, kop.length).setValues(rows);
  sh.setFrozenRows(1);
  return sh;
}

function kleurStatus(sh, rows, kolom) {
  if (!rows.length) return;
  const kleuren = { 'OK': '#d9ead3', 'BIJNA OP': '#fce5cd', 'OP': '#f4cccc', 'NIET GEVONDEN': '#eeeeee' };
  sh.getRange(2, kolom, rows.length, 1)
    .setBackgrounds(rows.map(r => [kleuren[r[kolom - 1]] || null]));
}

function volgendeDag(d) {
  const t = new Date(d + 'T00:00:00Z');
  t.setUTCDate(t.getUTCDate() + 1);
  return t.toISOString().slice(0, 10);
}

function vorigeDag(d) {
  const t = new Date(d + 'T00:00:00Z');
  t.setUTCDate(t.getUTCDate() - 1);
  return t.toISOString().slice(0, 10);
}

/**
 * Stuurt een overzicht per account per e-mail.
 * saldoRows: [id, naam, gestort, laatsteStorting, besteed, gisteren, gem7, saldo, dagenOver, status, bijgewerkt]
 */
function stuurMail(ss, saldoRows) {
  if (!EMAIL || !saldoRows.length) return;
  const waarschuwing = saldoRows.some(r => r[9] !== 'OK');
  if (MAIL_ALLEEN_BIJ_WAARSCHUWING && !waarschuwing) return;

  const kleur = { 'OK': '#d9ead3', 'BIJNA OP': '#fce5cd', 'OP': '#f4cccc', 'NIET GEVONDEN': '#eeeeee' };
  const td = 'style="padding:6px 10px;border-bottom:1px solid #ddd"';
  const tdR = 'style="padding:6px 10px;border-bottom:1px solid #ddd;text-align:right"';

  const rijen = saldoRows.map(r =>
    '<tr>' +
    '<td ' + td + '><b>' + r[1] + '</b><br><span style="color:#888">' + r[0] + '</span></td>' +
    '<td ' + tdR + '>' + euro(r[5]) + '</td>' +
    '<td ' + tdR + '>' + euro(r[4]) + '</td>' +
    '<td ' + tdR + '>' + euro(r[2]) + '</td>' +
    '<td ' + tdR + '><b>' + euro(r[7]) + '</b></td>' +
    '<td ' + tdR + '>' + (r[8] === '' ? '-' : r[8]) + '</td>' +
    '<td style="padding:6px 10px;border-bottom:1px solid #ddd;background:' + (kleur[r[9]] || '#fff') + '">' + r[9] + '</td>' +
    '</tr>'
  ).join('');

  const th = 'style="padding:6px 10px;text-align:left;background:#f3f3f3"';
  const html =
    '<p>Google Ads budgetoverzicht (kosten t/m gisteren):</p>' +
    '<table style="border-collapse:collapse;font-family:Arial,sans-serif;font-size:13px">' +
    '<tr><th ' + th + '>Account</th><th ' + th + '>Gisteren</th><th ' + th + '>Totaal besteed</th>' +
    '<th ' + th + '>Totaal gestort</th><th ' + th + '>Saldo</th><th ' + th + '>Dagen</th><th ' + th + '>Status</th></tr>' +
    rijen + '</table>' +
    '<p><a href="' + ss.getUrl() + '">Open de sheet</a></p>';

  const tekst = saldoRows.map(r =>
    r[1] + ' (' + r[0] + '): gisteren ' + euro(r[5]) + ', saldo ' + euro(r[7]) + ', ' + r[9]
  ).join('\n');

  const onderwerp = (waarschuwing ? '⚠️ ' : '') + 'Google Ads saldo: ' +
    saldoRows.map(r => r[1] + ' ' + euro(r[7])).join(' | ');

  MailApp.sendEmail({ to: EMAIL, subject: onderwerp, body: tekst, htmlBody: html });
}

function euro(n) {
  if (n === '' || n === null || n === undefined) return '-';
  return '€ ' + Number(n).toFixed(2).replace('.', ',').replace(/\B(?=(\d{3})+(?!\d))/g, '.');
}

function rond(n) {
  return Math.round(n * 100) / 100;
}