Trigger Functions Not Executing for Non-Creator Users in Latest Google Sheets Version

Other Questions Google Sheets
1

I’m having trouble with trigger functions in the new Google Sheets. They only seem to work for the person who created them. This wasn’t a problem in the old version.

Here’s what I did:

  1. Created a new spreadsheet
  2. Wrote a script to add a custom menu
  3. Set up a trigger to run when the sheet is opened
  4. It works fine when I open the sheet
  5. But when another user with edit rights opens it, nothing happens

I’ve tested this with the code below:

function makeMenu() {
  var options = [
    { name: 'Click Me', functionName: 'doStuff' }
  ];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('My Menu', options);
}

function doStuff() {
  // Function currently does nothing
}

The code itself isn’t the issue because it works manually as the second user. The problem seems to be with the open trigger in the new Sheets environment. I’ve observed this across different Google accounts and spreadsheets. Has anyone encountered this before or found a solution?

2

I’ve been grappling with this exact problem recently. After some digging, I found that Google’s latest security updates are the culprit. What worked for me was switching to time-driven triggers instead of open triggers. They’re more reliable across different users.

Here’s what I did:

  1. Went to the script editor
  2. Set up a time-driven trigger to run every minute
  3. In the trigger function, I checked if the spreadsheet was open
  4. If it was, I ran the menu creation code

It’s a bit of a hack, but it’s been working consistently for my team. The slight delay is barely noticeable, and it beats having non-functional triggers. Just remember to set appropriate permissions for the script to access the necessary resources.

Hope this helps! Let me know if you need more details on the implementation.

3

hey mate, had the same issue last week. turns out google changed some permission stuff. try checkin the script’s authorization settings. make sure it’s set to run as you (the creator) and not the user opening it. that fixed it for me. good luck!

4

I’ve encountered this issue recently as well. It appears to be related to the new security measures Google has implemented in Sheets. One workaround I found effective is to create an installable trigger instead of a simple trigger. Here’s what you can do:

Go to the Apps Script editor, navigate to ‘Triggers’ in the left sidebar, click ‘+ Add Trigger’ at the bottom right, and set it to run your function when the spreadsheet opens.

This method seems to execute for all users with appropriate permissions, not just the creator. Additionally, ensure your script is deployed as a web app with ‘Execute as: Me’ and ‘Who has access: Anyone with Google Account’. This combination has resolved the issue for most of my team members.