1

I'm writing this Workspace Sheets Add-On and have this simple installable trigger set to trigger whenever a the spreadsheet is opened:

function whenOpen(e) {
  Logger.log('Open trigger ' + JSON.stringify(e));
}

const ss = SpreadsheetApp.getActive();
const currentTriggers = ScriptApp.getUserTriggers(ss);
if (currentTriggers.length == 0) {
    ScriptApp.newTrigger('whenOpen').forSpreadsheet(ss).onOpen().create();
}

When testing it, I can see that it gets triggered successfully on my own account from project executions and I see a message that looks like Open trigger {"authMode":"FULL","source":{},"triggerUid":"[...]","user":{[...]}}.

When I run this from a different account on the same spreadsheet, the event seems to get sent but I just see immediate failure instead with no logs with any additional information:

Failed with no logs

Perhaps this is some authentication issue? I understand the trigger will be run from the context of the user that installs the trigger (based on this) but I still expect it to run without error. I thought it might be a scoping issue but I'm already requesting https://www.googleapis.com/auth/script.scriptapp to allow for scripts to run in the background.

My questions:

  • Is there any way to get more detailed error messages to why this is failing?
  • What is causing this trigger to fail to run?
Rubén
  • 34,714
  • 9
  • 70
  • 166
nico
  • 2,022
  • 4
  • 23
  • 37
  • 1
    This seems weird as I tested it on my side and it actually runs. My testing proved that you only need `editor` permission on the other accounts for the trigger to be triggered. Although the output was unexpected. `Open trigger {"authMode":"FULL","source":{},"triggerUid":"6771708","user":{"email":"","nickname":""}}` I was expecting for the script to show the email and nickname of the actual user who opened the sheet or at least the owner's. – NightEye Apr 13 '21 at 21:23
  • hi @nico, can you try the process from scratch again? maybe something was amiss as I was not able to replicate your issue. – NightEye Apr 13 '21 at 21:27
  • Hi @nico, I have actually replicated the issue where no logs were showing during other account opening the sheet and results to `failed`. Try refreshing the execution tab. Mine actually shows nothing at first and when I refreshed the tab, it now shows. Have you checked if yours was just a case of delayed logs? – NightEye Apr 13 '21 at 21:38
  • Maybe these references can help: [How can my Google Apps Script be run by others the Sheet is shared with?](https://stackoverflow.com/a/57071543/14606045) or this one [Google Apps Script does not fully execute for other users after V8 was introduced](https://stackoverflow.com/a/62389148/14606045) – NightEye Apr 13 '21 at 21:49
  • 1
    I will try to make another simple add-on to see if I can replicate it with a bare-bones app. I still have no information inside the failed executions. – nico Apr 13 '21 at 22:29
  • 1
    I'm still running into the same issue. I have been running this as a Workspace Add-On instead of an Editor Add-On. Could that explain the difference in behavior I wonder? – nico Apr 13 '21 at 23:14
  • 1
    hi @nico, I'm all out of idea as of now. Were the references above helped you in any way? We might as well wait for others to chime in with this issue. – NightEye Apr 14 '21 at 15:09

1 Answers1

1

The documentation doesn't make this clear but there are two types of add-ons:

  • Editor Add-Ons which are installed to the document
  • Workspace Add-Ons which are installed to the user

See: https://developers.google.com/workspace/add-ons/concepts/types

I needed to use an Editor Add-On to have the behavior I wanted.

nico
  • 2,022
  • 4
  • 23
  • 37