02 hourly lite google ads script
本地来源：Knowledge/World/项目/Practice/Google-Ads/ads-data-script/02_hourly-lite_google-ads-script.txt
// 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',
HOURLY_TIMELINE_SHEET: '91_HOURLY_TIMELINE',
TODAY_CAMPAIGN_SHEET: 'L10_campaign_today',
TODAY_DETAIL_SHEET: 'L20_keyword_country_today',
ENABLE_FEISHU: true,
SEND_FALLBACK_ALERT_WHEN_SUMMARY_MISSING: true,
SHOW_POLICY_LINE_WHEN_ZERO: false,
INCLUDE_HISTORY_LINE: true,
INCLUDE_HOURLY_TIMELINE_LINE: true,
INCLUDE_MONITOR_CUMULATIVE_LINE: true,
HOURLY_TIMELINE_RECENT_HOURS: 10,
HOURLY_TIMELINE_KEEP_ROWS: 3000,
RAC_FEISHU_WEBHOOK_URL: 'https://open.feishu.cn/open-apis/bot/v2/hook/2c4db3a0-7bdc-42a6-bc0e-44da7344fe87',
RAC_FEISHU_TOKEN: 'oRbJgofj3viHwkzjflTozh',
FEISHU_WEBHOOK_URL: 'https://open.feishu.cn/open-apis/bot/v2/hook/2c4db3a0-7bdc-42a6-bc0e-44da7344fe87',
FEISHU_TOKEN: 'oRbJgofj3viHwkzjflTozh',
};
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');
const prevHourWindow = getPreviousHourWindow(tz);
ensureLiteLogHeader(ss);
writeLiteStatus(ss, runId, account, tz, 'RUNNING', '');
const jobs = buildLiteJobs(prevHourWindow);
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;
let timelineContext = null;
try {
summary = buildLiteSummary(ss, runId, account, tz, prevHourWindow);
} 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),
]);
}
if (summary) {
try {
timelineContext = appendHourlyTimelineAndBuildContext(ss, summary);
} catch (e) {
appendLiteLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.HOURLY_TIMELINE_SHEET,
0,
0,
truncate(String(e), 1000),
]);
}
}
writeLiteStatus(ss, runId, account, tz, 'FINISHED', '');
if (CONFIG.ENABLE_FEISHU && (summary || CONFIG.SEND_FALLBACK_ALERT_WHEN_SUMMARY_MISSING)) {
try {
const text = summary
? buildLiteFeishuText(summary, timelineContext)
: buildLiteFallbackFeishuText(runId, account, tz, 'SUMMARY_MISSING_OR_BUILD_FAILED');
const sendResult = sendFeishuText(text);
appendLiteLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
sendResult && sendResult.sent ? 'OK' : 'WARN',
summary ? 'FEISHU' : 'FEISHU_FALLBACK',
0,
0,
sendResult && sendResult.sent
? `sent code=${String(sendResult.code)} source=${safeString(sendResult.source)}`
: `not sent reason=${safeString(sendResult && sendResult.reason)} code=${String(sendResult && sendResult.code)} source=${safeString(sendResult && sendResult.source)} msg=${safeString(sendResult && sendResult.appMsg)}`,
]);
} catch (e) {
appendLiteLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
'FEISHU',
0,
0,
truncate(String(e), 1000),
]);
}
}
}
function getPreviousHourWindow(tz) {
const now = new Date();
const currentHourStart = new Date(now.getTime());
currentHourStart.setMinutes(0, 0, 0);
const prevHourStart = new Date(currentHourStart.getTime() - 60 * 60 * 1000);
const prevHourEnd = currentHourStart;
const date = Utilities.formatDate(prevHourStart, tz, 'yyyy-MM-dd');
const hour = Number(Utilities.formatDate(prevHourStart, tz, 'H'));
const startText = Utilities.formatDate(prevHourStart, tz, 'yyyy-MM-dd HH:00');
const endText = Utilities.formatDate(prevHourEnd, tz, 'yyyy-MM-dd HH:00');
return {
date: date,
hour: hour,
startText: startText,
endText: endText,
label: `${startText} ~ ${endText}`,
};
}
function buildLiteJobs(prevHourWindow) {
const hourDate = safeString(prevHourWindow && prevHourWindow.date);
const hourNum = Number(prevHourWindow && prevHourWindow.hour);
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_prev_hour',
minCols: 14,
query: `
SELECT
campaign.id,
campaign.name,
campaign.status,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
metrics.search_impression_share,
metrics.search_rank_lost_impression_share,
metrics.search_budget_lost_impression_share,
segments.date,
segments.hour
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date = '${hourDate}'
AND segments.hour = ${hourNum}
`,
fallbackQuery: `
SELECT
campaign.id,
campaign.name,
campaign.status,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
segments.date,
segments.hour
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date = '${hourDate}'
AND segments.hour = ${hourNum}
`,
},
{
tab: 'L20_keyword_country_prev_hour',
minCols: 13,
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,
segments.date,
segments.hour
FROM keyword_view
WHERE campaign.status != REMOVED
AND ad_group_criterion.status != REMOVED
AND segments.date = '${hourDate}'
AND segments.hour = ${hourNum}
AND metrics.clicks > 0
`,
fallbackQuery: `
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,
segments.date
FROM keyword_view
WHERE campaign.status != REMOVED
AND ad_group_criterion.status != REMOVED
AND segments.date = '${hourDate}'
AND metrics.clicks > 0
`,
},
{
tab: CONFIG.TODAY_CAMPAIGN_SHEET,
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: CONFIG.TODAY_DETAIL_SHEET,
minCols: 13,
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,
segments.geo_target_country,
metrics.clicks,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions
FROM keyword_view
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND ad_group_criterion.status != REMOVED
AND segments.date DURING TODAY
AND metrics.clicks > 0
`,
fallbackQuery: `
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,
metrics.conversions
FROM keyword_view
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
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.fallbackQuery) return '';
const msg = safeString(err).toUpperCase();
if (
msg.indexOf('BAD_FIELD_NAME') < 0 &&
msg.indexOf('UNRECOGNIZED') < 0 &&
msg.indexOf('INVALID') < 0 &&
msg.indexOf('PROHIBITED_SEGMENT') < 0 &&
msg.indexOf('INCOMPATIBLE') < 0
) {
return '';
}
return safeString(job.fallbackQuery);
}
function aggregateCampaignSheet(sh, includeShareMetrics) {
const empty = {
campaigns: 0,
nonzeroCampaigns: 0,
impressions: 0,
clicks: 0,
costMicros: 0,
conversions: 0,
ctr: 0,
avgCpc: 0,
searchIS: null,
rankLostIS: null,
budgetLostIS: null,
};
if (!sh || sh.getLastRow() < 1) return empty;
const vals = sh.getDataRange().getValues();
if (!vals || vals.length === 0 || !vals[0]) return empty;
const h = vals[0];
if (
h.indexOf('metrics.impressions') < 0 ||
h.indexOf('metrics.clicks') < 0 ||
h.indexOf('metrics.cost_micros') < 0 ||
h.indexOf('metrics.conversions') < 0
) {
return empty;
}
const iImpr = findCol(h, 'metrics.impressions');
const iClk = findCol(h, 'metrics.clicks');
const iCost = findCol(h, 'metrics.cost_micros');
const iConv = findCol(h, 'metrics.conversions');
const iSearchIS = includeShareMetrics ? findColOptional(h, 'metrics.search_impression_share') : -1;
const iRankLostIS = includeShareMetrics ? findColOptional(h, 'metrics.search_rank_lost_impression_share') : -1;
const iBudgetLostIS = includeShareMetrics ? findColOptional(h, 'metrics.search_budget_lost_impression_share') : -1;
let campaigns = 0;
let nonzeroCampaigns = 0;
let impressions = 0;
let clicks = 0;
let costMicros = 0;
let conversions = 0;
let weightedImprForShare = 0;
let sumSearchIS = 0;
let sumRankLostIS = 0;
let sumBudgetLostIS = 0;
for (let i = 1; i < vals.length; i++) {
const row = vals[i];
const impr = toNumber(row[iImpr]);
const clk = toNumber(row[iClk]);
const cost = toNumber(row[iCost]);
const conv = toNumber(row[iConv]);
campaigns++;
if (impr > 0 || clk > 0 || cost > 0) nonzeroCampaigns++;
impressions += impr;
clicks += clk;
costMicros += cost;
conversions += conv;
if (includeShareMetrics && impr > 0) {
weightedImprForShare += impr;
if (iSearchIS >= 0) sumSearchIS += normalizeShareMetric(row[iSearchIS]) * impr;
if (iRankLostIS >= 0) sumRankLostIS += normalizeShareMetric(row[iRankLostIS]) * impr;
if (iBudgetLostIS >= 0) sumBudgetLostIS += normalizeShareMetric(row[iBudgetLostIS]) * impr;
}
}
return {
campaigns: campaigns,
nonzeroCampaigns: nonzeroCampaigns,
impressions: impressions,
clicks: clicks,
costMicros: costMicros,
conversions: conversions,
ctr: impressions > 0 ? clicks / impressions : 0,
avgCpc: clicks > 0 ? (costMicros / 1000000) / clicks : 0,
searchIS: includeShareMetrics && weightedImprForShare > 0 ? sumSearchIS / weightedImprForShare : null,
rankLostIS: includeShareMetrics && weightedImprForShare > 0 ? sumRankLostIS / weightedImprForShare : null,
budgetLostIS: includeShareMetrics && weightedImprForShare > 0 ? sumBudgetLostIS / weightedImprForShare : null,
};
}
function aggregateTodayFromAdsApi() {
const out = {
impressions: 0,
clicks: 0,
costMicros: 0,
conversions: 0,
};
const q = `
SELECT
metrics.impressions,
metrics.clicks,
metrics.cost_micros,
metrics.conversions
FROM campaign
WHERE campaign.status != 'REMOVED'
AND segments.date DURING TODAY
`;
const it = AdsApp.search(q);
while (it.hasNext()) {
const row = it.next();
out.impressions += toNumber(row.metrics && row.metrics.impressions);
out.clicks += toNumber(row.metrics && row.metrics.clicks);
out.costMicros += toNumber(row.metrics && row.metrics.costMicros);
out.conversions += toNumber(row.metrics && row.metrics.conversions);
}
return out;
}
function buildLiteSummary(ss, runId, account, tz, prevHourWindow) {
const out = prepareLiteSheet(ss, CONFIG.SUMMARY_SHEET, 3);
out.getRange(1, 1, 1, 3).setValues([['metric', 'value', 'note']]);
const hourAgg = aggregateCampaignSheet(
ss.getSheetByName('L10_campaign_prev_hour'),
true
);
const todayAgg = aggregateTodayFromAdsApi();
const hourSearchIS =
hourAgg.searchIS === null ? 'n/a' : Number(hourAgg.searchIS).toFixed(4);
const hourRankLostIS =
hourAgg.rankLostIS === null ? 'n/a' : Number(hourAgg.rankLostIS).toFixed(4);
const hourBudgetLostIS =
hourAgg.budgetLostIS === null
? 'n/a'
: Number(hourAgg.budgetLostIS).toFixed(4);
const hourDate = safeString(prevHourWindow && prevHourWindow.date);
const hourNum = Number(prevHourWindow && prevHourWindow.hour);
const hourLabel =
(prevHourWindow && prevHourWindow.label) ||
`${hourDate} ${String(hourNum)}:00`;
var summaryRows = [
['run_id', runId, 'unique run id'],
['account_id', String(account.getCustomerId()), 'google ads customer id'],
['account_name', safeString(account.getName()), 'google ads account name'],
['account_timezone', account.getTimeZone(), 'account timezone'],
['exported_at', formatDate(new Date(), tz), 'snapshot time'],
['summary_version', '2026-03-06-v3', 'hourly-lite summary format version'],
['hour_window', hourLabel, 'strict previous full hour'],
['hour_date', hourDate, 'segments.date'],
['hour_of_day', hourNum, 'segments.hour (0-23)'],
['prev_hour_campaigns', hourAgg.campaigns, 'campaigns in previous hour'],
[
'prev_hour_nonzero_campaigns',
hourAgg.nonzeroCampaigns,
'campaigns with traffic in previous hour',
],
[
'prev_hour_impressions',
Math.round(hourAgg.impressions),
'sum metrics.impressions',
],
['prev_hour_clicks', Math.round(hourAgg.clicks), 'sum metrics.clicks'],
['prev_hour_ctr', hourAgg.ctr.toFixed(4), 'clicks/impressions'],
[
'prev_hour_cost',
(hourAgg.costMicros / 1000000).toFixed(2),
'account currency',
],
['prev_hour_avg_cpc', hourAgg.avgCpc.toFixed(4), 'cost/clicks'],
[
'prev_hour_conversions',
hourAgg.conversions.toFixed(2),
'sum conversions',
],
[
'prev_hour_search_impression_share',
hourSearchIS,
'impression-weighted avg (0-1)',
],
[
'prev_hour_search_lost_is_rank',
hourRankLostIS,
'impression-weighted avg (0-1)',
],
[
'prev_hour_search_lost_is_budget',
hourBudgetLostIS,
'impression-weighted avg (0-1)',
],
['today_impressions', Math.round(todayAgg.impressions), 'sum metrics.impressions DURING TODAY'],
['today_clicks', Math.round(todayAgg.clicks), 'sum metrics.clicks DURING TODAY'],
['today_cost', (todayAgg.costMicros / 1000000).toFixed(2), 'sum cost DURING TODAY (account currency)'],
['today_conversions', todayAgg.conversions.toFixed(2), 'sum conversions DURING TODAY'],
['today_metrics_source', 'AdsApp.search campaign DURING TODAY', 'authoritative today snapshot source'],
// Backward-compatible aliases for old consumers.
['today_impr', Math.round(todayAgg.impressions), 'alias of today_impressions'],
['today_conv', todayAgg.conversions.toFixed(2), 'alias of today_conversions'],
];
out.getRange(2, 1, summaryRows.length, 3).setValues(summaryRows);
trimLiteSheet(out, 3);
return {
runId: runId,
accountId: String(account.getCustomerId()),
accountName: safeString(account.getName()),
accountTimezone: account.getTimeZone(),
exportedAt: formatDate(new Date(), tz),
prevHourLabel: hourLabel,
prevHourDate: hourDate,
prevHourNum: hourNum,
prevHourImpr: Math.round(hourAgg.impressions),
prevHourClk: Math.round(hourAgg.clicks),
prevHourCost: (hourAgg.costMicros / 1000000).toFixed(2),
prevHourConv: hourAgg.conversions.toFixed(2),
prevHourSearchIS: hourSearchIS,
prevHourRankLostIS: hourRankLostIS,
prevHourBudgetLostIS: hourBudgetLostIS,
todayImpr: Math.round(todayAgg.impressions),
todayClk: Math.round(todayAgg.clicks),
todayCost: (todayAgg.costMicros / 1000000).toFixed(2),
todayConv: todayAgg.conversions.toFixed(2),
todaySearchIS: 'n/a',
todayRankLostIS: 'n/a',
todayBudgetLostIS: 'n/a',
policyIssueRate: 'n/a',
};
}
function buildLiteFeishuText(summary, timelineContext) {
const ctx = timelineContext || {
hourDelta: null,
recentHourlyLine: '',
monitorCumulative: null,
regressionDetected: false,
segmentResetCount: 0,
};
const lines = [
'[Google Ads Hourly Lite]',
`run_id: ${summary.runId}`,
`account: ${summary.accountName || summary.accountId} (${summary.accountId})`,
`exported_at: ${summary.exportedAt}`,
`上一小时窗口: ${summary.prevHourLabel}`,
`上一小时: 曝光 ${formatInt(summary.prevHourImpr)} | 点击 ${formatInt(summary.prevHourClk)} | 花费 ${formatDecimal(summary.prevHourCost, 2)}`,
`今日累计快照: 曝光 ${formatInt(summary.todayImpr)} | 点击 ${formatInt(summary.todayClk)} | 花费 ${formatDecimal(summary.todayCost, 2)}`,
];
if (toNumber(summary.todayConv) > 0) {
lines.push(`今日累计转化: ${formatDecimal(summary.todayConv, 2)}`);
}
if (ctx.hourDelta) {
lines.push(
`较上次小时快照新增: 曝光 ${formatInt(ctx.hourDelta.impressions)} | 点击 ${formatInt(ctx.hourDelta.clicks)} | 花费 ${formatDecimal(ctx.hourDelta.cost, 2)}`
);
}
if (ctx.regressionDetected) {
lines.push(
'警告: 今日累计快照较上一条小时快照出现回退，已把小时监控累计从当前快照重新分段，避免把旧脏数据继续滚入今天累计。'
);
}
if (CONFIG.INCLUDE_HOURLY_TIMELINE_LINE && ctx.recentHourlyLine) {
lines.push(ctx.recentHourlyLine);
}
if (
CONFIG.INCLUDE_MONITOR_CUMULATIVE_LINE &&
ctx.monitorCumulative &&
(toNumber(ctx.monitorCumulative.impressions) > 0 ||
toNumber(ctx.monitorCumulative.clicks) > 0 ||
toNumber(ctx.monitorCumulative.cost) > 0 ||
toNumber(ctx.monitorCumulative.conversions) > 0)
) {
lines.push(
`今日小时监控累计: 曝光 ${formatInt(ctx.monitorCumulative.impressions)} | 点击 ${formatInt(ctx.monitorCumulative.clicks)} | 花费 ${formatDecimal(ctx.monitorCumulative.cost, 2)}`
);
}
return lines.join('\n');
}
function buildLiteFallbackFeishuText(runId, account, tz, reason) {
const nowText = formatDate(new Date(), tz);
return [
'[Google Ads Hourly Lite] FALLBACK',
`run_id: ${safeString(runId)}`,
`time: ${nowText}`,
`account_id: ${safeString(account && account.getCustomerId && account.getCustomerId())}`,
`account_name: ${safeString(account && account.getName && account.getName())}`,
`reason: ${safeString(reason) || 'UNKNOWN'}`,
'note: summary build failed or empty; check 00_RUN_LOG_LITE and 90_LITE_SUMMARY immediately.',
].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) {
Logger.log('Feishu skipped: reason=NO_WEBHOOK source=' + credentialSource);
return { sent: false, code: 0, reason: 'NO_WEBHOOK', source: credentialSource };
}
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;
}
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',
};
}
}
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(
CONFIG.RAC_FEISHU_WEBHOOK_URL || CONFIG.FEISHU_WEBHOOK_URL
).trim();
const cToken = safeString(CONFIG.RAC_FEISHU_TOKEN || CONFIG.FEISHU_TOKEN).trim();
if (pWebhook && pToken) {
return { webhook: pWebhook, token: pToken, source: 'script_properties_pair' };
}
if (cWebhook && cToken) {
return { webhook: cWebhook, token: cToken, source: 'config_pair' };
}
if (pWebhook && !pToken) {
return { webhook: pWebhook, token: '', source: 'script_properties_webhook_only' };
}
if (cWebhook && !cToken) {
return { webhook: cWebhook, token: '', source: 'config_webhook_only' };
}
return { webhook: pWebhook || cWebhook || '', token: pToken || cToken || '', source: 'incomplete' };
}
function debugPingFeishu() {
const now = Utilities.formatDate(new Date(), 'Asia/Shanghai', 'yyyy-MM-dd HH:mm:ss');
const result = sendFeishuText('[Google Ads Hourly Lite] 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(
CONFIG.RAC_FEISHU_WEBHOOK_URL || CONFIG.FEISHU_WEBHOOK_URL || ''
).trim();
const configToken = safeString(CONFIG.RAC_FEISHU_TOKEN || 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;
}
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 getOrCreateHourlyTimelineSheet(ss) {
const sh = getOrCreateSheet(ss, CONFIG.HOURLY_TIMELINE_SHEET);
if (sh.getLastRow() === 0) {
sh.getRange(1, 1, 1, 15).setValues([[
'timestamp',
'day_key',
'run_id',
'today_impr',
'today_clicks',
'today_cost',
'today_conv',
'delta_impr',
'delta_clicks',
'delta_cost',
'delta_conv',
'search_is',
'rank_lost',
'budget_lost',
'policy_issue_rate',
]]);
}
return sh;
}
function appendHourlyTimelineAndBuildContext(ss, summary) {
const sh = getOrCreateHourlyTimelineSheet(ss);
const accountTimezone = safeString(summary.accountTimezone).trim();
const exportedAtText = normalizeLiteTimestampText(
summary.exportedAt,
accountTimezone
);
const dayKey = exportedAtText.substring(0, 10);
const prev = readLatestHourlyTimelineRow(sh);
const current = {
impressions: toNumber(summary.todayImpr),
clicks: toNumber(summary.todayClk),
cost: toNumber(summary.todayCost),
conversions: toNumber(summary.todayConv),
};
const prevDayKey = prev ? safeString(prev.dayKey) : '';
const sameDay = prev && prevDayKey === dayKey;
const regressionDetected =
sameDay &&
isHourlySnapshotRegression(current, {
impressions: prev.todayImpressions,
clicks: prev.todayClicks,
cost: prev.todayCost,
conversions: prev.todayConversions,
});
const hourDelta = {
impressions: regressionDetected
? 0
: sameDay
? calcPositiveDelta(current.impressions, prev.todayImpressions)
: current.impressions,
clicks: regressionDetected
? 0
: sameDay
? calcPositiveDelta(current.clicks, prev.todayClicks)
: current.clicks,
cost: regressionDetected
? 0
: sameDay
? calcPositiveDelta(current.cost, prev.todayCost)
: current.cost,
conversions: regressionDetected
? 0
: sameDay
? calcPositiveDelta(current.conversions, prev.todayConversions)
: current.conversions,
};
sh.appendRow([
exportedAtText,
dayKey,
safeString(summary.runId),
formatInt(current.impressions),
formatInt(current.clicks),
formatDecimal(current.cost, 2),
formatDecimal(current.conversions, 2),
formatInt(hourDelta.impressions),
formatInt(hourDelta.clicks),
formatDecimal(hourDelta.cost, 2),
formatDecimal(hourDelta.conversions, 2),
safeString(summary.todaySearchIS),
safeString(summary.todayRankLostIS),
safeString(summary.todayBudgetLostIS),
safeString(summary.policyIssueRate),
]);
const keepRows = Math.max(200, Number(CONFIG.HOURLY_TIMELINE_KEEP_ROWS || 3000));
const maxRows = keepRows + 1;
if (sh.getLastRow() > maxRows) {
sh.deleteRows(2, sh.getLastRow() - maxRows);
}
const segment = readLatestHourlyTimelineSegment(sh, dayKey);
const recentRows = segment.rows.slice(
Math.max(0, segment.rows.length - Math.max(1, Number(CONFIG.HOURLY_TIMELINE_RECENT_HOURS || 10)))
);
const monitorCumulative = sumHourlyTimelineRows(segment.rows);
return {
hourDelta: hourDelta,
recentHourlyLine: buildRecentHourlyLine(recentRows, accountTimezone),
monitorCumulative: monitorCumulative,
regressionDetected: regressionDetected,
segmentResetCount: segment.resetCount,
};
}
function readLatestHourlyTimelineRow(sh) {
if (!sh || sh.getLastRow() < 2) return null;
const row = sh.getRange(sh.getLastRow(), 1, 1, 15).getValues()[0];
return {
dayKey: safeString(row[1]),
todayImpressions: toNumber(row[3]),
todayClicks: toNumber(row[4]),
todayCost: toNumber(row[5]),
todayConversions: toNumber(row[6]),
};
}
function readLatestHourlyTimelineSegment(sh, dayKey) {
if (!sh || sh.getLastRow() < 2) {
return { rows: [], resetCount: 0 };
}
const targetDay = safeString(dayKey).trim();
const values = sh.getRange(2, 1, sh.getLastRow() - 1, 15).getValues();
const sameDayRows = targetDay
? values.filter(function(row) {
return safeString(row[1]).trim() === targetDay;
})
: values;
if (sameDayRows.length <= 1) {
return { rows: sameDayRows, resetCount: 0 };
}
let segmentStart = 0;
let resetCount = 0;
for (let i = 1; i < sameDayRows.length; i++) {
const prev = sameDayRows[i - 1];
const current = sameDayRows[i];
if (
isHourlySnapshotRegression(
{
impressions: toNumber(current[3]),
clicks: toNumber(current[4]),
cost: toNumber(current[5]),
conversions: toNumber(current[6]),
},
{
impressions: toNumber(prev[3]),
clicks: toNumber(prev[4]),
cost: toNumber(prev[5]),
conversions: toNumber(prev[6]),
}
)
) {
segmentStart = i;
resetCount++;
}
}
return {
rows: sameDayRows.slice(segmentStart),
resetCount: resetCount,
};
}
function sumHourlyTimelineRows(rows) {
if (!rows || rows.length === 0) {
return { impressions: 0, clicks: 0, cost: 0, conversions: 0 };
}
let impr = 0;
let clicks = 0;
let cost = 0;
let conv = 0;
for (let i = 0; i < rows.length; i++) {
impr += toNumber(rows[i][7]);
clicks += toNumber(rows[i][8]);
cost += toNumber(rows[i][9]);
conv += toNumber(rows[i][10]);
}
return {
impressions: impr,
clicks: clicks,
cost: cost,
conversions: conv,
};
}
function buildRecentHourlyLine(rows, tz) {
if (!rows || rows.length === 0) return '';
const changed = rows.filter(function(row) {
return toNumber(row[8]) > 0 || toNumber(row[9]) > 0 || toNumber(row[10]) > 0;
});
const last = rows[rows.length - 1];
const lastTotalClicks = last ? formatInt(last[4]) : '0';
const lastTotalCost = last ? formatDecimal(last[5], 2) : '0.00';
if (changed.length === 0) {
return `最近${String(rows.length)}次小时轨迹: 无新增点击 | 当前总点击 ${lastTotalClicks} | 当前总花费 ${lastTotalCost}`;
}
const parts = changed.slice(-4).map(function(row) {
const ts = normalizeLiteTimestampText(row[0], tz);
const hh = ts.length >= 13 ? ts.substring(11, 13) : '--';
return `${hh}:00 +${formatInt(row[8])}clk/$${formatDecimal(row[9], 2)}`;
});
return `最近${String(rows.length)}次小时轨迹: ${parts.join(' | ')} | 当前总点击 ${lastTotalClicks} | 当前总花费 ${lastTotalCost}`;
}
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 normalizeLiteTimestampText(value, tz) {
const zone = safeString(tz).trim() || 'America/Los_Angeles';
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 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 formatInt(v) {
return String(Math.max(0, Math.round(toNumber(v))));
}
function formatDecimal(v, digits) {
const n = toNumber(v);
if (!isFinite(n)) return '0';
return n.toFixed(digits);
}
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 isMeaningfulHourlyDrop(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 isHourlySnapshotRegression(currentSnapshot, previousSnapshot) {
return (
isMeaningfulHourlyDrop(
currentSnapshot && currentSnapshot.clicks,
previousSnapshot && previousSnapshot.clicks,
3,
0.2
) ||
isMeaningfulHourlyDrop(
currentSnapshot && currentSnapshot.impressions,
previousSnapshot && previousSnapshot.impressions,
10,
0.2
) ||
isMeaningfulHourlyDrop(
currentSnapshot && currentSnapshot.cost,
previousSnapshot && previousSnapshot.cost,
0.5,
0.2
)
);
}
function normalizeShareMetric(v) {
const n = toNumber(v);
if (n <= 0) return 0;
if (n <= 1) return n;
if (n <= 100) return n / 100;
return 1;
}
function findCol(headers, name) {
const idx = headers.indexOf(name);
if (idx < 0) throw new Error('missing column: ' + name);
return idx;
}
function findColOptional(headers, name) {
return headers.indexOf(name);
}
本文档为站内渲染。原始文件本地路径：saas/source/knowledge-world/Knowledge-World-项目-Practice-Google-Ads-ads-data-script-02_ho-26b886.txt（仅本地保留，不入库不部署）