01 every minute feishu monitor apps script
本地来源：Knowledge/World/项目/Practice/Google-Ads/ads-data-script/01_every-minute-feishu-monitor_apps-script.txt
// IMPORTANT:
// 1) This file is for script.google.com (Apps Script) only.
// 2) Do NOT use this file in ads.google.com Scripts list (Google Ads Scripts requires main()).
// 3) Paste this file as-is into ONE project; do NOT wrap with `function main() { ... }`.
// 4) Do NOT mix 01/02/03 in the same project.
const MINUTE_MONITOR_CONFIG = {
// 存放分钟监控内部状态（比如 99_MM_CLICK_STATE）
STATE_SPREADSHEET_URL:
'https://docs.google.com/spreadsheets/d/1IRVoni0t8VQc2u9G35ajuC28vRyCBiJ3wYULpnipDwI/edit',
// 读取 hourly-lite 输出的汇总与关键词明细
SOURCE_SPREADSHEET_URL:
'https://docs.google.com/spreadsheets/d/1ak5fsF8J4kZYh_IQdTrMI0Goeus9ToV6j9kd8xJ_suI/edit',
SUMMARY_SHEET: '90_LITE_SUMMARY',
DETAIL_SHEET: 'L20_keyword_country_today',
STATE_SHEET: '99_MM_CLICK_STATE',
TIMELINE_SHEET: '98_MM_TIMELINE',
HEARTBEAT_EVERY_MINUTES: 30,
// 飞书正文中的增量窗口（用于“近X分钟”汇总）
REPORT_WINDOW_MINUTES: 30,
FAILED_SEND_RETRY_MINUTES: 5,
TIMELINE_RECENT_MINUTES: 10,
TIMELINE_KEEP_ROWS: 6000,
DETAIL_TOP_N: 5,
DETAIL_URL_MAX_LEN: 90,
ENABLE_FEISHU: true,
SUPPRESS_ZERO_POLICY_LINE: true,
INCLUDE_HISTORY_SNAPSHOT_LINE: true,
INCLUDE_TIMELINE_LINES: true,
INCLUDE_MONITOR_CUMULATIVE_LINE: true,
RAC_FEISHU_WEBHOOK_URL: 'https://open.feishu.cn/open-apis/bot/v2/hook/2c4db3a0-7bdc-42a6-bc0e-44da7344fe87',
RAC_FEISHU_TOKEN: 'oRbJgofj3viHwkzjflTozh',
};
/**
* Apps Script 时间触发器每分钟跑一次。
* 说明：
* - Google Ads 报表并非实时流；本脚本是在每分钟轮询“最新快照”
* - 当 run_id 变化（通常来自 hourly-lite 新跑完）时，计算关键词维度新增点击并推送
*/
function minuteHeartbeat() {
const lock = LockService.getScriptLock();
if (!lock.tryLock(3000)) {
Logger.log('[MinuteMonitor] skip: another execution is running');
return;
}
try {
Logger.log('[MinuteMonitor] heartbeat start');
const now = new Date();
const sourceSs = openSourceSpreadsheet();
const map = readSummaryMap(sourceSs);
const snapshotValidation = validateSummarySnapshot(map);
if (!snapshotValidation.ok) {
Logger.log(
'[MinuteMonitor] skip: invalid summary snapshot. reason=' +
snapshotValidation.reason +
', run_id=' +
safeString(map.run_id) +
', exported_at=' +
safeString(map.exported_at)
);
return;
}
const digest = buildDigest(map);
const runId = safeString(map.run_id);
const props = PropertiesService.getScriptProperties();
const currentAccountId = safeString(map.account_id).trim();
const currentAccountName = safeString(map.account_name).trim();
const currentAccountTimezone = safeString(map.account_timezone).trim();
const currentDayKey =
extractExportDayKey(map.exported_at, map.account_timezone) ||
localNowText().substring(0, 10);
const lastDigest = props.getProperty('MM_LAST_DIGEST') || '';
const lastRunId = props.getProperty('MM_LAST_RUN_ID') || '';
const lastSendMs = Number(props.getProperty('MM_LAST_SEND_MS') || '0');
const lastSendAttemptMs = Number(props.getProperty('MM_LAST_SEND_ATTEMPT_MS') || '0');
const lastTodayClicks = Number(props.getProperty('MM_LAST_TODAY_CLICKS') || '0');
const lastAccountId = safeString(props.getProperty('MM_LAST_ACCOUNT_ID')).trim();
const lastDayKey = safeString(props.getProperty('MM_LAST_DAY_KEY')).trim();
const elapsedMin = lastSendMs > 0 ? (now.getTime() - lastSendMs) / 60000 : 99999;
const attemptElapsedMin =
lastSendAttemptMs > 0 ? (now.getTime() - lastSendAttemptMs) / 60000 : 99999;
const currentTodayImpressions = pickMetricNumber(map, ['today_impressions', 'today_impr']);
const currentTodayClicks = pickMetricNumber(map, ['today_clicks']);
const currentTodayCost = pickMetricNumber(map, ['today_cost']);
const currentTodayConversions = pickMetricNumber(map, ['today_conversions', 'today_conv']);
const accountChanged =
Boolean(lastAccountId && currentAccountId && currentAccountId !== lastAccountId);
const dayChanged =
Boolean(lastDayKey && currentDayKey && currentDayKey !== lastDayKey);
const baselineResetNeeded = accountChanged || dayChanged;
const hasObservedBaseline =
!baselineResetNeeded &&
safeString(props.getProperty('MM_LAST_OBS_TODAY_CLICKS')) !== '';
const lastObservedImpressions = Number(props.getProperty('MM_LAST_OBS_TODAY_IMPR') || '0');
const lastObservedClicks = Number(props.getProperty('MM_LAST_OBS_TODAY_CLICKS') || '0');
const lastObservedCost = Number(props.getProperty('MM_LAST_OBS_TODAY_COST') || '0');
const lastObservedConversions = Number(props.getProperty('MM_LAST_OBS_TODAY_CONV') || '0');
const usesDerivedTodayMetrics =
safeString(map.today_metrics_source) === 'L10_campaign_today';
const suspectTodayRegression =
!baselineResetNeeded &&
hasObservedBaseline &&
isSuspectTodayRegression(
{
impressions: currentTodayImpressions,
clicks: currentTodayClicks,
cost: currentTodayCost,
conversions: currentTodayConversions,
},
{
impressions: lastObservedImpressions,
clicks: lastObservedClicks,
cost: lastObservedCost,
conversions: lastObservedConversions,
}
);
const shouldResetBrokenZeroBaseline =
hasObservedBaseline &&
!suspectTodayRegression &&
usesDerivedTodayMetrics &&
lastObservedImpressions <= 0 &&
lastObservedClicks <= 0 &&
lastObservedCost <= 0 &&
lastObservedConversions <= 0 &&
(currentTodayImpressions > 0 ||
currentTodayClicks > 0 ||
currentTodayCost > 0 ||
currentTodayConversions > 0);
let minuteDeltaImpressions = shouldResetBrokenZeroBaseline
? 0
: hasObservedBaseline
? calcPositiveDelta(currentTodayImpressions, lastObservedImpressions)
: 0;
let minuteDeltaClicks = shouldResetBrokenZeroBaseline
? 0
: hasObservedBaseline
? calcPositiveDelta(currentTodayClicks, lastObservedClicks)
: 0;
let minuteDeltaCost = shouldResetBrokenZeroBaseline
? 0
: hasObservedBaseline
? calcPositiveDelta(currentTodayCost, lastObservedCost)
: 0;
let minuteDeltaConversions = shouldResetBrokenZeroBaseline
? 0
: hasObservedBaseline
? calcPositiveDelta(currentTodayConversions, lastObservedConversions)
: 0;
if (baselineResetNeeded || suspectTodayRegression) {
minuteDeltaImpressions = 0;
minuteDeltaClicks = 0;
minuteDeltaCost = 0;
minuteDeltaConversions = 0;
}
const prevCumImpressions = toNumber(props.getProperty('MM_CUM_IMPR_DELTA') || '0');
const prevCumClicks = toNumber(props.getProperty('MM_CUM_CLICKS_DELTA') || '0');
const prevCumCost = toNumber(props.getProperty('MM_CUM_COST_DELTA') || '0');
const prevCumConversions = toNumber(props.getProperty('MM_CUM_CONV_DELTA') || '0');
const monitorCumImpressions = baselineResetNeeded
? 0
: suspectTodayRegression
? prevCumImpressions
: prevCumImpressions + minuteDeltaImpressions;
const monitorCumClicks = baselineResetNeeded
? 0
: suspectTodayRegression
? prevCumClicks
: prevCumClicks + minuteDeltaClicks;
const monitorCumCost = baselineResetNeeded
? 0
: suspectTodayRegression
? prevCumCost
: prevCumCost + minuteDeltaCost;
const monitorCumConversions = baselineResetNeeded
? 0
: suspectTodayRegression
? prevCumConversions
: prevCumConversions + minuteDeltaConversions;
const changedSummary = digest !== lastDigest;
const runIdChanged = Boolean(runId && runId !== lastRunId);
const heartbeatDue = elapsedMin >= MINUTE_MONITOR_CONFIG.HEARTBEAT_EVERY_MINUTES;
const retryDueAfterFailure =
attemptElapsedMin >= MINUTE_MONITOR_CONFIG.FAILED_SEND_RETRY_MINUTES;
const clicksIncreased = currentTodayClicks > lastTodayClicks;
const stateSs = openStateSpreadsheet();
let detailResult = null;
if (runIdChanged) {
if (baselineResetNeeded || suspectTodayRegression) {
persistCurrentDetailState(sourceSs, stateSs, runId);
} else {
detailResult = computeClickDeltaAndPersist(sourceSs, stateSs, runId);
}
}
const mode = accountChanged
? 'ACCOUNT_SWITCH'
: dayChanged
? 'DAY_RESET'
: suspectTodayRegression
? runIdChanged
? 'NEW_RUN_SUSPECT'
: 'SUSPECT'
: runIdChanged
? 'NEW_RUN'
: clicksIncreased
? 'CLICK_UP'
: changedSummary
? 'CHANGE'
: 'HEARTBEAT';
appendMinuteTimelineRow(stateSs, {
timestamp: now,
accountTimezone: currentAccountTimezone,
runId: runId,
todayImpressions: currentTodayImpressions,
todayClicks: currentTodayClicks,
todayCost: currentTodayCost,
todayConversions: currentTodayConversions,
deltaImpressions: minuteDeltaImpressions,
deltaClicks: minuteDeltaClicks,
deltaCost: minuteDeltaCost,
deltaConversions: minuteDeltaConversions,
mode: mode,
});
if (shouldResetBrokenZeroBaseline) {
Logger.log(
'[MinuteMonitor] reset stale zero baseline after deriving today totals from L10_campaign_today'
);
}
if (accountChanged) {
Logger.log(
'[MinuteMonitor] account switch detected: prev=' +
lastAccountId +
', current=' +
currentAccountId
);
}
if (dayChanged) {
Logger.log(
'[MinuteMonitor] day rollover detected: prev=' +
lastDayKey +
', current=' +
currentDayKey
);
}
if (suspectTodayRegression) {
Logger.log(
'[MinuteMonitor] suspicious today regression: prevClicks=' +
lastObservedClicks +
', currentClicks=' +
currentTodayClicks +
', prevImpr=' +
lastObservedImpressions +
', currentImpr=' +
currentTodayImpressions
);
}
const minuteTimelineRows = readRecentMinuteTimelineRows(
stateSs,
MINUTE_MONITOR_CONFIG.TIMELINE_RECENT_MINUTES,
currentAccountTimezone
);
const recentMinuteLine = buildRecentMinuteLine(
minuteTimelineRows,
currentAccountTimezone
);
const reportWindowMinutes = Math.max(
1,
Number(
MINUTE_MONITOR_CONFIG.REPORT_WINDOW_MINUTES ||
MINUTE_MONITOR_CONFIG.HEARTBEAT_EVERY_MINUTES ||
30
)
);
const reportWindowRows = readRecentMinuteTimelineRows(
stateSs,
reportWindowMinutes,
currentAccountTimezone
);
const reportWindowDelta = sumMinuteTimelineDeltas(reportWindowRows);
const quietNoSignal =
!changedSummary &&
!runIdChanged &&
!clicksIncreased &&
!accountChanged &&
!dayChanged &&
!suspectTodayRegression;
if (quietNoSignal && (!heartbeatDue || !retryDueAfterFailure)) {
Logger.log(
'[MinuteMonitor] skip send: runId=' +
runId +
', lastRunId=' +
lastRunId +
', changedSummary=' +
changedSummary +
', runIdChanged=' +
runIdChanged +
', heartbeatDue=' +
heartbeatDue +
', retryDueAfterFailure=' +
retryDueAfterFailure +
', clicksIncreased=' +
clicksIncreased +
', currentTodayClicks=' +
currentTodayClicks +
', lastTodayClicks=' +
lastTodayClicks
);
return;
}
const text = buildFeishuText(map, mode, detailResult, {
minuteDelta: {
impressions: minuteDeltaImpressions,
clicks: minuteDeltaClicks,
cost: minuteDeltaCost,
conversions: minuteDeltaConversions,
},
windowDelta: reportWindowDelta,
windowMinutes: reportWindowMinutes,
monitorCumulative: {
impressions: monitorCumImpressions,
clicks: monitorCumClicks,
cost: monitorCumCost,
conversions: monitorCumConversions,
},
recentMinuteLine: recentMinuteLine,
currentAccountId: currentAccountId,
currentAccountName: currentAccountName,
currentAccountTimezone: currentAccountTimezone,
previousAccountId: lastAccountId,
currentDayKey: currentDayKey,
previousDayKey: lastDayKey,
accountChanged: accountChanged,
dayChanged: dayChanged,
suspectTodayRegression: suspectTodayRegression,
previousSnapshot: {
impressions: lastObservedImpressions,
clicks: lastObservedClicks,
cost: lastObservedCost,
conversions: lastObservedConversions,
},
currentSnapshot: {
impressions: currentTodayImpressions,
clicks: currentTodayClicks,
cost: currentTodayCost,
conversions: currentTodayConversions,
},
});
let sendResult = { sent: true, code: 0, reason: 'FEISHU_DISABLED' };
if (MINUTE_MONITOR_CONFIG.ENABLE_FEISHU) {
sendResult = sendFeishuText(text);
Logger.log(
'[MinuteMonitor] send result: mode=' +
mode +
', runId=' +
runId +
', changedSummary=' +
changedSummary +
', runIdChanged=' +
runIdChanged +
', heartbeatDue=' +
heartbeatDue +
', retryDueAfterFailure=' +
retryDueAfterFailure +
', clicksIncreased=' +
clicksIncreased +
', currentTodayClicks=' +
currentTodayClicks +
', lastTodayClicks=' +
lastTodayClicks +
', sent=' +
String(sendResult && sendResult.sent) +
', code=' +
String(sendResult && sendResult.code)
);
}
Logger.log('[MinuteMonitor] heartbeat end');
const sendSucceeded = Boolean(sendResult && sendResult.sent);
props.setProperty('MM_LAST_DIGEST', digest);
props.setProperty('MM_LAST_RUN_ID', runId);
props.setProperty('MM_LAST_SEND_ATTEMPT_MS', String(now.getTime()));
if (sendSucceeded) {
props.setProperty('MM_LAST_SEND_MS', String(now.getTime()));
}
if (currentAccountId) props.setProperty('MM_LAST_ACCOUNT_ID', currentAccountId);
if (currentAccountName) props.setProperty('MM_LAST_ACCOUNT_NAME', currentAccountName);
if (currentAccountTimezone) props.setProperty('MM_LAST_ACCOUNT_TZ', currentAccountTimezone);
if (currentDayKey) props.setProperty('MM_LAST_DAY_KEY', currentDayKey);
if (accountChanged || dayChanged) {
props.setProperty('MM_LAST_TODAY_CLICKS', String(currentTodayClicks));
props.setProperty('MM_LAST_OBS_TODAY_IMPR', String(currentTodayImpressions));
props.setProperty('MM_LAST_OBS_TODAY_CLICKS', String(currentTodayClicks));
props.setProperty('MM_LAST_OBS_TODAY_COST', String(currentTodayCost));
props.setProperty('MM_LAST_OBS_TODAY_CONV', String(currentTodayConversions));
props.setProperty('MM_CUM_IMPR_DELTA', '0');
props.setProperty('MM_CUM_CLICKS_DELTA', '0');
props.setProperty('MM_CUM_COST_DELTA', '0');
props.setProperty('MM_CUM_CONV_DELTA', '0');
} else if (!suspectTodayRegression) {
props.setProperty('MM_LAST_TODAY_CLICKS', String(currentTodayClicks));
props.setProperty('MM_LAST_OBS_TODAY_IMPR', String(currentTodayImpressions));
props.setProperty('MM_LAST_OBS_TODAY_CLICKS', String(currentTodayClicks));
props.setProperty('MM_LAST_OBS_TODAY_COST', String(currentTodayCost));
props.setProperty('MM_LAST_OBS_TODAY_CONV', String(currentTodayConversions));
props.setProperty('MM_CUM_IMPR_DELTA', String(monitorCumImpressions));
props.setProperty('MM_CUM_CLICKS_DELTA', String(monitorCumClicks));
props.setProperty('MM_CUM_COST_DELTA', String(monitorCumCost));
props.setProperty('MM_CUM_CONV_DELTA', String(monitorCumConversions));
}
} finally {
SpreadsheetApp.flush();
lock.releaseLock();
}
}
/** 一键安装“每分钟触发器” */
function installMinuteTrigger() {
clearMinuteTriggers();
ScriptApp.newTrigger('minuteHeartbeat')
.timeBased()
.everyMinutes(1)
.inTimezone('Asia/Shanghai')
.create();
}
/** 清理当前项目内 minuteHeartbeat 的触发器 */
function clearMinuteTriggers() {
const all = ScriptApp.getProjectTriggers();
for (let i = 0; i < all.length; i++) {
if (all[i].getHandlerFunction() === 'minuteHeartbeat') {
ScriptApp.deleteTrigger(all[i]);
}
}
}
/**
* 首次启用前执行一次：用当前快照建立基线，避免首次告警推送大量历史增量。
*/
function initializeMinuteMonitorBaseline() {
const sourceSs = openSourceSpreadsheet();
const stateSs = openStateSpreadsheet();
const map = readSummaryMap(sourceSs);
const runId = safeString(map.run_id);
const digest = buildDigest(map);
const detailMap = readKeywordDetailMap(sourceSs);
const stateSh = getOrCreateStateSheet(stateSs);
persistStateMap(stateSh, detailMap, runId);
const props = PropertiesService.getScriptProperties();
props.setProperty('MM_LAST_RUN_ID', runId);
props.setProperty('MM_LAST_DIGEST', digest);
const now = String(new Date().getTime());
const todayImpressions = pickMetricNumber(map, ['today_impressions', 'today_impr']);
const todayClicks = pickMetricNumber(map, ['today_clicks']);
const todayCost = pickMetricNumber(map, ['today_cost']);
const todayConversions = pickMetricNumber(map, ['today_conversions', 'today_conv']);
props.setProperty('MM_LAST_SEND_MS', now);
props.setProperty('MM_LAST_SEND_ATTEMPT_MS', now);
props.setProperty('MM_LAST_TODAY_CLICKS', String(todayClicks));
props.setProperty('MM_LAST_OBS_TODAY_IMPR', String(todayImpressions));
props.setProperty('MM_LAST_OBS_TODAY_CLICKS', String(todayClicks));
props.setProperty('MM_LAST_OBS_TODAY_COST', String(todayCost));
props.setProperty('MM_LAST_OBS_TODAY_CONV', String(todayConversions));
props.setProperty('MM_CUM_IMPR_DELTA', '0');
props.setProperty('MM_CUM_CLICKS_DELTA', '0');
props.setProperty('MM_CUM_COST_DELTA', '0');
props.setProperty('MM_CUM_CONV_DELTA', '0');
if (safeString(map.account_id).trim()) {
props.setProperty('MM_LAST_ACCOUNT_ID', safeString(map.account_id).trim());
}
if (safeString(map.account_name).trim()) {
props.setProperty('MM_LAST_ACCOUNT_NAME', safeString(map.account_name).trim());
}
if (safeString(map.account_timezone).trim()) {
props.setProperty('MM_LAST_ACCOUNT_TZ', safeString(map.account_timezone).trim());
}
const dayKey = extractExportDayKey(map.exported_at, map.account_timezone);
if (dayKey) {
props.setProperty('MM_LAST_DAY_KEY', dayKey);
}
}
function readSummaryMap(ss) {
const sh = ss.getSheetByName(MINUTE_MONITOR_CONFIG.SUMMARY_SHEET);
if (!sh || sh.getLastRow() < 2) {
return {
run_id: '',
exported_at: '',
today_impressions: '0',
today_clicks: '0',
today_cost: '0',
today_conversions: '0',
disapproved_ads: '0',
approved_limited_ads: '0',
policy_issue_rate: '0',
note: 'summary sheet missing or empty',
};
}
const values = sh.getRange(2, 1, sh.getLastRow() - 1, 3).getValues();
const map = {};
const rawMap = {};
for (let i = 0; i < values.length; i++) {
const k = safeString(values[i][0]).trim();
if (!k) continue;
rawMap[k] = values[i][1];
map[k] = safeString(values[i][1]);
}
if (typeof rawMap.exported_at !== 'undefined') {
map.exported_at = normalizeTimestampText(
rawMap.exported_at,
safeString(rawMap.account_timezone || map.account_timezone).trim()
);
}
return enrichSummaryMapWithTodayTotals(ss, map);
}
function enrichSummaryMapWithTodayTotals(ss, map) {
const todayKeys = [
'today_impressions',
'today_clicks',
'today_cost',
'today_conversions',
];
const hasMissingTodayMetric = todayKeys.some(function (key) {
return safeString(map[key]).trim() === '';
});
const summaryToday = {
impressions: pickMetricNumber(map, ['today_impressions', 'today_impr']),
clicks: pickMetricNumber(map, ['today_clicks']),
cost: pickMetricNumber(map, ['today_cost']),
conversions: pickMetricNumber(map, ['today_conversions', 'today_conv']),
};
const derivedToday = deriveTodayTotalsFromCampaignSheet(ss);
const derivedHasSignal =
derivedToday.impressions > 0 ||
derivedToday.clicks > 0 ||
derivedToday.cost > 0 ||
derivedToday.conversions > 0;
const summaryLooksEmpty =
summaryToday.impressions <= 0 &&
summaryToday.clicks <= 0 &&
summaryToday.cost <= 0 &&
summaryToday.conversions <= 0;
if (!hasMissingTodayMetric && !(summaryLooksEmpty && derivedHasSignal)) {
return map;
}
map.today_impressions = String(derivedToday.impressions);
map.today_impr = String(derivedToday.impressions);
map.today_clicks = String(derivedToday.clicks);
map.today_cost = String(derivedToday.cost);
map.today_conversions = String(derivedToday.conversions);
map.today_conv = String(derivedToday.conversions);
map.today_metrics_source = 'L10_campaign_today';
Logger.log(
'[MinuteMonitor] derived today totals from L10_campaign_today: ' +
JSON.stringify(derivedToday)
);
return map;
}
function deriveTodayTotalsFromCampaignSheet(ss) {
const sh = ss.getSheetByName('L10_campaign_today');
if (!sh || sh.getLastRow() < 2) {
return { impressions: 0, clicks: 0, cost: 0, conversions: 0 };
}
const values = sh.getDataRange().getValues();
const header = values[0];
const idxImpr = findColOptional(header, 'metrics.impressions');
const idxClicks = findColOptional(header, 'metrics.clicks');
const idxCostMicros = findColOptional(header, 'metrics.cost_micros');
const idxConversions = findColOptional(header, 'metrics.conversions');
if (idxImpr < 0 && idxClicks < 0 && idxCostMicros < 0 && idxConversions < 0) {
return { impressions: 0, clicks: 0, cost: 0, conversions: 0 };
}
const out = { impressions: 0, clicks: 0, cost: 0, conversions: 0 };
for (let i = 1; i < values.length; i++) {
const row = values[i];
if (!row || row.length === 0) continue;
out.impressions += idxImpr >= 0 ? toNumber(row[idxImpr]) : 0;
out.clicks += idxClicks >= 0 ? toNumber(row[idxClicks]) : 0;
out.cost += idxCostMicros >= 0 ? toNumber(row[idxCostMicros]) / 1000000 : 0;
out.conversions += idxConversions >= 0 ? toNumber(row[idxConversions]) : 0;
}
return out;
}
function computeClickDeltaAndPersist(sourceSs, stateSs, runId) {
const detailMap = readKeywordDetailMap(sourceSs);
const stateSh = getOrCreateStateSheet(stateSs);
const prev = readStateMap(stateSh);
const deltas = [];
let totalDeltaClicks = 0;
const keys = Object.keys(detailMap);
for (let i = 0; i < keys.length; i++) {
const key = keys[i];
const curr = detailMap[key];
const prevClicks = prev[key] ? toNumber(prev[key].clicks) : 0;
const delta = curr.clicks - prevClicks;
if (delta <= 0) continue;
totalDeltaClicks += delta;
deltas.push({
delta: delta,
campaign: curr.campaign,
keyword: curr.keyword,
country: curr.country,
url: curr.url,
avgCpc: curr.avgCpc,
clicks: curr.clicks,
});
}
deltas.sort(function (a, b) {
if (b.delta !== a.delta) return b.delta - a.delta;
if (b.clicks !== a.clicks) return b.clicks - a.clicks;
return a.campaign < b.campaign ? -1 : 1;
});
persistStateMap(stateSh, detailMap, runId);
return {
detailRows: keys.length,
deltaClicks: totalDeltaClicks,
topRows: deltas.slice(0, MINUTE_MONITOR_CONFIG.DETAIL_TOP_N),
};
}
function persistCurrentDetailState(sourceSs, stateSs, runId) {
const detailMap = readKeywordDetailMap(sourceSs);
const stateSh = getOrCreateStateSheet(stateSs);
persistStateMap(stateSh, detailMap, runId);
return {
detailRows: Object.keys(detailMap).length,
deltaClicks: 0,
topRows: [],
};
}
function openSourceSpreadsheet() {
const url =
MINUTE_MONITOR_CONFIG.SOURCE_SPREADSHEET_URL ||
MINUTE_MONITOR_CONFIG.STATE_SPREADSHEET_URL;
if (!url) throw new Error('SOURCE_SPREADSHEET_URL is required');
return SpreadsheetApp.openByUrl(url);
}
function openStateSpreadsheet() {
const url =
MINUTE_MONITOR_CONFIG.STATE_SPREADSHEET_URL ||
MINUTE_MONITOR_CONFIG.SOURCE_SPREADSHEET_URL;
if (!url) throw new Error('STATE_SPREADSHEET_URL is required');
return SpreadsheetApp.openByUrl(url);
}
function readKeywordDetailMap(ss) {
const sh = ss.getSheetByName(MINUTE_MONITOR_CONFIG.DETAIL_SHEET);
if (!sh || sh.getLastRow() < 2) {
return {};
}
const values = sh.getDataRange().getValues();
const h = values[0];
const idxCampaignId = findColOptional(h, 'campaign.id');
const idxCampaignName = findColOptional(h, 'campaign.name');
const idxAdGroupId = findColOptional(h, 'ad_group.id');
const idxCriterionId = findColOptional(h, 'ad_group_criterion.criterion_id');
const idxKeywordText = findColOptional(h, 'ad_group_criterion.keyword.text');
const idxMatchType = findColOptional(h, 'ad_group_criterion.keyword.match_type');
const idxFinalUrls = findColOptional(h, 'ad_group_criterion.final_urls');
const idxCountry = findColOptional(h, 'segments.geo_target_country');
const idxClicks = findColOptional(h, 'metrics.clicks');
const idxAvgCpc = findColOptional(h, 'metrics.average_cpc');
if (idxClicks < 0) {
return {};
}
const out = {};
for (let i = 1; i < values.length; i++) {
const row = values[i];
const clicks = toNumber(row[idxClicks]);
if (clicks <= 0) continue;
const campaignId = idxCampaignId >= 0 ? safeString(row[idxCampaignId]) : '';
const adGroupId = idxAdGroupId >= 0 ? safeString(row[idxAdGroupId]) : '';
const criterionId = idxCriterionId >= 0 ? safeString(row[idxCriterionId]) : '';
const country = idxCountry >= 0 ? compactCountry(row[idxCountry]) : '';
const url = idxFinalUrls >= 0 ? parseFirstUrl(row[idxFinalUrls]) : '';
const key = [campaignId, adGroupId, criterionId, country, url].join('|');
const keywordText = idxKeywordText >= 0 ? safeString(row[idxKeywordText]) : '';
const matchType = idxMatchType >= 0 ? safeString(row[idxMatchType]) : '';
const normalizedMatchType = normalizeMatchType(matchType);
const landingType = classifyLandingType(url);
out[key] = {
clicks: clicks,
campaign: idxCampaignName >= 0 ? safeString(row[idxCampaignName]) : campaignId,
keyword: keywordText || '(not set)',
matchType: normalizedMatchType,
type: landingType,
country: country || 'N/A',
url: url,
avgCpc: idxAvgCpc >= 0 ? toNumber(row[idxAvgCpc]) : 0,
};
}
return out;
}
function getOrCreateStateSheet(ss) {
let sh = ss.getSheetByName(MINUTE_MONITOR_CONFIG.STATE_SHEET);
if (!sh) {
sh = ss.insertSheet(MINUTE_MONITOR_CONFIG.STATE_SHEET);
sh.hideSheet();
}
return sh;
}
function readStateMap(sh) {
if (sh.getLastRow() < 2) return {};
const values = sh.getRange(2, 1, sh.getLastRow() - 1, 2).getValues();
const out = {};
for (let i = 0; i < values.length; i++) {
const key = safeString(values[i][0]);
if (!key) continue;
out[key] = { clicks: toNumber(values[i][1]) };
}
return out;
}
function persistStateMap(sh, detailMap, runId) {
const now = Utilities.formatDate(new Date(), 'Asia/Shanghai', 'yyyy-MM-dd HH:mm:ss');
const rows = Object.keys(detailMap).map(function (key) {
const x = detailMap[key];
return [
key,
x.clicks,
x.campaign,
x.keyword,
x.country,
x.url,
x.avgCpc,
runId,
now,
];
});
sh.clearContents();
sh.getRange(1, 1, 1, 9).setValues([[
'key',
'clicks',
'campaign',
'keyword',
'country',
'url',
'avg_cpc',
'run_id',
'updated_at',
]]);
if (rows.length > 0) {
sh.getRange(2, 1, rows.length, 9).setValues(rows);
}
}
function buildDigest(map) {
const keys = [
'run_id',
'today_impressions',
'today_clicks',
'today_cost',
'today_conversions',
'disapproved_ads',
'approved_limited_ads',
'policy_issue_rate',
];
const raw = keys.map((k) => `${k}=${map[k] || ''}`).join('|');
const bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, raw);
return bytes.map((b) => ('0' + (b & 0xff).toString(16)).slice(-2)).join('');
}
function validateSummarySnapshot(map) {
const runId = safeString((map || {}).run_id).trim();
if (!runId || runId === '-') {
return { ok: false, reason: 'missing_run_id' };
}
const exportedAt = safeString((map || {}).exported_at).trim();
if (!exportedAt) {
return { ok: false, reason: 'missing_exported_at' };
}
return { ok: true, reason: 'ok' };
}
function normalizeTimestampText(value, tz) {
const zone = safeString(tz).trim() || 'Asia/Shanghai';
if (value instanceof Date) {
return Utilities.formatDate(value, zone, 'yyyy-MM-dd HH:mm:ss');
}
const text = safeString(value).trim();
if (!text) return '';
const standard = text.match(
/^(\d{4}-\d{2}-\d{2})(?:[ T](\d{2}:\d{2}:\d{2}))?$/
);
if (standard) {
return `${standard[1]} ${standard[2] || '00:00:00'}`;
}
const parsed = new Date(text);
if (!isNaN(parsed.getTime())) {
return Utilities.formatDate(parsed, zone, 'yyyy-MM-dd HH:mm:ss');
}
return text;
}
function extractExportDayKey(exportedAt, tz) {
const text = normalizeTimestampText(exportedAt, tz);
const m = text.match(/^(\d{4}-\d{2}-\d{2})/);
return m ? m[1] : '';
}
function isMeaningfulDrop(currentValue, previousValue, absoluteFloor, ratioFloor) {
const current = toNumber(currentValue);
const previous = toNumber(previousValue);
if (!isFinite(current) || !isFinite(previous) || previous <= 0) return false;
const drop = previous - current;
if (drop <= 0) return false;
return drop >= Math.max(absoluteFloor, previous * ratioFloor);
}
function isSuspectTodayRegression(currentSnapshot, previousSnapshot) {
return (
isMeaningfulDrop(
currentSnapshot && currentSnapshot.clicks,
previousSnapshot && previousSnapshot.clicks,
3,
0.2
) ||
isMeaningfulDrop(
currentSnapshot && currentSnapshot.impressions,
previousSnapshot && previousSnapshot.impressions,
10,
0.2
) ||
isMeaningfulDrop(
currentSnapshot && currentSnapshot.cost,
previousSnapshot && previousSnapshot.cost,
0.5,
0.2
)
);
}
function buildFeishuText(map, mode, detailResult, monitorExtras) {
const runId = safeString(map.run_id) || '-';
const accountTimezone =
safeString(map.account_timezone) ||
safeString(monitorExtras && monitorExtras.currentAccountTimezone) ||
'';
const exportedAt =
normalizeTimestampText(map.exported_at, accountTimezone) || localNowText();
const accountId =
safeString(map.account_id) ||
safeString(monitorExtras && monitorExtras.currentAccountId) ||
'-';
const accountName =
safeString(map.account_name) ||
safeString(monitorExtras && monitorExtras.currentAccountName) ||
'';
const todayImpressions = pickMetricNumber(map, ['today_impressions', 'today_impr']);
const todayClicks = pickMetricNumber(map, ['today_clicks']);
const todayCost = pickMetricNumber(map, ['today_cost']);
const todayConversions = pickMetricNumber(map, ['today_conversions', 'today_conv']);
const minuteDelta = (monitorExtras && monitorExtras.minuteDelta) || {
impressions: 0,
clicks: 0,
cost: 0,
};
const windowDelta = (monitorExtras && monitorExtras.windowDelta) || {
impressions: 0,
clicks: 0,
cost: 0,
conversions: 0,
};
const windowMinutes = Number(
(monitorExtras && monitorExtras.windowMinutes) ||
MINUTE_MONITOR_CONFIG.REPORT_WINDOW_MINUTES ||
30
);
const monitorCumulative = (monitorExtras && monitorExtras.monitorCumulative) || {
impressions: 0,
clicks: 0,
cost: 0,
conversions: 0,
};
const recentMinuteLine = safeString(
monitorExtras && monitorExtras.recentMinuteLine
).trim();
const policySummary = buildExtraPolicySummary(map);
const historyLine = buildHistorySnapshotLine(map);
const metricsSource =
safeString(map.today_metrics_source).trim() || '90_LITE_SUMMARY';
const lines = [
`[Ads Minute Monitor] ${mode}`,
`时间: ${exportedAt}`,
`Run: ${runId}`,
`账号: ${accountName ? accountName + ' ' : ''}(${accountId})${accountTimezone ? ' | 时区: ' + accountTimezone : ''}`,
`今日累计快照: 曝光 ${formatInt(todayImpressions)} | 点击 ${formatInt(todayClicks)} | 花费 ${formatDecimal(todayCost, 2)}`,
];
if (todayConversions > 0) {
lines.push(`今日累计转化: ${formatDecimal(todayConversions, 2)}`);
}
lines.push(
metricsSource === 'L10_campaign_today'
? '口径: summary 缺值，已回退到 L10_campaign_today 汇总'
: '口径: 90_LITE_SUMMARY 的 today_* 当日快照'
);
if (
windowMinutes > 0 &&
(toNumber(windowDelta.impressions) > 0 ||
toNumber(windowDelta.clicks) > 0 ||
toNumber(windowDelta.cost) > 0 ||
toNumber(windowDelta.conversions) > 0)
) {
lines.push(
`近${windowMinutes}分钟增量: 曝光 ${formatInt(windowDelta.impressions)} | 点击 ${formatInt(windowDelta.clicks)} | 花费 ${formatDecimal(windowDelta.cost, 2)} | 转化 ${formatDecimal(windowDelta.conversions, 2)}`
);
}
if (
MINUTE_MONITOR_CONFIG.INCLUDE_MONITOR_CUMULATIVE_LINE &&
(toNumber(monitorCumulative.impressions) > 0 ||
toNumber(monitorCumulative.clicks) > 0 ||
toNumber(monitorCumulative.cost) > 0 ||
toNumber(monitorCumulative.conversions) > 0)
) {
lines.push(
`监控累计增量: 曝光 ${formatInt(monitorCumulative.impressions)} | 点击 ${formatInt(monitorCumulative.clicks)} | 花费 ${formatDecimal(monitorCumulative.cost, 2)} | 转化 ${formatDecimal(monitorCumulative.conversions, 2)}`
);
}
if (MINUTE_MONITOR_CONFIG.INCLUDE_TIMELINE_LINES && recentMinuteLine) {
lines.push(recentMinuteLine);
}
if (monitorExtras && monitorExtras.accountChanged) {
lines.push(
`警告: 账号切换 ${safeString(monitorExtras.previousAccountId) || '-'} -> ${accountId}，已重置本地基线；旧累计不可直接比较。`
);
}
if (monitorExtras && monitorExtras.dayChanged) {
lines.push(
`提示: 已按账号时区重置日期基线 ${safeString(monitorExtras.previousDayKey) || '-'} -> ${safeString(monitorExtras.currentDayKey) || '-'}。这通常发生在首次修正时区口径或自然跨天时，不代表广告数据回退。`
);
}
if (monitorExtras && monitorExtras.suspectTodayRegression) {
const prevSnapshot = (monitorExtras && monitorExtras.previousSnapshot) || {};
lines.push(
`警告: 今日累计快照较上次观察值回退。上次 点击 ${formatInt(prevSnapshot.clicks)} / 当前 点击 ${formatInt(todayClicks)}，上次 花费 ${formatDecimal(prevSnapshot.cost, 2)} / 当前 花费 ${formatDecimal(todayCost, 2)}。这更像上游表被覆盖、账号切换或线上脚本版本漂移，不应当当作真实累计下降。`
);
}
if (historyLine) {
lines.push(historyLine);
}
if (!MINUTE_MONITOR_CONFIG.SUPPRESS_ZERO_POLICY_LINE && policySummary) {
lines.push(`政策: ${policySummary}`);
}
if (detailResult && toNumber(detailResult.deltaClicks) > 0) {
lines.push(`新增点击: +${formatInt(detailResult.deltaClicks)}`);
if (detailResult.topRows && detailResult.topRows.length > 0) {
lines.push('新增点击明细:');
for (let i = 0; i < detailResult.topRows.length; i++) {
const x = detailResult.topRows[i];
lines.push(`${i + 1}) +${formatInt(x.delta)} | CPC ${formatCpc(x.avgCpc)}`);
lines.push(` Campaign: ${safeString(x.campaign) || 'N/A'}`);
lines.push(` Keyword: ${safeString(x.keyword) || 'N/A'}`);
lines.push(` URL: ${compactDisplayUrl(x.url)}`);
}
}
}
return lines.filter(Boolean).join('\n');
}
function sendFeishuText(text) {
const prop = PropertiesService.getScriptProperties();
const resolved = resolveFeishuTarget(prop);
const webhook = resolved.webhook;
const token = resolved.token;
const credentialSource = resolved.source;
if (!webhook) return { sent: false, code: 0, reason: 'NO_WEBHOOK' };
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 "signature verification":
// IMPORTANT:
// Feishu expected formula equals Python:
// string_to_sign = timestamp + "\\n" + secret
// sign = Base64(HMAC_SHA256("", string_to_sign))
// (NOT HMAC(string_to_sign, secret))
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;
}
try {
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) +
', source=' +
credentialSource +
', serverDate=' +
serverDate +
', body=' +
body
);
return {
sent: false,
code: code,
appCode: appCode,
appMsg: appMsg,
body: body,
source: credentialSource,
reason: 'API_REJECTED',
};
}
return { sent: true, code: code, appCode: appCode, appMsg: appMsg, source: credentialSource };
} catch (e) {
Logger.log('Feishu send exception: ' + String(e));
return {
sent: false,
code: -1,
error: String(e),
source: credentialSource,
reason: 'EXCEPTION',
};
}
}
/**
* 关键修复：
* - 之前 webhook/token 分别按“各自优先级”取值，可能出现“旧 webhook + 新 token”混搭
* - 混搭会导致 19021（签名不匹配）
* - 现在要求优先使用同一来源的一对凭据，禁止默认混搭
*/
function resolveFeishuTarget(prop) {
const pWebhook = safeString(prop.getProperty('RAC_FEISHU_WEBHOOK_URL')).trim();
const pToken = safeString(prop.getProperty('RAC_FEISHU_TOKEN')).trim();
const cWebhook = safeString(
MINUTE_MONITOR_CONFIG.RAC_FEISHU_WEBHOOK_URL ||
MINUTE_MONITOR_CONFIG.FEISHU_WEBHOOK_URL
).trim();
const cToken = safeString(
MINUTE_MONITOR_CONFIG.RAC_FEISHU_TOKEN || MINUTE_MONITOR_CONFIG.FEISHU_TOKEN
).trim();
// 1) Script Properties 全量配置优先
if (pWebhook && pToken) {
return { webhook: pWebhook, token: pToken, source: 'script_properties_pair' };
}
// 2) 常量配置全量配置次优先
if (cWebhook && cToken) {
return { webhook: cWebhook, token: cToken, source: 'config_pair' };
}
// 3) 无签名模式（仅 webhook）
if (pWebhook && !pToken) {
return { webhook: pWebhook, token: '', source: 'script_properties_webhook_only' };
}
if (cWebhook && !cToken) {
return { webhook: cWebhook, token: '', source: 'config_webhook_only' };
}
// 4) 最后兜底：如果只配置了 token 没有 webhook，仍返回空 webhook 由上层 NO_WEBHOOK 处理
return { webhook: pWebhook || cWebhook || '', token: pToken || cToken || '', source: 'incomplete' };
}
/** 手动连通性测试：Run 一次，检查返回 sent/code */
function debugPingFeishu() {
const now = Utilities.formatDate(new Date(), 'Asia/Shanghai', 'yyyy-MM-dd HH:mm:ss');
const result = sendFeishuText('[Ads Minute Monitor] debug ping at ' + now);
Logger.log('debugPingFeishu result: ' + JSON.stringify(result));
return result;
}
/** 只看密钥元信息（长度/空格），不打印真实密钥 */
function debugFeishuSecretMeta() {
const prop = PropertiesService.getScriptProperties();
const resolved = resolveFeishuTarget(prop);
const webhook = safeString(resolved.webhook).trim();
const tokenTrim = safeString(resolved.token).trim();
const propWebhook = safeString(prop.getProperty('RAC_FEISHU_WEBHOOK_URL')).trim();
const propToken = safeString(prop.getProperty('RAC_FEISHU_TOKEN')).trim();
const configWebhook = safeString(
MINUTE_MONITOR_CONFIG.RAC_FEISHU_WEBHOOK_URL ||
MINUTE_MONITOR_CONFIG.FEISHU_WEBHOOK_URL ||
''
).trim();
const configToken = safeString(
MINUTE_MONITOR_CONFIG.RAC_FEISHU_TOKEN || MINUTE_MONITOR_CONFIG.FEISHU_TOKEN || ''
).trim();
const out = {
selectedSource: resolved.source,
webhookPrefix: webhook ? webhook.substring(0, 45) : '',
webhookSuffix: webhook ? webhook.substring(Math.max(0, webhook.length - 8)) : '',
selectedTokenLen: tokenTrim.length,
propWebhookLen: propWebhook.length,
propTokenLen: propToken.length,
configWebhookLen: configWebhook.length,
configTokenLen: configToken.length,
};
Logger.log('debugFeishuSecretMeta: ' + JSON.stringify(out));
return out;
}
/**
* 一键清理旧的 Script Properties 飞书凭据。
* 用途：当你怀疑项目里残留了旧 webhook/secret 覆盖了新配置时，先运行这个函数一次。
*/
function clearRacFeishuScriptProperties() {
const prop = PropertiesService.getScriptProperties();
prop.deleteProperty('RAC_FEISHU_WEBHOOK_URL');
prop.deleteProperty('RAC_FEISHU_TOKEN');
const out = {
cleared: true,
selectedAfterClear: resolveFeishuTarget(prop).source,
};
Logger.log('clearRacFeishuScriptProperties: ' + JSON.stringify(out));
return out;
}
/** 手动看当前触发判断状态（不发消息） */
function debugMinuteState() {
const sourceSs = openSourceSpreadsheet();
const map = readSummaryMap(sourceSs);
const props = PropertiesService.getScriptProperties();
const state = {
runId: safeString(map.run_id),
accountId: safeString(map.account_id),
accountTimezone: safeString(map.account_timezone),
dayKey: extractExportDayKey(map.exported_at, map.account_timezone),
todayClicks: toNumber(map.today_clicks),
lastRunId: props.getProperty('MM_LAST_RUN_ID') || '',
lastAccountId: props.getProperty('MM_LAST_ACCOUNT_ID') || '',
lastDayKey: props.getProperty('MM_LAST_DAY_KEY') || '',
lastTodayClicks: Number(props.getProperty('MM_LAST_TODAY_CLICKS') || '0'),
lastDigest: props.getProperty('MM_LAST_DIGEST') || '',
lastSendMs: Number(props.getProperty('MM_LAST_SEND_MS') || '0'),
lastSendAttemptMs: Number(props.getProperty('MM_LAST_SEND_ATTEMPT_MS') || '0'),
lastObsImpressions: Number(props.getProperty('MM_LAST_OBS_TODAY_IMPR') || '0'),
lastObsClicks: Number(props.getProperty('MM_LAST_OBS_TODAY_CLICKS') || '0'),
lastObsCost: Number(props.getProperty('MM_LAST_OBS_TODAY_COST') || '0'),
lastObsConversions: Number(props.getProperty('MM_LAST_OBS_TODAY_CONV') || '0'),
cumImpressionsDelta: Number(props.getProperty('MM_CUM_IMPR_DELTA') || '0'),
cumClicksDelta: Number(props.getProperty('MM_CUM_CLICKS_DELTA') || '0'),
cumCostDelta: Number(props.getProperty('MM_CUM_COST_DELTA') || '0'),
cumConversionsDelta: Number(props.getProperty('MM_CUM_CONV_DELTA') || '0'),
heartbeatEveryMinutes: MINUTE_MONITOR_CONFIG.HEARTBEAT_EVERY_MINUTES,
failedSendRetryMinutes: MINUTE_MONITOR_CONFIG.FAILED_SEND_RETRY_MINUTES,
};
Logger.log('debugMinuteState: ' + JSON.stringify(state));
return state;
}
/**
* 修复辅助：把当前 summary 快照设为新基线，并清理 timeline 中 run_id 为空的脏行。
* 适用场景：历史上出现过 "Run: -" 的错误告警后，想一次性把状态对齐。
*/
function rebaseMinuteMonitorState() {
const sourceSs = openSourceSpreadsheet();
const stateSs = openStateSpreadsheet();
const map = readSummaryMap(sourceSs);
const snapshotValidation = validateSummarySnapshot(map);
if (!snapshotValidation.ok) {
throw new Error('cannot rebase: invalid summary snapshot, reason=' + snapshotValidation.reason);
}
const props = PropertiesService.getScriptProperties();
const nowMs = String(new Date().getTime());
const runId = safeString(map.run_id);
const digest = buildDigest(map);
const todayImpr = toNumber(map.today_impressions);
const todayClicks = toNumber(map.today_clicks);
const todayCost = toNumber(map.today_cost);
const todayConv = toNumber(map.today_conversions);
props.setProperty('MM_LAST_RUN_ID', runId);
props.setProperty('MM_LAST_DIGEST', digest);
props.setProperty('MM_LAST_SEND_MS', nowMs);
props.setProperty('MM_LAST_SEND_ATTEMPT_MS', nowMs);
props.setProperty('MM_LAST_TODAY_CLICKS', String(todayClicks));
props.setProperty('MM_LAST_OBS_TODAY_IMPR', String(todayImpr));
props.setProperty('MM_LAST_OBS_TODAY_CLICKS', String(todayClicks));
props.setProperty('MM_LAST_OBS_TODAY_COST', String(todayCost));
props.setProperty('MM_LAST_OBS_TODAY_CONV', String(todayConv));
props.setProperty('MM_CUM_IMPR_DELTA', '0');
props.setProperty('MM_CUM_CLICKS_DELTA', '0');
props.setProperty('MM_CUM_COST_DELTA', '0');
props.setProperty('MM_CUM_CONV_DELTA', '0');
if (safeString(map.account_id).trim()) {
props.setProperty('MM_LAST_ACCOUNT_ID', safeString(map.account_id).trim());
}
if (safeString(map.account_name).trim()) {
props.setProperty('MM_LAST_ACCOUNT_NAME', safeString(map.account_name).trim());
}
if (safeString(map.account_timezone).trim()) {
props.setProperty('MM_LAST_ACCOUNT_TZ', safeString(map.account_timezone).trim());
}
const dayKey = extractExportDayKey(map.exported_at, map.account_timezone);
if (dayKey) {
props.setProperty('MM_LAST_DAY_KEY', dayKey);
}
const removed = cleanInvalidTimelineRows(stateSs);
resetMinuteTimelineSheet(stateSs);
const out = {
rebased: true,
runId: runId,
digest: digest,
removedInvalidTimelineRows: removed,
};
Logger.log('rebaseMinuteMonitorState: ' + JSON.stringify(out));
return out;
}
function cleanInvalidTimelineRows(ss) {
const sh = ss.getSheetByName(MINUTE_MONITOR_CONFIG.TIMELINE_SHEET);
if (!sh || sh.getLastRow() < 2) return 0;
const lastRow = sh.getLastRow();
const values = sh.getRange(2, 1, lastRow - 1, 2).getValues();
const rowsToDelete = [];
for (let i = 0; i < values.length; i++) {
const runId = safeString(values[i][1]).trim();
if (!runId || runId === '-') {
rowsToDelete.push(i + 2);
}
}
for (let j = rowsToDelete.length - 1; j >= 0; j--) {
sh.deleteRow(rowsToDelete[j]);
}
return rowsToDelete.length;
}
function findColOptional(headers, name) {
return headers.indexOf(name);
}
function parseFirstUrl(v) {
const s = safeString(v).trim();
if (!s) return '';
// 常见格式：["https://a.com","https://b.com"] 或 [https://a.com]
const m = s.match(/https?:\/\/[^\],\s"]+/i);
if (m && m[0]) return m[0];
return s;
}
function compactCountry(v) {
const s = safeString(v);
if (!s) return '';
if (s.indexOf('geoTargetConstants/') >= 0) {
return s.split('/').pop();
}
return s;
}
function toFixed4(v) {
const n = toNumber(v);
return n.toFixed(4);
}
function calcPositiveDelta(currentValue, previousValue) {
const c = toNumber(currentValue);
const p = toNumber(previousValue);
if (!isFinite(c) || !isFinite(p)) return 0;
if (c <= p) return 0;
return c - p;
}
function getOrCreateMinuteTimelineSheet(ss) {
let sh = ss.getSheetByName(MINUTE_MONITOR_CONFIG.TIMELINE_SHEET);
if (!sh) {
sh = ss.insertSheet(MINUTE_MONITOR_CONFIG.TIMELINE_SHEET);
}
if (sh.getLastRow() === 0) {
sh.getRange(1, 1, 1, 11).setValues([[
'timestamp',
'run_id',
'mode',
'today_impr',
'today_clicks',
'today_cost',
'today_conv',
'delta_impr',
'delta_clicks',
'delta_cost',
'delta_conv',
]]);
}
return sh;
}
function appendMinuteTimelineRow(ss, row) {
const sh = getOrCreateMinuteTimelineSheet(ss);
const accountTimezone = safeString(row.accountTimezone).trim() || 'Asia/Shanghai';
sh.appendRow([
normalizeTimestampText(row.timestamp, accountTimezone) || localNowText(),
safeString(row.runId),
safeString(row.mode),
formatInt(row.todayImpressions),
formatInt(row.todayClicks),
formatDecimal(row.todayCost, 2),
formatDecimal(row.todayConversions, 2),
formatInt(row.deltaImpressions),
formatInt(row.deltaClicks),
formatDecimal(row.deltaCost, 2),
formatDecimal(row.deltaConversions, 2),
]);
const keepRows = Math.max(200, Number(MINUTE_MONITOR_CONFIG.TIMELINE_KEEP_ROWS || 6000));
const maxRows = keepRows + 1; // + header
if (sh.getLastRow() > maxRows) {
sh.deleteRows(2, sh.getLastRow() - maxRows);
}
}
function readRecentMinuteTimelineRows(ss, limit, tz) {
const sh = ss.getSheetByName(MINUTE_MONITOR_CONFIG.TIMELINE_SHEET);
if (!sh || sh.getLastRow() < 2) return [];
const rowCount = sh.getLastRow() - 1;
if (rowCount <= 0) return [];
const values = sh.getRange(2, 1, rowCount, 11).getValues();
const accountTimezone = safeString(tz).trim() || 'Asia/Shanghai';
const minutes = Math.max(1, Number(limit || 10));
const cutoffText = Utilities.formatDate(
new Date(new Date().getTime() - minutes * 60000),
accountTimezone,
'yyyy-MM-dd HH:mm:ss'
);
return values.filter(function (row) {
const ts = normalizeTimestampText(row[0], accountTimezone);
return Boolean(ts) && ts >= cutoffText;
});
}
function sumMinuteTimelineDeltas(rows) {
const out = { impressions: 0, clicks: 0, cost: 0, conversions: 0 };
if (!rows || rows.length === 0) return out;
for (let i = 0; i < rows.length; i++) {
out.impressions += toNumber(rows[i][7]);
out.clicks += toNumber(rows[i][8]);
out.cost += toNumber(rows[i][9]);
out.conversions += toNumber(rows[i][10]);
}
return out;
}
function buildRecentMinuteLine(rows, tz) {
if (!rows || rows.length === 0) return '';
const accountTimezone = safeString(tz).trim() || 'Asia/Shanghai';
const tail = rows.slice(
Math.max(0, rows.length - Math.max(1, Number(MINUTE_MONITOR_CONFIG.TIMELINE_RECENT_MINUTES || 10)))
);
const changed = tail.filter(function(row) {
return toNumber(row[8]) > 0 || toNumber(row[9]) > 0 || toNumber(row[10]) > 0;
});
const last = tail[tail.length - 1];
const lastTotalClicks = last ? formatInt(last[4]) : '0';
const lastTotalCost = last ? formatDecimal(last[5], 2) : '0.00';
if (changed.length === 0) {
return `最近${String(tail.length)}分钟: 无新增点击 | 当前总点击 ${lastTotalClicks} | 当前总花费 ${lastTotalCost}`;
}
const parts = changed.slice(-3).map(function(row) {
const ts = normalizeTimestampText(row[0], accountTimezone);
const hhmm = ts.length >= 16 ? ts.substring(11, 16) : ts;
return `${hhmm} +${formatInt(row[8])}clk/$${formatDecimal(row[9], 2)}`;
});
return `最近${String(tail.length)}分钟: ${parts.join(' | ')} | 当前总点击 ${lastTotalClicks} | 当前总花费 ${lastTotalCost}`;
}
function resetMinuteTimelineSheet(ss) {
const sh = ss.getSheetByName(MINUTE_MONITOR_CONFIG.TIMELINE_SHEET);
if (!sh) return;
sh.clearContents();
sh.getRange(1, 1, 1, 11).setValues([[
'timestamp',
'run_id',
'mode',
'today_impr',
'today_clicks',
'today_cost',
'today_conv',
'delta_impr',
'delta_clicks',
'delta_cost',
'delta_conv',
]]);
}
function localNowText() {
return Utilities.formatDate(new Date(), 'Asia/Shanghai', 'yyyy-MM-dd HH:mm:ss');
}
function formatInt(v) {
const n = Math.max(0, Math.round(toNumber(v)));
return String(n);
}
function formatDecimal(v, digits) {
const n = toNumber(v);
if (!isFinite(n)) return '0';
return n.toFixed(digits);
}
function trimTrailingZero(s) {
if (s.indexOf('.') < 0) return s;
return s.replace(/\.?0+$/, '');
}
function formatRate(v) {
const n = toNumber(v);
if (!isFinite(n)) return '0%';
if (n <= 1) return trimTrailingZero((n * 100).toFixed(2)) + '%';
return trimTrailingZero(n.toFixed(2)) + '%';
}
function formatCpc(v) {
const n = toNumber(v);
if (!isFinite(n) || n <= 0) return '0';
const normalized = n >= 1000 ? n / 1000000 : n;
return trimTrailingZero(normalized.toFixed(4));
}
function normalizeMatchType(v) {
const s = safeString(v).trim();
if (!s) return 'N/A';
return s.toUpperCase();
}
function classifyLandingType(url) {
if (!url) return 'other';
try {
const u = new URL(url);
const path = u.pathname || '/';
if (path.indexOf('/tools/') >= 0) return 'tools';
if (path.indexOf('/models/') >= 0) return 'models';
if (path.indexOf('/pricing') >= 0) return 'pricing';
if (path.indexOf('/checkout/') >= 0) return 'checkout';
return 'other';
} catch (_e) {
return 'other';
}
}
function compactDisplayUrl(url) {
const s = safeString(url).trim();
if (!s) return 'N/A';
let out = s;
try {
const u = new URL(s);
out = u.hostname + u.pathname;
} catch (_e) {}
const maxLen = Number(MINUTE_MONITOR_CONFIG.DETAIL_URL_MAX_LEN || 90);
if (out.length <= maxLen) return out;
return out.substring(0, Math.max(0, maxLen - 3)) + '...';
}
function buildExtraPolicySummary(map) {
const skip = {
run_id: true,
exported_at: true,
today_impressions: true,
today_clicks: true,
today_cost: true,
today_conversions: true,
disapproved_ads: true,
approved_limited_ads: true,
policy_issue_rate: true,
note: true,
};
const parts = [];
const keys = Object.keys(map || {});
for (let i = 0; i < keys.length; i++) {
const k = keys[i];
if (skip[k]) continue;
if (!/(policy|disapproved|approved_limited|under_review|eligible|campaign)/i.test(k)) {
continue;
}
const val = safeString(map[k]).trim();
if (!val) continue;
if (isZeroLikePolicyValue(val)) continue;
parts.push(normalizePolicyLabel(k) + ' ' + val);
if (parts.length >= 6) break;
}
return parts.join(' | ');
}
function buildHistorySnapshotLine(map) {
if (!MINUTE_MONITOR_CONFIG.INCLUDE_HISTORY_SNAPSHOT_LINE) return '';
const yesterdayKeys = ['yesterday_clicks', 'yesterday_cost', 'yesterday_conversions'];
const last7Keys = ['last7_clicks', 'last7_cost', 'last7_conversions'];
const last30Keys = [
'last30_clicks',
'last30_cost',
'last30_conversions',
'last_30d_clicks',
'last_30d_cost',
'last_30d_conversions',
];
const parts = [];
if (hasAnyMetricValue(map, yesterdayKeys)) {
const yClicks = pickMetricNumber(map, ['yesterday_clicks']);
const yCost = pickMetricNumber(map, ['yesterday_cost']);
const yConv = pickMetricNumber(map, ['yesterday_conversions']);
parts.push(
`昨日: 点击 ${formatInt(yClicks)} | 花费 ${formatDecimal(yCost, 2)} | 转化 ${formatDecimal(yConv, 2)}`
);
}
if (hasAnyMetricValue(map, last7Keys)) {
const d7Clicks = pickMetricNumber(map, ['last7_clicks']);
const d7Cost = pickMetricNumber(map, ['last7_cost']);
const d7Conv = pickMetricNumber(map, ['last7_conversions']);
parts.push(
`近7天: 点击 ${formatInt(d7Clicks)} | 花费 ${formatDecimal(d7Cost, 2)} | 转化 ${formatDecimal(d7Conv, 2)}`
);
}
if (hasAnyMetricValue(map, last30Keys)) {
const d30Clicks = pickMetricNumber(map, ['last30_clicks', 'last_30d_clicks']);
const d30Cost = pickMetricNumber(map, ['last30_cost', 'last_30d_cost']);
const d30Conv = pickMetricNumber(map, ['last30_conversions', 'last_30d_conversions']);
parts.push(
`近30天: 点击 ${formatInt(d30Clicks)} | 花费 ${formatDecimal(d30Cost, 2)} | 转化 ${formatDecimal(d30Conv, 2)}`
);
}
if (parts.length === 0) return '';
return '历史: ' + parts.join(' || ');
}
function hasAnyMetricValue(map, keys) {
for (let i = 0; i < keys.length; i++) {
const key = keys[i];
const raw = safeString((map || {})[key]).trim();
if (raw !== '') return true;
}
return false;
}
function pickMetricNumber(map, keys) {
for (let i = 0; i < keys.length; i++) {
const key = keys[i];
const raw = safeString((map || {})[key]).trim();
if (raw !== '') return toNumber(raw);
}
return 0;
}
function normalizePolicyLabel(k) {
return safeString(k)
.replace(/_/g, ' ')
.replace(/\s+/g, ' ')
.trim();
}
function isZeroLikePolicyValue(v) {
const s = safeString(v).trim();
if (!s) return true;
const normalized = s.replace(/%/g, '').trim();
if (!normalized) return true;
if (/^[-+]?0+(\.0+)?$/.test(normalized)) return true;
return false;
}
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;
}
本文档为站内渲染。原始文件本地路径：saas/source/knowledge-world/Knowledge-World-项目-Practice-Google-Ads-ads-data-script-01_ev-a84cb9.txt（仅本地保留，不入库不部署）