Google Sheets App Script: Trigger on a copied sheet

Question

I am trying to run a Google Sheets App Script whenever a checkbox is ticked.

• Other users should be allowed to tick the checkbox.

When ticked, an App Script shows/hides different parts of the sheet.

• This works fine for the owner of the sheet, but not for other users.

I would like to achieve the following workflow:

1. User makes a copy of the entire Google Sheet
2. User shares copy with colleagues
3. App Script fires for user and colleagues

Things I have tried:

a. Simple Trigger DEMO

• Using an onEdit Simple Trigger fails for other users due to "You are trying to edit a protected cell or object."

b. Installable Trigger DEMO

• Setting up an Installable Trigger allows other users to run the App Script.
• However, manually setting up a trigger each time the sheet is copied can not reasonably be expected from users.

How would I want to trigger the App Script on a copied sheet?

Answer 1

To make it easy for the owner of the new spreadsheet to install a trigger, add a custom menu item, like this:

/**
* Simple trigger that runs each time the user opens the spreadsheet.
* Adds a menu item.
*
* @param {Object} e The onOpen() event object.
*/
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Add trigger')
    .addItem('Add on edit trigger', 'installOnEditTrigger')
    .addToUi();
}

/**
* Installs a trigger that runs each time the user hand edits the spreadsheet.
* Deletes any previous instances of ON_EDIT triggers.
*/
function installOnEditTrigger() {
  // version 1.0, written by --Hyde, 21 February 2025
  deleteTriggers_(ScriptApp.EventType.ON_EDIT);
  ScriptApp.newTrigger('watchCheckboxes_')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create();
}

/**
* Deletes all installable triggers of the type triggerType associated with the current
* script project that are owned by the current user in the current document.
*
* @param {EventType} triggerType One of ScriptApp.EventType.ON_EDIT, .ON_FORM_SUBMIT, .ON_OPEN, .ON_CHANGE, .CLOCK (time-driven triggers) or .ON_EVENT_UPDATED (Calendar events).
*/
function deleteTriggers_(triggerType) {
  // version 1.3, written by --Hyde, 19 September 2023
  const triggers = triggerType === ScriptApp.EventType.CLOCK
    ? ScriptApp.getProjectTriggers()
    : ScriptApp.getUserTriggers(SpreadsheetApp.getActive());
  triggers.forEach(trigger => {
    if (trigger.getEventType() === triggerType) {
      ScriptApp.deleteTrigger(trigger);
    }
  });
}

To make this work with your current simple trigger script project, rename the onEdit(e) function to watchCheckboxes_(e).

When the user chooses the new menu item, they are asked to authorize the script project. The authorization is only requested once. The trigger will run under the account of the user who created the trigger.