Apps Script to automatically send email when threshold is met

[Nic Harbour]

All,

I have a Google Sheet that is made up of three (3) sheets.  I'm in need of an Apps Script that would automatically send out an email when one of the following four (4) thresholds is met (60%, 80%, 100%, or over 100%) on the SO THOLD sheet.

Below is the link and URL to my Google Sheet:

Link:  SO THOLD

URL:  https://docs.google.com/spreadsheets/d/1Sj_f-twUXR-pSHogEOaPZcNYfFeKAZzpIQVRFs8bbRc/edit?usp=sharing

Thank you in advance to anyone who can help!

[-RAGHZ- Plays]

Available Functions:

• setupTrigger() - Sets up automatic hourly checking (run once)
• testEmailNow() - Test the script immediately without waiting
• removeTriggers() - Disable automatic checking if needed

/**
 * Threshold Email Notification Script for Google Sheets
 * Monitors SO THOLD sheet and sends emails when thresholds of 60%, 80%, 100%, or >100% are met
 *
 * SETUP INSTRUCTIONS:
 * 1. Open your Google Sheet
 * 2. Go to Extensions > Apps Script
 * 3. Delete any existing code and paste this script
 * 4. Update the EMAIL_RECIPIENT variable below with your email address
 * 5. Save the script (File > Save or Ctrl+S)
 * 6. Run the "setupTrigger" function once to enable automatic checking
 * 7. Authorize the script when prompted
 */

// ===== CONFIGURATION - CHANGE THIS =====
var EMAIL_RECIPIENT = 'your-...@example.com'; // CHANGE THIS to your email address
// =======================================

/**
 * Main function to check thresholds and send email alerts
 * This will automatically run based on the trigger you set up
 */
function checkThresholdsAndEmail() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('SO THOLD');
   
    if (!sheet) {
      Logger.log('Error: Sheet "SO THOLD" not found');
      return;
    }
   
    // Get all data from the sheet
    var lastRow = sheet.getLastRow();
    if (lastRow <= 1) {
      Logger.log('No data rows found');
      return;
    }
   
    // Get data range (skip header row)
    var data = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).getValues();
   
    // Arrays to store alerts by threshold type
    var alerts60 = [];
    var alerts80 = [];
    var alerts100 = [];
    var alertsOver100 = [];
   
    // Check each row for threshold conditions
    for (var i = 0; i < data.length; i++) {
      var row = data[i];
      var soNumber = row[0]; // Column A - SO number
      var soHC = row[1]; // Column B - SO HC
      var cogs = row[2]; // Column C - COGS
      var po = row[3]; // Column D - PO
      var po60 = row[4]; // Column E - PO60
      var po80 = row[5]; // Column F - PO80
      var po100 = row[6]; // Column G - PO100
      var balance = row[7]; // Column H - BALANCE
      var threshold = row[8]; // Column I - THOLD
     
      // Skip empty rows
      if (!soNumber) continue;
     
      // Parse threshold value (remove % sign and convert to number)
      var thresholdValue = parseThresholdValue(threshold);
     
      // Create alert object
      var alertData = {
        so: soNumber,
        soHC: soHC,
        cogs: cogs,
        po: po,
        po60: po60,
        po80: po80,
        po100: po100,
        balance: balance,
        threshold: threshold
      };
     
      // Categorize alerts by threshold
      if (thresholdValue >= 100) {
        if (thresholdValue > 100) {
          alertsOver100.push(alertData);
        } else {
          alerts100.push(alertData);
        }
      } else if (thresholdValue >= 80) {
        alerts80.push(alertData);
      } else if (thresholdValue >= 60) {
        alerts60.push(alertData);
      }
    }
   
    // Send email if any alerts were found
    if (alerts60.length > 0 || alerts80.length > 0 || alerts100.length > 0 || alertsOver100.length > 0) {
      sendAlertEmail(alerts60, alerts80, alerts100, alertsOver100);
    } else {
      Logger.log('No threshold alerts found');
    }
   
  } catch (error) {
    Logger.log('Error in checkThresholdsAndEmail: ' + error.toString());
  }
}

/**
 * Parse threshold value from string (e.g., "60%" to 60, "105%" to 105)
 */
function parseThresholdValue(threshold) {
  if (typeof threshold === 'number') {
    return threshold;
  }
  if (typeof threshold === 'string') {
    var numValue = parseFloat(threshold.replace('%', ''));
    return isNaN(numValue) ? 0 : numValue;
  }
  return 0;
}

/**
 * Send email alert with all threshold notifications
 */
function sendAlertEmail(alerts60, alerts80, alerts100, alertsOver100) {
  var subject = 'Sales Order Threshold Alert - ' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MM/dd/yyyy HH:mm');
  var body = '<html><body style="font-family: Arial, sans-serif;">';
  body += '<h2 style="color: #333;">Sales Order Threshold Alert</h2>';
  body += '<p>The following sales orders have reached their threshold levels:</p>';
 
  // Over 100% alerts (highest priority)
  if (alertsOver100.length > 0) {
    body += '<h3 style="color: #cc0000; background-color: #ffe6e6; padding: 10px;">🚨 OVER 100% Threshold (' + alertsOver100.length + ' orders)</h3>';
    body += formatAlertTable(alertsOver100);
  }
 
  // 100% alerts
  if (alerts100.length > 0) {
    body += '<h3 style="color: #ff6600; background-color: #fff0e6; padding: 10px;">⚠️ 100% Threshold (' + alerts100.length + ' orders)</h3>';
    body += formatAlertTable(alerts100);
  }
 
  // 80% alerts
  if (alerts80.length > 0) {
    body += '<h3 style="color: #ff9900; background-color: #fff8e6; padding: 10px;">⚠️ 80% Threshold (' + alerts80.length + ' orders)</h3>';
    body += formatAlertTable(alerts80);
  }
 
  // 60% alerts
  if (alerts60.length > 0) {
    body += '<h3 style="color: #ffcc00; background-color: #fffce6; padding: 10px;">⚠️ 60% Threshold (' + alerts60.length + ' orders)</h3>';
    body += formatAlertTable(alerts60);
  }
 
  body += '<p style="margin-top: 20px; color: #666; font-size: 12px;">This is an automated alert from your Google Sheet. ';
  body += 'Alert generated at: ' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'MM/dd/yyyy HH:mm:ss') + '</p>';
  body += '</body></html>';
 
  // Send the email
  MailApp.sendEmail({
    to: EMAIL_RECIPIENT,
    subject: subject,
    htmlBody: body
  });
 
  Logger.log('Alert email sent to: ' + EMAIL_RECIPIENT);
}

/**
 * Format alert data as HTML table
 */
function formatAlertTable(alerts) {
  var table = '<table style="border-collapse: collapse; width: 100%; margin-bottom: 20px;">';
  table += '<thead><tr style="background-color: #f2f2f2;">';
  table += '<th style="border: 1px solid #ddd; padding: 8px; text-align: left;">SO</th>';
  table += '<th style="border: 1px solid #ddd; padding: 8px; text-align: right;">SO HC</th>';
  table += '<th style="border: 1px solid #ddd; padding: 8px; text-align: right;">COGS</th>';
  table += '<th style="border: 1px solid #ddd; padding: 8px; text-align: right;">PO</th>';
  table += '<th style="border: 1px solid #ddd; padding: 8px; text-align: right;">PO60</th>';
  table += '<th style="border: 1px solid #ddd; padding: 8px; text-align: right;">PO80</th>';
  table += '<th style="border: 1px solid #ddd; padding: 8px; text-align: right;">PO100</th>';
  table += '<th style="border: 1px solid #ddd; padding: 8px; text-align: right;">Balance</th>';
  table += '<th style="border: 1px solid #ddd; padding: 8px; text-align: center;">Threshold</th>';
  table += '</tr></thead><tbody>';
 
  for (var i = 0; i < alerts.length; i++) {
    var alert = alerts[i];
    table += '<tr>';
    table += '<td style="border: 1px solid #ddd; padding: 8px;">' + alert.so + '</td>';
    table += '<td style="border: 1px solid #ddd; padding: 8px; text-align: right;">' + formatNumber(alert.soHC) + '</td>';
    table += '<td style="border: 1px solid #ddd; padding: 8px; text-align: right;">' + formatNumber(alert.cogs) + '</td>';
    table += '<td style="border: 1px solid #ddd; padding: 8px; text-align: right;">' + formatNumber(alert.po) + '</td>';
    table += '<td style="border: 1px solid #ddd; padding: 8px; text-align: right;">' + formatNumber(alert.po60) + '</td>';
    table += '<td style="border: 1px solid #ddd; padding: 8px; text-align: right;">' + formatNumber(alert.po80) + '</td>';
    table += '<td style="border: 1px solid #ddd; padding: 8px; text-align: right;">' + formatNumber(alert.po100) + '</td>';
    table += '<td style="border: 1px solid #ddd; padding: 8px; text-align: right;">' + formatNumber(alert.balance) + '</td>';
    table += '<td style="border: 1px solid #ddd; padding: 8px; text-align: center; font-weight: bold;">' + alert.threshold + '</td>';
    table += '</tr>';
  }
 
  table += '</tbody></table>';
  return table;
}

/**
 * Format number with 2 decimal places
 */
function formatNumber(num) {
  if (typeof num === 'number') {
    return num.toFixed(2);
  }
  return num;
}

/**
 * Setup automatic trigger to check thresholds
 * Run this function ONCE to set up automatic checking
 * You can choose the frequency: hourly, daily, etc.
 */
function setupTrigger() {
  // Delete existing triggers to avoid duplicates
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === 'checkThresholdsAndEmail') {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
 
  // Create new trigger - runs every hour
  // You can change this to suit your needs:
  // .everyHours(1) - every hour
  // .everyHours(6) - every 6 hours
  // .atHour(9) - daily at 9 AM
  ScriptApp.newTrigger('checkThresholdsAndEmail')
    .timeBased()
    .everyHours(1)
    .create();
 
  Logger.log('Trigger set up successfully! The script will now run automatically every hour.');
  Browser.msgBox('Success!', 'Automatic trigger has been set up. The script will check thresholds every hour and send email alerts.', Browser.Buttons.OK);
}

/**
 * Remove all triggers (if you want to disable automatic checking)
 */
function removeTriggers() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  Logger.log('All triggers removed');
  Browser.msgBox('Triggers Removed', 'All automatic triggers have been removed.', Browser.Buttons.OK);
}

/**
 * Test function - run this to test the email without waiting for the trigger
 */
function testEmailNow() {
  checkThresholdsAndEmail();
  Browser.msgBox('Test Complete', 'Check your email and the script logs (View > Logs) for results.', Browser.Buttons.OK);

[Nic Harbour]

RAGHZ -plays,

Many 🙏🏼 my friend for sharing your knowledge and explaining with details RE my “ask”!

I’ll do a deep dive on this in the coming days.

Regards,

- Nic

Nic Harbour

Business Data Octopus

nic.h...@bdo.systems

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/cfa42b16-3aa4-441e-a675-2686c18085a0n%40googlegroups.com.