03 daily full google ads script
本地来源：广告/ads-scripts/03_daily-full_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/1-e-xppqbrDcuInoMHcUzsCOiJJdwaUq8jnAGUP2Hnd0/edit',
MAX_RUNTIME_MINUTES: 24,
RESUME_KEY: 'ADS_EXPORT_NEXT_JOB_INDEX',
LOG_SHEET: '00_RUN_LOG',
STATUS_SHEET: '00_STATUS',
FRESHNESS_SHEET: '00_DATA_FRESHNESS',
KEYWORD_QUALITY_SHEET: '07_keyword_quality_7d',
KEYWORD_QUALITY_SHARD_STATS_SHEET: '07_keyword_quality_shard_stats',
ZERO_IMPR_SAMPLE_SHEET: '08_keyword_zero_impr_7d_sample',
NEG_ADGROUP_SAMPLE_SHEET: '11_neg_keywords_adgroup_sample',
KEYWORD_INVENTORY_SHEET: '07_keyword_inventory_all',
POLICY_TOPIC_SHEET: '20_policy_topic_details',
DIAG_SHEET: '90_DIAG_SUMMARY',
TOP_CAMPAIGNS_SHEET: '91_top_campaigns',
BASE_ROWS: 60,
BASE_COLS: 8,
ZERO_IMPR_MAX_ROWS: 15000,
NEG_ADGROUP_MAX_ROWS: 20000,
KEYWORD_INVENTORY_MAX_ROWS: 100000,
KEYWORD_QUALITY_SHARD_MAX_ROWS: 180000,
KEYWORD_QUALITY_SHARD_CAMPAIGN_BATCH: 60,
WORKBOOK_CELL_SOFT_LIMIT: 9000000,
JOB_START_HEADROOM_MINUTES: 4,
POST_STEP_HEADROOM_MINUTES: 2,
LOOP_HEADROOM_MINUTES: 1,
FORCE_FULL_REFRESH: false,
DROP_UNMANAGED_SHEETS: false,
CLEAR_RUN_LOG_EACH_RUN: true,
ENABLE_FEISHU: true,
FEISHU_WEBHOOK_URL: '',
FEISHU_TOKEN: '',
};
function main() {
const ss = SpreadsheetApp.openByUrl(CONFIG.SPREADSHEET_URL);
const account = AdsApp.currentAccount();
const tz = account.getTimeZone();
const dateWindow = getDateWindow(tz);
const start = new Date();
const runId = Utilities.formatDate(start, tz, 'yyyyMMdd-HHmmss');
const jobs = buildJobs(dateWindow);
const props = PropertiesService.getScriptProperties();
if (CONFIG.FORCE_FULL_REFRESH) {
props.deleteProperty(CONFIG.RESUME_KEY);
}
let nextIdx = parseInt(props.getProperty(CONFIG.RESUME_KEY) || '0', 10);
if (isNaN(nextIdx) || nextIdx < 0 || nextIdx > jobs.length) nextIdx = 0;
ensureLogHeader(ss);
if (nextIdx === 0) {
hardResetWorkbook(ss, jobs);
}
writeStatus(
ss,
runId,
account,
formatDate(start, tz),
nextIdx,
jobs.length,
'RUNNING',
''
);
for (let i = nextIdx; i < jobs.length; i++) {
if (isNearTimeout(start, CONFIG.JOB_START_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
i,
jobs.length,
jobs[i].tab,
'near timeout before starting next heavy job',
jobs[i].query
);
return;
}
const cells = getWorkbookCellCount(ss);
if (cells > CONFIG.WORKBOOK_CELL_SOFT_LIMIT) {
props.setProperty(CONFIG.RESUME_KEY, String(i));
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'PAUSED_CELL_GUARD',
jobs[i].tab,
0,
0,
`workbook cells near limit: ${cells}`,
truncate(jobs[i].query, 500),
]);
writeStatus(
ss,
runId,
account,
formatDate(start, tz),
i,
jobs.length,
'PAUSED_CELL_GUARD',
`cells=${cells}`
);
return;
}
runExportJob(ss, account, tz, runId, jobs[i]);
props.setProperty(CONFIG.RESUME_KEY, String(i + 1));
writeStatus(
ss,
runId,
account,
formatDate(start, tz),
i + 1,
jobs.length,
'RUNNING',
''
);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
'POST_STEPS',
'near timeout before post-step exports',
''
);
return;
}
try {
const rows = exportZeroImprKeywordSample(ss, dateWindow, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.ZERO_IMPR_SAMPLE_SHEET,
rows,
0,
'',
'AdsApp.search zero impression keyword sample',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.ZERO_IMPR_SAMPLE_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search zero impression keyword sample',
]);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
'near timeout before negative keyword sample',
''
);
return;
}
try {
const rows = exportNegAdgroupKeywordSample(ss, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
rows,
0,
'',
'AdsApp.search negative adgroup keyword sample',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search negative adgroup keyword sample',
]);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
CONFIG.KEYWORD_INVENTORY_SHEET,
'near timeout before keyword inventory snapshot',
''
);
return;
}
try {
const rows = exportKeywordInventorySnapshot(ss, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.KEYWORD_INVENTORY_SHEET,
rows,
0,
'',
'AdsApp.search keyword inventory snapshot',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.KEYWORD_INVENTORY_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search keyword inventory snapshot',
]);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
CONFIG.KEYWORD_QUALITY_SHEET,
'near timeout before keyword quality sharded export',
''
);
return;
}
try {
const qualityRes = exportKeywordQualitySharded(ss, dateWindow, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.KEYWORD_QUALITY_SHEET,
qualityRes.rows,
0,
`countries=${qualityRes.countries}; shards=${qualityRes.shards}`,
'AdsApp.search keyword quality sharded',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.KEYWORD_QUALITY_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search keyword quality sharded',
]);
}
if (isNearTimeout(start, CONFIG.POST_STEP_HEADROOM_MINUTES)) {
pauseForTimeout(
ss,
props,
runId,
account,
tz,
start,
jobs.length,
jobs.length,
CONFIG.POLICY_TOPIC_SHEET,
'near timeout before policy topic export',
''
);
return;
}
try {
const topicRows = exportPolicyTopicDetails(ss, start);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
CONFIG.POLICY_TOPIC_SHEET,
topicRows,
0,
'',
'AdsApp.search policy details',
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.POLICY_TOPIC_SHEET,
0,
0,
truncate(String(e), 1000),
'AdsApp.search policy details',
]);
}
try {
buildDiagnosis(ss);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.DIAG_SHEET,
0,
0,
truncate(String(e), 1000),
'buildDiagnosis',
]);
}
try {
buildTopCampaigns(ss);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.TOP_CAMPAIGNS_SHEET,
0,
0,
truncate(String(e), 1000),
'buildTopCampaigns',
]);
}
try {
writeDataFreshness(ss, account, tz, runId, dateWindow);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
CONFIG.FRESHNESS_SHEET,
0,
0,
truncate(String(e), 1000),
'writeDataFreshness',
]);
}
props.deleteProperty(CONFIG.RESUME_KEY);
writeStatus(
ss,
runId,
account,
formatDate(start, tz),
jobs.length,
jobs.length,
'FINISHED',
'all jobs finished'
);
if (CONFIG.ENABLE_FEISHU) {
try {
sendFeishuText(buildDailyFeishuText(ss, runId, account, tz, start));
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
'FEISHU',
0,
0,
truncate(String(e), 1000),
'send daily summary',
]);
}
}
}
function buildJobs(dateWindow) {
return [
{
tab: '01_customer',
minCols: 5,
query: `
SELECT
customer.id,
customer.descriptive_name,
customer.currency_code,
customer.time_zone,
customer.status
FROM customer
`,
},
{
tab: '02_campaign_metrics_30d',
minCols: 17,
query: `
SELECT
campaign.id,
campaign.name,
campaign.status,
campaign.serving_status,
campaign.advertising_channel_type,
campaign.bidding_strategy_type,
campaign_budget.amount_micros,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value,
metrics.search_impression_share,
metrics.search_rank_lost_impression_share,
metrics.search_budget_lost_impression_share
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date BETWEEN '${dateWindow.last30Start}' AND '${dateWindow.today}'
`,
},
{
tab: '03_campaign_daily_30d',
minCols: 11,
query: `
SELECT
campaign.id,
campaign.name,
campaign.status,
segments.date,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date BETWEEN '${dateWindow.last30Start}' AND '${dateWindow.today}'
`,
},
{
tab: '04_ad_group_metrics_30d',
minCols: 14,
query: `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group.status,
ad_group.type,
ad_group.cpc_bid_micros,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value
FROM ad_group
WHERE ad_group.status != REMOVED
AND segments.date BETWEEN '${dateWindow.last30Start}' AND '${dateWindow.today}'
`,
},
{
tab: '05_ad_policy_status',
minCols: 10,
query: `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_ad.ad.id,
ad_group_ad.status,
ad_group_ad.policy_summary.approval_status,
ad_group_ad.policy_summary.review_status,
ad_group_ad.ad.type,
ad_group_ad.ad.final_urls
FROM ad_group_ad
WHERE ad_group_ad.status != REMOVED
`,
},
{
tab: '06_asset_text_performance',
minCols: 9,
query: `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_ad.ad.id,
ad_group_ad_asset_view.field_type,
ad_group_ad_asset_view.performance_label,
asset.id,
asset.text_asset.text
FROM ad_group_ad_asset_view
`,
},
{
tab: '09_search_terms_7d',
minCols: 12,
query: `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
search_term_view.search_term,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions,
metrics.conversions_value
FROM search_term_view
WHERE segments.date BETWEEN '${dateWindow.last30Start}' AND '${dateWindow.today}'
AND metrics.impressions > 0
LIMIT 100000
`,
},
{
tab: '10_neg_keywords_campaign',
minCols: 6,
query: `
SELECT
campaign.id,
campaign.name,
campaign_criterion.criterion_id,
campaign_criterion.status,
campaign_criterion.keyword.text,
campaign_criterion.keyword.match_type
FROM campaign_criterion
WHERE campaign_criterion.negative = TRUE
AND campaign_criterion.type = KEYWORD
`,
},
{
tab: '12_campaign_today',
minCols: 9,
query: `
SELECT
campaign.id,
campaign.name,
campaign.status,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date DURING TODAY
`,
},
{
tab: '13_campaign_yesterday',
minCols: 9,
query: `
SELECT
campaign.id,
campaign.name,
campaign.status,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions
FROM campaign
WHERE campaign.status != REMOVED
AND campaign.advertising_channel_type = SEARCH
AND segments.date DURING YESTERDAY
`,
},
];
}
function runExportJob(ss, account, tz, runId, job) {
const t0 = new Date();
try {
const sh = prepareTargetSheet(ss, job.tab, job.minCols || CONFIG.BASE_COLS);
AdsApp.report(job.query).exportToSheet(sh);
const rows = Math.max(sh.getLastRow() - 1, 0);
trimSheetToContent(sh, job.minCols || CONFIG.BASE_COLS);
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'OK',
job.tab,
rows,
secondsBetween(t0, new Date()),
'',
truncate(job.query, 500),
]);
} catch (e) {
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'ERROR',
job.tab,
0,
secondsBetween(t0, new Date()),
truncate(String(e), 1000),
truncate(job.query, 500),
]);
}
}
function exportZeroImprKeywordSample(ss, dateWindow, startDate) {
const sh = prepareTargetSheet(ss, CONFIG.ZERO_IMPR_SAMPLE_SHEET, 9);
sh.getRange(1, 1, 1, 9).setValues([[
'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.cpc_bid_micros',
'metrics.impressions',
]]);
const q = `
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.cpc_bid_micros,
metrics.impressions
FROM keyword_view
WHERE ad_group_criterion.status = ENABLED
AND segments.date BETWEEN '${dateWindow.last7Start}' AND '${dateWindow.today}'
AND metrics.impressions = 0
LIMIT ${CONFIG.ZERO_IMPR_MAX_ROWS}
`;
const it = AdsApp.search(q);
const outRows = [];
while (it.hasNext() && outRows.length < CONFIG.ZERO_IMPR_MAX_ROWS) {
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) break;
const x = it.next();
outRows.push([
safeString(x.campaign && x.campaign.id),
safeString(x.campaign && x.campaign.name),
safeString(x.adGroup && x.adGroup.id),
safeString(x.adGroup && x.adGroup.name),
safeString(x.adGroupCriterion && x.adGroupCriterion.criterionId),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.text
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.matchType
),
toNumber(x.adGroupCriterion && x.adGroupCriterion.cpcBidMicros),
toNumber(x.metrics && x.metrics.impressions),
]);
}
if (outRows.length > 0) {
sh.getRange(2, 1, outRows.length, 9).setValues(outRows);
}
trimSheetToContent(sh, 9);
return outRows.length;
}
function exportNegAdgroupKeywordSample(ss, startDate) {
const sh = prepareTargetSheet(ss, CONFIG.NEG_ADGROUP_SAMPLE_SHEET, 8);
sh.getRange(1, 1, 1, 8).setValues([[
'campaign.id',
'campaign.name',
'ad_group.id',
'ad_group.name',
'ad_group_criterion.criterion_id',
'ad_group_criterion.status',
'ad_group_criterion.keyword.text',
'ad_group_criterion.keyword.match_type',
]]);
const q = `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_criterion.criterion_id,
ad_group_criterion.status,
ad_group_criterion.keyword.text,
ad_group_criterion.keyword.match_type
FROM ad_group_criterion
WHERE ad_group_criterion.negative = TRUE
AND ad_group_criterion.type = KEYWORD
LIMIT ${CONFIG.NEG_ADGROUP_MAX_ROWS}
`;
const it = AdsApp.search(q);
const outRows = [];
while (it.hasNext() && outRows.length < CONFIG.NEG_ADGROUP_MAX_ROWS) {
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) break;
const x = it.next();
outRows.push([
safeString(x.campaign && x.campaign.id),
safeString(x.campaign && x.campaign.name),
safeString(x.adGroup && x.adGroup.id),
safeString(x.adGroup && x.adGroup.name),
safeString(x.adGroupCriterion && x.adGroupCriterion.criterionId),
safeString(x.adGroupCriterion && x.adGroupCriterion.status),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.text
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.matchType
),
]);
}
if (outRows.length > 0) {
sh.getRange(2, 1, outRows.length, 8).setValues(outRows);
}
trimSheetToContent(sh, 8);
return outRows.length;
}
function exportKeywordInventorySnapshot(ss, startDate) {
const sh = prepareTargetSheet(ss, CONFIG.KEYWORD_INVENTORY_SHEET, 14);
sh.getRange(1, 1, 1, 14).setValues([[
'campaign.id',
'campaign.name',
'ad_group.id',
'ad_group.name',
'ad_group_criterion.criterion_id',
'ad_group_criterion.status',
'ad_group_criterion.keyword.text',
'ad_group_criterion.keyword.match_type',
'ad_group_criterion.cpc_bid_micros',
'ad_group_criterion.quality_info.quality_score',
'ad_group_criterion.quality_info.creative_quality_score',
'ad_group_criterion.quality_info.post_click_quality_score',
'ad_group_criterion.quality_info.search_predicted_ctr',
'ad_group_criterion.final_urls',
]]);
const q = `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_criterion.criterion_id,
ad_group_criterion.status,
ad_group_criterion.keyword.text,
ad_group_criterion.keyword.match_type,
ad_group_criterion.cpc_bid_micros,
ad_group_criterion.quality_info.quality_score,
ad_group_criterion.quality_info.creative_quality_score,
ad_group_criterion.quality_info.post_click_quality_score,
ad_group_criterion.quality_info.search_predicted_ctr,
ad_group_criterion.final_urls
FROM ad_group_criterion
WHERE ad_group_criterion.type = KEYWORD
AND ad_group_criterion.negative = FALSE
AND ad_group_criterion.status != REMOVED
LIMIT ${CONFIG.KEYWORD_INVENTORY_MAX_ROWS}
`;
const it = AdsApp.search(q);
const outRows = [];
while (it.hasNext() && outRows.length < CONFIG.KEYWORD_INVENTORY_MAX_ROWS) {
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) break;
const x = it.next();
outRows.push([
safeString(x.campaign && x.campaign.id),
safeString(x.campaign && x.campaign.name),
safeString(x.adGroup && x.adGroup.id),
safeString(x.adGroup && x.adGroup.name),
safeString(x.adGroupCriterion && x.adGroupCriterion.criterionId),
safeString(x.adGroupCriterion && x.adGroupCriterion.status),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.text
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.matchType
),
toNumber(x.adGroupCriterion && x.adGroupCriterion.cpcBidMicros),
toNumber(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.qualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.creativeQualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.postClickQualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.searchPredictedCtr
),
safeJson(x.adGroupCriterion && x.adGroupCriterion.finalUrls),
]);
}
if (outRows.length > 0) {
sh.getRange(2, 1, outRows.length, 14).setValues(outRows);
}
trimSheetToContent(sh, 14);
return outRows.length;
}
function exportKeywordQualitySharded(ss, dateWindow, startDate) {
const out = prepareTargetSheet(ss, CONFIG.KEYWORD_QUALITY_SHEET, 20);
out.getRange(1, 1, 1, 20).setValues([[
'campaign.id',
'campaign.name',
'campaign.country_code',
'ad_group.id',
'ad_group.name',
'ad_group_criterion.criterion_id',
'ad_group_criterion.status',
'ad_group_criterion.keyword.text',
'ad_group_criterion.keyword.match_type',
'ad_group_criterion.cpc_bid_micros',
'ad_group_criterion.quality_info.quality_score',
'ad_group_criterion.quality_info.creative_quality_score',
'ad_group_criterion.quality_info.post_click_quality_score',
'ad_group_criterion.quality_info.search_predicted_ctr',
'metrics.impressions',
'metrics.clicks',
'metrics.ctr',
'metrics.average_cpc',
'metrics.cost_micros',
'metrics.conversions',
]]);
const shardStats = prepareTargetSheet(ss, CONFIG.KEYWORD_QUALITY_SHARD_STATS_SHEET, 9);
shardStats.getRange(1, 1, 1, 9).setValues([[
'country_code',
'shard_index',
'campaign_count',
'campaign_ids',
'rows_exported',
'status',
'error_or_note',
'window_from',
'window_to',
]]);
const campaignMeta = loadCampaignShardMeta(ss);
const byCountry = {};
for (let i = 0; i < campaignMeta.length; i++) {
const x = campaignMeta[i];
const country = extractCountryFromCampaignName(x.name);
if (!byCountry[country]) byCountry[country] = [];
byCountry[country].push(x);
}
const countries = Object.keys(byCountry).sort(function(a, b) {
return byCountry[b].length - byCountry[a].length;
});
let writeAt = 2;
let shardWriteAt = 2;
let totalRows = 0;
let shardCount = 0;
let hitCap = false;
const maxRows = CONFIG.KEYWORD_QUALITY_SHARD_MAX_ROWS;
const batchSize = Math.max(1, CONFIG.KEYWORD_QUALITY_SHARD_CAMPAIGN_BATCH);
for (let i = 0; i < countries.length; i++) {
const country = countries[i];
const group = byCountry[country];
const chunks = chunkList(group, batchSize);
for (let c = 0; c < chunks.length; c++) {
shardCount++;
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) {
shardStats.getRange(shardWriteAt, 1, 1, 9).setValues([[
country,
shardCount,
chunks[c].length,
chunks[c].map(function(v) { return v.id; }).join('|'),
0,
'TIMEOUT_GUARD',
'stopped before shard execution due to runtime headroom',
dateWindow.last7Start,
dateWindow.today,
]]);
shardWriteAt++;
trimSheetToContent(out, 20);
trimSheetToContent(shardStats, 9);
return {
rows: totalRows,
shards: shardCount,
countries: countries.length,
};
}
const ids = [];
for (let m = 0; m < chunks[c].length; m++) {
const id = normalizeGaqlId(chunks[c][m].id);
if (id) ids.push(id);
}
if (ids.length === 0) continue;
const q = `
SELECT
campaign.id,
campaign.name,
ad_group.id,
ad_group.name,
ad_group_criterion.criterion_id,
ad_group_criterion.status,
ad_group_criterion.keyword.text,
ad_group_criterion.keyword.match_type,
ad_group_criterion.cpc_bid_micros,
ad_group_criterion.quality_info.quality_score,
ad_group_criterion.quality_info.creative_quality_score,
ad_group_criterion.quality_info.post_click_quality_score,
ad_group_criterion.quality_info.search_predicted_ctr,
metrics.impressions,
metrics.clicks,
metrics.ctr,
metrics.average_cpc,
metrics.cost_micros,
metrics.conversions
FROM keyword_view
WHERE ad_group_criterion.status != REMOVED
AND segments.date BETWEEN '${dateWindow.last7Start}' AND '${dateWindow.today}'
AND campaign.id IN (${ids.join(',')})
`;
const localRows = [];
let status = 'OK';
let note = '';
try {
const it = AdsApp.search(q);
while (it.hasNext()) {
if (totalRows >= maxRows) {
hitCap = true;
break;
}
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) {
status = 'TIMEOUT_GUARD';
note = 'stopped while reading shard due to runtime headroom';
break;
}
const x = it.next();
localRows.push([
safeString(x.campaign && x.campaign.id),
safeString(x.campaign && x.campaign.name),
extractCountryFromCampaignName(x.campaign && x.campaign.name),
safeString(x.adGroup && x.adGroup.id),
safeString(x.adGroup && x.adGroup.name),
safeString(x.adGroupCriterion && x.adGroupCriterion.criterionId),
safeString(x.adGroupCriterion && x.adGroupCriterion.status),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.text
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.keyword &&
x.adGroupCriterion.keyword.matchType
),
toNumber(x.adGroupCriterion && x.adGroupCriterion.cpcBidMicros),
toNumber(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.qualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.creativeQualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.postClickQualityScore
),
safeString(
x.adGroupCriterion &&
x.adGroupCriterion.qualityInfo &&
x.adGroupCriterion.qualityInfo.searchPredictedCtr
),
toNumber(x.metrics && x.metrics.impressions),
toNumber(x.metrics && x.metrics.clicks),
toNumber(x.metrics && x.metrics.ctr),
toNumber(x.metrics && x.metrics.averageCpc),
toNumber(x.metrics && x.metrics.costMicros),
toNumber(x.metrics && x.metrics.conversions),
]);
totalRows++;
}
} catch (e) {
status = 'ERROR';
note = truncate(String(e), 500);
}
if (localRows.length > 0) {
out.getRange(writeAt, 1, localRows.length, 20).setValues(localRows);
writeAt += localRows.length;
}
if (hitCap && status === 'OK') {
status = 'ROW_CAP_REACHED';
note = `stopped at ${maxRows} rows`;
}
shardStats.getRange(shardWriteAt, 1, 1, 9).setValues([[
country,
shardCount,
ids.length,
ids.join('|'),
localRows.length,
status,
note,
dateWindow.last7Start,
dateWindow.today,
]]);
shardWriteAt++;
if (hitCap || status === 'TIMEOUT_GUARD') {
trimSheetToContent(out, 20);
trimSheetToContent(shardStats, 9);
return {
rows: totalRows,
shards: shardCount,
countries: countries.length,
};
}
}
if (hitCap) break;
}
trimSheetToContent(out, 20);
trimSheetToContent(shardStats, 9);
return {
rows: totalRows,
shards: shardCount,
countries: countries.length,
};
}
function loadCampaignShardMeta(ss) {
const sh = ss.getSheetByName('02_campaign_metrics_30d');
if (!sh || sh.getLastRow() < 2) return [];
const values = sh.getDataRange().getValues();
const h = values[0];
const iId = findCol(h, 'campaign.id');
const iName = findCol(h, 'campaign.name');
const seen = {};
const out = [];
for (let i = 1; i < values.length; i++) {
const id = normalizeGaqlId(values[i][iId]);
if (!id || seen[id]) continue;
seen[id] = true;
out.push({
id: id,
name: safeString(values[i][iName]),
});
}
return out;
}
function exportPolicyTopicDetails(ss, startDate) {
const sh = prepareTargetSheet(ss, CONFIG.POLICY_TOPIC_SHEET, 12);
sh.getRange(1, 1, 1, 12).setValues([[
'source',
'campaign_name',
'ad_group_name',
'ad_id',
'approval_status',
'review_status',
'topic',
'type',
'evidence_json',
'notes',
'captured_at',
'resource',
]]);
const outRows = [];
const ts = Utilities.formatDate(
new Date(),
AdsApp.currentAccount().getTimeZone(),
'yyyy-MM-dd HH:mm:ss'
);
const adQuery = `
SELECT
campaign.name,
ad_group.name,
ad_group_ad.ad.id,
ad_group_ad.policy_summary.approval_status,
ad_group_ad.policy_summary.review_status
FROM ad_group_ad
WHERE ad_group_ad.status != REMOVED
AND ad_group_ad.policy_summary.approval_status != APPROVED
`;
const adIt = AdsApp.search(adQuery);
while (adIt.hasNext()) {
if (startDate && isNearTimeout(startDate, CONFIG.LOOP_HEADROOM_MINUTES)) break;
const row = adIt.next();
const ps = row.adGroupAd && row.adGroupAd.policySummary;
const entries = (ps && ps.policyTopicEntries) ? ps.policyTopicEntries : [];
if (!entries || entries.length === 0) {
outRows.push([
'AD',
row.campaign ? row.campaign.name : '',
row.adGroup ? row.adGroup.name : '',
row.adGroupAd && row.adGroupAd.ad ? row.adGroupAd.ad.id : '',
ps ? safeString(ps.approvalStatus) : '',
ps ? safeString(ps.reviewStatus) : '',
'',
'',
'',
'no policyTopicEntries on ad object',
ts,
'ad_group_ad',
]);
continue;
}
for (let i = 0; i < entries.length; i++) {
const e = entries[i];
outRows.push([
'AD',
row.campaign ? row.campaign.name : '',
row.adGroup ? row.adGroup.name : '',
row.adGroupAd && row.adGroupAd.ad ? row.adGroupAd.ad.id : '',
ps ? safeString(ps.approvalStatus) : '',
ps ? safeString(ps.reviewStatus) : '',
safeString(e.topic),
safeString(e.type),
safeJson(e.evidences || []),
'',
ts,
'ad_group_ad',
]);
}
}
try {
const assetQuery = `
SELECT
asset.id,
asset.name,
asset.type
FROM asset
WHERE asset.status != REMOVED
LIMIT 1
`;
AdsApp.search(assetQuery);
outRows.push([
'ASSET',
'',
'',
'',
'',
'',
'',
'',
'',
'asset-level policy topic entries not reliably exposed in Ads Scripts report/search for this account version',
ts,
'asset',
]);
} catch (e) {
outRows.push([
'ASSET',
'',
'',
'',
'',
'',
'',
'',
'',
'asset policy details query not supported: ' + truncate(String(e), 300),
ts,
'asset',
]);
}
if (outRows.length > 0) {
sh.getRange(2, 1, outRows.length, 12).setValues(outRows);
}
trimSheetToContent(sh, 12);
return outRows.length;
}
function buildDiagnosis(ss) {
const out = prepareTargetSheet(ss, CONFIG.DIAG_SHEET, 3);
out.getRange(1, 1, 1, 3).setValues([['metric', 'value', 'note']]);
const c = ss.getSheetByName('02_campaign_metrics_30d');
if (!c || c.getLastRow() < 2) {
out.appendRow(['error', 'missing 02_campaign_metrics_30d', 'rerun script']);
trimSheetToContent(out, 3);
return;
}
const vals = c.getDataRange().getValues();
const h = vals[0];
const iStatus = findCol(h, 'campaign.status');
const iImpr = findCol(h, 'metrics.impressions');
const iClicks = findCol(h, 'metrics.clicks');
const iCost = findCol(h, 'metrics.cost_micros');
const iConv = findCol(h, 'metrics.conversions');
const iConvVal = findCol(h, 'metrics.conversions_value');
const iRankLost = findCol(h, 'metrics.search_rank_lost_impression_share');
const iBudgetLost = findCol(h, 'metrics.search_budget_lost_impression_share');
let total = 0;
let enabled = 0;
let enabledZeroImpr = 0;
let impr = 0;
let clicks = 0;
let cost = 0;
let conv = 0;
let convValue = 0;
let highRankLost = 0;
let highBudgetLost = 0;
for (let r = 1; r < vals.length; r++) {
const row = vals[r];
const status = safeString(row[iStatus]);
const vImpr = toNumber(row[iImpr]);
const vClicks = toNumber(row[iClicks]);
const vCost = toNumber(row[iCost]);
const vConv = toNumber(row[iConv]);
const vConvVal = toNumber(row[iConvVal]);
const vRankLost = toNumber(row[iRankLost]);
const vBudgetLost = toNumber(row[iBudgetLost]);
total++;
impr += vImpr;
clicks += vClicks;
cost += vCost;
conv += vConv;
convValue += vConvVal;
if (status === 'ENABLED') {
enabled++;
if (vImpr === 0) enabledZeroImpr++;
}
if (vImpr >= 100 && vRankLost >= 0.5) highRankLost++;
if (vImpr >= 100 && vBudgetLost >= 0.3) highBudgetLost++;
}
let disapprovedAds = 0;
let approvedLimitedAds = 0;
let adCount = 0;
let campaignWithAds = 0;
let adGroupWithAds = 0;
const adPol = ss.getSheetByName('05_ad_policy_status');
if (adPol && adPol.getLastRow() >= 2) {
const p = adPol.getDataRange().getValues();
const ph = p[0];
const iA = findCol(ph, 'ad_group_ad.policy_summary.approval_status');
const iCampaignId = findCol(ph, 'campaign.id');
const iAdGroupId = findCol(ph, 'ad_group.id');
const campaignSet = new Set();
const adGroupSet = new Set();
for (let i = 1; i < p.length; i++) {
const approval = safeString(p[i][iA]);
adCount++;
if (approval === 'DISAPPROVED') disapprovedAds++;
if (approval === 'APPROVED_LIMITED') approvedLimitedAds++;
campaignSet.add(safeString(p[i][iCampaignId]));
adGroupSet.add(safeString(p[i][iAdGroupId]));
}
campaignWithAds = campaignSet.has('') ? campaignSet.size - 1 : campaignSet.size;
adGroupWithAds = adGroupSet.has('') ? adGroupSet.size - 1 : adGroupSet.size;
}
let adGroupTotal = 0;
const ag = ss.getSheetByName('04_ad_group_metrics_30d');
if (ag && ag.getLastRow() >= 2) {
const a = ag.getDataRange().getValues();
const ah = a[0];
const iAgId = findCol(ah, 'ad_group.id');
const adGroupSet = new Set();
for (let i = 1; i < a.length; i++) {
adGroupSet.add(safeString(a[i][iAgId]));
}
adGroupTotal = adGroupSet.has('') ? adGroupSet.size - 1 : adGroupSet.size;
}
let runErrors = 0;
const log = ss.getSheetByName(CONFIG.LOG_SHEET);
if (log && log.getLastRow() >= 2) {
const l = log.getDataRange().getValues();
const lh = l[0];
const iS = findCol(lh, 'status');
for (let i = 1; i < l.length; i++) {
if (safeString(l[i][iS]) === 'ERROR') runErrors++;
}
}
const ctr = impr > 0 ? clicks / impr : 0;
const avgCpc = clicks > 0 ? cost / 1000000 / clicks : 0;
const cpa = conv > 0 ? cost / 1000000 / conv : 0;
const roas = cost > 0 ? convValue / (cost / 1000000) : 0;
const campaignWithAdsRate = total > 0 ? campaignWithAds / total : 0;
const adGroupWithAdsRate = adGroupTotal > 0 ? adGroupWithAds / adGroupTotal : 0;
const approvedLimitedRate = adCount > 0 ? approvedLimitedAds / adCount : 0;
const disapprovedRate = adCount > 0 ? disapprovedAds / adCount : 0;
const range = getCampaignDailyDateRange(ss);
const keywordQualityRows = getSheetDataRowCount(ss, CONFIG.KEYWORD_QUALITY_SHEET);
const searchTermRows = getSheetDataRowCount(ss, '09_search_terms_7d');
const keywordInventoryRows = getSheetDataRowCount(ss, CONFIG.KEYWORD_INVENTORY_SHEET);
out.appendRow(['campaign_total', total, 'campaign count in 30d table']);
out.appendRow(['campaign_enabled', enabled, 'enabled campaign count']);
out.appendRow(['campaign_with_ads', campaignWithAds, 'campaigns having at least one ad in 05_ad_policy_status']);
out.appendRow(['campaign_with_ads_rate', campaignWithAdsRate.toFixed(4), 'campaign_with_ads / campaign_total']);
out.appendRow(['adgroup_total', adGroupTotal, 'ad group count in 04_ad_group_metrics_30d']);
out.appendRow(['adgroup_with_ads', adGroupWithAds, 'ad groups having at least one ad in 05_ad_policy_status']);
out.appendRow(['adgroup_with_ads_rate', adGroupWithAdsRate.toFixed(4), 'adgroup_with_ads / adgroup_total']);
out.appendRow(['ads_total', adCount, 'ad count in 05_ad_policy_status']);
out.appendRow(['enabled_zero_impr_30d', enabledZeroImpr, 'enabled campaigns with zero impressions']);
out.appendRow(['enabled_zero_impr_rate', enabled > 0 ? (enabledZeroImpr / enabled).toFixed(4) : '0', 'zero impression rate']);
out.appendRow(['impressions_30d', Math.round(impr), 'sum metrics.impressions']);
out.appendRow(['clicks_30d', Math.round(clicks), 'sum metrics.clicks']);
out.appendRow(['ctr_30d', ctr.toFixed(4), 'clicks / impressions']);
out.appendRow(['cost_30d', (cost / 1000000).toFixed(2), 'sum cost (account currency)']);
out.appendRow(['avg_cpc_30d', avgCpc.toFixed(4), 'cost / clicks']);
out.appendRow(['conversions_30d', conv.toFixed(2), 'sum conversions']);
out.appendRow(['conv_value_30d', convValue.toFixed(2), 'sum conversion value']);
out.appendRow(['cpa_30d', cpa.toFixed(4), 'cost / conversions']);
out.appendRow(['roas_30d', roas.toFixed(4), 'conversion value / cost']);
out.appendRow(['disapproved_ads', disapprovedAds, 'from 05_ad_policy_status']);
out.appendRow(['disapproved_ads_rate', disapprovedRate.toFixed(4), 'disapproved_ads / ads_total']);
out.appendRow(['approved_limited_ads', approvedLimitedAds, 'from 05_ad_policy_status']);
out.appendRow(['approved_limited_ads_rate', approvedLimitedRate.toFixed(4), 'approved_limited_ads / ads_total']);
out.appendRow(['jobs_error_count', runErrors, 'ERROR count in 00_RUN_LOG']);
out.appendRow(['keyword_quality_rows', keywordQualityRows, `row count of ${CONFIG.KEYWORD_QUALITY_SHEET}`]);
out.appendRow(['search_term_rows', searchTermRows, 'row count of 09_search_terms_7d']);
out.appendRow(['keyword_inventory_rows', keywordInventoryRows, `row count of ${CONFIG.KEYWORD_INVENTORY_SHEET}`]);
out.appendRow(['daily_data_earliest', range.earliest, 'from 03_campaign_daily_30d']);
out.appendRow(['daily_data_latest', range.latest, 'from 03_campaign_daily_30d']);
out.appendRow(['high_rank_lost_campaigns', highRankLost, 'impr>=100 and rank lost >=0.5']);
out.appendRow(['high_budget_lost_campaigns', highBudgetLost, 'impr>=100 and budget lost >=0.3']);
if (campaignWithAdsRate < 0.1) {
out.appendRow(['action_0', 'increase ad coverage first', 'too many campaigns/ad groups have no ads']);
}
if (disapprovedAds > 0) {
out.appendRow(['action_1', 'fix policy first', 'disapproved ads block serving']);
}
if (approvedLimitedRate > 0.15) {
out.appendRow(['action_1b', 'reduce policy-limited assets', 'approved_limited ratio is high']);
}
if (enabled > 0 && enabledZeroImpr / enabled > 0.5) {
out.appendRow(['action_2', 'consolidate campaigns', 'too many enabled campaigns with zero impression']);
}
if (runErrors > 0) {
out.appendRow(['action_2b', 'rerun and check ERROR tabs', 'missing tables will make diagnosis incomplete']);
}
if (keywordInventoryRows > 0 && keywordQualityRows === 0) {
out.appendRow(['action_2c', 'not a script bug: no keyword traffic', 'keywords exist but no rows in keyword metrics window']);
}
if (keywordInventoryRows === 0) {
out.appendRow(['action_2d', 'no positive keyword inventory found', 'check whether keyword upload has succeeded']);
}
if (searchTermRows === 0) {
out.appendRow(['action_2e', 'search term report empty', 'usually means near-zero serving in current window']);
}
if (highBudgetLost > highRankLost) {
out.appendRow(['action_3', 'raise budget first', 'budget lost is dominant']);
} else if (highRankLost > 0) {
out.appendRow(['action_3', 'raise bids and quality', 'rank lost is dominant']);
}
trimSheetToContent(out, 3);
}
function buildTopCampaigns(ss) {
const source = ss.getSheetByName('02_campaign_metrics_30d');
const out = prepareTargetSheet(ss, CONFIG.TOP_CAMPAIGNS_SHEET, 13);
out.getRange(1, 1, 1, 13).setValues([[
'rank',
'campaign_id',
'campaign_name',
'status',
'impressions_30d',
'clicks_30d',
'ctr_30d',
'cost_30d',
'avg_cpc_30d',
'conversions_30d',
'conv_value_30d',
'cpa_30d',
'roas_30d',
]]);
if (!source || source.getLastRow() < 2) {
out.appendRow(['1', '', 'no source data', '', 0, 0, 0, 0, 0, 0, 0, 0, 0]);
trimSheetToContent(out, 13);
return;
}
const vals = source.getDataRange().getValues();
const h = vals[0];
const iId = findCol(h, 'campaign.id');
const iName = findCol(h, 'campaign.name');
const iStatus = findCol(h, 'campaign.status');
const iImpr = findCol(h, 'metrics.impressions');
const iClicks = findCol(h, 'metrics.clicks');
const iCost = findCol(h, 'metrics.cost_micros');
const iConv = findCol(h, 'metrics.conversions');
const iConvVal = findCol(h, 'metrics.conversions_value');
const rows = [];
for (let r = 1; r < vals.length; r++) {
const row = vals[r];
const impr = toNumber(row[iImpr]);
const clicks = toNumber(row[iClicks]);
const costMicros = toNumber(row[iCost]);
const conv = toNumber(row[iConv]);
const convVal = toNumber(row[iConvVal]);
const cost = costMicros / 1000000;
const ctr = impr > 0 ? clicks / impr : 0;
const cpa = conv > 0 ? cost / conv : 0;
const avgCpc = clicks > 0 ? cost / clicks : 0;
const roas = cost > 0 ? convVal / cost : 0;
rows.push({
id: safeString(row[iId]),
name: safeString(row[iName]),
status: safeString(row[iStatus]),
impr: impr,
clicks: clicks,
ctr: ctr,
cost: cost,
avgCpc: avgCpc,
conv: conv,
convVal: convVal,
cpa: cpa,
roas: roas,
});
}
rows.sort(function(a, b) {
if (b.conv !== a.conv) return b.conv - a.conv;
if (b.convVal !== a.convVal) return b.convVal - a.convVal;
if (b.clicks !== a.clicks) return b.clicks - a.clicks;
return b.impr - a.impr;
});
const topN = Math.min(rows.length, 100);
const outRows = [];
for (let i = 0; i < topN; i++) {
const x = rows[i];
outRows.push([
i + 1,
x.id,
x.name,
x.status,
Math.round(x.impr),
Math.round(x.clicks),
x.ctr,
x.cost,
x.avgCpc,
x.conv,
x.convVal,
x.cpa,
x.roas,
]);
}
if (outRows.length > 0) {
out.getRange(2, 1, outRows.length, 13).setValues(outRows);
}
trimSheetToContent(out, 13);
}
function writeDataFreshness(ss, account, tz, runId, dateWindow) {
const sh = prepareTargetSheet(ss, CONFIG.FRESHNESS_SHEET, 2);
const now = new Date();
const range = getCampaignDailyDateRange(ss);
sh.getRange(1, 1, 1, 2).setValues([['key', 'value']]);
sh.getRange(2, 1, 10, 2).setValues([
['run_id', runId],
['exported_at_account_tz', formatDate(now, tz)],
['account_id', String(account.getCustomerId())],
['account_name', safeString(account.getName())],
['account_timezone', tz],
['daily_data_earliest', range.earliest || dateWindow.last30Start],
['daily_data_latest', range.latest || dateWindow.today],
['is_realtime_stream', 'NO'],
['data_type', 'snapshot at script run time'],
['note', `window=${dateWindow.last30Start}..${dateWindow.today}; today metrics can still update later`],
]);
trimSheetToContent(sh, 2);
}
function getCampaignDailyDateRange(ss) {
const out = { earliest: '', latest: '' };
const sh = ss.getSheetByName('03_campaign_daily_30d');
if (!sh || sh.getLastRow() < 2) return out;
const values = sh.getDataRange().getValues();
const h = values[0];
const idxDate = h.indexOf('segments.date');
if (idxDate < 0) return out;
let min = null;
let max = null;
for (let i = 1; i < values.length; i++) {
const d = values[i][idxDate];
if (!d) continue;
const key = normalizeDateKey(d);
if (!key) continue;
if (min === null || key < min) min = key;
if (max === null || key > max) max = key;
}
out.earliest = min || '';
out.latest = max || '';
return out;
}
function getDateWindow(tz) {
const now = new Date();
return {
today: Utilities.formatDate(now, tz, 'yyyy-MM-dd'),
yesterday: Utilities.formatDate(
new Date(now.getTime() - 24 * 60 * 60 * 1000),
tz,
'yyyy-MM-dd'
),
last7Start: Utilities.formatDate(
new Date(now.getTime() - 6 * 24 * 60 * 60 * 1000),
tz,
'yyyy-MM-dd'
),
last30Start: Utilities.formatDate(
new Date(now.getTime() - 29 * 24 * 60 * 60 * 1000),
tz,
'yyyy-MM-dd'
),
};
}
function normalizeDateKey(v) {
if (v instanceof Date) {
return Utilities.formatDate(v, 'UTC', 'yyyy-MM-dd');
}
const s = String(v);
if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s;
return '';
}
function extractCountryFromCampaignName(name) {
const s = safeString(name);
const parts = s.split('-');
if (parts.length >= 2 && /^[A-Za-z]{2}$/.test(parts[1])) {
return parts[1].toUpperCase();
}
return 'ZZ';
}
function normalizeGaqlId(v) {
const s = safeString(v).replace(/\.0$/, '').trim();
return /^\d+$/.test(s) ? s : '';
}
function chunkList(arr, size) {
const out = [];
const n = Math.max(1, Number(size) || 1);
for (let i = 0; i < arr.length; i += n) {
out.push(arr.slice(i, i + n));
}
return out;
}
function hardResetWorkbook(ss, jobs) {
const managedTabs = getManagedTabs(jobs);
const managedSet = new Set(managedTabs);
if (CONFIG.DROP_UNMANAGED_SHEETS) {
const all = ss.getSheets();
for (let i = all.length - 1; i >= 0; i--) {
const sh = all[i];
const name = sh.getName();
if (managedSet.has(name)) continue;
if (ss.getSheets().length <= 1) {
sh.clear();
resizeSheetGrid(sh, CONFIG.BASE_ROWS, CONFIG.BASE_COLS);
} else {
ss.deleteSheet(sh);
}
}
}
if (CONFIG.CLEAR_RUN_LOG_EACH_RUN) {
const logSh = getOrCreateSheet(ss, CONFIG.LOG_SHEET);
logSh.clear();
resizeSheetGrid(logSh, CONFIG.BASE_ROWS, 10);
}
const statusSh = getOrCreateSheet(ss, CONFIG.STATUS_SHEET);
statusSh.clear();
resizeSheetGrid(statusSh, CONFIG.BASE_ROWS, 2);
const extra = [
CONFIG.FRESHNESS_SHEET,
CONFIG.KEYWORD_QUALITY_SHEET,
CONFIG.KEYWORD_QUALITY_SHARD_STATS_SHEET,
CONFIG.ZERO_IMPR_SAMPLE_SHEET,
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
CONFIG.KEYWORD_INVENTORY_SHEET,
CONFIG.POLICY_TOPIC_SHEET,
CONFIG.DIAG_SHEET,
CONFIG.TOP_CAMPAIGNS_SHEET,
];
const colMap = {};
for (let i = 0; i < jobs.length; i++) {
colMap[jobs[i].tab] = jobs[i].minCols || CONFIG.BASE_COLS;
}
colMap[CONFIG.FRESHNESS_SHEET] = 2;
colMap[CONFIG.KEYWORD_QUALITY_SHEET] = 20;
colMap[CONFIG.KEYWORD_QUALITY_SHARD_STATS_SHEET] = 9;
colMap[CONFIG.ZERO_IMPR_SAMPLE_SHEET] = 9;
colMap[CONFIG.NEG_ADGROUP_SAMPLE_SHEET] = 8;
colMap[CONFIG.KEYWORD_INVENTORY_SHEET] = 14;
colMap[CONFIG.POLICY_TOPIC_SHEET] = 12;
colMap[CONFIG.DIAG_SHEET] = 3;
colMap[CONFIG.TOP_CAMPAIGNS_SHEET] = 13;
const tabs = jobs.map((j) => j.tab).concat(extra);
for (let i = 0; i < tabs.length; i++) {
prepareTargetSheet(ss, tabs[i], colMap[tabs[i]] || CONFIG.BASE_COLS);
}
ensureLogHeader(ss);
}
function getManagedTabs(jobs) {
return jobs.map((j) => j.tab).concat([
CONFIG.LOG_SHEET,
CONFIG.STATUS_SHEET,
CONFIG.FRESHNESS_SHEET,
CONFIG.KEYWORD_QUALITY_SHEET,
CONFIG.KEYWORD_QUALITY_SHARD_STATS_SHEET,
CONFIG.ZERO_IMPR_SAMPLE_SHEET,
CONFIG.NEG_ADGROUP_SAMPLE_SHEET,
CONFIG.KEYWORD_INVENTORY_SHEET,
CONFIG.POLICY_TOPIC_SHEET,
CONFIG.DIAG_SHEET,
CONFIG.TOP_CAMPAIGNS_SHEET,
]);
}
function prepareTargetSheet(ss, name, minCols) {
const sh = getOrCreateSheet(ss, name);
sh.clear();
resizeSheetGrid(sh, CONFIG.BASE_ROWS, Math.max(CONFIG.BASE_COLS, minCols || CONFIG.BASE_COLS));
return sh;
}
function trimSheetToContent(sh, minCols) {
const lastRow = Math.max(sh.getLastRow(), 1);
const lastCol = Math.max(sh.getLastColumn(), minCols || 1);
const targetRows = Math.max(lastRow + 5, 20);
const targetCols = Math.max(lastCol + 1, minCols || 1);
resizeSheetGrid(sh, targetRows, targetCols);
}
function resizeSheetGrid(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 getWorkbookCellCount(ss) {
const sheets = ss.getSheets();
let total = 0;
for (let i = 0; i < sheets.length; i++) {
total += sheets[i].getMaxRows() * sheets[i].getMaxColumns();
}
return total;
}
function getOrCreateSheet(ss, name) {
let sh = ss.getSheetByName(name);
if (!sh) sh = ss.insertSheet(name);
return sh;
}
function ensureLogHeader(ss) {
const sh = getOrCreateSheet(ss, CONFIG.LOG_SHEET);
if (sh.getLastRow() === 0) {
sh.getRange(1, 1, 1, 10).setValues([[
'run_id',
'timestamp',
'account_id',
'account_name',
'status',
'tab',
'row_count',
'duration_sec',
'message',
'query',
]]);
}
}
function appendLog(ss, row) {
const sh = getOrCreateSheet(ss, CONFIG.LOG_SHEET);
sh.appendRow(row);
}
function writeStatus(ss, runId, account, startedAt, nextIndex, totalJobs, status, note) {
const sh = getOrCreateSheet(ss, CONFIG.STATUS_SHEET);
sh.clear();
sh.getRange(1, 1, 10, 2).setValues([
['run_id', runId],
['account_id', String(account.getCustomerId())],
['account_name', safeString(account.getName())],
['started_at', startedAt],
['next_job_index', nextIndex],
['total_jobs', totalJobs],
['status', status],
['progress', `'${nextIndex}/${totalJobs}`],
['note', note],
['updated_at', formatDate(new Date(), account.getTimeZone())],
]);
trimSheetToContent(sh, 2);
}
function getSheetDataRowCount(ss, name) {
const sh = ss.getSheetByName(name);
if (!sh) return 0;
return Math.max(sh.getLastRow() - 1, 0);
}
function resetResumeCheckpoint() {
PropertiesService.getScriptProperties().deleteProperty(CONFIG.RESUME_KEY);
}
function isNearTimeout(startDate, headroomMinutes) {
return elapsedMinutes(startDate) >= (CONFIG.MAX_RUNTIME_MINUTES - (headroomMinutes || 0));
}
function pauseForTimeout(
ss,
props,
runId,
account,
tz,
startedAt,
nextIndex,
totalJobs,
tab,
note,
query
) {
props.setProperty(CONFIG.RESUME_KEY, String(nextIndex));
appendLog(ss, [
runId,
formatDate(new Date(), tz),
String(account.getCustomerId()),
safeString(account.getName()),
'TIMEOUT_GUARD',
tab,
0,
0,
note,
truncate(query || '', 500),
]);
writeStatus(
ss,
runId,
account,
formatDate(startedAt, tz),
nextIndex,
totalJobs,
'PAUSED_TIMEOUT',
note
);
}
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 buildDailyFeishuText(ss, runId, account, tz, startedAt) {
const diag = readMetricSheetMap(ss, CONFIG.DIAG_SHEET);
const topCampaignLines = readTopCampaignLines(ss, CONFIG.TOP_CAMPAIGNS_SHEET, 3);
const lines = [
'[Ads Daily Full] FINISHED',
`run_id: ${runId}`,
`account: ${safeString(account.getName())} (${String(account.getCustomerId())})`,
`started_at: ${formatDate(startedAt, tz)}`,
`finished_at: ${formatDate(new Date(), tz)}`,
`latest_data_date: ${diag.daily_data_latest || ''}`,
`30d: impr=${diag.impressions_30d || '0'}, clicks=${diag.clicks_30d || '0'}, cost=${diag.cost_30d || '0'}, conv=${diag.conversions_30d || '0'}`,
`policy: disapproved=${diag.disapproved_ads || '0'}, approved_limited=${diag.approved_limited_ads || '0'}, errors=${diag.jobs_error_count || '0'}`,
'note: snapshot-based reporting (not per-click realtime stream)',
];
if (topCampaignLines.length > 0) {
lines.push('top_campaigns_30d:');
for (let i = 0; i < topCampaignLines.length; i++) {
lines.push(topCampaignLines[i]);
}
}
return lines.join('\n');
}
function readMetricSheetMap(ss, sheetName) {
const sh = ss.getSheetByName(sheetName);
if (!sh || sh.getLastRow() < 2) return {};
const values = sh.getDataRange().getValues();
const headers = values[0];
const idxMetric = headers.indexOf('metric');
const idxValue = headers.indexOf('value');
if (idxMetric < 0 || idxValue < 0) return {};
const map = {};
for (let i = 1; i < values.length; i++) {
const key = safeString(values[i][idxMetric]).trim();
if (!key) continue;
map[key] = safeString(values[i][idxValue]);
}
return map;
}
function readTopCampaignLines(ss, sheetName, limit) {
const sh = ss.getSheetByName(sheetName);
if (!sh || sh.getLastRow() < 2) return [];
const values = sh.getDataRange().getValues();
const headers = values[0];
const idxRank = findColOptional(headers, 'rank');
const idxName = findColOptional(headers, 'campaign_name');
const idxClicks = findColOptional(headers, 'clicks_30d');
const idxConv = findColOptional(headers, 'conversions_30d');
const idxCost = findColOptional(headers, 'cost_30d');
if (idxName < 0) return [];
const out = [];
const maxRows = Math.max(1, limit || 3);
for (let i = 1; i < values.length && out.length < maxRows; i++) {
const name = safeString(values[i][idxName]);
if (!name) continue;
const rank = idxRank >= 0 ? safeString(values[i][idxRank]) : String(out.length + 1);
const clicks = idxClicks >= 0 ? safeString(values[i][idxClicks]) : '';
const conv = idxConv >= 0 ? safeString(values[i][idxConv]) : '';
const cost = idxCost >= 0 ? safeString(values[i][idxCost]) : '';
out.push(`${rank}. ${name} | conv=${conv || '0'} | clicks=${clicks || '0'} | cost=${cost || '0'}`);
}
return out;
}
function sendFeishuText(text) {
const prop = PropertiesService.getScriptProperties();
const webhookRaw =
prop.getProperty('RAC_FEISHU_WEBHOOK_URL') ||
CONFIG.RAC_FEISHU_WEBHOOK_URL ||
CONFIG.FEISHU_WEBHOOK_URL;
const tokenRaw =
prop.getProperty('RAC_FEISHU_TOKEN') ||
CONFIG.RAC_FEISHU_TOKEN ||
CONFIG.FEISHU_TOKEN;
const webhook = safeString(webhookRaw).trim();
const token = safeString(tokenRaw).trim();
if (!webhook) return;
const isFeishuWebhook = /open\.feishu\.cn\/open-apis\/bot\/v2\/hook\//.test(webhook);
const payload = {
msg_type: 'text',
content: { text: text },
};
const headers = { 'Content-Type': 'application/json' };
let signedTimestamp = '';
if (token && isFeishuWebhook) {
// Feishu custom bot sign:
// string_to_sign = timestamp + "\\n" + secret
// sign = Base64(HMAC_SHA256("", string_to_sign))
signedTimestamp = String(Math.floor(new Date().getTime() / 1000));
const stringToSign = signedTimestamp + '\n' + token;
const signBytes = Utilities.computeHmacSha256Signature(
'',
stringToSign,
Utilities.Charset.UTF_8
);
payload.timestamp = signedTimestamp;
payload.sign = Utilities.base64Encode(signBytes);
} else if (token) {
headers.Authorization = 'Bearer ' + token;
}
const resp = UrlFetchApp.fetch(webhook, {
method: 'post',
headers: headers,
payload: JSON.stringify(payload),
muteHttpExceptions: true,
});
const code = resp.getResponseCode();
const body = resp.getContentText() || '';
const allHeaders = resp.getAllHeaders ? resp.getAllHeaders() : {};
const serverDate =
(allHeaders && (allHeaders.Date || allHeaders.date)) ? String(allHeaders.Date || allHeaders.date) : '';
let appCode = null;
let appMsg = '';
if (body) {
try {
const parsed = JSON.parse(body);
if (parsed && typeof parsed.code !== 'undefined') {
appCode = Number(parsed.code);
appMsg = String(parsed.msg || parsed.message || '');
} else if (parsed && typeof parsed.StatusCode !== 'undefined') {
appCode = Number(parsed.StatusCode);
appMsg = String(parsed.StatusMessage || '');
}
} catch (_e) {}
}
const okHttp = code >= 200 && code < 300;
const okApp = appCode === null || appCode === 0;
if (!okHttp || !okApp) {
Logger.log(
'Feishu send failed. code=' +
code +
', appCode=' +
String(appCode) +
', appMsg=' +
appMsg +
', signedTs=' +
signedTimestamp +
', tokenLen=' +
String(token ? token.length : 0) +
', serverDate=' +
serverDate +
', body=' +
body
);
}
}
function safeJson(v) {
if (v === null || v === undefined) return '';
try {
const s = JSON.stringify(v);
return s === undefined ? '' : s;
} catch (e) {
return safeString(v);
}
}
function safeString(v) {
if (v === null || v === undefined) return '';
return String(v);
}
function toNumber(v) {
if (v === null || v === undefined || v === '') return 0;
const n = Number(v);
return isNaN(n) ? 0 : n;
}
function findCol(headers, name) {
const idx = headers.indexOf(name);
if (idx < 0) throw new Error('missing column: ' + name);
return idx;
}
function findColOptional(headers, name) {
return headers.indexOf(name);
}
本文档为站内渲染。原始文件本地路径：saas/source/ads/广告-ads-scripts-03_daily-full_google-ads-script-68e550.txt（仅本地保留，不入库不部署）