Triggers (onEdit, onOpen) in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What happens when onEdit trigger modifies a cell?

You have a Google Sheets script with an onEdit(e) trigger that changes the value of cell B1 whenever any cell in column A is edited.

If you edit cell A2, what will be the value of B1 after the script runs?

Google Sheets

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  if (range.getColumn() == 1) { // column A
    sheet.getRange('B1').setValue('Edited');
  }
}

A B1 will show 'Edited'

B The script will cause an error

C B1 will show the edited cell's value

D B1 will be empty

💡 Hint

Think about what the script does when a cell in column A is edited.

The script checks if the edited cell is in column A. If yes, it sets B1 to 'Edited'. So after editing A2, B1 will show 'Edited'.

❓ Function Choice

intermediate

Which trigger runs when the spreadsheet is opened?

You want to run a script automatically every time your Google Sheets file is opened. Which function name should you use?

A function onEdit()

B function onOpen()

C function onChange()

D function onLoad()

💡 Hint

Think about the trigger that activates when the file opens.

The onOpen() function runs automatically when the spreadsheet is opened. The others run on different events.

🎯 Scenario

advanced

Avoiding infinite loops in onEdit triggers

You wrote an onEdit(e) trigger that changes cell B1 whenever any cell in column B is edited. But when you edit B2, the script runs repeatedly and freezes your sheet.

Why does this happen?

Google Sheets

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  if (range.getColumn() == 2) { // column B
    sheet.getRange('B1').setValue('Updated');
  }
}

A Because column B is protected

B Because the script has a syntax error

C Because onEdit triggers only run once per edit

D Because changing B1 triggers onEdit again, causing an infinite loop

💡 Hint

Think about what happens when the script edits a cell inside onEdit.

The script edits B1 inside onEdit. Editing B1 triggers onEdit again, causing the script to run repeatedly and freeze the sheet.

❓ data_analysis

advanced

Detecting which cell was edited in onEdit trigger

In an onEdit(e) trigger, you want to find the address of the edited cell to log it.

Which expression gives the correct cell address (like 'B3')?

A e.source.getActiveCell().getA1Notation()

B e.range.getRow() + e.range.getColumn()

C e.range.getA1Notation()

D e.value

💡 Hint

Check the event object properties for the edited range.

e.range.getA1Notation() returns the address of the edited cell like 'B3'. The others do not return the cell address correctly.

🧠 Conceptual

expert

Why use installable triggers instead of simple triggers?

Simple triggers like onEdit(e) run automatically but have restrictions. Which is a key reason to use installable triggers instead?

A Installable triggers can access user data and perform actions requiring authorization

B Installable triggers run faster than simple triggers

C Installable triggers can only run on mobile devices

D Installable triggers do not require any authorization

💡 Hint

Think about what simple triggers cannot do due to security limits.

Simple triggers cannot access services that require user authorization. Installable triggers can, allowing more powerful scripts.