04 sheets auto export apps script

const EXPORT_CONFIG = {
  // 必填：要导出的目标表 URL（可填 02-hourly-ads 或 03-daily-ads）
  SPREADSHEET_URL: '',
  DRIVE_FOLDER_ID: '',
  EXPORT_XLSX: true,
  EXPORT_CSV_SHEETS: ['90_LITE_SUMMARY', '90_DIAG_SUMMARY', '91_top_campaigns', '00_STATUS', '00_DATA_FRESHNESS'],
  ENABLE_FEISHU: true,
  FEISHU_WEBHOOK_URL: 'https://open.feishu.cn/open-apis/bot/v2/hook/2c4db3a0-7bdc-42a6-bc0e-44da7344fe87',
  FEISHU_TOKEN: 'oRbJgofj3viHwkzjflTozh',
};

/**
 * 建议 1h 触发一次。
 * 输出到 Google Drive（云端）。
 */
function exportAdsSnapshot() {
  if (!EXPORT_CONFIG.SPREADSHEET_URL) {
    throw new Error('EXPORT_CONFIG.SPREADSHEET_URL is required');
  }
  const ss = SpreadsheetApp.openByUrl(EXPORT_CONFIG.SPREADSHEET_URL);
  const id = ss.getId();
  const tz = ss.getSpreadsheetTimeZone() || 'Asia/Shanghai';
  const ts = Utilities.formatDate(new Date(), tz, 'yyyyMMdd-HHmmss');

  const folder = getTargetFolder();
  const links = [];

  if (EXPORT_CONFIG.EXPORT_XLSX) {
    const xlsxBlob = fetchExportBlob(id, `format=xlsx`);
    const xlsxFile = folder.createFile(xlsxBlob.setName(`all-ads-${ts}.xlsx`));
    links.push(`XLSX: ${xlsxFile.getUrl()}`);
  }

  for (let i = 0; i < EXPORT_CONFIG.EXPORT_CSV_SHEETS.length; i++) {
    const name = EXPORT_CONFIG.EXPORT_CSV_SHEETS[i];
    const sh = ss.getSheetByName(name);
    if (!sh) continue;
    const gid = sh.getSheetId();
    const csvBlob = fetchExportBlob(id, `format=csv&gid=${gid}`);
    const file = folder.createFile(csvBlob.setName(`${name}-${ts}.csv`));
    links.push(`${name}: ${file.getUrl()}`);
  }

  if (EXPORT_CONFIG.ENABLE_FEISHU) {
    const text = ['[Ads Snapshot Export]', `time: ${Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd HH:mm:ss')}`]
      .concat(links)
      .join('\n');
    sendFeishuText(text);
  }
}

/**
 * 可选：一键安装每小时触发器。
 */
function installHourlyExportTrigger() {
  clearExportTriggers();
  ScriptApp.newTrigger('exportAdsSnapshot')
    .timeBased()
    .everyHours(1)
    .inTimezone('Asia/Shanghai')
    .create();
}

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

function getTargetFolder() {
  if (EXPORT_CONFIG.DRIVE_FOLDER_ID) {
    return DriveApp.getFolderById(EXPORT_CONFIG.DRIVE_FOLDER_ID);
  }
  return DriveApp.getRootFolder();
}

function fetchExportBlob(spreadsheetId, query) {
  const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/export?${query}`;
  const token = ScriptApp.getOAuthToken();
  return UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + token },
    muteHttpExceptions: true,
  }).getBlob();
}

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

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

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

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

本文档为站内渲染。原始文件本地路径：saas/source/ads/广告-ads-data-script-04_sheets-auto-export_apps-script-6e3e66.txt（仅本地保留，不入库不部署）