01 every minute feishu monitor apps script
本地来源：广告/ads-scripts/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',
HEARTBEAT_EVERY_MINUTES: 30,
DETAIL_TOP_N: 12,
ENABLE_FEISHU: 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() {
Logger.log('[MinuteMonitor] heartbeat start');
const now = new Date();
const sourceSs = openSourceSpreadsheet();
const map = readSummaryMap(sourceSs);
const digest = buildDigest(map);
const runId = safeString(map.run_id);
const props = PropertiesService.getScriptProperties();
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 lastTodayClicks = Number(props.getProperty('MM_LAST_TODAY_CLICKS') || '0');
const elapsedMin = lastSendMs > 0 ? (now.getTime() - lastSendMs) / 60000 : 99999;
const currentTodayClicks = toNumber(map.today_clicks);
const changedSummary = digest !== lastDigest;
const runIdChanged = Boolean(runId && runId !== lastRunId);
const heartbeatDue = elapsedMin >= MINUTE_MONITOR_CONFIG.HEARTBEAT_EVERY_MINUTES;
const clicksIncreased = currentTodayClicks > lastTodayClicks;
let detailResult = null;
if (runIdChanged) {
const stateSs = openStateSpreadsheet();
detailResult = computeClickDeltaAndPersist(sourceSs, stateSs, runId);
}
if (!changedSummary && !runIdChanged && !heartbeatDue && !clicksIncreased) {
Logger.log(
'[MinuteMonitor] skip send: runId=' +
runId +
', lastRunId=' +
lastRunId +
', changedSummary=' +
changedSummary +
', runIdChanged=' +
runIdChanged +
', heartbeatDue=' +
heartbeatDue +
', clicksIncreased=' +
clicksIncreased +
', currentTodayClicks=' +
currentTodayClicks +
', lastTodayClicks=' +
lastTodayClicks
);
return;
}
const mode = runIdChanged
? 'NEW_RUN'
: clicksIncreased
? 'CLICK_UP'
: changedSummary
? 'CHANGE'
: 'HEARTBEAT';
const text = buildFeishuText(map, mode, detailResult);
if (MINUTE_MONITOR_CONFIG.ENABLE_FEISHU) {
const sendResult = sendFeishuText(text);
Logger.log(
'[MinuteMonitor] send result: mode=' +
mode +
', runId=' +
runId +
', changedSummary=' +
changedSummary +
', runIdChanged=' +
runIdChanged +
', heartbeatDue=' +
heartbeatDue +
', clicksIncreased=' +
clicksIncreased +
', currentTodayClicks=' +
currentTodayClicks +
', lastTodayClicks=' +
lastTodayClicks +
', sent=' +
String(sendResult && sendResult.sent) +
', code=' +
String(sendResult && sendResult.code)
);
}
Logger.log('[MinuteMonitor] heartbeat end');
props.setProperty('MM_LAST_DIGEST', digest);
props.setProperty('MM_LAST_RUN_ID', runId);
props.setProperty('MM_LAST_SEND_MS', String(now.getTime()));
props.setProperty('MM_LAST_TODAY_CLICKS', String(currentTodayClicks));
}
/** 一键安装“每分钟触发器” */
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);
props.setProperty('MM_LAST_SEND_MS', String(new Date().getTime()));
props.setProperty('MM_LAST_TODAY_CLICKS', String(toNumber(map.today_clicks)));
}
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 = {};
for (let i = 0; i < values.length; i++) {
const k = safeString(values[i][0]).trim();
if (!k) continue;
map[k] = safeString(values[i][1]);
}
return map;
}
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 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]) : '';
out[key] = {
clicks: clicks,
campaign: idxCampaignName >= 0 ? safeString(row[idxCampaignName]) : campaignId,
keyword: keywordText + (matchType ? ' [' + matchType + ']' : ''),
country: country,
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 buildFeishuText(map, mode, detailResult) {
const lines = [
`[Ads Minute Monitor] ${mode}`,
`run_id: ${map.run_id || ''}`,
`exported_at: ${map.exported_at || ''}`,
`today: impr=${map.today_impressions || '0'}, clicks=${map.today_clicks || '0'}, cost=${map.today_cost || '0'}, conv=${map.today_conversions || '0'}`,
`policy: disapproved=${map.disapproved_ads || '0'}, approved_limited=${map.approved_limited_ads || '0'}, issue_rate=${map.policy_issue_rate || '0'}`,
`note: data is snapshot-based (not per-click realtime stream)`,
];
if (detailResult) {
lines.push(
`delta_clicks_since_prev_run: ${detailResult.deltaClicks} (detail_rows=${detailResult.detailRows})`
);
if (detailResult.topRows && detailResult.topRows.length > 0) {
lines.push('top_delta_details:');
for (let i = 0; i < detailResult.topRows.length; i++) {
const x = detailResult.topRows[i];
lines.push(
`${i + 1}. +${x.delta} clicks | camp=${x.campaign} | kw=${x.keyword} | country=${x.country || 'N/A'} | cpc=${toFixed4(x.avgCpc)} | url=${x.url || 'N/A'}`
);
}
}
}
if (map.note) {
lines.push(`note2: ${map.note}`);
}
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,
};
}
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 };
}
}
/**
* 关键修复：
* - 之前 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),
todayClicks: toNumber(map.today_clicks),
lastRunId: props.getProperty('MM_LAST_RUN_ID') || '',
lastTodayClicks: Number(props.getProperty('MM_LAST_TODAY_CLICKS') || '0'),
lastDigest: props.getProperty('MM_LAST_DIGEST') || '',
lastSendMs: Number(props.getProperty('MM_LAST_SEND_MS') || '0'),
heartbeatEveryMinutes: MINUTE_MONITOR_CONFIG.HEARTBEAT_EVERY_MINUTES,
};
Logger.log('debugMinuteState: ' + JSON.stringify(state));
return state;
}
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 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/ads/广告-ads-scripts-01_every-minute-feishu-monitor_apps-script-34b347.txt（仅本地保留，不入库不部署）