What trigger should I use for Send Instant Email Alert When KPI Cell Exceeds Critical Threshold?

This script is designed for: Time-driven (Hourly). Configure it under Apps Script > Triggers (clock icon).

Google Sheets AutomationDifficulty: Beginner⚡ Time-driven (Hourly)

Send Instant Email Alert When KPI Cell Exceeds Critical Threshold

Q: What OAuth permissions does Send Instant Email Alert When KPI Cell Exceeds Critical Threshold require?

This script requests the following authorized scopes: https://www.googleapis.com/auth/spreadsheets.readonly, https://www.googleapis.com/auth/gmail.send.

Monitors key performance indicators (such as server error count, bounce rate, or cash balance) and triggers an immediate high-priority alert email if safety thresholds are breached.

Required OAuth Scopes: https://www.googleapis.com/auth/spreadsheets.readonly • https://www.googleapis.com/auth/gmail.send

Code.gs (Apps Script V8 Runtime)

function checkKpiThresholdAndAlert() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard');
  const errorRate = sheet.getRange('B5').getValue(); // e.g. 0.05 (5%)
  const threshold = 0.02; // 2%
  
  if (errorRate > threshold) {
    const recipient = '[email protected]';
    const subject = '⚠️ URGENT: Error Rate Spike Detected (' + (errorRate * 100).toFixed(1) + '%)';
    const body = 'Warning: Dashboard metric in cell B5 has exceeded acceptable thresholds.\n\nCurrent: ' + 
      (errorRate * 100).toFixed(1) + '%\nThreshold: ' + (threshold * 100) + '%\n\nPlease inspect production logs.';
      
    GmailApp.sendEmail(recipient, subject, body);
  }
}

Line-by-Line Code Explanation

Line 2-4: Evaluates target KPI cell B5 against a 2% threshold.

Line 6-12: Dispatches priority notification to technical leads upon threshold breach.

Step-by-Step Setup & Deployment

Adjust cell coordinate B5 and threshold value to match your business metrics.
Configure an hourly or 15-minute time-driven trigger.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Google Sheets Automation Snippets

Google Sheets