onOpen Trigger – Apps Script

The onOpen(e) trigger fires automatically whenever a user opens the spreadsheet. It's most commonly used to add a custom menu to the spreadsheet's toolbar, but it can also display alerts, refresh data, or set up the environment for the user.

Adding a Custom Menu on Open

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Sales Tools")
    .addItem("Send Daily Report", "sendDailyReport")
    .addItem("Send Weekly Digest", "sendWeeklyDigest")
    .addSeparator()
    .addItem("Generate Contracts", "bulkGenerateContracts")
    .addItem("Create Region Folders", "createRegionFolders")
    .addToUi();
}

Every time someone opens the Sales Tracker, this menu appears in the toolbar with quick access to the team's most-used automations.

Showing a Welcome Alert

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

  var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMMM dd, yyyy");
  ui.alert("Sales Tracker", "Welcome! Today is " + today + ".\nRemember to update deal statuses before end of day.", ui.ButtonSet.OK);
}

Refreshing Summary Stats on Open

This example recalculates key metrics and writes them to a Dashboard sheet every time the file is opened:

function onOpen() {
  refreshDashboard();
  addSalesMenu();
}

function refreshDashboard() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("Sales Tracker");
  var dashboard = ss.getSheetByName("Dashboard");

  if (!dashboard) return;

  var data = dataSheet.getRange(2, 1, dataSheet.getLastRow() - 1, 8).getValues();
  // Columns: [Customer Name, Email, Product, Amount, Region, Sales Rep, Status, Date]

  var closed = data.filter(function(r) { return r[6] === "Closed"; });
  var inProgress = data.filter(function(r) { return r[6] === "In Progress"; });
  var totalMRR = closed.reduce(function(sum, r) { return sum + r[3]; }, 0);

  dashboard.getRange("B2").setValue(closed.length);
  dashboard.getRange("B3").setValue(inProgress.length);
  dashboard.getRange("B4").setValue("$" + totalMRR + "/mo");
  dashboard.getRange("B5").setValue(new Date());

  Logger.log("Dashboard refreshed.");
}

function addSalesMenu() {
  SpreadsheetApp.getUi()
    .createMenu("Sales Tools")
    .addItem("Send Daily Report", "sendDailyReport")
    .addItem("Refresh Dashboard", "refreshDashboard")
    .addToUi();
}

With the sample data, the Dashboard updates to show 2 Closed, 1 In Progress, and $398/mo total MRR each time the file opens.

Conditional Logic Based on Day

Show a reminder only on Mondays:

function onOpen() {
  var day = new Date().getDay(); // 0 = Sunday, 1 = Monday
  if (day === 1) {
    SpreadsheetApp.getUi().alert(
      "Monday Reminder",
      "Don't forget to update your deal statuses for the weekly digest!",
      SpreadsheetApp.getUi().ButtonSet.OK
    );
  }

  addSalesMenu();
}

onOpen Limitations

The simple onOpen trigger runs with limited permissions — it cannot send emails, make HTTP requests, or access external APIs. For those use cases, create an installable onOpen trigger:

function createOnOpenTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger("onOpenInstallable")
    .forSpreadsheet(ss)
    .onOpen()
    .create();
}

function onOpenInstallable(e) {
  // Can send emails, access Drive, call external APIs
  var lastOpened = PropertiesService.getScriptProperties().getProperty("lastOpened");
  PropertiesService.getScriptProperties().setProperty("lastOpened", new Date().toString());

  Logger.log("Previously opened: " + lastOpened);
}