02 hourly lite google ads script

// IMPORTANT: Paste this file as-is into ONE script project.
// Do NOT wrap it with `function main() { ... }` and do NOT mix 01/02/03 in the same project.

const CONFIG = {
  SPREADSHEET_URL:
    'https://docs.google.com/spreadsheets/d/1ak5fsF8J4kZYh_IQdTrMI0Goeus9ToV6j9kd8xJ_suI/edit',
  MAX_RUNTIME_MINUTES: 20,
  STATUS_SHEET: '00_STATUS_LITE',
  LOG_SHEET: '00_RUN_LOG_LITE',
  SUMMARY_SHEET: '90_LITE_SUMMARY',
  ENABLE_FEISHU: true,
  FEISHU_WEBHOOK_URL: '',
  FEISHU_TOKEN: '',
};

function main() {
  const ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
  const account = AdsApp.currentAccount();
  const tz = account.getTimeZone();
  const startedAt = new Date();
  const runId = Utilities.formatDate(startedAt, tz, 'yyyyMMdd-HHmmss');

  ensureLiteLogHeader(ss);
  writeLiteStatus(ss, runId, account, tz, 'RUNNING', '');

  const jobs = buildLiteJobs();
  for (let i = 0; i < jobs.length; i++) {
    if (elapsedMinutes(startedAt) >= CONFIG.MAX_RUNTIME_MINUTES) {
      appendLiteLog(ss, [
        runId,
        formatDate(new Date(), tz),
        String(account.getCustomerId()),
        safeString(account.getName()),
        'TIMEOUT_GUARD',
        jobs[i].tab,
        0,
        0,
        'hourly-lite hit timeout guard',
      ]);
      writeLiteStatus(ss, runId, account, tz, 'PAUSED_TIMEOUT', jobs[i].tab);
      return;
    }

    runLiteExportJob(ss, account, tz, runId, jobs[i]);
  }

  let summary = null;
  try {
    summary = buildLiteSummary(ss, runId, account, tz);
  } catch (e) {
    appendLiteLog(ss, [
      runId,
      formatDate(new Date(), tz),
      String(account.getCustomerId()),
      safeString(account.getName()),
      'ERROR',
      CONFIG.SUMMARY_SHEET,
      0,
      0,
      truncate(String(e), 1000),
    ]);
  }

  writeLiteStatus(ss, runId, account, tz, 'FINISHED', '');

  if (CONFIG.ENABLE_FEISHU && summary) {
    try {
      sendFeishuText(buildLiteFeishuText(summary));
    } catch (e) {
      appendLiteLog(ss, [
        runId,
        formatDate(new Date(), tz),
        String(account.getCustomerId()),
        safeString(account.getName()),
        'ERROR',
        'FEISHU',
        0,
        0,
        truncate(String(e), 1000),
      ]);
    }
  }
}

function buildLiteJobs() {
  return [
    {
      tab: 'L01_customer',
      minCols: 5,
      query: `
        SELECT
          customer.id,
          customer.descriptive_name,
          customer.currency_code,
          customer.time_zone,
          customer.status
        FROM customer
      `,
    },
    {
      tab: 'L10_campaign_today',
      minCols: 9,
      query: `
        SELECT
          campaign.id,
          campaign.name,
          campaign.status,
          metrics.impressions,
          metrics.clicks,
          metrics.ctr,
          metrics.average_cpc,
          metrics.cost_micros,
          metrics.conversions
        FROM campaign
        WHERE campaign.status != REMOVED
          AND campaign.advertising_channel_type = SEARCH
          AND segments.date DURING TODAY
      `,
    },
    {
      tab: 'L11_campaign_yesterday',
      minCols: 9,
      query: `
        SELECT
          campaign.id,
          campaign.name,
          campaign.status,
          metrics.impressions,
          metrics.clicks,
          metrics.ctr,
          metrics.average_cpc,
          metrics.cost_micros,
          metrics.conversions
        FROM campaign
        WHERE campaign.status != REMOVED
          AND campaign.advertising_channel_type = SEARCH
          AND segments.date DURING YESTERDAY
      `,
    },
    {
      tab: 'L12_campaign_7d_nonzero',
      minCols: 9,
      query: `
        SELECT
          campaign.id,
          campaign.name,
          campaign.status,
          metrics.impressions,
          metrics.clicks,
          metrics.ctr,
          metrics.average_cpc,
          metrics.cost_micros,
          metrics.conversions
        FROM campaign
        WHERE campaign.status != REMOVED
          AND campaign.advertising_channel_type = SEARCH
          AND segments.date DURING LAST_7_DAYS
          AND (
            metrics.impressions > 0
            OR metrics.clicks > 0
            OR metrics.cost_micros > 0
          )
      `,
    },
    {
      tab: 'L13_ad_policy_status',
      minCols: 8,
      query: `
        SELECT
          campaign.id,
          campaign.name,
          ad_group.id,
          ad_group.name,
          ad_group_ad.ad.id,
          ad_group_ad.policy_summary.approval_status,
          ad_group_ad.policy_summary.review_status,
          ad_group_ad.status
        FROM ad_group_ad
        WHERE ad_group_ad.status != REMOVED
      `,
    },
    {
      tab: 'L20_keyword_country_today',
      minCols: 11,
      query: `
        SELECT
          campaign.id,
          campaign.name,
          ad_group.id,
          ad_group.name,
          ad_group_criterion.criterion_id,
          ad_group_criterion.keyword.text,
          ad_group_criterion.keyword.match_type,
          ad_group_criterion.final_urls,
          metrics.clicks,
          metrics.average_cpc,
          metrics.cost_micros
        FROM keyword_view
        WHERE campaign.status != REMOVED
          AND ad_group_criterion.status != REMOVED
          AND segments.date DURING TODAY
          AND metrics.clicks > 0
      `,
    },
  ];
}

function runLiteExportJob(ss, account, tz, runId, job) {
  const t0 = new Date();
  const sh = prepareLiteSheet(ss, job.tab, job.minCols || 8);
  try {
    AdsApp.report(job.query).exportToSheet(sh);
    const rows = Math.max(sh.getLastRow() - 1, 0);
    trimLiteSheet(sh, job.minCols || 8);

    appendLiteLog(ss, [
      runId,
      formatDate(new Date(), tz),
      String(account.getCustomerId()),
      safeString(account.getName()),
      'OK',
      job.tab,
      rows,
      secondsBetween(t0, new Date()),
      '',
    ]);
  } catch (e) {
    const fallbackQuery = getLiteFallbackQuery(job, e);
    if (fallbackQuery) {
      try {
        AdsApp.report(fallbackQuery).exportToSheet(sh);
        const fallbackRows = Math.max(sh.getLastRow() - 1, 0);
        trimLiteSheet(sh, job.minCols || 8);
        appendLiteLog(ss, [
          runId,
          formatDate(new Date(), tz),
          String(account.getCustomerId()),
          safeString(account.getName()),
          'OK_FALLBACK',
          job.tab,
          fallbackRows,
          secondsBetween(t0, new Date()),
          truncate(`fallback after error: ${String(e)}`, 1000),
        ]);
        return;
      } catch (fallbackErr) {
        appendLiteLog(ss, [
          runId,
          formatDate(new Date(), tz),
          String(account.getCustomerId()),
          safeString(account.getName()),
          'ERROR',
          job.tab,
          0,
          secondsBetween(t0, new Date()),
          truncate(`primary=${String(e)} | fallback=${String(fallbackErr)}`, 1000),
        ]);
        return;
      }
    }

    appendLiteLog(ss, [
      runId,
      formatDate(new Date(), tz),
      String(account.getCustomerId()),
      safeString(account.getName()),
      'ERROR',
      job.tab,
      0,
      secondsBetween(t0, new Date()),
      truncate(String(e), 1000),
    ]);
  }
}

function getLiteFallbackQuery(job, err) {
  if (!job || job.tab !== 'L12_campaign_7d_nonzero') return '';
  const msg = safeString(err).toUpperCase();
  if (
    msg.indexOf('BAD_FIELD_NAME') < 0 &&
    msg.indexOf('UNRECOGNIZED') < 0 &&
    msg.indexOf('INVALID') < 0
  ) {
    return '';
  }
  return `
    SELECT
      campaign.id,
      campaign.name,
      campaign.status,
      metrics.impressions,
      metrics.clicks,
      metrics.ctr,
      metrics.average_cpc,
      metrics.cost_micros,
      metrics.conversions
    FROM campaign
    WHERE campaign.status != REMOVED
      AND campaign.advertising_channel_type = SEARCH
      AND segments.date DURING LAST_7_DAYS
  `;
}

function buildLiteSummary(ss, runId, account, tz) {
  const out = prepareLiteSheet(ss, CONFIG.SUMMARY_SHEET, 3);
  out.getRange(1, 1, 1, 3).setValues([['metric', 'value', 'note']]);

  const today = mustGetSheet(ss, 'L10_campaign_today');
  const tVals = today.getDataRange().getValues();
  const th = tVals[0];
  const iTImpr = findCol(th, 'metrics.impressions');
  const iTClk = findCol(th, 'metrics.clicks');
  const iTCost = findCol(th, 'metrics.cost_micros');
  const iTConv = findCol(th, 'metrics.conversions');

  let todayCampaigns = 0;
  let todayNonzeroCampaigns = 0;
  let todayImpr = 0;
  let todayClk = 0;
  let todayCost = 0;
  let todayConv = 0;

  for (let i = 1; i < tVals.length; i++) {
    const row = tVals[i];
    const impr = toNumber(row[iTImpr]);
    const clk = toNumber(row[iTClk]);
    const cost = toNumber(row[iTCost]);
    const conv = toNumber(row[iTConv]);

    todayCampaigns++;
    if (impr > 0 || clk > 0 || cost > 0) todayNonzeroCampaigns++;
    todayImpr += impr;
    todayClk += clk;
    todayCost += cost;
    todayConv += conv;
  }

  const policy = mustGetSheet(ss, 'L13_ad_policy_status');
  const pVals = policy.getDataRange().getValues();
  const ph = pVals[0];
  const iAppr = findCol(ph, 'ad_group_ad.policy_summary.approval_status');
  const iCamp = findCol(ph, 'campaign.id');
  const iAdg = findCol(ph, 'ad_group.id');

  let adCount = 0;
  let disapproved = 0;
  let approvedLimited = 0;
  const campaignsWithAds = new Set();
  const adgroupsWithAds = new Set();

  for (let i = 1; i < pVals.length; i++) {
    const row = pVals[i];
    const approval = safeString(row[iAppr]);
    const cId = safeString(row[iCamp]);
    const aId = safeString(row[iAdg]);
    adCount++;
    if (approval === 'DISAPPROVED') disapproved++;
    if (approval === 'APPROVED_LIMITED') approvedLimited++;
    if (cId) campaignsWithAds.add(cId);
    if (aId) adgroupsWithAds.add(aId);
  }

  const ctr = todayImpr > 0 ? todayClk / todayImpr : 0;
  const avgCpc = todayClk > 0 ? (todayCost / 1000000) / todayClk : 0;

  out.getRange(2, 1, 16, 3).setValues([
    ['run_id', runId, 'unique run id'],
    ['account_id', String(account.getCustomerId()), 'google ads customer id'],
    ['account_timezone', account.getTimeZone(), 'account timezone'],
    ['exported_at', formatDate(new Date(), tz), 'snapshot time'],
    ['today_campaigns', todayCampaigns, 'campaigns in today table'],
    ['today_nonzero_campaigns', todayNonzeroCampaigns, 'campaigns with traffic today'],
    ['today_impressions', Math.round(todayImpr), 'sum metrics.impressions'],
    ['today_clicks', Math.round(todayClk), 'sum metrics.clicks'],
    ['today_ctr', ctr.toFixed(4), 'clicks/impressions'],
    ['today_cost', (todayCost / 1000000).toFixed(2), 'account currency'],
    ['today_avg_cpc', avgCpc.toFixed(4), 'cost/clicks'],
    ['today_conversions', todayConv.toFixed(2), 'sum conversions'],
    ['ads_total', adCount, 'ads counted in L13_ad_policy_status'],
    ['disapproved_ads', disapproved, 'approval_status=DISAPPROVED'],
    ['approved_limited_ads', approvedLimited, 'approval_status=APPROVED_LIMITED'],
    ['campaigns_with_ads', campaignsWithAds.size, 'unique campaign.id in L13'],
  ]);

  out.appendRow(['adgroups_with_ads', adgroupsWithAds.size, 'unique ad_group.id in L13']);
  out.appendRow(['policy_issue_rate', adCount > 0 ? ((disapproved + approvedLimited) / adCount).toFixed(4) : '0', '(disapproved+approved_limited)/ads_total']);

  trimLiteSheet(out, 3);

  return {
    runId: runId,
    accountId: String(account.getCustomerId()),
    exportedAt: formatDate(new Date(), tz),
    todayImpr: Math.round(todayImpr),
    todayClk: Math.round(todayClk),
    todayCost: (todayCost / 1000000).toFixed(2),
    todayConv: todayConv.toFixed(2),
    disapproved: disapproved,
    approvedLimited: approvedLimited,
    policyIssueRate: adCount > 0 ? ((disapproved + approvedLimited) / adCount).toFixed(4) : '0',
  };
}

function buildLiteFeishuText(summary) {
  return [
    '[Google Ads Hourly Lite]',
    `run_id: ${summary.runId}`,
    `account: ${summary.accountId}`,
    `exported_at: ${summary.exportedAt}`,
    `today: impr=${summary.todayImpr}, clicks=${summary.todayClk}, cost=${summary.todayCost}, conv=${summary.todayConv}`,
    `policy: disapproved=${summary.disapproved}, approved_limited=${summary.approvedLimited}, issue_rate=${summary.policyIssueRate}`,
  ].join('\n');
}

function sendFeishuText(text) {
  const prop = PropertiesService.getScriptProperties();
  const webhookRaw =
    prop.getProperty('RAC_FEISHU_WEBHOOK_URL') ||
    CONFIG.RAC_FEISHU_WEBHOOK_URL ||
    CONFIG.FEISHU_WEBHOOK_URL;
  const tokenRaw =
    prop.getProperty('RAC_FEISHU_TOKEN') ||
    CONFIG.RAC_FEISHU_TOKEN ||
    CONFIG.FEISHU_TOKEN;
  const webhook = safeString(webhookRaw).trim();
  const token = safeString(tokenRaw).trim();
  if (!webhook) return;
  const isFeishuWebhook = /open\.feishu\.cn\/open-apis\/bot\/v2\/hook\//.test(webhook);

  const payload = {
    msg_type: 'text',
    content: { text: text },
  };

  const headers = { 'Content-Type': 'application/json' };
  let signedTimestamp = '';
  if (token && isFeishuWebhook) {
    // Feishu custom bot sign:
    // string_to_sign = timestamp + "\\n" + secret
    // sign = Base64(HMAC_SHA256("", string_to_sign))
    signedTimestamp = String(Math.floor(new Date().getTime() / 1000));
    const stringToSign = signedTimestamp + '\n' + token;
    const signBytes = Utilities.computeHmacSha256Signature(
      '',
      stringToSign,
      Utilities.Charset.UTF_8
    );
    payload.timestamp = signedTimestamp;
    payload.sign = Utilities.base64Encode(signBytes);
  } else if (token) {
    headers.Authorization = 'Bearer ' + token;
  }

  const resp = UrlFetchApp.fetch(webhook, {
    method: 'post',
    headers: headers,
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  });
  const code = resp.getResponseCode();
  const body = resp.getContentText() || '';
  const allHeaders = resp.getAllHeaders ? resp.getAllHeaders() : {};
  const serverDate =
    (allHeaders && (allHeaders.Date || allHeaders.date)) ? String(allHeaders.Date || allHeaders.date) : '';

  let appCode = null;
  let appMsg = '';
  if (body) {
    try {
      const parsed = JSON.parse(body);
      if (parsed && typeof parsed.code !== 'undefined') {
        appCode = Number(parsed.code);
        appMsg = String(parsed.msg || parsed.message || '');
      } else if (parsed && typeof parsed.StatusCode !== 'undefined') {
        appCode = Number(parsed.StatusCode);
        appMsg = String(parsed.StatusMessage || '');
      }
    } catch (_e) {}
  }

  const okHttp = code >= 200 && code < 300;
  const okApp = appCode === null || appCode === 0;
  if (!okHttp || !okApp) {
    Logger.log(
      'Feishu send failed. code=' +
        code +
        ', appCode=' +
        String(appCode) +
        ', appMsg=' +
        appMsg +
        ', signedTs=' +
        signedTimestamp +
        ', tokenLen=' +
        String(token ? token.length : 0) +
        ', serverDate=' +
        serverDate +
        ', body=' +
        body
    );
  }
}

function writeLiteStatus(ss, runId, account, tz, status, note) {
  const sh = prepareLiteSheet(ss, CONFIG.STATUS_SHEET, 2);
  sh.getRange(1, 1, 8, 2).setValues([
    ['run_id', runId],
    ['account_id', String(account.getCustomerId())],
    ['account_timezone', account.getTimeZone()],
    ['status', status],
    ['note', note],
    ['updated_at', formatDate(new Date(), tz)],
    ['is_realtime_stream', 'NO'],
    ['type', 'hourly-lite'],
  ]);
  trimLiteSheet(sh, 2);
}

function ensureLiteLogHeader(ss) {
  const sh = getOrCreateSheet(ss, CONFIG.LOG_SHEET);
  if (sh.getLastRow() === 0) {
    sh.getRange(1, 1, 1, 9).setValues([[
      'run_id',
      'timestamp',
      'account_id',
      'account_name',
      'status',
      'tab',
      'row_count',
      'duration_sec',
      'message',
    ]]);
  }
}

function appendLiteLog(ss, row) {
  getOrCreateSheet(ss, CONFIG.LOG_SHEET).appendRow(row);
}

function mustGetSheet(ss, name) {
  const sh = ss.getSheetByName(name);
  if (!sh || sh.getLastRow() < 2) {
    throw new Error('missing or empty sheet: ' + name);
  }
  return sh;
}

function prepareLiteSheet(ss, name, minCols) {
  const sh = getOrCreateSheet(ss, name);
  sh.clear();
  resizeLiteSheet(sh, 60, Math.max(8, minCols || 8));
  return sh;
}

function trimLiteSheet(sh, minCols) {
  const targetRows = Math.max(sh.getLastRow() + 5, 20);
  const targetCols = Math.max(sh.getLastColumn() + 1, minCols || 1);
  resizeLiteSheet(sh, targetRows, targetCols);
}

function resizeLiteSheet(sh, targetRows, targetCols) {
  const tr = Math.max(1, targetRows);
  const tc = Math.max(1, targetCols);
  const maxRows = sh.getMaxRows();
  const maxCols = sh.getMaxColumns();

  if (maxRows > tr) sh.deleteRows(tr + 1, maxRows - tr);
  else if (maxRows < tr) sh.insertRowsAfter(maxRows, tr - maxRows);

  if (maxCols > tc) sh.deleteColumns(tc + 1, maxCols - tc);
  else if (maxCols < tc) sh.insertColumnsAfter(maxCols, tc - maxCols);
}

function getOrCreateSheet(ss, name) {
  let sh = ss.getSheetByName(name);
  if (!sh) sh = ss.insertSheet(name);
  return sh;
}

function elapsedMinutes(startDate) {
  return (new Date().getTime() - startDate.getTime()) / 60000;
}

function secondsBetween(a, b) {
  return Math.round((b.getTime() - a.getTime()) / 1000);
}

function formatDate(d, tz) {
  return Utilities.formatDate(d, tz, 'yyyy-MM-dd HH:mm:ss');
}

function truncate(text, maxLen) {
  const s = String(text || '');
  return s.length <= maxLen ? s : s.substring(0, maxLen - 3) + '...';
}

function safeString(v) {
  if (v === null || v === undefined) return '';
  return String(v);
}

function toNumber(v) {
  if (v === null || v === undefined || v === '') return 0;
  const n = Number(v);
  return isNaN(n) ? 0 : n;
}

function findCol(headers, name) {
  const idx = headers.indexOf(name);
  if (idx < 0) throw new Error('missing column: ' + name);
  return idx;
}

本文档为站内渲染。原始文件本地路径：saas/source/ads/广告-ads-scripts-02_hourly-lite_google-ads-script-43b540.txt（仅本地保留，不入库不部署）