Appearance
Google Ads script - google sheets
/**
* 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;
}