AppScript to Summarize PDFs via Gemini & Email Members

Analytics Reporter.js

// =============================================================================
// Monthly Analytics Reporter
// Stack: Google Apps Script + Gemini API
// Trigger: 8:30 AM on the 1st of each month
//
// Setup steps:
//   1. Run setupScriptProperties() once to store your config
//   2. Run createMonthlyTrigger() once to register the time-based trigger
//   3. Authorize the script when prompted
//   4. That's it — it runs itself every month
// =============================================================================


// =============================================================================
// CONFIGURATION
// Store sensitive values in Script Properties (File > Project properties >
// Script properties), NOT as constants here.
//
// Required Script Properties:
//   GEMINI_API_KEY    — from https://aistudio.google.com/app/apikey
//   TRACKING_SHEET_ID — Google Sheet ID for month-over-month data
//   NOTIFY_EMAIL      — your email for error alerts
//
// Board recipients are pulled dynamically from Google Contacts (see getBoardEmails).
// No need to maintain an email list here — just keep the Contacts label up to date.
// =============================================================================

const CONFIG = {
  // Gmail search — update these to match your Looker Studio report
  SENDER: 'looker-studio-noreply@google.com',
  SUBJECT_PREFIX: 'Your Org Monthly Report',       // ← match your report's subject line

  // For testing — script creates a draft instead of sending when true
  TEST_EMAIL: 'your-test-email@example.com',        // ← your personal email for review
  USE_TEST_MODE: true,                              // ← flip to false when going live

  // Google Contacts label for recipients (case-sensitive, must match exactly)
  BOARD_CONTACTS_LABEL: 'Your-Board-Label',         // ← your Contacts label name

  // Gemini model
  GEMINI_MODEL: 'gemini-2.5-flash',
  GEMINI_ENDPOINT: 'https://generativelanguage.googleapis.com/v1beta/models/',
};


// =============================================================================
// MAIN ENTRY POINT
// Called by the monthly time-based trigger
// =============================================================================

function runMonthlyReport() {
  console.log('=== Monthly Analytics Reporter started ===');

  const props = PropertiesService.getScriptProperties();
  const apiKey = props.getProperty('GEMINI_API_KEY');
  const sheetId = props.getProperty('TRACKING_SHEET_ID');
  const notifyEmail = props.getProperty('NOTIFY_EMAIL');

  if (!apiKey) {
    sendErrorAlert(notifyEmail, 'Missing GEMINI_API_KEY in Script Properties. Run setupScriptProperties() to configure.');
    return;
  }

  try {
    // Step 1: Find the PDF in Gmail
    console.log('Searching Gmail for monthly report...');
    const reportData = findMonthlyReport();

    if (!reportData) {
      const msg = `No report found matching "${CONFIG.SUBJECT_PREFIX}" from ${CONFIG.SENDER}. The email may not have arrived yet — try running manually after the report usually arrives.`;
      console.warn(msg);
      sendErrorAlert(notifyEmail, msg);
      return;
    }

    console.log(`Found report: "${reportData.subject}" with attachment "${reportData.fileName}" (${Math.round(reportData.pdfBytes.length / 1024)} KB)`);

    // Step 2: Analyze with Gemini
    console.log('Sending PDF to Gemini for analysis...');
    const analysis = analyzeWithGemini(reportData.pdfBytes, apiKey);
    console.log('Gemini analysis complete.');

    // Step 3: Track month-over-month (if sheet is configured)
    let momContext = '';
    if (sheetId) {
      console.log('Recording metrics to tracking sheet...');
      momContext = updateTrackingSheet(sheetId, analysis);
    } else {
      console.log('No TRACKING_SHEET_ID set — skipping month-over-month tracking.');
    }

    // Step 4: Resolve recipients and send the email
    let recipients;
    if (CONFIG.USE_TEST_MODE) {
      recipients = CONFIG.TEST_EMAIL;
      console.log(`TEST MODE: Creating draft for ${recipients}`);
    } else {
      console.log(`Looking up "${CONFIG.BOARD_CONTACTS_LABEL}" in Google Contacts...`);
      const boardEmails = getBoardEmails();
      if (boardEmails.length === 0) {
        throw new Error(`No email addresses found in the "${CONFIG.BOARD_CONTACTS_LABEL}" Contacts label. Check that the label exists and members have email addresses.`);
      }
      recipients = boardEmails.join(',');
      console.log(`Sending to ${boardEmails.length} recipients: ${recipients}`);
    }

    sendBoardEmail(recipients, reportData, analysis, momContext);

    // Step 5: Move the original Looker email to trash
    // Only runs after a successful send so we never lose the PDF if something went wrong upstream
    try {
      const message = GmailApp.getMessageById(reportData.messageId);
      message.moveToTrash();
      console.log(`Original report email moved to trash (message ID: ${reportData.messageId})`);
    } catch (trashErr) {
      // Non-fatal — log it but don't fail the run over a cleanup step
      console.warn('Could not move original email to trash:', trashErr.message);
    }

    console.log('=== Monthly report sent successfully ===');

  } catch (err) {
    console.error('Fatal error in runMonthlyReport:', err);
    sendErrorAlert(notifyEmail, `Unhandled error in monthly report: ${err.message}\n\nStack: ${err.stack}`);
  }
}


// =============================================================================
// CONTACTS: READ RECIPIENTS FROM GOOGLE CONTACTS LABEL
// Uses the People API (must be enabled under Services in the editor).
// Membership is managed entirely in Google Contacts — no code changes
// needed when someone joins or leaves the board.
//
// To enable: Services (+ icon in sidebar) > People API > Add
// =============================================================================

function getBoardEmails() {
  // List all contact groups and find the one matching our label
  const groupsResponse = People.ContactGroups.list({ pageSize: 200 });
  const groups = groupsResponse.contactGroups || [];
  const boardGroup = groups.find(g => g.name === CONFIG.BOARD_CONTACTS_LABEL);

  if (!boardGroup) {
    throw new Error(
      `Contact group "${CONFIG.BOARD_CONTACTS_LABEL}" not found. ` +
      `Check contacts.google.com and confirm the label name matches exactly (it's case-sensitive).`
    );
  }

  // Fetch the group with its member resource names
  const groupDetail = People.ContactGroups.get(boardGroup.resourceName, {
    maxMembers: 200,
  });

  const memberResourceNames = groupDetail.memberResourceNames || [];
  if (memberResourceNames.length === 0) {
    console.warn(`"${CONFIG.BOARD_CONTACTS_LABEL}" group exists but has no members.`);
    return [];
  }

  console.log(`Found ${memberResourceNames.length} members in "${CONFIG.BOARD_CONTACTS_LABEL}"`);

  // Batch-fetch contact details to get email addresses
  const batchResponse = People.People.getBatchGet({
    resourceNames: memberResourceNames,
    personFields: 'emailAddresses,names',
  });

  const emails = [];
  for (const entry of (batchResponse.responses || [])) {
    const person = entry.person;
    if (!person) continue;

    // Prefer the primary email; fall back to the first available
    const emailObj = (person.emailAddresses || []).find(e => e.metadata?.primary)
                  || (person.emailAddresses || [])[0];

    if (emailObj?.value) {
      emails.push(emailObj.value);
      const name = person.names?.[0]?.displayName || emailObj.value;
      console.log(`  Recipient: ${name} <${emailObj.value}>`);
    } else {
      const name = person.names?.[0]?.displayName || entry.requestedResourceName;
      console.warn(`  Skipping ${name} — no email address on file in Contacts.`);
    }
  }

  return emails;
}

/**
 * Run this to verify your Contacts setup before going live.
 * Logs all email addresses found in your board Contacts label.
 */
function testGetBoardEmails() {
  const emails = getBoardEmails();
  console.log(`\nTotal recipients found: ${emails.length}`);
  console.log(emails.join('\n'));
}


// =============================================================================
// STEP 1: FIND THE REPORT IN GMAIL
// Returns { subject, fileName, pdfBytes, messageId } or null if not found
// =============================================================================

function findMonthlyReport() {
  // Build a subject search string for the current month
  // Looker Studio report subjects include the send date (e.g., "Mar 1, 2026")
  const today = new Date();
  const monthName = today.toLocaleString('en-US', { month: 'short' }); // "Mar"
  const year = today.getFullYear();
  const day = today.getDate(); // 1
  const expectedSubjectFragment = `${CONFIG.SUBJECT_PREFIX} - ${monthName} ${day}, ${year}`;

  const query = `from:(${CONFIG.SENDER}) subject:("${expectedSubjectFragment}") has:attachment`;
  console.log(`Gmail query: ${query}`);

  const threads = GmailApp.search(query, 0, 5);

  if (!threads || threads.length === 0) {
    // Fallback: try a broader search in case date format varies slightly
    const broadQuery = `from:(${CONFIG.SENDER}) subject:("${CONFIG.SUBJECT_PREFIX}") has:attachment newer_than:2d`;
    console.log(`No exact match — trying broader query: ${broadQuery}`);
    const broadThreads = GmailApp.search(broadQuery, 0, 5);
    if (!broadThreads || broadThreads.length === 0) return null;
    threads.push(...broadThreads);
  }

  // Look through found messages for a PDF attachment
  for (const thread of threads) {
    const messages = thread.getMessages();
    for (const message of messages) {
      const attachments = message.getAttachments();
      for (const attachment of attachments) {
        if (attachment.getContentType() === 'application/pdf') {
          return {
            subject: message.getSubject(),
            fileName: attachment.getName(),
            pdfBytes: attachment.getBytes(),
            messageId: message.getId(),
          };
        }
      }
    }
  }

  return null; // Found emails but no PDF attachment
}


// =============================================================================
// STEP 2: ANALYZE WITH GEMINI
// Sends the PDF as base64 to the Gemini multimodal API
// Returns a structured analysis object
// =============================================================================

function analyzeWithGemini(pdfBytes, apiKey) {
  const base64Pdf = Utilities.base64Encode(pdfBytes);
  const prompt = buildGeminiPrompt();

  const requestBody = {
    contents: [{
      parts: [
        {
          inline_data: {
            mime_type: 'application/pdf',
            data: base64Pdf,
          }
        },
        {
          text: prompt
        }
      ]
    }],
    generationConfig: {
      temperature: 0.3,      // Lower = more factual and consistent
      maxOutputTokens: 8192, // Generous limit — the JSON response with summaries can easily exceed 2048
      responseMimeType: 'application/json', // Forces Gemini to return valid JSON only
    }
  };

  const url = `${CONFIG.GEMINI_ENDPOINT}${CONFIG.GEMINI_MODEL}:generateContent?key=${apiKey}`;

  const response = UrlFetchApp.fetch(url, {
    method: 'POST',
    contentType: 'application/json',
    payload: JSON.stringify(requestBody),
    muteHttpExceptions: true,
  });

  const statusCode = response.getResponseCode();
  const responseText = response.getContentText();

  if (statusCode !== 200) {
    throw new Error(`Gemini API error (${statusCode}): ${responseText}`);
  }

  const json = JSON.parse(responseText);
  const rawText = json.candidates?.[0]?.content?.parts?.[0]?.text;

  if (!rawText) {
    throw new Error('Gemini returned an empty response. Full response: ' + responseText);
  }

  // Log the start and end of what Gemini returned — helps catch truncation issues
  console.log('Gemini response length:', rawText.length, 'chars');
  console.log('Gemini response START:', rawText.substring(0, 300));
  console.log('Gemini response END:', rawText.substring(Math.max(0, rawText.length - 200)));

  // Parse the JSON response with layered fallbacks
  try {
    return JSON.parse(rawText); // Attempt 1: direct parse
  } catch (e1) {
    console.warn('Direct parse failed:', e1.message);
    try {
      // Attempt 2: strip markdown code fences and retry
      const stripped = rawText.replace(/^```json\n?/, '').replace(/\n?```$/, '').trim();
      return JSON.parse(stripped);
    } catch (e2) {
      console.warn('Strip-fences parse failed:', e2.message);
      try {
        // Attempt 3: find the outermost { ... } and parse that
        const match = rawText.match(/\{[\s\S]*\}/);
        if (match) {
          console.log('Attempt 3 extracted block length:', match[0].length);
          return JSON.parse(match[0]);
        }
      } catch (e3) {
        console.warn('Regex-extract parse failed:', e3.message);
      }
      // All attempts failed — return raw text so the email still sends
      console.warn('Could not parse Gemini response as JSON after 3 attempts.');
      return { rawSummary: rawText, parseError: true };
    }
  }
}


// =============================================================================
// GEMINI PROMPT
// Customize this for your organization and report structure.
// =============================================================================

function buildGeminiPrompt() {
  const today = new Date();
  const reportMonth = new Date(today.getFullYear(), today.getMonth() - 1, 1);
  const monthLabel = reportMonth.toLocaleString('en-US', { month: 'long', year: 'numeric' });

  return `
You are analyzing a Google Analytics / Looker Studio PDF report for a nonprofit organization's website. The report covers ${monthLabel}.

Your audience is a nonprofit board of directors — volunteers who care about mission impact and community reach, not technical SEO metrics. Write for clarity and mission alignment, not for marketers.

Please extract and analyze the following from the PDF:

1. TRAFFIC OVERVIEW: Total sessions, unique users, and pageviews for the month.
2. TOP TRAFFIC SOURCES: Which channels drove the most visitors (organic search, direct, social, referral, email)?
3. TOP PAGES: Which pages were most visited? What does this tell us about what the community is looking for?
4. GEOGRAPHIC REACH: Where are visitors coming from? IMPORTANT: This is a US-based nonprofit with a local and domestic focus. Any significant traffic from countries with no plausible connection to the organization's mission should be treated as likely bot or automated traffic — do NOT present it as genuine international reach or audience interest. If suspicious non-local traffic appears, note it briefly but flag that it is likely bot traffic and not reflective of real community engagement.
5. DEVICE BREAKDOWN: Desktop vs. mobile vs. tablet (if available)?
6. KEY OBSERVATIONS: 2-3 things that stand out as genuinely interesting or worth the board's attention.
7. NOTABLE TRENDS: Any significant increases, decreases, or patterns compared to what the data suggests?

Return your response as a JSON object with exactly this structure (no markdown, just raw JSON):

{
  "reportMonth": "${monthLabel}",
  "trafficOverview": {
    "sessions": "<number or 'N/A'>",
    "users": "<number or 'N/A'>",
    "pageviews": "<number or 'N/A'>",
    "summary": "<1-2 sentence plain English summary>"
  },
  "topTrafficSources": [
    { "channel": "<name>", "sessions": "<number>", "percentage": "<pct>" }
  ],
  "topPages": [
    { "page": "<page title or path>", "views": "<number>", "insight": "<why this matters>" }
  ],
  "geographicHighlights": "<Focus on meaningful domestic traffic. Flag any suspicious international traffic as likely bots. Use 'Primarily local/US-based traffic' if that is the accurate picture.>",
  "deviceBreakdown": {
    "desktop": "<pct or N/A>",
    "mobile": "<pct or N/A>",
    "tablet": "<pct or N/A>"
  },
  "keyObservations": [
    "<observation 1>",
    "<observation 2>",
    "<observation 3>"
  ],
  "boardReadySummary": "<3-4 sentences written directly to the board, connecting website activity to mission impact. Avoid jargon. This is the most important field.>"
}

If a data point is not present in the report, use "N/A" rather than guessing.
`;
}


// =============================================================================
// STEP 3: MONTH-OVER-MONTH TRACKING
// Appends a row to a Google Sheet for trend tracking
// Returns a brief MoM context string to include in the email (or '' if first run)
// =============================================================================

function updateTrackingSheet(sheetId, analysis) {
  // Skip tracking if Gemini couldn't parse the report — don't pollute the
  // sheet with a row of N/As that would skew month-over-month comparisons
  if (analysis.parseError) {
    console.warn('Skipping tracking sheet update — Gemini response was not structured JSON.');
    return '';
  }

  const ss = SpreadsheetApp.openById(sheetId);
  let sheet = ss.getSheetByName('Monthly Metrics');

  // Create the sheet with headers if it doesn't exist yet
  if (!sheet) {
    sheet = ss.insertSheet('Monthly Metrics');
    sheet.appendRow([
      'Month',
      'Sessions',
      'Users',
      'Pageviews',
      'Top Channel',
      'Top Page',
      'Recorded At',
    ]);
    sheet.getRange(1, 1, 1, 7).setFontWeight('bold');
    console.log('Created "Monthly Metrics" sheet with headers.');
  }

  // Build the row — gracefully handle parse errors or missing fields
  const ov = analysis.trafficOverview || {};
  const topSource = (analysis.topTrafficSources || [])[0] || {};
  const topPage = (analysis.topPages || [])[0] || {};

  const newRow = [
    analysis.reportMonth || 'Unknown',
    ov.sessions || 'N/A',
    ov.users || 'N/A',
    ov.pageviews || 'N/A',
    topSource.channel ? `${topSource.channel} (${topSource.percentage})` : 'N/A',
    topPage.page || 'N/A',
    new Date().toLocaleString('en-US', { timeZone: 'America/New_York' }),
  ];

  sheet.appendRow(newRow);

  // Build a month-over-month comparison string for the email
  const lastRow = sheet.getLastRow();
  if (lastRow <= 2) {
    return ''; // This is the first data row — nothing to compare yet
  }

  // Get the previous month's row
  const prevRow = sheet.getRange(lastRow - 1, 1, 1, 7).getValues()[0];
  const prevSessions = prevRow[1];
  const currSessions = ov.sessions;

  if (prevSessions && currSessions && !isNaN(parseInt(prevSessions)) && !isNaN(parseInt(currSessions))) {
    const prev = parseInt(prevSessions.toString().replace(/,/g, ''));
    const curr = parseInt(currSessions.toString().replace(/,/g, ''));
    const delta = curr - prev;
    const pct = ((delta / prev) * 100).toFixed(1);
    const direction = delta >= 0 ? 'up' : 'down';
    const sign = delta >= 0 ? '+' : '';
    return `Month over month: Sessions are ${direction} ${sign}${pct}% (${sign}${delta.toLocaleString()} sessions) compared to ${prevRow[0]}.`;
  }

  return '';
}


// =============================================================================
// STEP 4: SEND THE BOARD EMAIL
// In TEST_MODE, creates a draft for review. In production, sends directly.
// =============================================================================

function sendBoardEmail(recipient, reportData, analysis, momContext) {
  const today = new Date();
  const reportMonth = new Date(today.getFullYear(), today.getMonth() - 1, 1);
  const monthLabel = reportMonth.toLocaleString('en-US', { month: 'long', year: 'numeric' });

  const subject = `Website Report — ${monthLabel}`;
  const body = buildEmailBody(analysis, momContext, monthLabel);
  const pdfBlob = Utilities.newBlob(reportData.pdfBytes, 'application/pdf', reportData.fileName);

  const options = {
    htmlBody: body,
    attachments: [pdfBlob],
    name: 'Analytics Bot',
  };

  if (CONFIG.USE_TEST_MODE) {
    // In test mode, create a draft for review instead of sending
    GmailApp.createDraft(CONFIG.TEST_EMAIL, subject, '', options);
    console.log(`TEST MODE: Draft created for ${CONFIG.TEST_EMAIL} — check your Drafts folder.`);
  } else {
    GmailApp.sendEmail(recipient, subject, '', options);
    console.log(`Email sent to ${recipient}`);
  }
}


function buildEmailBody(analysis, momContext, monthLabel) {
  // Handle the case where Gemini couldn't parse into structured JSON
  if (analysis.parseError) {
    return `
      <div style="font-family: Arial, sans-serif; max-width: 640px; margin: 0 auto; color: #333;">
        <h2 style="color: #1a73e8;">Website Report — ${monthLabel}</h2>
        <p>The monthly website analytics report is attached. Here is the AI-generated summary:</p>
        <div style="background: #f8f9fa; border-left: 4px solid #1a73e8; padding: 16px; margin: 16px 0;">
          <pre style="white-space: pre-wrap; font-family: Arial, sans-serif;">${analysis.rawSummary}</pre>
        </div>
        <p style="color: #888; font-size: 12px;">Generated automatically by the Analytics Reporter.</p>
      </div>
    `;
  }

  const ov = analysis.trafficOverview || {};
  const sources = (analysis.topTrafficSources || []).slice(0, 3);
  const pages = (analysis.topPages || []).slice(0, 3);
  const observations = (analysis.keyObservations || []);

  const sourcesHtml = sources.map(s =>
    `<li><strong>${s.channel}</strong>: ${s.sessions} sessions (${s.percentage})</li>`
  ).join('');

  const pagesHtml = pages.map(p =>
    `<li><strong>${p.page}</strong>: ${p.views} views${p.insight ? ` — ${p.insight}` : ''}</li>`
  ).join('');

  const observationsHtml = observations.map(o =>
    `<li>${o}</li>`
  ).join('');

  const momHtml = momContext
    ? `<p style="background: #e8f5e9; border-left: 4px solid #34a853; padding: 12px; margin: 16px 0;">${momContext}</p>`
    : '';

  return `
<div style="font-family: Arial, sans-serif; max-width: 640px; margin: 0 auto; color: #333; line-height: 1.6;">

  <h2 style="color: #1a73e8; margin-bottom: 4px;">Website Report</h2>
  <p style="color: #888; margin-top: 0;">${monthLabel}</p>

  <div style="background: #f0f7ff; border-left: 4px solid #1a73e8; padding: 16px; margin: 20px 0; border-radius: 4px;">
    <h3 style="margin-top: 0; color: #1a73e8;">Board Summary</h3>
    <p style="margin-bottom: 0;">${analysis.boardReadySummary || 'See attached PDF for full details.'}</p>
  </div>

  ${momHtml}

  <h3>Traffic at a Glance</h3>
  <table style="width: 100%; border-collapse: collapse; margin-bottom: 20px;">
    <tr style="background: #f8f9fa;">
      <td style="padding: 10px; border: 1px solid #e0e0e0; font-weight: bold;">Sessions</td>
      <td style="padding: 10px; border: 1px solid #e0e0e0;">${ov.sessions || 'N/A'}</td>
    </tr>
    <tr>
      <td style="padding: 10px; border: 1px solid #e0e0e0; font-weight: bold;">Users</td>
      <td style="padding: 10px; border: 1px solid #e0e0e0;">${ov.users || 'N/A'}</td>
    </tr>
    <tr style="background: #f8f9fa;">
      <td style="padding: 10px; border: 1px solid #e0e0e0; font-weight: bold;">Pageviews</td>
      <td style="padding: 10px; border: 1px solid #e0e0e0;">${ov.pageviews || 'N/A'}</td>
    </tr>
  </table>

  ${sourcesHtml ? `
  <h3>Top Traffic Sources</h3>
  <ul style="padding-left: 20px;">${sourcesHtml}</ul>
  ` : ''}

  ${pagesHtml ? `
  <h3>Most Visited Pages</h3>
  <ul style="padding-left: 20px;">${pagesHtml}</ul>
  ` : ''}

  ${observationsHtml ? `
  <h3>What Stood Out This Month</h3>
  <ul style="padding-left: 20px;">${observationsHtml}</ul>
  ` : ''}

  ${analysis.geographicHighlights && analysis.geographicHighlights !== 'N/A' ? `
  <h3>Geographic Reach</h3>
  <p>${analysis.geographicHighlights}</p>
  ` : ''}

  <hr style="border: none; border-top: 1px solid #e0e0e0; margin: 24px 0;">
  <p style="color: #888; font-size: 12px;">
    The full report is attached as a PDF. This summary was generated automatically
    using the Google Analytics Looker report and Gemini AI.<br>
    Questions? Reply to this email.
  </p>

</div>
  `.trim();
}


// =============================================================================
// ERROR ALERTS
// Sends a plain-text alert to the configured notify email
// =============================================================================

function sendErrorAlert(notifyEmail, message) {
  if (!notifyEmail) {
    console.error('No NOTIFY_EMAIL configured. Error message was:', message);
    return;
  }
  GmailApp.sendEmail(
    notifyEmail,
    '[Analytics Bot] Error Alert',
    `The monthly analytics reporter encountered an issue:\n\n${message}\n\nTimestamp: ${new Date().toISOString()}`
  );
}


// =============================================================================
// SETUP: RUN THESE ONCE
// =============================================================================

/**
 * Run this function once from the Apps Script editor to store your config.
 * Update the values below before running.
 */
function setupScriptProperties() {
  const props = PropertiesService.getScriptProperties();
  props.setProperties({
    'GEMINI_API_KEY':     'YOUR_API_KEY_HERE',        // from https://aistudio.google.com/app/apikey
    'TRACKING_SHEET_ID': 'YOUR_GOOGLE_SHEET_ID_HERE', // the ID from the Sheet URL
    'NOTIFY_EMAIL':      'your-email@example.com',    // where to send error alerts
    // Recipients come from your Google Contacts label automatically.
    // No email list to maintain here.
  });
  console.log('Script properties saved successfully.');
}

/**
 * Run this function once to register the monthly trigger.
 * It will fire at ~8:30 AM on the 1st of each month.
 * (Apps Script triggers have a ~15-minute execution window.)
 */
function createMonthlyTrigger() {
  // Remove any existing triggers for runMonthlyReport to avoid duplicates
  const existing = ScriptApp.getProjectTriggers();
  for (const trigger of existing) {
    if (trigger.getHandlerFunction() === 'runMonthlyReport') {
      ScriptApp.deleteTrigger(trigger);
      console.log('Removed existing trigger.');
    }
  }

  ScriptApp.newTrigger('runMonthlyReport')
    .timeBased()
    .onMonthDay(1)
    .atHour(8)
    .nearMinute(30)
    .create();

  console.log('Monthly trigger created: runMonthlyReport will fire at ~8:30 AM on the 1st of each month.');
}

/**
 * Run this manually at any time to test the full workflow.
 * While USE_TEST_MODE is true, creates a draft instead of sending.
 */
function runManualTest() {
  console.log('Running manual test...');
  runMonthlyReport();
}