如何用Google Apps Script监控列数据，当G列值大于K列时触发邮件

Hey there! I get it—your G column updates automatically via the internet, so the basic onEdit() trigger won’t fire because it only responds to manual user edits. Let’s get this working with the right trigger setup and a solid check function.

Step 1: Write the Core Check & Email Function #

First, let’s build a function that scans each row, compares G and K, and sends your email when the condition is met. I’ll include a way to avoid duplicate emails too (since repeated checks would spam you otherwise):

function checkAndSendAlerts() {
  // Grab your sheet (replace "Sheet1" with your actual sheet name if needed)
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const allData = sheet.getDataRange().getValues();
  const statusColumn = 11; // Column L (0-indexed) to track if we already sent an email

  // Skip header row (start at index 1, which is row 2 in the sheet)
  for (let rowIndex = 1; rowIndex < allData.length; rowIndex++) {
    const gValue = allData[rowIndex][6]; // G is column 7 (0-indexed)
    const kValue = allData[rowIndex][10]; // K is column 11 (0-indexed)
    const alreadySent = allData[rowIndex][statusColumn];

    // Make sure we're comparing numbers, not strings, and haven't sent an email yet
    if (typeof gValue === 'number' && typeof kValue === 'number' && gValue > kValue && !alreadySent) {
      // Call your existing email function (replace with your own code)
      sendCustomEmail(rowIndex + 1, gValue, kValue);
      // Mark this row as alerted in column L
      sheet.getRange(rowIndex + 1, statusColumn + 1).setValue("Alert Sent");
    }
  }
}

// Replace this with your existing email sending logic
function sendCustomEmail(rowNumber, gVal, kVal) {
  const recipient = "your-email@domain.com";
  const subject = `⚠️ Sheet Alert: Row ${rowNumber} G > K`;
  const body = `Heads up! In row ${rowNumber}, the value in column G (${gVal}) is now greater than column K (${kVal}).`;
  
  MailApp.sendEmail(recipient, subject, body);
}

Step 2: Set Up a Time-Driven Trigger #

Since your G column updates automatically, we need a trigger that runs on a schedule to check the values regularly. Here’s how to set it up:

1. Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
2. Click the clock icon (Triggers) in the left sidebar.
3. Click Add Trigger in the bottom-right corner.
4. Configure the trigger like this:
   • Choose which function to run: Select checkAndSendAlerts
   • Choose which deployment to run: Pick the latest deployment (usually "Head")
   • Select event source: Choose Time-driven
   • Select type of time based trigger: Pick a frequency that matches your G column’s update rate (e.g., "Minute timer" > "Every 5 minutes" for frequent updates)
5. Click Save—you’ll need to authorize the script to access your sheet and send emails (follow the prompts, you may need to click "Advanced" > "Go to [Script Name]" to bypass the warning).

Key Notes to Avoid Issues #

• Duplicate Emails: The column L marker ensures we only send one alert per row once G exceeds K. If you want to re-alert if G stays above K, just remove the !alreadySent check and the status column code.
• Debugging: Test the checkAndSendAlerts function manually first—click the play button in the script editor to run it and verify it sends emails correctly (check your spam folder if you don’t see it).
• Data Types: The typeof checks prevent errors if G/K have non-numeric values (like text or empty cells).

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