CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Greg Griffin
Greg Griffin

Reputation: 21

onSpecialEdit() for Google App Script on a sheet with many users executes for all, each time

This can't be right. What am I missing?

I have a Google Sheet with scripts. The scripts are using certain ui functions that require authentication and special permissions coming from an onEdit() event. Therefore, I had to create onSpecialEdit() and the custom trigger to run that function with event source of the spreadsheet and event type onEdit(). This all works fine. Script does what it should.

From what I've seen, each person who needs to have this ability and access the special edit will need their own trigger. That is created for them automatically. Triggers all work for each person.

Now, what I see is that when I do an action that triggers onEdit(), it appears to run the onSpecialEdit() 20 times...once for each person who has that trigger....I'm assuming.

This can't be right. What am I missing? Is there another way to create triggers where they are only executed by the person triggering? Is this approach wrong?

Upvotes: 0

Views: 273

Answers (1)

Iamblichus
Iamblichus

Reputation: 19309

Issue:

  • You want to have an onEdit trigger that has access to services that require authorization.
  • These services should have access private of the user who has caused the trigger to run by editing the spreadsheet.
  • Since the services require authorization, it cannot be a simple but an installed trigger (Restrictions).
  • Since installed triggers act on behalf of the user who installed them, they can only access data of the user who installed them.
  • Therefore, each user whose data should be accessed needs to install the trigger, so multiple triggers should be installed.
  • A trigger runs whenever any user edits the spreadsheet, it doesn't have to be the user who installed the trigger. Because of this, all of the installed onEdit triggers will run whenever any user edits the spreadsheet.

Solution:

Use Session.getActiveUser() and Session.getEffectiveUser() to check whether the user editing the spreadsheet (getActiveUser) is the same as the one who installed the spreadsheet (getEffectiveUser). If it's not, stop the function execution. This way, only one of the multiple onEdit triggers will make the corresponding actions.

Code sample:

function onEditTrigger(e) {
  const active = Session.getActiveUser().getEmail();
  const effective = Session.getEffectiveUser().getEmail();
  if (active === effective) {
    // TRIGGER ACTIONS
  }
}

Upvotes: 0

Related Questions