Hey there! Let's optimize your Google Apps Script for ROI monitoring step by step. I'll adjust the trigger logic to ±15% and make the percentage output clean and readable, perfect for your newbie-friendly workflow.

function checkROIChange() {
  // Get active spreadsheet and sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Pull current and previous day ROI values
  const currentROI = sheet.getRange("D2").getValue();
  const previousROI = sheet.getRange("D3").getValue();

  // Prevent division by zero if previous ROI is 0
  if (previousROI === 0) {
    console.log("Previous day ROI is 0 — can't calculate change percentage.");
    return;
  }

  // Calculate and format change percentage
  const changePercentage = ((currentROI - previousROI) / previousROI) * 100;
  const formattedChange = Utilities.formatString("%.2f%%", changePercentage);

  // Check if change meets ±15% threshold
  const alertThreshold = 15;
  if (Math.abs(changePercentage) >= alertThreshold) {
    // Customize email details here
    const recipient = "your-alert-email@example.com";
    const subject = `ROI Alert: ${formattedChange} Change Detected`;
    const emailBody = `
      Hi there,

      A significant ROI change has been recorded:
      - Current ROI: ${currentROI}
      - Previous Day ROI: ${previousROI}
      - Total Change: ${formattedChange}

      Please review the data as soon as possible.
    `;

    // Send alert email
    MailApp.sendEmail(recipient, subject, emailBody);
    console.log("Alert email sent successfully!");
  } else {
    console.log(`ROI change (${formattedChange}) is within normal range — no alert needed.`);
  }
}

1. ±15% Trigger Logic #

• Uses Math.abs(changePercentage) to check if the absolute value of the change is 15% or more, covering both 15%+ increases and 15%+ drops
• Added a safety check for zero previous ROI to avoid division-by-zero errors (super common for new scripts!)
• Clear threshold variable (alertThreshold) makes it easy to adjust the percentage later if needed

2. Clean Percentage Formatting #

• Uses Utilities.formatString("%.2f%%", changePercentage) to:
  • Round the percentage to 2 decimal places (e.g., 15.37% instead of 15.36789%)
  • Add a proper percent symbol (%% escapes the symbol since single % is a formatting placeholder)
• The result will look like +16.42% or -17.10% — clear and professional for emails

Quick Newbie Tips #

• Replace your-alert-email@example.com with your actual recipient email
• Set up a time-driven trigger (in the script editor: Edit > Current project's triggers) to run this script automatically daily
• Check logs (View > Logs) to debug calculation results if something doesn't work as expected

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