我用Codex 跑了一天内容类联盟客，才发现自动化最难的不是发邮件

const CONFIG = {SHEET_NAME: 'outreach_queue',SENDER_NAME: 'anker Team',SENDER_EMAIL: 'andyanker@gmail.com',OFFICIAL_EMAIL: 'xxx@ankerhome.com',DEFAULT_PRODUCT_LINKS: 'https://www.ankerhome.com/collections/DRY_RUN: true};const HEADERS = ['record_id', 'contact_name', 'media_name', 'email', 'email_validation_status', 'site_url', 'category', 'owner','media_type_segment', 'personalization_angle', 'brand_proof', 'official_contact_email','outreach_status', 'template_key', 'send_ready', 'first_sent_at', 'followup_1_sent_at', 'followup_2_sent_at','last_contacted_at', 'reply_status', 'next_action_date', 'free_code', 'product_links', 'notes'];function setupOutreachSheet() {const ss = SpreadsheetApp.getActiveSpreadsheet();let sheet = ss.getSheetByName(CONFIG.SHEET_NAME);if (!sheet) sheet = ss.insertSheet(CONFIG.SHEET_NAME);sheet.clear();sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);sheet.setFrozenRows(1);}function enrichPersonalizationFields() {const sheet = getSheet_();const rows = getRows_(sheet);const headerMap = getHeaderMap_(sheet);rows.forEach(row => {const segment = detectMediaSegment_(row.category || row.media_name || '');setIfExists_(sheet, row._rowIndex, headerMap, 'media_type_segment', segment);setIfExists_(sheet, row._rowIndex, headerMap, 'personalization_angle', buildPersonalizationAngle_(segment));setIfExists_(sheet, row._rowIndex, headerMap, 'brand_proof', buildBrandProof_(segment));setIfExists_(sheet, row._rowIndex, headerMap, 'official_contact_email', CONFIG.OFFICIAL_EMAIL);});}function runOutreachAutomation() {const sheet = getSheet_();const data = getRows_(sheet);const now = new Date();let sent = 0;data.forEach(row => {const decision = decideTemplate_(row, now);if (!decision.shouldSend) return;const subject = renderTemplate_(decision.subject, row);const body = renderTemplate_(decision.body, row);if (!CONFIG.DRY_RUN) {GmailApp.sendEmail(row.email, subject, body, {name: CONFIG.SENDER_NAME,replyTo: CONFIG.OFFICIAL_EMAIL,cc: CONFIG.OFFICIAL_EMAIL});}applyPostSendUpdates_(sheet, row._rowIndex, decision.statusUpdates, now, subject);sent += 1;});returnprocessed ${data.length}, sent ${sent}, dry_run=${CONFIG.DRY_RUN};}function decideTemplate_(row, now) {const outreach = String(row.outreach_status || '').trim();const reply = String(row.reply_status || '').trim();const ready = String(row.send_ready || '').trim();const ev = String(row.email_validation_status || '').trim();const allowed = ['mx_ok', 'valid_format', 'risky_generic_mx_ok'];if (ready !== '是') return { shouldSend: false };if (!allowed.includes(ev)) return { shouldSend: false };if (!row.email) return { shouldSend: false };if (outreach === '待开发') {return {shouldSend: true,subject: 'Collaboration Opportunity with anker Smart Shades',body: getFirstOutreachBody_(),statusUpdates: {outreach_status: '已发送首封',first_sent_at: now,last_contacted_at: now,reply_status: reply || '未回复',template_key: 'first_outreach'}};}if (outreach === '已发送首封' && reply === '未回复' && daysSince_(row.first_sent_at, now) >= 3) {return {shouldSend: true,subject: 'Quick Follow-up on anker Collaboration',body: getFollowup1Body_(),statusUpdates: {outreach_status: '已发送跟进1',followup_1_sent_at: now,last_contacted_at: now,template_key: 'followup_1'}};}if (reply === '有意向' && String(row.template_key || '') === 'interested_reply') {return {shouldSend: true,subject: 'Re: anker Review Collaboration',body: getInterestedReplyBody_(),statusUpdates: {outreach_status: '已回意向',last_contacted_at: now}};}if (outreach === '已回意向' && row.free_code) {return {shouldSend: true,subject: 'Your anker Free Code for Order',body: getFreeCodeBody_(),statusUpdates: {outreach_status: '已发FreeCode',last_contacted_at: now,template_key: 'send_free_code'}};}if (outreach === '已发FreeCode' && reply !== '已下单' && daysSince_(row.last_contacted_at, now) >= 3) {return {shouldSend: true,subject: 'Follow-up on Your anker Free Code',body: getFreeCodeNoOrderBody_(),statusUpdates: {outreach_status: '已催下单',last_contacted_at: now,template_key: 'free_code_no_order'}};}return { shouldSend: false };}function detectMediaSegment_(text) {const t = String(text || '').toLowerCase();if (hasAny_(t, ['smart home','home automation','home assistant','matter','thread','homekit','alexa','google assistant'])) return 'smart_home';if (hasAny_(t, ['3c tech','gadget','tech review','consumer tech','gear'])) return 'tech_review';if (hasAny_(t, ['diy','home improvement','install','renovation','tools','builder'])) return 'diy_home_improvement';if (hasAny_(t, ['design','decor','interior','window treatment','roman shades','roller shades'])) return 'interior_design';if (hasAny_(t, ['outdoor','patio','pergola','garden','outdoor shades'])) return 'outdoor_living';if (hasAny_(t, ['lifestyle','family','home living','renter','convenience'])) return 'lifestyle_home';return 'general_home';}function buildPersonalizationAngle_(segment) {const map = {smart_home: 'Emphasize ecosystem integration, automation workflows, and compatibility with HomeKit/Alexa/Google Assistant.',tech_review: 'Emphasize premium smart features, app/voice control, motorization, and product review value.',diy_home_improvement: 'Emphasize installation experience, customization, and before/after transformation value.',interior_design: 'Emphasize aesthetics, custom fabrics, light control, and design-led home upgrades.',outdoor_living: 'Emphasize patio comfort, privacy, sun protection, and outdoor shade automation.',lifestyle_home: 'Emphasize convenience, privacy, comfort, and modern family living.',general_home: 'Emphasize practical smart-home comfort and premium customization.'};return map[segment] || map.general_home;}function buildBrandProof_(segment) {return 'anker offers custom-made smart shades with remote, app, and voice control, compatible with HomeKit, Alexa, Google Assistant, and SmartThings.';}function renderPersonalizedIntro_(row) {const segment = row.media_type_segment || detectMediaSegment_(row.category || row.media_name || '');const intros = {smart_home:Given your focus on smart home and automation content, I thought anker could be a strong fit for your audience—especially for setups involving HomeKit, Alexa, Google Assistant, and broader smart home ecosystems.,tech_review:Given your focus on tech reviews and connected devices, I thought anker could be a strong fit for your audience as a premium real-world smart home upgrade worth reviewing.,diy_home_improvement:Given your focus on DIY and home improvement, I thought anker could be a strong fit for your audience—especially around installation experience, customization, and real home upgrades.,interior_design:Given your focus on interiors and design-led home content, I thought anker could be a strong fit for your audience by combining aesthetics with smart automation.,outdoor_living:Given your focus on outdoor living and patio comfort, I thought anker could be a strong fit for your audience—especially for privacy, sun protection, and outdoor shade control.,lifestyle_home:Given your focus on lifestyle and home living content, I thought anker could be a strong fit for your audience for daily comfort, privacy, and convenience.,general_home:I thought anker could be a strong fit for your audience as a premium smart home upgrade.};return intros[segment] || intros.general_home;}function getFirstOutreachBody_() {return `Hi {{name}},Hope you’re doing well.I’m reaching out from anker. We came across your work in smart home and home improvement, and thought our smart window treatments could be a strong fit for your audience.anker offers customized motorized shades and curtains designed for modern homes — with options for privacy, blackout, light control, and smart-home convenience.A few features that tend to stand out:Easy installationRemote control and solar charging optionsCompatibility with major smart home systemsCustom sizing for different rooms and window typesWe’ve previously collaborated with media and review platforms such as CNET, Wirecutter, PCMag, and TechRadar, and we’d love to explore whether there could be a fit for a review, feature, or sample-based collaboration with you as well.If you’re open to it, I’d be happy to share more details and suitable product options for your space.Would you be interested in taking a look?Best regards,Andy WangAffiliate & Influencer Marketing ManagerankerOutreach Email: {{sender_email}}Official Email: {{official_email}}`;}function getFollowup1Body_() {return `Hi {{name}},Just following up on my previous email about a potential collaboration with anker.{{personalized_intro}}If you might be interested in reviewing our smart shades, I’d be happy to share more details.Best regards,{{sender_name}}Outreach Email: {{sender_email}}Official Email: {{official_email}}`;}function getInterestedReplyBody_() {return `Hi {{name}},Thank you for your interest in reviewing anker.Could you let us know roughly how many shades you need? For the first collaboration, we usually start with 1-2 shades depending on the project.You can review our most popular collections here:{{product_links}}You can also check our measurement guide here:https://www.ankerhome.com/pages/how-to-measure-windows-for-shadesBest regards,{{sender_name}}Outreach Email: {{sender_email}}Official Email: {{official_email}}`;}function getFreeCodeBody_() {return `Hi {{name}},Thank you again for your interest in anker.You can use the following free code to place your order on our website:Free Code: {{free_code}}Popular collections:{{product_links}}Once you place your order, please share your order number with us.Best regards,{{sender_name}}Outreach Email: {{sender_email}}Official Email: {{official_email}}`;}function getFreeCodeNoOrderBody_() {return `Hi {{name}},We wanted to follow up regarding the anker free code we shared earlier.Have you had a chance to measure your windows and plan the order? Once you’re ready, feel free to place the order and share your order number with us.Best regards,{{sender_name}}Outreach Email: {{sender_email}}Official Email: {{official_email}}`;}function renderTemplate_(tpl, row) {return String(tpl).replaceAll('{{name}}', row.contact_name || row.media_name || 'there').replaceAll('{{sender_name}}', CONFIG.SENDER_NAME).replaceAll('{{sender_email}}', CONFIG.SENDER_EMAIL).replaceAll('{{official_email}}', row.official_contact_email || CONFIG.OFFICIAL_EMAIL).replaceAll('{{free_code}}', row.free_code || '').replaceAll('{{product_links}}', row.product_links || CONFIG.DEFAULT_PRODUCT_LINKS).replaceAll('{{personalized_intro}}', renderPersonalizedIntro_(row)).replaceAll('{{brand_proof}}', row.brand_proof || buildBrandProof_(row.media_type_segment || 'general_home'));}function applyPostSendUpdates_(sheet, rowIndex, updates, now, subject) {const headerMap = getHeaderMap_(sheet);Object.keys(updates).forEach(key => {if (headerMap[key]) sheet.getRange(rowIndex, headerMap[key]).setValue(updates[key]);});if (headerMap.notes) {const oldNote = sheet.getRange(rowIndex, headerMap.notes).getValue();const line =[${Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss')}] ${CONFIG.DRY_RUN ? 'DRY_RUN' : 'SENT'}: ${subject};sheet.getRange(rowIndex, headerMap.notes).setValue(oldNote ? oldNote + '\n' + line : line);}}function getRows_(sheet) {const values = sheet.getDataRange().getValues();const headers = values[0];return values.slice(1).map((row, idx) => {const obj = { _rowIndex: idx + 2 };headers.forEach((h, i) => obj[h] = row[i]);return obj;});}function getHeaderMap_(sheet) {const headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];const map = {};headers.forEach((h, i) => map[h] = i + 1);return map;}function setIfExists_(sheet, rowIndex, headerMap, key, value) {if (headerMap[key]) sheet.getRange(rowIndex, headerMap[key]).setValue(value);}function getSheet_() {const ss = SpreadsheetApp.getActiveSpreadsheet();const sheet = ss.getSheetByName(CONFIG.SHEET_NAME);if (!sheet) throw new Error('Sheet not found: ' + CONFIG.SHEET_NAME);return sheet;}function daysSince_(value, now) {if (!value) return 999;const dt = new Date(value);return Math.floor((now - dt) / (1000 * 60 * 60 * 24));}function hasAny_(text, arr) {return arr.some(k => String(text).toLowerCase().includes(k));}