onEdit and onOpen Triggers in Google Apps Script: Automate Sheets Without Clicking a Button

Running a script manually by clicking the Run button works fine during development. In production, it is not good enough. You want the automation to fire when something actually happens -- when a team member edits a cell, when a spreadsheet opens for the day, when data changes in a column that drives other logic.

Google Apps Script provides a trigger system that makes this possible. Instead of waiting for a human to initiate a script, triggers listen for specific events and execute your function automatically in response. Two of the most useful triggers for spreadsheet automation are onEdit, which fires whenever a cell is changed, and onOpen, which fires whenever the spreadsheet is opened.

This guide covers how both triggers work, the important distinction between simple and installable triggers, how to use the event object to respond intelligently to changes, and three practical examples you can adapt for real workflows: auto-timestamping edits, validating data on entry, and sending an email notification when a critical cell changes.

Simple Triggers vs Installable Triggers

Before writing any trigger code, you need to understand a distinction that catches many developers off guard: Apps Script has two categories of triggers, and they behave very differently.

Simple triggers are functions named with reserved names that Apps Script recognises automatically. You do not set them up in the Triggers panel -- they are activated the moment you save a function with the right name in a bound script (a script attached to a specific Sheet, Doc, or Form). The reserved names for simple triggers include onEdit, onOpen, onSelectionChange, and onFormSubmit.

Simple triggers are convenient, but they come with meaningful limitations:

• They run under the permissions of the user who is currently viewing the sheet -- not the owner who wrote the script.
• They cannot use services that require authorisation, including MailApp, GmailApp, SpreadsheetApp.openById(), DriveApp, or any service that accesses resources beyond the current spreadsheet.
• They have a 30-second execution time limit.
• They cannot access or modify files outside the current spreadsheet.
• They do not show authorisation prompts to users.

Installable triggers are set up manually in the Triggers panel (or programmatically via ScriptApp.newTrigger()). They run under the identity and full permissions of the account that installed them. This means they can send emails, access other files in Drive, call external APIs, and do anything an authorised script can do. The trade-off is a few extra setup steps and the fact that the trigger owner must have appropriate permissions on the spreadsheet.

The rule of thumb: use simple triggers for lightweight, in-spreadsheet logic (formatting, validation, custom menus). Use installable triggers for anything that needs to reach outside the spreadsheet.

The onEdit(e) Trigger and the Event Object

The onEdit function fires every time any cell in the spreadsheet is edited by any user. The most important aspect of working with onEdit is the event object -- a parameter conventionally named e -- that Apps Script passes to your function automatically. The event object tells you exactly what changed and where.

The event object contains the following properties:

• e.range — a Range object representing the cell or cells that were edited
• e.value — the new value that was entered (for single-cell edits)
• e.oldValue — the previous value in the cell before the edit (only available for single-cell edits where the old value was a simple non-formula value)
• e.source — the Spreadsheet object the edit occurred in
• e.user — the User object representing the person who made the edit (email address via e.user.getEmail())

Here is a minimal onEdit function that logs what changed:

function onEdit(e) {
  var range     = e.range;
  var sheetName = range.getSheet().getName();
  var cellRef   = range.getA1Notation();
  var newValue  = e.value;

  Logger.log('Edit detected in sheet: ' + sheetName + ', cell: ' + cellRef + ', new value: ' + newValue);
}

Save this in your bound script and edit any cell in the spreadsheet. The log will record the change. No manual run needed, no trigger panel configuration -- just a function named onEdit and Apps Script does the rest.

Targeting Specific Sheets and Columns

A naively written onEdit function fires on every single edit in every sheet. For most real-world automations, you want to restrict the logic to a specific sheet and column. Use the event object's range property to check where the edit happened before doing any work:

function onEdit(e) {
  var range  = e.range;
  var sheet  = range.getSheet();
  var col    = range.getColumn();
  var row    = range.getRow();

  // Only react to edits in the 'Orders' sheet, column 4 (column D)
  if (sheet.getName() !== 'Orders') return;
  if (col !== 4) return;
  if (row < 2) return; // Skip the header row

  // Your logic here -- e.g., respond to a status change in column D
  Logger.log('Status changed in Orders sheet, row ' + row + ' to: ' + e.value);
}

The early return statements act as guards. If the edit did not happen in the right place, the function exits immediately without doing anything. This keeps the trigger fast and avoids side effects on unrelated edits.

Practical Example 1: Auto-Timestamp on Edit

One of the most frequently requested spreadsheet automations is auto-timestamping. When a user updates a value in a column (for example, marking an order as "Dispatched"), you want the adjacent column to automatically record the date and time of that change.

This is a perfect use case for a simple onEdit trigger. It only writes to the current spreadsheet, so no authorisation beyond the current file is needed.

Setup: Create a sheet named "Orders" with the following columns:

• Column A: Order ID
• Column B: Customer Name
• Column C: Order Value
• Column D: Status ("Pending", "Processing", "Dispatched", "Complete")
• Column E: Status Last Updated (auto-filled by the script)

function onEdit(e) {
  var range  = e.range;
  var sheet  = range.getSheet();
  var col    = range.getColumn();
  var row    = range.getRow();

  // Only stamp when column D (Status) is edited in the Orders sheet
  if (sheet.getName() !== 'Orders') return;
  if (col !== 4) return;
  if (row < 2) return; // Skip header

  // Write the current timestamp into column E of the same row
  var timestampCell = sheet.getRange(row, 5);
  var now = new Date();
  timestampCell.setValue(now);
  timestampCell.setNumberFormat('dd/mm/yyyy hh:mm:ss'); // Australian date format
}

Every time a user changes a value in column D of the Orders sheet, column E in that row is automatically set to the current date and time, formatted as dd/mm/yyyy hh:mm:ss. The user does not need to do anything else.

A few details worth noting:

• sheet.getRange(row, 5) uses row and column numbers rather than A1 notation, which is more reliable inside loops and dynamic references.
• setNumberFormat('dd/mm/yyyy hh:mm:ss') ensures the timestamp cell displays in the Australian date format (day before month) rather than the US format Google Sheets uses by default.
• The timestamp reflects the moment of the edit according to the script's execution time, which may be a few seconds after the actual keystroke due to server round-trip time.

The onOpen Trigger and Custom Menus

The onOpen trigger fires every time the spreadsheet is opened by any user. Its most common use is creating a custom menu in the spreadsheet's menu bar that gives users easy access to functions without needing to open the Apps Script editor.

Here is how to add a custom menu to a spreadsheet:

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('CloudGeeks Tools')
    .addItem('Highlight Overdue Rows', 'highlightOverdueRows')
    .addItem('Send Weekly Summary', 'sendWeeklySummary')
    .addSeparator()
    .addSubMenu(
      ui.createMenu('Reports')
        .addItem('Export to PDF', 'exportToPdf')
        .addItem('Refresh Dashboard', 'refreshDashboard')
    )
    .addToUi();
}

When the spreadsheet opens, a new menu item labelled "CloudGeeks Tools" appears in the menu bar alongside File, Edit, View, and the other standard menus. Each item in the menu calls the named function when clicked.

addSeparator() draws a horizontal line between menu items for visual grouping. addSubMenu() nests a second menu inside the first, useful when you have several related functions.

The function names passed to addItem() must exactly match functions defined in the same script project. If a referenced function does not exist, clicking the menu item will throw an error.

Important limitation: Because onOpen is a simple trigger, it runs without full authorisation. The createMenu() call itself does not require special permissions -- it just builds the menu. However, the functions that those menu items call do run with full user authorisation when clicked. This means it is fine to put MailApp.sendEmail() calls inside sendWeeklySummary() -- that function runs in full auth context when triggered by the menu click, even though onOpen itself cannot do those things directly.

Practical Example 2: Data Validation on Entry

The second practical pattern is using onEdit to enforce data rules the moment a user enters a value, before they move on to the next cell. While Google Sheets has built-in data validation, Apps Script validation lets you implement complex rules that the native UI cannot express -- such as checking a value against another sheet, enforcing conditional rules that depend on multiple columns, or providing a specific user-facing message with context.

This script validates that values entered in column C (Order Value) are positive numbers, and resets the cell and alerts the user if the rule is violated:

function onEdit(e) {
  var range  = e.range;
  var sheet  = range.getSheet();
  var col    = range.getColumn();
  var row    = range.getRow();

  // Only validate column C (Order Value) in the Orders sheet
  if (sheet.getName() !== 'Orders') return;
  if (col !== 3) return;
  if (row < 2) return;

  var enteredValue = e.value;
  var numericValue = parseFloat(enteredValue);

  // Reject non-numeric values or values less than or equal to zero
  if (isNaN(numericValue) || numericValue <= 0) {
    // Revert the cell to its old value, or clear it if there was no previous value
    if (e.oldValue !== undefined) {
      range.setValue(e.oldValue);
    } else {
      range.clearContent();
    }

    // Show an alert (note: alerts only work in simple triggers when user is active in the sheet)
    SpreadsheetApp.getUi().alert(
      'Invalid Entry',
      'Order Value must be a positive number. The entry "' + enteredValue + '" has been removed.',
      SpreadsheetApp.getUi().ButtonSet.OK
    );
  }
}

When a user types a letter, a negative number, or zero into the Order Value column, the script immediately reverts the cell and shows a clear explanation. The user stays in context -- they see exactly which value was rejected and why.

One nuance: SpreadsheetApp.getUi().alert() works inside a simple onEdit trigger when the user is actively viewing the sheet (i.e., the edit was made by a human in the browser, not programmatically). If the trigger fires in response to a programmatic edit via the API, the UI call will fail silently. For scripts where programmatic edits are possible, wrap UI calls in a try/catch block.

Practical Example 3: Email Notification on Critical Cell Change (Installable Trigger)

This example requires an installable trigger because it uses MailApp to send email -- a service that a simple trigger cannot access. The use case: a team shared a Google Sheet to track project risks. Whenever someone changes the value in the "Risk Level" column (column E) to "Critical", the project manager receives an email notification immediately.

Step 1: Write the function

function notifyOnCriticalRisk(e) {
  var range  = e.range;
  var sheet  = range.getSheet();
  var col    = range.getColumn();
  var row    = range.getRow();

  // Only react to the Risk Level column (column E) in the Risk Register sheet
  if (sheet.getName() !== 'Risk Register') return;
  if (col !== 5) return;
  if (row < 2) return;

  var newValue = e.value;

  if (newValue === 'Critical') {
    // Gather context from adjacent cells
    var riskName    = sheet.getRange(row, 1).getValue();  // Column A: Risk Name
    var description = sheet.getRange(row, 2).getValue();  // Column B: Description
    var owner       = sheet.getRange(row, 3).getValue();  // Column C: Risk Owner
    var editedBy    = e.user ? e.user.getEmail() : 'Unknown user';

    var subject = '[ACTION REQUIRED] Critical risk flagged: ' + riskName;
    var body =
      'A risk in the Risk Register has been elevated to Critical status.\n\n' +
      'Risk Name: ' + riskName + '\n' +
      'Description: ' + description + '\n' +
      'Risk Owner: ' + owner + '\n' +
      'Flagged by: ' + editedBy + '\n' +
      'Time: ' + new Date().toLocaleString('en-AU') + '\n\n' +
      'Please review the Risk Register immediately:\n' +
      SpreadsheetApp.getActiveSpreadsheet().getUrl();

    MailApp.sendEmail('projectmanager@yourdomain.com.au', subject, body);
  }
}

Note that the function is not named onEdit. It has a custom name (notifyOnCriticalRisk) because it will be registered as an installable trigger, not a simple one.

Step 2: Install the trigger

1. In the Apps Script editor, click the clock icon in the left sidebar (Triggers).
2. Click Add Trigger in the bottom right.
3. Set Function to run to notifyOnCriticalRisk.
4. Set Deployment to Head.
5. Set Event source to "From spreadsheet".
6. Set Event type to "On edit".
7. Click Save and complete the authorisation flow.

The trigger is now installed under your account's permissions. When any user edits column E of the Risk Register sheet to "Critical", the email fires using your account's Gmail authorisation -- regardless of who made the change.

Alternatively, set up the trigger programmatically:

function installCriticalRiskTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // Remove any existing triggers for this function to avoid duplicates
  var existingTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < existingTriggers.length; i++) {
    if (existingTriggers[i].getHandlerFunction() === 'notifyOnCriticalRisk') {
      ScriptApp.deleteTrigger(existingTriggers[i]);
    }
  }

  // Install a fresh trigger
  ScriptApp.newTrigger('notifyOnCriticalRisk')
    .forSpreadsheet(ss)
    .onEdit()
    .create();

  Logger.log('Trigger installed successfully.');
}

Run installCriticalRiskTrigger() once and the installable trigger is registered. The programmatic approach is especially useful when deploying the same script across multiple spreadsheets -- you can call this setup function from a deployment script rather than manually configuring each one through the UI.

Limitations of Simple Triggers Worth Knowing

Beyond the services restriction, a few other limitations are worth being aware of before you commit to a simple trigger approach:

Simple triggers cannot be debugged with console output in the same way as manually-run functions. Because onEdit fires in response to a live user action, the execution is detached from the editor's Run button flow. To debug a simple trigger, add Logger.log() calls to your function, reproduce the edit in the spreadsheet, then open the Executions panel (the clock icon in the left sidebar) to see the logged output from the most recent executions.

Simple triggers do not retry on failure. If an onEdit function throws an unhandled exception, the error is recorded in the Executions panel, but no retry is attempted. For workflows where reliability matters, installable triggers can be configured with failure notification emails in the Triggers panel settings.

Simultaneous edits can cause race conditions. If two users edit the same sheet at the same moment, two onEdit calls may fire concurrently and attempt to write to the same cells. For most practical use cases this is unlikely, but for high-traffic shared sheets it is a consideration. The LockService API can be used to serialise concurrent trigger executions when necessary.

Simple triggers only fire for edits made through the Google Sheets UI or the Google Sheets API. They do not fire when data is changed by another Apps Script running programmatically -- for example, if one script calls range.setValue() on a cell in a sheet that has an onEdit trigger, that trigger does not fire. Only user-initiated changes (or changes via the Sheets API from an external caller) trigger the function.

Change Triggers: A Brief Note

In addition to onEdit, Apps Script also supports an onChange trigger that fires for structural changes to the spreadsheet -- inserting or deleting rows and columns, adding or removing sheets, and similar structural modifications. The onChange event object has a changeType property that tells you what kind of structural change occurred (INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT, OTHER).

onChange is an installable-only trigger (there is no simple equivalent). You set it up in the same Triggers panel as other installable triggers, selecting "On change" as the event type.

function onSheetStructureChange(e) {
  Logger.log('Change type: ' + e.changeType);

  if (e.changeType === 'INSERT_ROW') {
    // React to a new row being inserted -- e.g., initialise default values
    Logger.log('A new row was inserted.');
  }
}

For most spreadsheet automations, onEdit is sufficient. onChange is useful for audit tracking, enforcing structural rules (such as preventing certain columns from being deleted), or initialising data in freshly inserted rows.

Affiliate & Partner Programs

The following resource and referral link is relevant to teams looking to implement the automations in this guide.

Google Workspace

Apps Script triggers are available at no additional cost on every Google Workspace plan. If your organisation is still using free Google accounts or a legacy G Suite plan, upgrading to a current Workspace plan gives you access to installable triggers, extended execution quotas, and the ability to use MailApp and other authorised services from your scripts.

For Australian businesses, the current Workspace plans (in AUD) start from Business Starter. If you are evaluating a move to Google Workspace or looking to upgrade your existing plan, the link below provides access to current Australian pricing and plan comparisons. CloudGeeks is a Google Workspace reseller, and purchases through our referral link help support the free content we publish.

View Google Workspace plans and pricing for Australian businesses

Conclusion

Triggers are what turn a script into an automation. Without them, every piece of code you write requires a human to remember to run it. With onEdit and onOpen, your spreadsheets respond to events in real time -- stamping timestamps, enforcing data rules, creating menus, and sending notifications without any manual intervention.

The key things to take away: use simple triggers (the reserved-name functions) for lightweight logic that stays within the current spreadsheet; use installable triggers for anything that needs to send emails, access other files, or call external services. Always use the event object to scope your onEdit logic to the specific sheet and column that matters, rather than reacting to every edit in the file. And when reliability and observability matter for a production automation, install the trigger programmatically and configure failure notification emails so problems surface without you having to check the Executions panel manually.

The three practical examples in this guide -- auto-timestamps, entry validation, and critical-change notifications -- cover the majority of real-world trigger use cases. Once you are comfortable with these patterns, you will find they are composable: the same guard logic, the same event object properties, and the same conditional structure appear again and again across more complex automations.

Need help with your Google Workspace setup? Contact our team for a free consultation.

Ash Ganda is the founder of CloudGeeks, a Google Workspace consultancy helping Australian SMBs get more from their cloud tools. He writes about practical technology strategies at insights.cloudgeeks.com.au.