[GAS Beginner Series #21] Automate on File Open: How to Use onOpen(e) and Create Custom Menus

We are finally stepping into Phase 3: Automation, UI Extensions, and Error Handling—the core territory where the true power of Google Apps Script (GAS) comes to life!

Throughout parts 1 to 20, we ran our code by opening the GAS editor and clicking the “Run” button. However, in day-to-day operations, having to open the script editor every time you want to execute an automated task is quite inconvenient, isn’t it?

With onOpen(e), which we will learn today, you can automatically execute programs the moment a Google Spreadsheet is opened.

From displaying your own buttons (custom menus) in the top menu bar to showing pop-up alerts upon opening, you can transform your everyday spreadsheet into a full-fledged, professional internal business tool!

Even if you are completely new to programming, I will break down each technical term and walk through the code line by line in the clearest, most accessible way possible.

1. What Is onOpen(e)? The Core Concepts Explained Simply

Let’s start by demystifying what the name means and how the underlying mechanism works.

① What Is a “Trigger”?

In the GAS world, the word “trigger” appears frequently. Just like in everyday English, a trigger is the catalyst that sets an action in motion.

Whether it is “when this button is clicked,” “every morning at 9:00 AM,” or “when a file is opened,” the event or cue that causes a program to start running is called a trigger.

② onOpen Is a Special “Simple Trigger”

Normally, to execute a program automatically at a specific time or event, you need to configure time-driven triggers or installable triggers manually.

However, GAS provides built-in mechanisms where simply naming a function with a reserved identifier automatically detects events and runs your code. These are called “Simple Triggers.”

  • onOpen = A reserved function name that runs automatically “on” the event of “open.”

By simply declaring function onOpen(), Google’s system automatically detects when the document opens and immediately runs the code inside it.

③ What Does the (e) in Parentheses Mean?

When declaring the function, you write it as function onOpen(e) with an e inside the parentheses. This e stands for the Event Object.

It might sound intimidating, but think of it as a delivery package containing contextual information about how the file was opened.

The moment the file opens, Google packs useful details into this box (e) and passes it directly to your script:

  • Who opened the file (user information)
  • What permission level they opened it with (view-only, edit, etc.)
  • Which UI environment triggered the event

For now, it is completely fine to treat e as standard boilerplate syntax. But keeping in mind that it acts as a container holding metadata about the opening event will become very helpful when writing advanced scripts later on.

2. Hands-on Tutorial: Build a Daily Operations Portal for the Sapporo Office

Now that we understand the theory, let’s put it into practice!

In this exercise, we will create a “Sapporo Office Daily Operations Portal” for our team in Sapporo, building a script that adds a custom menu to the spreadsheet automatically whenever it is opened.

STEP 1: Prepare the Google Spreadsheet

  1. Create a new Google Spreadsheet in your web browser.
  2. Click “Untitled spreadsheet” in the top-left corner and rename it to “Sapporo Office Daily Operations Portal”.
  3. From the top menu bar, click Extensions > Apps Script.

STEP 2: Enter the Script

Once the GAS editor opens, delete any default code inside (such as function myFunction() { ... }), and copy & paste the following code block:

/**
 * Function executed automatically when the spreadsheet is opened
 * (Simple Trigger: onOpen)
 */
function onOpen(e) {
  // ① Get the user interface (UI) environment of the active spreadsheet
  var ui = SpreadsheetApp.getUi();
  
  // ② Create and add a custom menu to the top menu bar
  ui.createMenu('Sapporo Office Menu')
    .addItem('Record Daily Work Log', 'recordDailyLog')
    .addSeparator() // Draw a divider line inside the menu
    .addItem('Help & Manual', 'showHelp')
    .addToUi();
}

/**
 * Function executed when clicking "Record Daily Work Log"
 */
function recordDailyLog() {
  // Get the currently active sheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Append [Timestamp, Office Name, Task Name, Status] to the last row
  sheet.appendRow([new Date(), "Sapporo Office", "Daily Routine Inspection", "Completed"]);
  
  // Display a confirmation dialog on screen
  SpreadsheetApp.getUi().alert("[Sapporo Office] Today's work log has been added to the sheet!");
}

/**
 * Function executed when clicking "Help & Manual"
 */
function showHelp() {
  // Display a help dialog on screen
  SpreadsheetApp.getUi().alert("[Sapporo Office Portal Help]\n\nThis tool is a dedicated portal for tracking daily tasks at the Sapporo Office.\nContact: Sapporo General Affairs Team");
}

Once pasted, click the disk icon (Save project) at the top of the editor, or press Ctrl + S (Cmd + S on Mac) to save your code.

STEP 3: Verify Behavior (Reload the Sheet!)

Here is a vital rule you need to know:

Do not test onOpen by clicking the “Run” button inside the GAS editor! (We will cover the technical reason in the troubleshooting section below.)

The correct testing steps are as follows:

  1. Switch back to your browser tab displaying the Google Spreadsheet.
  2. Reload the page using your browser’s refresh button, or press F5 (Cmd + R on Mac).
  3. Wait a few seconds after the page finishes loading… and right in the top menu bar (next to “Help”), you will see the “Sapporo Office Menu” magically appear!

STEP 4: Try Out Your Custom Menu!

  1. Click the newly added “Sapporo Office Menu”.
  2. Select “Record Daily Work Log”.
  3. If this is the first time running it, an “Authorization Required” prompt may appear. Follow the on-screen steps to grant permission (click “Advanced” > “Go to Untitled project (unsafe)” > “Allow”).
  4. Once authorized, a pop-up alert will appear, and the current timestamp along with “Sapporo Office” log data will be appended to the bottom of your sheet!

3. Detailed Code Breakdown: How It Works Under the Hood

To help you apply these methods freely rather than simply memorizing snippets, let’s dissect the core statements line by line.

var ui = SpreadsheetApp.getUi();
  • SpreadsheetApp: The primary top-level service object in GAS responsible for managing Google Sheets.
  • getUi(): “UI” stands for User Interface—the visible parts human users interact with, such as buttons, dialog prompts, and menu bars. Calling getUi() prepares your script to interact directly with the spreadsheet interface.
ui.createMenu('Sapporo Office Menu')
  .addItem('Record Daily Work Log', 'recordDailyLog')
  .addSeparator()
  .addItem('Help & Manual', 'showHelp')
  .addToUi();

This structure uses a design pattern known as “method chaining.” By linking commands together with dots (.), we assemble the menu seamlessly.

  • createMenu('Menu Name'): Creates the top-level parent menu item displayed on the menu bar.
  • addItem('Label Text', 'functionName'): Adds an item inside the drop-down menu.
    • First parameter (left): The display text shown to users when they open the menu.
    • Second parameter (right): The name of the GAS function to call, enclosed in quotes.
  • addSeparator(): Inserts a horizontal divider line inside the menu for clean visual grouping.
  • addToUi(): Attaches the assembled menu structure directly into the spreadsheet UI. Remember: without calling this final method, the menu will never appear on the screen!

4. Crucial Security Boundaries: Understanding onOpen(e) Limitations

You might wonder: “If scripts can run automatically when a file is opened, can I automatically send an email too?” However, onOpen(e) is subject to strict security limitations by design.

Why Do These Restrictions Exist?

Imagine if a malicious actor shared a spreadsheet containing a script that immediately deleted all files in your Google Drive and emailed private data externally the second you opened it. You would suffer severe data loss just by previewing a file.

To prevent such vulnerabilities, simple triggers executed automatically without direct user confirmation are strictly barred from performing privileged or sensitive operations.

What onOpen Can and Cannot Do

Category Operation Allowed?
Allowed Add custom menus (createMenu) Yes
Allowed Show dialogs and alerts (alert) Yes
Allowed Read and write to active sheet cells Yes
Not Allowed Send automated emails (GmailApp) No (Throws Error)
Not Allowed Make external web or API requests (UrlFetchApp) No (Throws Error)
Not Allowed Access or modify other Google Drive files No (Throws Error)

*Note: If you need to perform actions requiring elevated permissions, such as sending emails, place that logic inside a function triggered by clicking a menu item, or utilize “Installable Triggers,” which we will cover in upcoming tutorials.

5. Top 3 Common Traps for Beginners and How to Fix Them

Whenever someone says, “My onOpen(e) script isn’t running!”, the issue almost always stems from one of the three mistakes below. Keep this checklist handy when troubleshooting.

❌ Trap 1: Clicking the “Run” Button in the Apps Script Editor

This is the single most common mistake among beginners.

If you select onOpen and click “Run” inside the script editor, the Google runtime does not provide the required event object (e). As a result, the script may throw errors or fail to bind the menu correctly.

Solution:

Always test onOpen by returning to the spreadsheet browser tab and refreshing the page (F5 or Cmd + R), never via the editor’s Run button!

❌ Trap 2: Function Name Typos and Casing Errors

JavaScript and Google Apps Script are strictly case-sensitive.

  • ❌ onopen (Lowercase “o”)
  • ❌ OnOpen (Uppercase leading “O”)
  • ❌ on_open (Includes an underscore)
  • ◯ onOpen (Only the second “O” in “Open” is capitalized)

If the capitalization is off by even one letter, Google will treat it as a standard user function rather than the reserved simple trigger, and it will not run automatically.

❌ Trap 3: Adding Parentheses () to addItem

Another frequent slip-up is adding parentheses when referencing the target function in your menu item.

// ❌ Incorrect: includes parentheses ()
.addItem('Record Daily Work Log', 'recordDailyLog()')

// ◯ Correct: string containing only the function name
.addItem('Record Daily Work Log', 'recordDailyLog')

The second argument of addItem must be passed as a string representing the function name alone. If you include parentheses, Google Sheets will not be able to locate and trigger the callback properly.

6. Summary and What’s Coming Next

In this session, we explored the fundamentals of automated execution in GAS using onOpen(e) and walked through how to build your own custom menus.

Key Takeaways

  1. onOpen(e) is a Simple Trigger that fires automatically whenever a file is opened
  2. SpreadsheetApp.getUi().createMenu() lets you assemble custom UI menus effortlessly
  3. Due to security restrictions, sensitive operations like sending emails cannot be executed directly within onOpen
  4. Always test simple triggers by refreshing the browser tab, not through the editor’s Run button

Seeing your custom tools integrated directly into Google Sheets gives an exciting glimpse into practical software development and workflow automation!

🚀 Next Up

Next time, we will explore another indispensable simple trigger: onEdit(e) (Automating actions when cells are edited)!

We will dive into real-time automation—such as automatically highlighting rows when a status changes to “Complete” or validating input values on the fly. Stay tuned!