clicks timeline 7

// IMPORTANT:
// 这个文件是 Apps Script 汇总脚本。
// 不要粘贴到 Google Ads Scripts 编辑器里。
// 正确位置是 https://script.google.com 的独立 Apps Script 项目。
// 如果你把它放进 Google Ads Scripts，会看到 “script has no main function”，
// 那不是代码逻辑错，而是运行环境放错了。
// 正确用法：
// 1. 在 Apps Script 里粘贴本文件
// 2. 先手动运行 mergeClicksTimelineShards() 完成授权
// 3. 再手动运行 installEvery10MinMergeTrigger() 创建 10 分钟触发器

const MERGE_CONFIG = {
  SPREADSHEET_URL:
    'https://docs.google.com/spreadsheets/d/1IRVoni0t8VQc2u9G35ajuC28vRyCBiJ3wYULpnipDwI/edit',
  SOURCE_SHEETS: [
    '21_clicks_timeline_shard_00',
    '21_clicks_timeline_shard_01',
    '21_clicks_timeline_shard_02',
    '21_clicks_timeline_shard_03',
    '21_clicks_timeline_shard_04',
    '21_clicks_timeline_shard_05',
  ],
  TARGET_SHEET: '21_clicks_timeline_all',
  STATUS_SHEET: '00_STATUS_CLICKS_TIMELINE_MERGE',
  LOG_SHEET: '00_RUN_LOG_CLICKS_TIMELINE_MERGE',
  BASE_ROWS: 200,
  BASE_COLS: 40,
  SORT_COLUMN_LABELS: ['captured_at', 'hour_bucket', 'campaign.id', 'ad_group_ad.ad.id'],
  ENABLE_FEISHU: true,
  FEISHU_WEBHOOK_URL:
    'https://open.feishu.cn/open-apis/bot/v2/hook/2c4db3a0-7bdc-42a6-bc0e-44da7344fe87',
  FEISHU_TOKEN: 'oRbJgofj3viHwkzjflTozh',
};

function mergeClicksTimelineShards() {
  const ss = SpreadsheetApp.openByUrl(MERGE_CONFIG.SPREADSHEET_URL);
  const tz = ss.getSpreadsheetTimeZone() || 'Asia/Shanghai';
  const runId = Utilities.formatDate(new Date(), tz, 'yyyyMMdd-HHmmss');

  const lock = LockService.getScriptLock();
  lock.waitLock(30000);

  try {
    const result = collectMergedRows(ss);
    writeMergedSheet(ss, result.header, result.rows);
    writeMergeStatus(ss, tz, runId, 'FINISHED', result.rows.length, '');
    appendMergeLog(ss, [
      runId,
      formatMergeTs(new Date(), tz),
      'FINISHED',
      String(result.rows.length),
      '',
    ]);
    maybeSendMergeFeishu(
      [
        '[Clicks Timeline Merge]',
        'status: FINISHED',
        `rows: ${String(result.rows.length)}`,
        `target: ${MERGE_CONFIG.TARGET_SHEET}`,
        `time: ${formatMergeTs(new Date(), tz)}`,
      ].join('\n')
    );
  } catch (e) {
    writeMergeStatus(ss, tz, runId, 'ERROR', 0, String(e));
    appendMergeLog(ss, [
      runId,
      formatMergeTs(new Date(), tz),
      'ERROR',
      '0',
      truncateMerge(String(e), 1000),
    ]);
    maybeSendMergeFeishu(
      [
        '[Clicks Timeline Merge]',
        'status: ERROR',
        `time: ${formatMergeTs(new Date(), tz)}`,
        truncateMerge(String(e), 1000),
      ].join('\n')
    );
    throw e;
  } finally {
    try {
      lock.releaseLock();
    } catch (e) {}
  }
}

function installEvery10MinMergeTrigger() {
  clearMergeTriggers();
  ScriptApp.newTrigger('mergeClicksTimelineShards')
    .timeBased()
    .everyMinutes(10)
    .inTimezone('Asia/Shanghai')
    .create();
}

function clearMergeTriggers() {
  const all = ScriptApp.getProjectTriggers();
  for (let i = 0; i < all.length; i++) {
    if (all[i].getHandlerFunction() === 'mergeClicksTimelineShards') {
      ScriptApp.deleteTrigger(all[i]);
    }
  }
}

function collectMergedRows(ss) {
  let header = null;
  const rows = [];
  const dedupe = {};

  for (let i = 0; i < MERGE_CONFIG.SOURCE_SHEETS.length; i++) {
    const name = MERGE_CONFIG.SOURCE_SHEETS[i];
    const sh = ss.getSheetByName(name);
    if (!sh) continue;
    const lastRow = sh.getLastRow();
    const lastCol = sh.getLastColumn();
    if (lastRow < 2 || lastCol < 1) continue;

    const values = sh.getRange(1, 1, lastRow, lastCol).getValues();
    if (!header) header = values[0];

    for (let r = 1; r < values.length; r++) {
      const row = values[r];
      if (!hasAnyValue(row)) continue;
      const key = row.join('\u001f');
      if (dedupe[key]) continue;
      dedupe[key] = true;
      rows.push(row);
    }
  }

  return {
    header: header || [],
    rows: rows,
  };
}

function writeMergedSheet(ss, header, rows) {
  const sh = getOrCreateMergeSheet(ss, MERGE_CONFIG.TARGET_SHEET);
  ensureMergeSheetSize(
    sh,
    Math.max(MERGE_CONFIG.BASE_ROWS, rows.length + 50),
    Math.max(MERGE_CONFIG.BASE_COLS, header.length)
  );
  sh.clearContents();

  if (header.length === 0) return;

  sh.getRange(1, 1, 1, header.length).setValues([header]);
  sh.setFrozenRows(1);

  if (rows.length > 0) {
    sh.getRange(2, 1, rows.length, header.length).setValues(rows);
    sortMergedSheet(sh, header, rows.length + 1);
  }
}

function sortMergedSheet(sh, header, lastRow) {
  const sortSpecs = [];
  for (let i = 0; i < MERGE_CONFIG.SORT_COLUMN_LABELS.length; i++) {
    const idx = header.indexOf(MERGE_CONFIG.SORT_COLUMN_LABELS[i]);
    if (idx >= 0) sortSpecs.push({ column: idx + 1, ascending: true });
  }
  if (sortSpecs.length === 0 || lastRow < 3) return;
  sh.getRange(2, 1, lastRow - 1, header.length).sort(sortSpecs);
}

function writeMergeStatus(ss, tz, runId, status, rows, note) {
  const sh = getOrCreateMergeSheet(ss, MERGE_CONFIG.STATUS_SHEET);
  ensureMergeSheetSize(sh, 20, 2);
  const values = [
    ['key', 'value'],
    ['run_id', runId],
    ['ts', formatMergeTs(new Date(), tz)],
    ['status', status],
    ['target_sheet', MERGE_CONFIG.TARGET_SHEET],
    ['source_sheets', MERGE_CONFIG.SOURCE_SHEETS.join(', ')],
    ['rows_written', String(rows)],
    ['note', note],
  ];
  sh.clearContents();
  sh.getRange(1, 1, values.length, 2).setValues(values);
  sh.setFrozenRows(1);
}

function appendMergeLog(ss, row) {
  const sh = getOrCreateMergeSheet(ss, MERGE_CONFIG.LOG_SHEET);
  ensureMergeSheetSize(sh, MERGE_CONFIG.BASE_ROWS, 5);
  if (sh.getLastRow() === 0) {
    sh.getRange(1, 1, 1, 5).setValues([['run_id', 'ts', 'status', 'rows', 'note']]);
    sh.setFrozenRows(1);
  }
  sh.appendRow(row);
}

function maybeSendMergeFeishu(text) {
  if (!MERGE_CONFIG.ENABLE_FEISHU) return;
  const prop = PropertiesService.getScriptProperties();
  const webhook =
    prop.getProperty('RAC_FEISHU_WEBHOOK_URL') ||
    prop.getProperty('FEISHU_WEBHOOK_URL') ||
    MERGE_CONFIG.FEISHU_WEBHOOK_URL;
  const token =
    prop.getProperty('RAC_FEISHU_TOKEN') ||
    prop.getProperty('FEISHU_TOKEN') ||
    MERGE_CONFIG.FEISHU_TOKEN;
  if (!webhook) return;

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

  const headers = { 'Content-Type': 'application/json' };
  if (token) headers.Authorization = 'Bearer ' + token;

  UrlFetchApp.fetch(webhook, {
    method: 'post',
    headers: headers,
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  });
}

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

function ensureMergeSheetSize(sh, minRows, minCols) {
  if (sh.getMaxRows() < minRows) {
    sh.insertRowsAfter(sh.getMaxRows(), minRows - sh.getMaxRows());
  }
  if (sh.getMaxColumns() < minCols) {
    sh.insertColumnsAfter(sh.getMaxColumns(), minCols - sh.getMaxColumns());
  }
}

function hasAnyValue(row) {
  for (let i = 0; i < row.length; i++) {
    if (row[i] !== '' && row[i] !== null) return true;
  }
  return false;
}

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

function truncateMerge(text, limit) {
  const s = String(text || '');
  return s.length <= limit ? s : s.slice(0, limit);
}

本文档为站内渲染。原始文件本地路径：saas/source/knowledge-world/Knowledge-World-项目-Practice-Google-Ads-ads-data-script-click-476426.txt（仅本地保留，不入库不部署）