PMax N-Gram Lens — PMax Negative Keyword Script

non-MCCSchedule: Weekly

Script

// pmax-ngram-lens — EN 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 = "pmax-ngram-lens";
const L = {"lang":"en","locale":"en-US","text_lang":"en","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":"This period","kpi.prev":"Previous","yes":"yes","no":"no","mail.full_report":"Full report","mail.open_report":"Open report","note.no_rows":"No rows.","note.truncated":"Showing {shown} of {total} rows (max_rows_written).","note.no_campaigns":"No campaigns with impressions match the current filters.","settings.key":"Key","settings.value":"Value","settings.notes":"Notes","settings.script":"script that owns this Sheet (do not edit)","settings.account":"Google Ads account that owns this Sheet (do not edit)","note.truncated_queries":"⚠ {n} quer(y/ies) hit the read limit — results incomplete; narrow the window","note.account_neg_failed":"⚠ account-level negatives could not be read:","note.skipped_time":"skipped — execution time budget","note.data_error":"data error:","note.failed":"FAILED —","note.no_final_url":"(no final URL)","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"],"m.impressions":"Impr.","m.clicks":"Clicks","m.ctr":"CTR","m.cost":"Cost","m.cpc":"Avg. CPC","m.conversions":"Conv.","m.value":"Conv. value","m.cr":"Conv. rate","m.cpa":"CPA","m.roas":"ROAS","m.aov":"AOV","m.vpc":"Value / click","col.account":"Account","col.period":"Period","col.currency":"Currency","col.date":"Date","col.campaign":"Campaign","col.ad_group":"Ad group","col.n":"N","col.phrase":"Phrase","col.queries":"Queries","col.source":"Source","col.why":"Why","title":"PMax N-Gram Lens — search term analysis","tab.summary":"Summary","tab.account_ngrams":"Account n-grams","tab.campaign_ngrams":"Campaign n-grams","tab.categories":"Categories","tab.brand":"Brand split","tab.suggestions":"Suggestions","tab.terms":"Terms","alias.summary":["Özet","Zusammenfassung"],"alias.account_ngrams":["Hesap n-gramları","Konto-N-Gramme"],"alias.campaign_ngrams":["Kampanya n-gramları","Kampagnen-N-Gramme"],"alias.categories":["Kategoriler","Kategorien","Category n-grams","Kategori n-gramları","Kategorie-N-Gramme"],"alias.brand":["Marka ayrımı","Marken-Split"],"alias.suggestions":["Tavsiyeler","Vorschläge"],"alias.terms":["Terimler","Suchbegriffe"],"col.category":"Category","col.segment":"Segment","col.paste_as":"Paste as","col.wasted_clicks":"Wasted clicks (est.)","col.search_term":"Search term","col.blocked":"Blocked by negative","brand.brand":"Brand","brand.generic":"Generic","uncategorised":"(uncategorised)","sum.pmax_campaigns":"PMax campaigns in scope","sum.queries":"Unique (campaign, term) rows analysed","sum.excluded":"Rows excluded by negatives","sum.sizes":"N-gram sizes","sum.account_ngrams":"Account n-grams (after thresholds)","sum.totals":"Totals","sum.brand_split":"Brand vs generic (share of clicks)","sum.neg_candidates":"Negative candidates","sum.cost_on_negatives":"Cost sitting on negative candidates","sum.notes":"Notes","sugg.negatives_pmax":"Negative candidates — review, then add as phrase negatives to the campaign (never auto-applied)","why.no_conv":"{clicks} clicks, 0 conversions","why.cpa":"CPA {cpa} vs account {acc}","why.cr_low":"Conv. rate {v} vs account {acc}","note.pmax_view":"Source: campaign_search_term_view — real search terms with cost, campaign level.","note.negatives_pmax":"Negatives checked: campaign-level keywords, attached shared lists (by ID), account-level negatives.","note.ngram_counting":"Each n-gram is counted once per query; broad negatives match any word order, phrase contiguous, exact the whole query.","note.categories_off":"Category roll-up is off (CONFIG.include_categories). It needs one extra query per campaign.","note.brand_off":"(set CONFIG.brand_terms to enable the split)"};

/* ===================== 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 PMax N-Gram Lens
 * @overview Performance Max search-term n-gram analysis on campaign_search_term_view (real search terms, with cost).
 *   Optional category roll-up from campaign_search_term_insight (category totals only, never merged with term rows).
 *   Brand vs generic split, campaign-level / shared-list / account-level negative awareness, ranked negative candidates.
 * Schedule: Weekly.
 */
const CONFIG = {
  spreadsheet_url: 'YOUR_SPREADSHEET_URL', account_id: '',
  days: 30, min_ngram: 1, max_ngram: 3,
  campaign_contains: '', campaign_not_contains: '', enabled_campaigns_only: true,
  min_query_impressions: 1, check_negatives: true,
  brand_terms: [], language: L.text_lang, stopwords: [],
  include_categories: false,          // extra query per campaign; can hit RESOURCE_EXHAUSTED on big accounts
  write_raw_terms: true, max_rows_written: 20000,
  thresholds: { query_count: 1, impressions: 20, clicks: 0, cost: 0 },
  suggestions: { negative_min_clicks: 20, negative_max_cr_ratio: 0.25, negative_max_cpa_multiple: 3, list_size: 100 }
};
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, ['summary', 'account_ngrams', 'campaign_ngrams', 'categories', 'brand', 'suggestions', 'terms'].map(k => ({ name: t_('tab.' + k), aliases: T['alias.' + k] })));
  const win = window_(CONFIG.days, 0, tz);
  let w = `campaign.advertising_channel_type = 'PERFORMANCE_MAX'` + (CONFIG.enabled_campaigns_only ? ` AND campaign.status = 'ENABLED'` : ` AND campaign.status IN ('ENABLED','PAUSED')`);
  if (CONFIG.campaign_contains) w += ` AND campaign.name LIKE '%${escLike_(CONFIG.campaign_contains)}%'`;
  if (CONFIG.campaign_not_contains) w += ` AND campaign.name NOT LIKE '%${escLike_(CONFIG.campaign_not_contains)}%'`;
  const campaigns = query_(`SELECT campaign.id, campaign.name FROM campaign WHERE ${w} AND segments.date BETWEEN '${win.start}' AND '${win.end}' AND metrics.impressions > 0`)
    .map(r => ({ id: String(r.campaign.id), name: r.campaign.name }));
  if (!campaigns.length) { writeEmptyScope_(ss); return; }
  const names = {}; campaigns.forEach(c => names[c.id] = c.name);
  const neg = CONFIG.check_negatives ? fetchNegatives_(campaigns.map(c => c.id), CONFIG.language, { adGroupLevel: false }) : null;
  const terms = [], errors = [];
  try { chunks_(campaigns.map(c => c.id), 200).forEach(chunk => query_(`SELECT campaign.id, campaign_search_term_view.search_term,
      metrics.impressions, metrics.clicks, metrics.cost_micros, metrics.conversions, metrics.conversions_value
    FROM campaign_search_term_view WHERE segments.date BETWEEN '${win.start}' AND '${win.end}' AND campaign.id IN (${chunk.join(',')})
      AND metrics.impressions >= ${CONFIG.min_query_impressions}`)
    .forEach(r => { const t = r.campaignSearchTermView && r.campaignSearchTermView.searchTerm; if (t) terms.push({ source: 'PMAX', campaignId: String(r.campaign.id), campaign: names[String(r.campaign.id)], adGroupId: '', term: t, m: rawOf_(r) }); })); }
  catch (e) { errors.push('campaign_search_term_view: FAILED — ' + e); Logger.log(errors[0]); }
  if (neg && neg.accountError) errors.push(t_('note.account_neg_failed') + ' ' + neg.accountError);
  if (TRUNCATED_) errors.push(t_('note.truncated_queries', { n: TRUNCATED_ }));
  const res = ngramAnalyse_(terms, neg, Object.assign({ rankBy: 'cost' }, CONFIG));
  const sugg = suggest_(res.account, res.totals);
  const cats = CONFIG.include_categories ? fetchCategories_(win, campaigns) : null;
  writeSummary_(ss, cid, currency, win, res, sugg, campaigns.length, errors);
  writeNgrams_(ss, 'account_ngrams', res.account, [], null, CONFIG.max_rows_written);
  writeNgrams_(ss, 'campaign_ngrams', res.campaign, ['campaign'], null, CONFIG.max_rows_written);
  writeCategories_(ss, cats);
  writeBrand_(ss, res.split);
  writeSuggestions_(ss, sugg);
  if (CONFIG.write_raw_terms) writeTerms_(ss, terms, res.blockedKeys);
  seal_(ss, t_('tab.summary'));
  Logger.log(`Done: ${res.queries} terms, ${res.excluded} excluded by negatives, ${res.account.length} account n-grams, ${sugg.length} negative candidates.`);
}
function writeEmptyScope_(ss) {
  ['summary','account_ngrams','campaign_ngrams','categories','brand','suggestions','terms'].forEach(k => { const sh = ss.getSheetByName(t_('tab.' + k)); if (sh) sh.getRange(1, 1, sh.getMaxRows(), sh.getMaxColumns()).clearContent(); });
  const sh = ss.getSheetByName(t_('tab.summary')); sh.getRange('A1').setValue(t_('title')).setFontWeight('bold').setFontSize(13); sh.getRange('A3').setValue(t_('note.no_campaigns'));
  seal_(ss, t_('tab.summary')); Logger.log(t_('note.no_campaigns'));
}
/* Category totals (search term insights). Kept separate: they aggregate differently from term rows. */
function fetchCategories_(win, campaigns) {
  const out = [];
  campaigns.forEach(c => { if (timeLeft_() < 120) { out.push({ campaign: c.name, category: '⚠ ' + t_('note.skipped_time'), m: derive_(zeroM_()) }); return; } try {
    query_(`SELECT campaign_search_term_insight.category_label, metrics.impressions, metrics.clicks, metrics.conversions, metrics.conversions_value
      FROM campaign_search_term_insight WHERE segments.date BETWEEN '${win.start}' AND '${win.end}' AND campaign_search_term_insight.campaign_id = ${c.id}`)
      .forEach(r => out.push({ campaign: c.name, category: (r.campaignSearchTermInsight && r.campaignSearchTermInsight.categoryLabel) || t_('uncategorised'), m: derive_(rawOf_(r, { noCost: true })) }));
  } catch (e) { out.push({ campaign: c.name, category: '⚠ ' + e, m: derive_(zeroM_()) }); } });
  return out;
}
function suggest_(rows, tot) {
  const s = CONFIG.suggestions;
  return rows.filter(r => r.clicks >= s.negative_min_clicks && (r.conversions === 0 || (isFinite(tot.cr) && r.cr < tot.cr * s.negative_max_cr_ratio) || (isFinite(tot.cpa) && isFinite(r.cpa) && r.cpa > tot.cpa * s.negative_max_cpa_multiple)))
    .map(r => Object.assign({ reason: r.conversions === 0 ? t_('why.no_conv', { clicks: r.clicks }) : (isFinite(r.cpa) && isFinite(tot.cpa) && r.cpa > tot.cpa * s.negative_max_cpa_multiple) ? t_('why.cpa', { cpa: fmtNum_(r.cpa, 2), acc: fmtNum_(tot.cpa, 2) }) : t_('why.cr_low', { v: fmtPct_(r.cr), acc: fmtPct_(tot.cr) }),
      wasted: isFinite(tot.cr) && tot.cr > 0 ? Math.max(0, r.clicks - r.conversions / tot.cr) : r.clicks }, r))
    .sort((x, y) => y.cost - x.cost || y.wasted - x.wasted).slice(0, s.list_size);
}
function writeCategories_(ss, cats) {
  const sh = ss.getSheetByName(t_('tab.categories'));
  const keys = ['impressions', 'clicks', 'ctr', 'conversions', 'value', 'cr'];
  if (!cats) { sh.clear(); sh.getRange('A1').setValue(t_('note.categories_off')); return; }
  table_(sh, 1, [t_('col.campaign'), t_('col.category')].concat(mHead_(keys)), cats.sort((x, y) => x.campaign.localeCompare(y.campaign) || y.m.clicks - x.m.clicks).map(c => [c.campaign, c.category].concat(mRow_(c.m, keys))), ['@', '@'].concat(mFmt_(keys)), CONFIG.max_rows_written);
}
function writeBrand_(ss, split) {
  const rows = [[t_('brand.brand')].concat(ngRow_(split.brand)), [t_('brand.generic')].concat(ngRow_(split.generic))];
  table_(ss.getSheetByName(t_('tab.brand')), 1, [t_('col.segment')].concat(ngHead_()), rows, ['@'].concat(ngFmt_()));
  if (!CONFIG.brand_terms.length) ss.getSheetByName(t_('tab.brand')).getRange(5, 1).setValue(t_('note.brand_off'));
}
function writeSuggestions_(ss, sugg) {
  const sh = ss.getSheetByName(t_('tab.suggestions')); sh.clear();
  sh.getRange('A1').setValue(t_('sugg.negatives_pmax')).setFontWeight('bold').setFontColor('#B91C1C');
  table_(sh, 2, [t_('col.n'), t_('col.phrase'), t_('col.paste_as'), t_('col.why'), t_('col.wasted_clicks')].concat(ngHead_()),
    sugg.map(r => [r.n, r.gram, `"${r.gram}"`, r.reason, Math.round(r.wasted)].concat(ngRow_(r))), ['0', '@', '@', '@', '#,##0'].concat(ngFmt_()));
}
function writeTerms_(ss, terms, blocked) {
  const keys = M_KEYS;
  table_(ss.getSheetByName(t_('tab.terms')), 1, [t_('col.campaign'), t_('col.search_term'), t_('col.blocked')].concat(mHead_(keys)),
    terms.slice().sort((x, y) => y.m.cost - x.m.cost || y.m.clicks - x.m.clicks).map(t => [t.campaign, t.term, blocked.has(t.campaignId + '|' + t.term.toLowerCase()) ? t_('yes') : ''].concat(mRow_(derive_(Object.assign({}, t.m)), keys))),
    ['@', '@', '@'].concat(mFmt_(keys)), CONFIG.max_rows_written);
}
function writeSummary_(ss, cid, currency, win, res, sugg, nCamp, errors) {
  const sh = ss.getSheetByName(t_('tab.summary')), t = res.totals, b = res.split;
  sh.getRange(2, 1, Math.max(1, sh.getMaxRows() - 1), Math.max(1, sh.getMaxColumns())).clear();
  sh.getRange('A1').setValue(t_('title')).setFontWeight('bold').setFontSize(13);
  const R = (label, v, fmt) => ({ r: [label, v], fmt: fmt }), H = label => ({ r: [label, ''], head: true });
  const items = [R(t_('col.account'), cid), R(t_('col.period'), win.label), R(t_('col.currency'), currency), R(t_('sum.pmax_campaigns'), nCamp),
    R(t_('sum.queries'), res.queries), R(t_('sum.excluded'), res.excluded), R(t_('sum.sizes'), `${CONFIG.min_ngram}–${CONFIG.max_ngram}`), R(t_('sum.account_ngrams'), res.account.length),
    null, H(t_('sum.totals')), R(t_('m.impressions'), t.impressions, '#,##0'), R(t_('m.clicks'), t.clicks, '#,##0'), R(t_('m.ctr'), nz_(t.ctr), '0.00%'), R(t_('m.cost'), t.cost, '#,##0.00'),
    R(t_('m.conversions'), t.conversions, '#,##0.0'), R(t_('m.value'), t.value, '#,##0.00'), R(t_('m.cr'), nz_(t.cr), '0.00%'), R(t_('m.cpa'), nz_(t.cpa), '#,##0.00'), R(t_('m.roas'), nz_(t.roas), '0.00'),
    null, H(t_('sum.brand_split')), R(t_('brand.brand'), t.clicks ? b.brand.clicks / t.clicks : 0, '0.0%'), R(t_('brand.generic'), t.clicks ? b.generic.clicks / t.clicks : 0, '0.0%'),
    null, H(t_('tab.suggestions')), R(t_('sum.neg_candidates'), sugg.length), R(t_('sum.cost_on_negatives'), sugg.reduce((s, r) => s + r.cost, 0), '#,##0.00'),
    null, H(t_('sum.notes')), R(t_('note.pmax_view'), ''), R(t_('note.negatives_pmax'), ''), R(t_('note.ngram_counting'), '')].concat(errors.map(e => R('⚠ ' + e, '')));
  sh.getRange(3, 1, items.length, 1).setNumberFormat('@');
  items.forEach((it, i) => { if (!it) return; const f = it.fmt || (typeof it.r[1] === 'string' ? '@' : null); if (f) sh.getRange(3 + i, 2).setNumberFormat(f); });
  sh.getRange(3, 1, items.length, 2).setValues(items.map(i => i ? i.r : ['', '']));
  items.forEach((it, i) => { if (it && it.head) sh.getRange(3 + i, 1, 1, 2).setFontWeight('bold').setBackground('#F1F5F9'); });
  sh.setColumnWidths(1, 1, 360); sh.setColumnWidths(2, 1, 220);
}

PMax N-Gram Lens is the Performance Max specialist of the suite. It reads the real search terms of PMax campaigns — with cost — from campaign_search_term_view, breaks them into n-grams, splits brand from generic traffic and ranks negative candidates you can paste as campaign-level phrase negatives.

How it works

Enabled (optionally also paused) PMax campaigns with impressions in the window are selected. Terms are queried in chunks of 200 campaigns with impressions, clicks, cost, conversions and value; there are no keyword-related segments on this view, so nothing is mixed in. Negatives are checked at campaign level, on attached shared lists (by ID) and at account level. If brand_terms is set, every term is classified as brand or generic and the click share is reported. The optional category roll-up from campaign_search_term_insight (include_categories) writes category totals only and is never merged with term rows because it needs one extra query per campaign. Negative candidates are ranked by wasted clicks, conversion-rate ratio and CPA multiple.

What it writes to the Sheet

Summary — PMax campaigns in scope, unique (campaign, term) rows analysed and excluded by negatives, n-gram sizes, totals, brand vs generic click share, negative candidate count, cost sitting on negative candidates, notes and ⚠ warnings.
Account n-grams, Campaign n-grams — phrase-level metrics account-wide and per campaign.
Categories — 8-column category totals, only when include_categories is true.
Brand split — brand vs generic segment metrics.
Suggestions — negative candidates with campaign, “Paste as”, wasted clicks (est.) and “Why”.
Terms — raw (campaign, term) rows with a “Blocked by negative” column, when write_raw_terms is true.

Setup

Create a new, empty Google Sheet with the Google account you will authorise the script with and copy its URL. Do not reuse a Sheet from another script — each Sheet is bound to one script and one Google Ads account.
In Google Ads open Tools → Scripts, create a new script, paste the whole code and give it a name.
Set CONFIG.spreadsheet_url to the Sheet URL. If you run the script from a manager (MCC) account, also set CONFIG.account_id to the client account ID (e.g. 123-456-7890); without it the script stops with a clear error.
Optional: fill CONFIG.email (comma-separated recipients) and/or CONFIG.slack_webhook. Keep the webhook in the code only — it is never written to the Sheet.
Authorise the script, run Preview once to check the log, then Run. The first run creates the tabs, the header rows and the _settings tab.
Schedule it: weekly, Monday at 04:00 account time or later.
Optional: download the Excel dashboard (button above), open its Read me tab and paste the Sheet tabs as described — the column layout matches the script output one-to-one.

Parameters

Parameter	Description	Default
`spreadsheet_url`	URL of the empty Google Sheet the script writes to.	`'YOUR_SPREADSHEET_URL'`
`account_id`	Client account ID; required only when running from a manager (MCC) account.	`''`
`days`	Length of the analysis window in days.	`30`
`min_ngram / max_ngram`	Smallest and largest n-gram size (1 = single words, 3 = three-word phrases).	`1 / 3`
`campaign_contains`	Only campaigns whose name contains this text.	`''`
`campaign_not_contains`	Exclude campaigns whose name contains this text.	`''`
`enabled_campaigns_only`	true = enabled campaigns only; false = enabled and paused.	`true`
`min_query_impressions`	Minimum impressions for a search term to be read.	`1`
`check_negatives`	Match terms against existing negatives (ad group, campaign, shared list, account level).	`true`
`brand_terms`	Brand words or phrases; empty = brand split disabled.	`[]`
`language`	Tokeniser language; defaults to the build language (en / tr / de).	`L.text_lang`
`stopwords`	Extra words to ignore when building n-grams.	`[]`
`include_categories`	Category roll-up from campaign_search_term_insight; one extra query per campaign, can hit RESOURCE_EXHAUSTED on large accounts.	`false`
`write_raw_terms`	Write the Terms tab with raw rows.	`true`
`max_rows_written`	Row cap per table; when exceeded a truncation note is added.	`20000`
`thresholds`	Minimum query count / impressions / clicks / cost for an n-gram to be listed.	`1 / 20 / 0 / 0`
`suggestions`	negative_min_clicks, negative_max_cr_ratio, negative_max_cpa_multiple, list_size.	`20 / 0.25 / 3 / 100`

All values live in the CONFIG block at the top of the script; keys that also exist in the _settings tab can be changed there.

The _settings tab

On the first run the script creates a _settings tab (Key / Value / Notes). Values there override the matching CONFIG entries, so day-to-day tuning happens in the Sheet without editing code. Numbers must be written without thousands separators and with at most two decimals (0.5 or 0,5). The rows script and account mark the Sheet’s owner: if another script or another account opens the same Sheet, it stops before writing anything. Internal tabs starting with an underscore (_settings, _history, _snapshot, _state, _baseline) are language-independent, so switching between the EN/TR/DE builds keeps the stored state.

Notifications

This script has no e-mail or Slack option; the Sheet and the dashboard are the output.

Notes and limits

Negatives are never applied automatically — review the Suggestions tab, then add them as phrase negatives to the campaign.
If the term query fails or hits the read limit, the Summary shows a ⚠ line instead of silently returning less data.
Every report tab carries a small attribution cell (H1:K1, warning-only protection) and the code header keeps a copyright line; both may be removed by anyone who edits the source.

Requirements

Google Ads Scripts access on the account (or on a manager account with CONFIG.account_id). An empty Google Sheet you own — the script creates its own tabs. Optional: e-mail recipients and a Slack incoming-webhook URL for notifications. No third-party API, no AI layer.