求助：Google Sheets Apps Script邮件告警脚本开发（多脚本尝试未成功）

Hey there, let's get those email alerts working for your Google Sheet. I know you've tried a few scripts already without luck, so I've put together a tailored solution that covers both of your required conditions, plus some tips to avoid common pitfalls.

The Working Script #

Copy this into your Apps Script editor (replace the placeholder email with your target address):

function checkAndSendAlerts() {
  // Your spreadsheet ID (pulled directly from your shared link)
  const spreadsheetId = "1QKh6OJ1JV401tKh7QggoWpNz4Sfi0ppjHXwSPF8kf4E";
  const sheet = SpreadsheetApp.openById(spreadsheetId).getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const recipient = "XXXX@yyy.com"; // Replace with your alert email

  // Loop through every data row (skipping the header row at index 0)
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    // Grab values from each relevant column (indexes start at 0)
    const colC = row[2];
    const colD = row[3];
    const colE = row[4];
    const colF = row[5];
    const colG = row[6];
    const colH = row[7];
    const colI = row[8];

    // Condition 1: C < D OR C > E → Send "Trigger" alert
    if (typeof colC === 'number' && typeof colD === 'number' && typeof colE === 'number') {
      if (colC < colD || colC > colE) {
        MailApp.sendEmail({
          to: recipient,
          subject: "Sheet Alert: Trigger Condition Met",
          body: "Trigger"
        });
        // Optional: Mark row as alerted to avoid repeats (uncomment below)
        // sheet.getRange(i+1, 10).setValue("Alert Sent");
      }
    }

    // Condition 2: C > (I×F) OR C < (H×G) → Send "Trigger reachd" alert
    const upperLimit = colI * colF;
    const lowerLimit = colH * colG;
    if (typeof colC === 'number' && typeof upperLimit === 'number' && typeof lowerLimit === 'number') {
      if (colC > upperLimit || colC < lowerLimit) {
        MailApp.sendEmail({
          to: recipient,
          subject: "Sheet Alert: Trigger Reached Condition Met",
          body: "Trigger reachd"
        });
        // Optional: Mark row as alerted to avoid repeats (uncomment below)
        // sheet.getRange(i+1, 10).setValue("Alert Reached Sent");
      }
    }
  }
}

Key Details & Customizations #

• Column Indexes: I used 0-based indexing (C = index 2, D = 3, etc.) since that's how Apps Script reads spreadsheet columns. Double-check if your sheet uses the same column order!
• Number Validation: Added checks to ensure we're only comparing numeric values (avoids errors if cells have text or empty values).
• Avoid Repeat Alerts: If you don't want the same row triggering emails every time the script runs, uncomment the sheet.getRange(...) lines. This marks row i+1 (since loops start at 1) in column J (index 9) with a status message. You can adjust the column number if you want to use a different column for tracking.

How to Set Up the Trigger #

1. Open your Google Sheet, go to Extensions > Apps Script.
2. Paste the script above, replace XXXX@yyy.com with your actual alert email.
3. Save the script (click the floppy disk icon) and name it something like SheetAlertSystem.
4. Click the clock icon (Triggers) on the left sidebar.
5. Click Add Trigger:
   • Choose function: checkAndSendAlerts
   • Choose event source: Time-driven
   • Choose type of time based trigger: Pick how often you want the script to check (e.g., every 5 minutes, hourly, daily)
   • Click Save and authorize the script when prompted (you'll need to allow access to your spreadsheet and email).

Troubleshooting Tips #

• Make sure all columns involved (C, D, E, F, G, H, I) are formatted as Number in your sheet (not plain text).
• If you're not getting emails, check your spam folder first. Also, verify that the script has permission to send emails (you can re-authorize by running the script manually once from the Apps Script editor).
• If you only need to monitor specific rows, adjust the loop range (e.g., for (let i = 1; i < 20; i++) to only check rows 2-20).

内容的提问来源于stack exchange，提问作者adinous