Hängt täglich eine Zeile mit 20 Kontometriken an den Report-Tab an und sendet per E-Mail einen Vergleich gestern / vorgestern / vor einer Woche.
// account-summary — DE build 2026-09-14
// Copyright 2026 Gökhan Güzel — gokhanguzel.com. All Rights Reserved. Generated file: edit src/ and i18n/, then run build.js.
// Do not share configured Slack webhook URLs.
'use strict';
const SCRIPT_ID = "account-summary";
const L = {"lang":"de","locale":"de-DE","text_lang":"de","dec":",","thou":"."};
const ATTRIBUTION = String.fromCharCode(169,32,103,111,107,104,97,110,103,117,122,101,108,46,99,111,109);
const T = {"kpi":"KPI","kpi.now":"Dieser Zeitraum","kpi.prev":"Vorher","yes":"ja","no":"nein","mail.full_report":"Vollständiger Bericht","mail.open_report":"Bericht öffnen","note.no_rows":"Keine Zeilen.","note.truncated":"{shown} von {total} Zeilen angezeigt (max_rows_written).","note.no_campaigns":"Keine Kampagnen mit Impressionen entsprechen den aktuellen Filtern.","settings.key":"Schlüssel","settings.value":"Wert","settings.notes":"Hinweise","settings.script":"Script, dem dieses Sheet gehört (nicht ändern)","settings.account":"Google Ads-Konto, dem dieses Sheet gehört (nicht ändern)","note.truncated_queries":"⚠ {n} Abfrage(n) am Leselimit — Ergebnis unvollständig; Zeitraum verkleinern","note.account_neg_failed":"⚠ Negativs auf Kontoebene nicht lesbar:","note.skipped_time":"übersprungen — Zeitbudget","note.data_error":"Datenfehler:","note.failed":"FEHLGESCHLAGEN —","note.no_final_url":"(keine finale URL)","m.impressions":"Impr.","m.clicks":"Klicks","m.ctr":"CTR","m.cost":"Kosten","m.cpc":"Ø CPC","m.conversions":"Conv.","m.value":"Conv.-Wert","m.cr":"Conv.-Rate","m.cpa":"Kosten/Conv.","m.roas":"ROAS","m.aov":"Ø Bestellwert","m.vpc":"Wert / Klick","col.account":"Konto","col.period":"Zeitraum","col.currency":"Währung","col.date":"Datum","col.campaign":"Kampagne","col.ad_group":"Anzeigengruppe","col.n":"N","col.phrase":"Phrase","col.queries":"Suchanfragen","col.source":"Quelle","col.why":"Warum","title":"Account Summary — Kontoübersicht","tab.report":"Bericht","settings.email":"Empfänger, kommagetrennt; leer = keine E-Mail","settings.last_check":"zuletzt geschriebener Tag (yyyy-MM-dd); für Nachfüllen ein früheres Datum eintragen (max. 90 Tage)","mail.subject":"Google Ads {cid} — Kontoübersicht {date}","mail.yesterday":"Gestern","mail.two_days_ago":"Vor zwei Tagen","mail.week_ago":"Vor einer Woche","f.cost_micros":"Kosten","f.average_cpc":"Ø CPC","f.ctr":"CTR","f.search_impression_share":"Such-Impr.-Anteil","f.impressions":"Impressionen","f.clicks":"Klicks","f.conversions":"Conversions","f.conversions_value":"Conv.-Wert","f.cost_per_conversion":"Kosten/Conv.","f.conversions_from_interactions_rate":"Conv.-Rate","f.all_conversions":"Alle Conv.","f.view_through_conversions":"View-Through-Conv.","f.search_budget_lost_impression_share":"Verl. IS (Budget)","f.search_rank_lost_impression_share":"Verl. IS (Rang)","f.search_top_impression_share":"Top-IS","f.search_absolute_top_impression_share":"Abs. Top-IS","f.average_cpm":"Ø CPM","f.interactions":"Interaktionen","f.interaction_rate":"Interaktionsrate","f.invalid_clicks":"Ungültige Klicks","alias.report":["Report","Rapor"],"alias.settings":["Settings","Ayarlar","Einstellungen"],"alias.history":["History","Kronoloji","Verlauf","Runs","Calismalar","Laeufe"],"alias.snapshot":["Snapshot","Snapshots","Kesit","Kesitler","Momentaufnahme","Momentaufnahmen"],"alias.state":["State","DurumBellegi","Zustand"],"alias.baseline":["Baseline","TemelCizgi","Basislinie"]};
/* ===================== common/core ===================== */
/* Pure helpers. No Sheets, no network. Everything here is unit-tested in test/run.js. */
/* AdsApp.search is report-backed and is not subject to selector entity limits. Keep a defensive count check only for
runtimes/iterators that expose totalNumEntities(); normally this stays at zero. */
let TRUNCATED_ = 0;
function query_(gaql) {
const out = [];
const it = AdsApp.search(gaql);
while (it.hasNext()) out.push(it.next());
try { if (typeof it.totalNumEntities === 'function') { const n = it.totalNumEntities(); if (n > out.length) { TRUNCATED_++; Logger.log(`Query truncated: ${out.length} of ${n} rows read — narrow the window or filters. ${gaql.slice(0, 120)}`); } } } catch (e) {}
return out;
}
/* Seconds left in the 30-minute execution window (Infinity when the runtime does not expose it, e.g. in the mock). */
function timeLeft_() { try { return AdsApp.getExecutionInfo().getRemainingTime(); } catch (e) { return Infinity; } }
function num_(v) { const x = parseFloat(v); return isFinite(x) ? x : 0; }
function nz_(v) { return isFinite(v) ? v : ''; }
function esc_(s) { return String(s).replace(/\\/g, '\\\\').replace(/'/g, "\\'"); }
function escLike_(s) { const m = { '[': '[[]', ']': '[]]', '%': '[%]', '_': '[_]' }; return esc_(s).replace(/[\[\]%_]/g, c => m[c]); }
function chunks_(arr, n) { const o = []; for (let i = 0; i < arr.length; i += n) o.push(arr.slice(i, i + n)); return o; }
function pct_(a, b) { return (typeof a === 'number' && typeof b === 'number' && isFinite(a) && isFinite(b) && b) ? a / b - 1 : NaN; }
/* Number formatting for e-mail / Slack. Deterministic: separators come from the build (L.dec / L.thou), not from
the runtime's ICU data. */
function fmtNum_(v, digits) {
if (typeof v !== 'number' || !isFinite(v)) return '–';
const d = digits === undefined ? (Math.abs(v) >= 1000 ? 0 : Math.abs(v) < 1 ? 3 : 2) : digits;
const parts = Math.abs(v).toFixed(d).split('.');
return (v < 0 ? '-' : '') + parts[0].replace(/\B(?=(\d{3})+(?!\d))/g, L.thou) + (parts[1] ? L.dec + parts[1] : '');
}
function fmtPct_(v, digits) { return isFinite(v) ? fmtNum_(v * 100, digits === undefined ? 2 : digits) + '%' : '–'; }
/* Metric bag: raw sums + derived rates. NaN means "undefined", never 0. */
function zeroM_() { return { impressions: 0, clicks: 0, cost: 0, conversions: 0, value: 0 }; }
function rawOf_(r, opts) {
const m = (r && r.metrics) || {}; opts = opts || {};
return {
impressions: num_(m.impressions), clicks: num_(m.clicks),
cost: opts.noCost ? 0 : num_(m.costMicros) / 1e6,
conversions: opts.noConv ? NaN : num_(m.conversions),
value: opts.noConv ? NaN : num_(m.conversionsValue)
};
}
function addM_(a, b) {
const sumKnown = (x, y) => isFinite(x) && isFinite(y) ? x + y : NaN;
return {
impressions: a.impressions + b.impressions, clicks: a.clicks + b.clicks, cost: a.cost + b.cost,
conversions: sumKnown(a.conversions, b.conversions),
value: sumKnown(a.value, b.value)
};
}
function derive_(o) {
o.ctr = o.impressions ? o.clicks / o.impressions : NaN;
o.cpc = o.clicks ? o.cost / o.clicks : NaN;
o.cr = o.clicks && isFinite(o.conversions) ? o.conversions / o.clicks : NaN;
o.cpa = o.conversions > 0 ? o.cost / o.conversions : NaN;
o.roas = o.cost > 0 && isFinite(o.value) ? o.value / o.cost : NaN;
o.aov = o.conversions > 0 ? o.value / o.conversions : NaN;
o.vpc = o.clicks ? o.value / o.clicks : NaN;
return o;
}
/* Text normalisation for search terms / negatives. language: 'tr' handles İ/ı. */
function normalize_(s, language) {
s = String(s || '');
s = language === 'tr' ? s.replace(/I/g, 'ı').replace(/İ/g, 'i').toLowerCase() : s.toLowerCase();
return s.replace(/[+\[\]"“”'’`^*]/g, ' ')
.replace(/[^\p{L}\p{N}\s\-\.]/gu, ' ')
.split(/\s+/).map(w => w.replace(/^[.\-]+|[.\-]+$/g, '')).filter(Boolean).join(' ');
}
function tokens_(s, language) { const n = normalize_(s, language); return n ? n.split(' ') : []; }
function hasSubseq_(hay, needle) {
outer: for (let i = 0; i + needle.length <= hay.length; i++) {
for (let j = 0; j < needle.length; j++) if (hay[i + j] !== needle[j]) continue outer;
return true;
}
return false;
}
/* exact = same tokens; phrase = contiguous; broad = every word present. */
function blocked_(q, negs) {
if (!negs) return false;
for (const n of negs) {
if (!n.words.length) continue;
if (n.mt === 'EXACT') { if (n.words.length === q.length && n.words.every((w, i) => w === q[i])) return true; }
else if (n.mt === 'PHRASE') { if (hasSubseq_(q, n.words)) return true; }
else if (n.words.every(w => q.includes(w))) return true;
}
return false;
}
/* Robust stats: median / MAD (σ-scaled), SD fallback. */
function median_(sorted) { const n = sorted.length; if (!n) return NaN; return n % 2 ? sorted[(n - 1) / 2] : (sorted[n / 2 - 1] + sorted[n / 2]) / 2; }
function robust_(arr) {
if (!arr.length) return { median: NaN, mad: NaN, scale: 0 };
const s = arr.slice().sort((a, b) => a - b), med = median_(s);
const mad = median_(s.map(v => Math.abs(v - med)).sort((a, b) => a - b)) * 1.4826;
let scale = mad;
if (!(scale > 0)) { const mean = arr.reduce((x, v) => x + v, 0) / arr.length; scale = Math.sqrt(arr.reduce((x, v) => x + (v - mean) * (v - mean), 0) / Math.max(1, arr.length - 1)); }
return { median: med, mad: mad, scale: scale };
}
/* Minimal HTML escape for anything user-generated that lands in e-mail. */
function h_(s) { return String(s === undefined || s === null ? '' : s).replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>').replace(/"/g, '"'); }
/* Text lookup. T is injected by the build; keys are stable, values are per language. */
function t_(key, vars) {
let s = T[key]; if (s === undefined) s = key;
return String(s).replace(/\{(\w+)\}/g, (m, k) => (vars && vars[k] !== undefined) ? vars[k] : m);
}
/* ===================== common/dates ===================== */
/* Calendar-based date arithmetic in the account time zone. No 864e5 shifts,
so DST transitions cannot skip or repeat a day. */
function tz_() { return AdsApp.currentAccount().getTimeZone(); }
function todayStr_(tz, base) { return Utilities.formatDate(base || new Date(), tz, 'yyyy-MM-dd'); }
function shiftDay_(dateStr, days) {
const p = dateStr.split('-').map(Number);
const d = new Date(Date.UTC(p[0], p[1] - 1, p[2] + days));
return d.getUTCFullYear() + '-' + String(d.getUTCMonth() + 1).padStart(2, '0') + '-' + String(d.getUTCDate()).padStart(2, '0');
}
/* window_(days, offset): `days` complete days, ending `offset+1` days before today. offset 0 → ends yesterday. */
function window_(days, offset, tz, base) {
const today = todayStr_(tz, base);
const end = shiftDay_(today, -(offset + 1)), start = shiftDay_(today, -(offset + days));
return { start: start, end: end, label: start + ' → ' + end };
}
const DOW_ = ['SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY'];
function dowOf_(dateStr) { const p = dateStr.split('-').map(Number); return DOW_[new Date(Date.UTC(p[0], p[1] - 1, p[2])).getUTCDay()]; }
/* Sheets may hand back a Date where we wrote a 'yyyy-MM-dd' string. Always normalise on read. */
function dateStrOf_(v, tz) {
if (v instanceof Date) return Utilities.formatDate(v, tz, 'yyyy-MM-dd');
return String(v === null || v === undefined ? '' : v).trim(); // strings are never truncated: keys like 'yyyy-MM-dd HH:mm' or '123-456-7890' stay intact
}
/* ===================== common/manager ===================== */
/* Runs as a single account, or selects one client account when started from a manager account. */
function isManager_() { try { if (typeof AdsManagerApp === 'undefined') return false; AdsManagerApp.accounts().withLimit(1).get(); return true; } catch (e) { return false; } }
function selectAccount_(accountId) {
if (!isManager_()) return;
if (!accountId) throw new Error('Running from a manager account: set CONFIG.account_id (e.g. 123-456-7890).');
const it = AdsManagerApp.accounts().withIds([String(accountId)]).get();
if (!it.hasNext()) throw new Error('Account ' + accountId + ' is not a client of this manager account.');
AdsManagerApp.select(it.next());
}
/* ===================== common/sheets ===================== */
/* Technical tabs are language-independent. User-facing tab names come from T. */
const TECH = { settings: '_settings', state: '_state', snapshot: '_snapshot', history: '_history', baseline: '_baseline' };
function openSpreadsheet_(url) {
if (!url || url === 'YOUR_SPREADSHEET_URL') throw new Error('Set CONFIG.spreadsheet_url to a Google Sheet you own.');
const ss = SpreadsheetApp.openByUrl(url);
claimSheet_(ss); // ownership first: nothing is written to a Sheet that belongs to another script or account
return seal_(ss); // every script opens its Sheet through the attribution layer
}
/* One Sheet per script. The first run records SCRIPT_ID in _settings; a different script pointed at the same Sheet stops
before it can overwrite tabs (ngram-lens and pmax-ngram-lens share tab names). */
function claimSheet_(ss) {
const sh = tab_(ss, TECH.settings, T['alias.settings']);
const cid = AdsApp.currentAccount().getCustomerId();
const kv = {}; if (sh.getLastRow() > 1) sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues().forEach(r => kv[String(r[0])] = String(r[1]));
if (kv.script && kv.script !== SCRIPT_ID) throw new Error(`This Sheet belongs to ${kv.script}. Use a separate Sheet for ${SCRIPT_ID}.`);
if (kv.account && kv.account !== cid) throw new Error(`This Sheet belongs to account ${kv.account}; running as ${cid}. Use a separate Sheet per account.`);
if (!kv.script || !kv.account) ensureSettings_(ss, []);
}
/* Title in A1, header in row `top` (2). Writes only what is missing, so the attribution cell in H1 never masks an empty
header row. Returns true when the header was (re)written. */
function ensureTitledHeader_(sheet, title, header) {
ensureSize_(sheet, 3, header.length);
if (String(sheet.getRange(1, 1).getValue()) === '') sheet.getRange(1, 1).setValue(title).setFontWeight('bold').setFontSize(13);
const a2 = sheet.getRange(2, 1).getValue();
if (String(a2) === header[0]) return false;
if (String(a2) !== '') sheet.insertRowsBefore(2, 1); // data written under a missing header (v2.2 layout): push it down, then add the header
styleHeader_(sheet.getRange(2, 1, 1, header.length).setValues([header])); sheet.setFrozenRows(2);
return true;
}
/* Find a tab by its current name or any legacy alias; rename legacy tabs once so state survives a language switch. */
function tab_(ss, name, aliases) {
let sh = ss.getSheetByName(name);
if (!sh && aliases) for (const a of aliases) { const old = ss.getSheetByName(a); if (old) { old.setName(name); sh = old; break; } }
if (!sh) sh = ss.insertSheet(name);
return sh;
}
/* specs: [{name, aliases:[...]}]. Removes the empty default tab whatever its locale name is. */
function ensureTabs_(ss, specs) {
const wanted = new Set(specs.map(s => s.name));
specs.forEach(s => tab_(ss, s.name, s.aliases));
const first = ss.getSheets()[0];
if (first && !wanted.has(first.getName()) && ss.getSheets().length > 1 && onlyAttribution_(first)) ss.deleteSheet(first);
}
/* A default tab that holds nothing but the attribution cell written by the early seal_ is still "empty". */
function onlyAttribution_(sheet) {
if (sheet.getLastRow() === 0) return true;
if (sheet.getLastRow() > 1) return false;
const row = sheet.getRange(1, 1, 1, Math.max(1, sheet.getLastColumn())).getValues()[0];
return row.every((v, i) => v === '' || (i === 7 && v === ATTRIBUTION));
}
/* Grow the grid BEFORE any getRange: Sheets throws on coordinates outside the current dimensions. */
function ensureSize_(sheet, rows, cols) {
if (cols > sheet.getMaxColumns()) sheet.insertColumnsAfter(sheet.getMaxColumns(), cols - sheet.getMaxColumns());
if (rows > sheet.getMaxRows()) sheet.insertRowsAfter(sheet.getMaxRows(), rows - sheet.getMaxRows());
}
function styleHeader_(rng) { return rng.setFontWeight('bold').setBackground('#1E293B').setFontColor('#ffffff'); }
/* Write a table. Rows above `top` are never touched (callers put titles there). formats[i] is a number format per column ('@' = text, applied BEFORE setValues so Sheets never auto-parses).
Rows beyond `cap` are dropped and a note is written. Returns the number of rows written. */
function table_(sheet, top, header, rows, formats, cap) {
let out = rows;
if (cap && rows.length > cap) out = rows.slice(0, cap);
ensureSize_(sheet, top + out.length + 1, header.length);
const maxR = sheet.getMaxRows(), maxC = sheet.getMaxColumns();
if (maxR >= top) sheet.getRange(top, 1, maxR - top + 1, maxC).clearContent(); // contents only: user notes/formats outside the table survive
styleHeader_(sheet.getRange(top, 1, 1, header.length).setValues([header]));
if (out.length) {
(formats || []).forEach((f, i) => { if (f) sheet.getRange(top + 1, i + 1, out.length, 1).setNumberFormat(f); });
sheet.getRange(top + 1, 1, out.length, header.length).setValues(out);
if (out.length < rows.length) sheet.getRange(top + 1 + out.length, 1).setValue(t_('note.truncated', { shown: out.length, total: rows.length }));
} else {
sheet.getRange(top + 1, 1).setValue(t_('note.no_rows'));
}
sheet.setFrozenRows(top);
return out.length;
}
/* Append rows with text formats applied first (appendRow cannot do that). */
function appendRows_(sheet, rows, formats) {
if (!rows.length) return;
const r0 = sheet.getLastRow() + 1, n = rows.length, w = rows[0].length;
ensureSize_(sheet, r0 + n - 1, w);
(formats || []).forEach((f, i) => { if (f) sheet.getRange(r0, i + 1, n, 1).setNumberFormat(f); });
sheet.getRange(r0, 1, n, w).setValues(rows);
}
/* A technical tab whose header row differs from what this version writes (older layout) is renamed to an archive tab and a
fresh one is created — rows of two schemas are never mixed. Returns the sheet to write to. */
function ensureSchema_(ss, name, header) {
const sh = ss.getSheetByName(name); if (!sh || sh.getLastRow() === 0) return sh;
const cur = sh.getRange(1, 1, 1, Math.max(1, sh.getLastColumn())).getValues()[0].map(String);
while (cur.length && cur[cur.length - 1] === '') cur.pop();
if (cur.join('|') === header.join('|')) return sh;
let n = 1, archive = name + '_archive'; while (ss.getSheetByName(archive)) archive = name + '_archive' + (++n);
sh.setName(archive); Logger.log(`${name}: old layout archived as ${archive}`);
return ss.insertSheet(name);
}
/* Idempotent history: one row per key (first `keyCols` columns, joined). Existing row with the same key is replaced. */
function upsertRow_(sheet, header, row, keyCols, formats, tz) {
ensureSize_(sheet, 2, Math.max(header.length, row.length));
const cur = sheet.getLastRow() === 0 ? [] : sheet.getRange(1, 1, 1, Math.max(1, sheet.getLastColumn())).getValues()[0].map(String).filter(Boolean);
if (cur.join('|') !== header.join('|')) {
const w = Math.max(header.length, sheet.getLastColumn()); sheet.getRange(1, 1, 1, w).clearContent();
styleHeader_(sheet.getRange(1, 1, 1, header.length).setValues([header])); sheet.setFrozenRows(1);
} // new/changed schema: clear stale cells, then rewrite header
const key = row.slice(0, keyCols).map(v => dateStrOf_(v, tz)).join('|');
const last = sheet.getLastRow();
if (last > 1) {
const keys = sheet.getRange(2, 1, last - 1, keyCols).getValues().map(r => r.map(v => dateStrOf_(v, tz)).join('|'));
const i = keys.indexOf(key);
if (i >= 0) {
const w = Math.max(row.length, sheet.getLastColumn()); sheet.getRange(i + 2, 1, 1, w).clearContent();
sheet.getRange(i + 2, 1, 1, row.length).setValues([row]); return;
}
}
appendRows_(sheet, [row], formats);
}
/* Replace a whole table safely: write the new rows first, then clear whatever is left below. */
function replaceRows_(sheet, header, rows, formats) {
const n = rows.length;
ensureSize_(sheet, n + 1, header.length);
if (sheet.getLastRow() === 0) styleHeader_(sheet.getRange(1, 1, 1, header.length).setValues([header]));
if (n) {
(formats || []).forEach((f, i) => { if (f) sheet.getRange(2, i + 1, n, 1).setNumberFormat(f); });
sheet.getRange(2, 1, n, header.length).setValues(rows);
}
const last = sheet.getLastRow();
if (last > n + 1) sheet.getRange(n + 2, 1, last - n - 1, Math.max(header.length, sheet.getLastColumn())).clearContent();
}
/* _settings: Key | Value | Notes. Keys are stable identifiers (never translated); notes are per language. */
function ensureSettings_(ss, defaults) {
const sh = tab_(ss, TECH.settings, T['alias.settings']);
ensureSize_(sh, 2 + defaults.length, 3);
if (sh.getLastRow() === 0) {
sh.getRange(1, 1, 1, 3).setNumberFormat('@').setValues([[t_('settings.key'), t_('settings.value'), t_('settings.notes')]]);
styleHeader_(sh.getRange(1, 1, 1, 3)); sh.setFrozenRows(1); sh.setColumnWidths(1, 1, 200); sh.setColumnWidths(3, 1, 420);
}
const all = [['script', SCRIPT_ID, t_('settings.script')], ['account', AdsApp.currentAccount().getCustomerId(), t_('settings.account')]].concat(defaults);
const last = sh.getLastRow();
const keys = last > 1 ? sh.getRange(2, 1, last - 1, 1).getValues().map(r => String(r[0])) : [];
const missing = all.filter(d => keys.indexOf(d[0]) < 0);
if (missing.length) { const r0 = sh.getLastRow() + 1; ensureSize_(sh, r0 + missing.length, 3); sh.getRange(r0, 1, missing.length, 3).setNumberFormat('@').setValues(missing.map(d => [d[0], d[1], d[2]])); }
if (keys.length) { // notes follow the current language; one batched write, only when something differs; values are never touched
const rng = sh.getRange(2, 3, keys.length, 1), cur = rng.getValues().map(r => String(r[0]));
const want = keys.map((k, i) => { const d = all.find(x => x[0] === k); return d && d[2] ? d[2] : cur[i]; });
if (want.join('\u0001') !== cur.join('\u0001')) rng.setValues(want.map(v => [v]));
}
return sh;
}
/* Reads _settings into cfg. Only keys present in `schema` are accepted; type comes from schema: 'num' | 'bool' | 'str' | 'list'. */
function readSettings_(ss, cfg, schema) {
const out = JSON.parse(JSON.stringify(cfg));
const sh = ss.getSheetByName(TECH.settings);
if (!sh || sh.getLastRow() < 2) return out;
sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues().forEach(([k, v]) => {
const type = schema[k]; if (!type || v === '' || v === null) return;
if (type === 'num') {
if (typeof v === 'number') { out[k] = v; return; }
const s = String(v).trim().replace(/\s/g, '');
if (!/^-?\d+([.,]\d+)?$/.test(s)) throw new Error(`_settings.${k}: "${v}" is not a number (use 0.5 or 0,5)`);
if (/^-?\d{1,3}[.,]\d{3}$/.test(s)) throw new Error(`_settings.${k}: "${v}" is ambiguous; do not use thousands separators (write 1000 or 0.5 / 0,5)`);
out[k] = parseFloat(s.replace(',', '.')); // decimal comma/dot accepted; ambiguous 1.000 / 1,000 is rejected
}
else if (type === 'bool') out[k] = /^(true|yes|1|on|evet|ja)$/i.test(String(v).trim());
else if (type === 'list') out[k] = String(v).split(',').map(s => s.trim()).filter(Boolean);
else out[k] = String(v).trim();
});
return out;
}
function writeSetting_(ss, key, value) {
const sh = ss.getSheetByName(TECH.settings); if (!sh) return;
const last = sh.getLastRow();
const keys = last > 1 ? sh.getRange(2, 1, last - 1, 1).getValues().map(r => String(r[0])) : [];
const i = keys.indexOf(key);
const cell = i >= 0 ? sh.getRange(i + 2, 2) : sh.getRange(last + 1, 2);
if (i < 0) sh.getRange(last + 1, 1).setNumberFormat('@').setValue(key);
cell.setNumberFormat('@').setValue(value);
}
/* ===================== common/seal ===================== */
/* Attribution cell. Warning-only protection (no editor management → no Session dependency, no lock-out of the
running user). Never throws: a report must not fail because a Sheet cell was edited.
Called twice: from openSpreadsheet_ (first tab, before anything else) and at the end of main() on the report tab.
Returns the spreadsheet so the open path depends on it. This is attribution, not DRM: anyone who edits the
source can remove it. */
function seal_(ss, tabName) {
try {
const sheet = (tabName && ss.getSheetByName(tabName)) || ss.getSheets().find(s => !s.getName().startsWith('_')) || ss.getSheets()[0];
const want = ATTRIBUTION;
const range = sheet.getRange('H1:K1');
let cur = ''; try { cur = String(range.getCell(1, 1).getValue()); } catch (e) {}
if (cur !== want) {
try { range.breakApart(); range.merge(); } catch (e) {}
range.getCell(1, 1).setValue(want).setFontWeight('bold').setFontColor('#0F766E').setHorizontalAlignment('center').setNote(want);
}
const has = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).some(p => p.getDescription() === want);
if (!has) range.protect().setDescription(want).setWarningOnly(true);
} catch (e) { Logger.log('Attribution skipped: ' + e); }
return ss;
}
/* ===================== common/notify ===================== */
function htmlShell_(title, subtitle, body, url) {
return '<div style="font:13px Arial,sans-serif;color:#1E293B;max-width:760px">' +
'<div style="background:#1E293B;color:#fff;padding:12px 16px;font-size:15px;font-weight:bold">' + h_(title) + '</div>' +
'<div style="padding:10px 16px;color:#64748B">' + h_(subtitle) + '</div>' + body +
'<div style="padding:12px 16px;color:#94A3B8;font-size:11px">' + (url ? h_(t_('mail.full_report')) + ': <a href="' + h_(url) + '">' + h_(url) + '</a><br>' : '') + h_(ATTRIBUTION) + '</div></div>';
}
function kpiTable_(rows) {
/* rows: [[label, now, prev, delta_html]] */
const th = (s, r) => '<th style="padding:5px 10px;text-align:' + (r ? 'right' : 'left') + '">' + h_(s) + '</th>';
return '<table style="border-collapse:collapse;width:100%"><tr style="background:#F1F5F9;color:#64748B;font-size:11px">' +
th(t_('kpi'), false) + th(t_('kpi.now'), true) + th(t_('kpi.prev'), true) + th('Δ', true) + '</tr>' +
rows.map(r => '<tr><td style="padding:5px 10px">' + h_(r[0]) + '</td><td style="padding:5px 10px;text-align:right;font-weight:bold">' + h_(r[1]) +
'</td><td style="padding:5px 10px;text-align:right;color:#64748B">' + h_(r[2]) + '</td><td style="padding:5px 10px;text-align:right">' + r[3] + '</td></tr>').join('') + '</table>';
}
function delta_(a, b, invert) {
const v = pct_(a, b); if (!isFinite(v)) return '';
const good = invert ? v < 0 : v > 0;
return '<span style="color:' + (good ? '#15803D' : '#B91C1C') + '">' + (v >= 0 ? '▲' : '▼') + ' ' + fmtPct_(Math.abs(v), 0) + '</span>';
}
function sendMail_(to, subject, html, text) {
if (!to) return false;
try { MailApp.sendEmail({ to: to, subject: subject, htmlBody: html, body: text || subject }); return true; }
catch (e) { Logger.log('E-mail failed: ' + e); return false; }
}
function slackText_(s) { return String(s === undefined || s === null ? '' : s).replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>'); }
function sendSlack_(webhook, text) {
if (!webhook) return false;
try {
const res = UrlFetchApp.fetch(webhook, { method: 'post', contentType: 'application/json', muteHttpExceptions: true, payload: JSON.stringify({ text: text + '\n_' + slackText_(ATTRIBUTION) + '_' }) });
const ok = res.getResponseCode() === 200;
if (!ok) Logger.log('Slack returned ' + res.getResponseCode() + ': ' + res.getContentText().slice(0, 200));
return ok;
} catch (e) { Logger.log('Slack failed: ' + e); return false; }
}
/* ===================== common/negatives ===================== */
/* Negative keywords by ID: ad group, campaign, shared lists attached to campaigns, and account level. */
function fetchNegatives_(campaignIds, language, opts) {
opts = opts || {};
const neg = { adGroup: {}, campaign: {}, account: [] };
const item = (text, mt) => ({ words: tokens_(text, language), mt: String(mt || '').toUpperCase() });
const push = (bucket, key, text, mt) => { (bucket[key] = bucket[key] || []).push(item(text, mt)); };
chunks_(campaignIds, 200).forEach(chunk => {
if (opts.adGroupLevel !== false) {
query_(`SELECT ad_group.id, ad_group_criterion.keyword.text, ad_group_criterion.keyword.match_type
FROM ad_group_criterion WHERE ad_group_criterion.negative = TRUE AND ad_group_criterion.type = 'KEYWORD'
AND ad_group_criterion.status != 'REMOVED' AND campaign.id IN (${chunk.join(',')})`)
.forEach(r => push(neg.adGroup, String(r.adGroup.id), r.adGroupCriterion.keyword.text, r.adGroupCriterion.keyword.matchType));
}
query_(`SELECT campaign.id, campaign_criterion.keyword.text, campaign_criterion.keyword.match_type
FROM campaign_criterion WHERE campaign_criterion.negative = TRUE AND campaign_criterion.type = 'KEYWORD'
AND campaign_criterion.status != 'REMOVED' AND campaign.id IN (${chunk.join(',')})`)
.forEach(r => push(neg.campaign, String(r.campaign.id), r.campaignCriterion.keyword.text, r.campaignCriterion.keyword.matchType));
});
const setToCampaigns = {};
chunks_(campaignIds, 200).forEach(chunk => {
query_(`SELECT campaign.id, shared_set.id FROM campaign_shared_set
WHERE shared_set.type = 'NEGATIVE_KEYWORDS' AND campaign_shared_set.status = 'ENABLED' AND shared_set.status = 'ENABLED'
AND campaign.id IN (${chunk.join(',')})`)
.forEach(r => (setToCampaigns[String(r.sharedSet.id)] = setToCampaigns[String(r.sharedSet.id)] || []).push(String(r.campaign.id)));
});
const setIds = Object.keys(setToCampaigns);
chunks_(setIds, 200).forEach(chunk => {
query_(`SELECT shared_set.id, shared_criterion.keyword.text, shared_criterion.keyword.match_type
FROM shared_criterion WHERE shared_criterion.type = 'KEYWORD' AND shared_set.id IN (${chunk.join(',')})`)
.forEach(r => setToCampaigns[String(r.sharedSet.id)].forEach(cid => push(neg.campaign, cid, r.sharedCriterion.keyword.text, r.sharedCriterion.keyword.matchType)));
});
/* Account-level negatives are a shared set of type ACCOUNT_LEVEL_NEGATIVE_KEYWORDS (customer_negative_criterion has no
keyword field; it only points at that list). */
try {
query_(`SELECT shared_criterion.keyword.text, shared_criterion.keyword.match_type
FROM shared_criterion WHERE shared_set.type = 'ACCOUNT_LEVEL_NEGATIVE_KEYWORDS' AND shared_criterion.type = 'KEYWORD' AND shared_set.status = 'ENABLED'`)
.forEach(r => neg.account.push(item(r.sharedCriterion.keyword.text, r.sharedCriterion.keyword.matchType)));
} catch (e) { Logger.log('Account-level negatives not readable: ' + e); neg.accountError = String(e); }
return neg;
}
function isBlocked_(tok, neg, campaignId, adGroupId) {
return blocked_(tok, neg.account) || blocked_(tok, neg.campaign[campaignId]) || (adGroupId ? blocked_(tok, neg.adGroup[adGroupId]) : false);
}
/* ===================== common/metrics ===================== */
/* Standard metric block for tables: header / row / number formats, all driven by T and one metric bag. */
const M_KEYS = ['impressions', 'clicks', 'ctr', 'cost', 'cpc', 'conversions', 'value', 'cr', 'cpa', 'roas'];
const M_FMT = ['#,##0', '#,##0', '0.00%', '#,##0.00', '#,##0.00', '#,##0.0', '#,##0.00', '0.00%', '#,##0.00', '0.00'];
function mHead_(keys) { return (keys || M_KEYS).map(k => t_('m.' + k)); }
function mRow_(m, keys) { return (keys || M_KEYS).map(k => nz_(m[k])); }
function mFmt_(keys) { return (keys || M_KEYS).map(k => M_FMT[M_KEYS.indexOf(k)] || null); }
/* ===================== common/ngram ===================== */
/* Shared n-gram engine for N-Gram Lens and PMax N-Gram Lens. terms: [{campaignId, campaign, adGroupId, term, source, categories?, m}] */
function ngramAnalyse_(terms, neg, opts) {
const lang = opts.language, stop = new Set((opts.stopwords || []).map(w => normalize_(w, lang)));
const brand = (opts.brand_terms || []).map(b => tokens_(b, lang)).filter(t => t.length);
const acc = {}, camp = {}, cat = {}, wc = {}, totals = zeroM_(), split = { brand: zeroM_(), generic: zeroM_() };
const blockedKeys = new Set(); let excluded = 0, queries = 0;
const bump = (map, key, seed, m) => { const a = map[key] = map[key] || Object.assign(zeroM_(), { queries: 0 }, seed); Object.assign(a, addM_(a, m)); a.queries++; };
terms.forEach(t => {
const tok = tokens_(t.term, lang); if (!tok.length) return;
if (neg && isBlocked_(tok, neg, t.campaignId, t.adGroupId)) { excluded++; blockedKeys.add(t.campaignId + '|' + t.term.toLowerCase()); return; }
queries++; Object.assign(totals, addM_(totals, t.m));
const isBrand = brand.length && brand.some(b => hasSubseq_(tok, b));
const side = isBrand ? split.brand : split.generic; Object.assign(side, addM_(side, t.m)); side.queries = (side.queries || 0) + 1;
bump(wc, tok.length > 6 ? '7+' : String(tok.length), { words: tok.length > 6 ? '7+' : String(tok.length) }, t.m);
const seen = new Set();
for (let n = opts.min_ngram; n <= opts.max_ngram && n <= tok.length; n++) for (let i = 0; i + n <= tok.length; i++) {
const words = tok.slice(i, i + n); if (n === 1 && stop.has(words[0])) continue;
const g = words.join(' '), k = n + '|' + g; if (seen.has(k)) continue; seen.add(k);
bump(acc, k, { n: n, gram: g }, t.m);
bump(camp, t.campaignId + '|' + k, { n: n, gram: g, campaign: t.campaign, source: t.source }, t.m);
(t.categories || []).forEach(c => bump(cat, t.campaignId + '|' + c + '|' + k, { n: n, gram: g, campaign: t.campaign, category: c }, t.m));
}
});
derive_(totals); derive_(split.brand); derive_(split.generic);
const th = opts.thresholds, keep = a => a.queries >= th.query_count && a.impressions >= th.impressions && a.clicks >= th.clicks && (a.cost || 0) >= (th.cost || 0);
const rank = opts.rankBy === 'clicks' ? ((x, y) => y.clicks - x.clicks || y.impressions - x.impressions || x.gram.localeCompare(y.gram))
: ((x, y) => y.cost - x.cost || y.impressions - x.impressions || x.gram.localeCompare(y.gram));
return { queries, excluded, totals, split, blockedKeys,
account: Object.values(acc).map(derive_).filter(keep).sort(rank),
campaign: Object.values(camp).map(derive_).filter(keep).sort((x, y) => x.campaign.localeCompare(y.campaign) || rank(x, y)),
category: Object.values(cat).map(derive_).filter(keep).sort((x, y) => x.campaign.localeCompare(y.campaign) || x.category.localeCompare(y.category) || rank(x, y)),
wordCount: ['1', '2', '3', '4', '5', '6', '7+'].map(k => derive_(Object.assign({ words: k, queries: 0 }, zeroM_(), wc[k] || {}))) };
}
const NG_KEYS = ['queries'].concat(M_KEYS);
function ngHead_(keys) { return (keys || NG_KEYS).map(k => k === 'queries' ? t_('col.queries') : t_('m.' + k)); }
function ngFmt_(keys) { return (keys || NG_KEYS).map(k => k === 'queries' ? '#,##0' : M_FMT[M_KEYS.indexOf(k)] || null); }
function ngRow_(a, keys) { return (keys || NG_KEYS).map(k => nz_(a[k])); }
function writeNgrams_(ss, tabKey, rows, segKeys, keys, cap) {
const head = segKeys.map(k => t_('col.' + k)).concat([t_('col.n'), t_('col.phrase')]).concat(ngHead_(keys));
const fmt = segKeys.map(() => '@').concat(['0', '@']).concat(ngFmt_(keys));
table_(ss.getSheetByName(t_('tab.' + tabKey)), 1, head, rows.map(r => segKeys.map(k => r[k]).concat([r.n, r.gram]).concat(ngRow_(r, keys))), fmt, cap);
}
/**
* @name Account Summary
* @overview Daily account-level metrics appended to one Report tab, plus a
* yesterday / two days ago / a week ago comparison e-mail. Self-contained:
* the script creates its own tabs and header row — no template, no named ranges.
* Schedule: Daily, 04:00 account time or later.
*/
const CONFIG = {
spreadsheet_url: 'YOUR_SPREADSHEET_URL',
email: '', // comma-separated; blank = no e-mail. Can be overridden in _settings.
backfill_days: 1, // first run: how many past days to fill (max 90)
refresh_days: 30, // re-query the last N days every run so late conversions are picked up (match your longest conversion window)
account_id: '', // only when running from a manager account
fields: [ // customer-resource metrics; the header row is generated from T
'metrics.cost_micros', 'metrics.average_cpc', 'metrics.ctr', 'metrics.search_impression_share',
'metrics.impressions', 'metrics.clicks', 'metrics.conversions', 'metrics.conversions_value',
'metrics.cost_per_conversion', 'metrics.conversions_from_interactions_rate', 'metrics.all_conversions',
'metrics.view_through_conversions', 'metrics.search_budget_lost_impression_share',
'metrics.search_rank_lost_impression_share', 'metrics.search_top_impression_share',
'metrics.search_absolute_top_impression_share', 'metrics.average_cpm', 'metrics.interactions',
'metrics.interaction_rate', 'metrics.invalid_clicks'
]
};
const SETTINGS_SCHEMA = { email: 'str', last_check: 'str' };
const MICROS = /(_micros|average_cpc|average_cpm|cost_per_conversion|average_cost|average_cpv)$/;
const SHARE = /impression_share$/;
const RATE = /(_rate|\.ctr)$/;
function main() {
selectAccount_(CONFIG.account_id);
const ss = openSpreadsheet_(CONFIG.spreadsheet_url);
const tz = tz_(), cid = AdsApp.currentAccount().getCustomerId(), currency = AdsApp.currentAccount().getCurrencyCode();
ss.setSpreadsheetTimeZone(tz);
ensureTabs_(ss, [{ name: t_('tab.report'), aliases: T['alias.report'] }, { name: TECH.settings, aliases: T['alias.settings'] }]);
ensureSettings_(ss, [['email', CONFIG.email, t_('settings.email')], ['last_check', '', t_('settings.last_check')]]);
const cfg = readSettings_(ss, CONFIG, SETTINGS_SCHEMA);
const sheet = ss.getSheetByName(t_('tab.report'));
const header = [t_('col.date')].concat(CONFIG.fields.map(f => t_('f.' + f.replace('metrics.', ''))));
const TOP = 2; // row 1 = title + attribution (H1:K1), row 2 = header, data from row 3
if (isLegacyTemplate_(ss, sheet)) throw new Error('This Sheet uses the old Google Account Summary template layout (named ranges, data from B6). Point the script at a new empty Sheet; the old report stays intact.');
ensureSize_(sheet, TOP + 1, header.length);
if (sheet.getLastRow() < TOP) { sheet.getRange('A1').setValue(t_('title')).setFontWeight('bold').setFontSize(13); styleHeader_(sheet.getRange(TOP, 1, 1, header.length).setValues([header])); sheet.setFrozenRows(TOP); }
const yesterday = window_(1, 0, tz).end;
const refresh = Math.max(1, Math.round(CONFIG.refresh_days) || 1), backfill = Math.min(90, Math.max(1, Math.round(CONFIG.backfill_days) || 1));
const firstNew = cfg.last_check ? shiftDay_(dateStrOf_(cfg.last_check, tz), 1) : shiftDay_(yesterday, -(backfill - 1));
const from = cfg.last_check ? [firstNew, shiftDay_(yesterday, -(refresh - 1))].sort()[0] : firstNew; // new days + refresh window, one query
const byDate = {};
query_(`SELECT segments.date, ${CONFIG.fields.join(', ')} FROM customer WHERE segments.date BETWEEN '${from}' AND '${yesterday}'`).forEach(r => byDate[r.segments.date] = r.metrics || {});
const existing = {}; if (sheet.getLastRow() > TOP) sheet.getRange(TOP + 1, 1, sheet.getLastRow() - TOP, 1).getValues().forEach((r, i) => existing[dateStrOf_(r[0], tz)] = TOP + 1 + i);
const rows = [], fmts = ['@'].concat(CONFIG.fields.map(fmtFor_)); let updated = 0;
for (let d = from; d <= yesterday; d = shiftDay_(d, 1)) {
const row = [d].concat(CONFIG.fields.map(f => valueOf_(f, byDate[d] || {})));
if (existing[d]) { sheet.getRange(existing[d], 2, 1, header.length - 1).setValues([row.slice(1)]); updated++; } // late conversions: refresh in place
else rows.push(row);
}
if (rows.length) { appendRows_(sheet, rows, fmts); sheet.getRange(TOP + 1, 1, sheet.getLastRow() - TOP, header.length).sort({ column: 1, ascending: true }); }
writeSetting_(ss, 'last_check', yesterday);
if (rows.length && cfg.email) sendSummary_(cfg.email, cid, currency, tz, ss.getUrl(), byDate);
seal_(ss, t_('tab.report'));
Logger.log(`${rows.length} day(s) added, ${updated} refreshed, up to ${yesterday}.`);
}
function isLegacyTemplate_(ss, sheet) {
try { if (ss.getRangeByName && (ss.getRangeByName('last_check') || ss.getRangeByName('account_id_report'))) return true; } catch (e) {}
return String(sheet.getRange(1, 1).getValue()) === '' && sheet.getLastRow() >= 5 && /^(Date|Tarih|Datum)$/i.test(String(sheet.getRange(5, 2).getValue()));
}
function fmtFor_(f) { return SHARE.test(f) || RATE.test(f) ? '0.00%' : MICROS.test(f) ? '#,##0.00' : '#,##0.##'; }
function rowFor_(byDate, d) {
if (byDate[d]) return byDate[d];
const rows = query_(`SELECT segments.date, ${CONFIG.fields.join(', ')} FROM customer WHERE segments.date BETWEEN '${d}' AND '${d}'`);
return (byDate[d] = rows.length ? rows[0].metrics || {} : {});
}
/* Higher is worse for these: colour the change the other way round. */
const INVERT = /(cost_micros|average_cpc|average_cpm|cost_per_conversion|lost_impression_share|invalid_clicks)$/;
function camel_(f) { return f.replace('metrics.', '').replace(/_([a-z])/g, (m, c) => c.toUpperCase()); }
/* Numbers stay numbers in the sheet; the number format does the display. Impression share < 0.1 is returned by the API as 0.0999 → shown as "<10%" only in e-mail. */
function valueOf_(f, m) { const v = m[camel_(f)]; if (v === undefined || v === null || v === '') return ''; return MICROS.test(f) ? num_(v) / 1e6 : num_(v); }
function display_(f, v) {
if (v === '' || !isFinite(v)) return '–';
if (SHARE.test(f)) return v <= 0.0999 ? '<10%' : fmtPct_(v, 1);
if (RATE.test(f)) return fmtPct_(v);
return fmtNum_(v, MICROS.test(f) ? 2 : (Number.isInteger(v) ? 0 : 2));
}
function change_(f, a, b) {
if (typeof a !== 'number' || typeof b !== 'number' || !isFinite(a) || !isFinite(b) || (SHARE.test(f) && (a <= 0.0999 || b <= 0.0999))) return '';
const d = a - b, s = (RATE.test(f) || SHARE.test(f)) ? fmtPct_(Math.abs(d)) : fmtNum_(Math.abs(d), MICROS.test(f) ? 2 : 0);
const good = INVERT.test(f) ? d <= 0 : d >= 0;
return '<span style="font-size:8pt;color:' + (good ? '#38761d' : '#cc0000') + '"> (' + (d >= 0 ? '+' : '−') + s + ')</span>';
}
function sendSummary_(to, cid, currency, tz, url, byDate) {
const y = window_(1, 0, tz).end, d2 = shiftDay_(y, -1), w = shiftDay_(y, -6);
const a = rowFor_(byDate, y), b = rowFor_(byDate, d2), c = rowFor_(byDate, w);
const th = s => '<th style="padding:5px 10px;text-align:right">' + h_(s) + '</th>';
const body = '<table style="border-collapse:collapse;width:100%"><tr style="background:#F1F5F9;color:#64748B;font-size:11px"><th></th>' +
th(t_('mail.yesterday')) + th(t_('mail.two_days_ago')) + th(t_('mail.week_ago')) + '</tr>' +
CONFIG.fields.map(f => { const va = valueOf_(f, a), vb = valueOf_(f, b), vc = valueOf_(f, c);
return '<tr><td style="padding:5px 10px">' + h_(t_('f.' + f.replace('metrics.', ''))) + '</td><td style="padding:5px 10px;text-align:right;font-weight:bold">' + display_(f, va) +
'</td><td style="padding:5px 10px;text-align:right">' + display_(f, vb) + change_(f, va, vb) + '</td><td style="padding:5px 10px;text-align:right">' + display_(f, vc) + change_(f, va, vc) + '</td></tr>'; }).join('') + '</table>';
const subject = t_('mail.subject', { cid: cid, date: y });
sendMail_(to, subject, htmlShell_(t_('title'), `${cid} · ${y} · ${currency}`, body, url), subject + '\n' + url);
}
non-MCC3 SprachenZeitplan: Daily
- Kategorien
- Google Ads