1

I need to determine if the onChange event in Google sheets is triggered by a Google service account or by a person, because I want to process the changed data only if the trigger source was human-made direct editing, otherwise stop processing.

I tried to identify it with Session.getActiverUser(), but, although the change is through the service account, shows my own email address.

I can not find anything on the Internet, nor in the Google documentation.

PS: to be more clear, I did bidirectional syncing from Firebase Realtime Database to Sheets and vice versa. Therefore, to avoid redundant update cycle, I want to stop processing as soon as change/update happens on behalf of the Service Account.

Or maybe there is some other way to avoid loop problem. I will be glad of any help

wol
  • 142
  • 1
  • 14
  • Are the users in your same domain? I mean those who execute `getActiveUsers()` – Jescanellas Jul 17 '20 at 10:28
  • No one executes the code, it gets automatically triggered whenever something changes in sheet. I need to somehow get identity of change author to decide further actions – wol Jul 17 '20 at 10:29
  • 1
    What about getEffectiveUser? – TheMaster Jul 18 '20 at 06:36
  • Still the same, returns my email. But let me try one more time with skydriver logging – wol Jul 18 '20 at 06:38
  • I just checked and Session.getEffectiveUser() returns my email too :( – wol Jul 18 '20 at 06:45
  • 1
    Check changeType and `user` in `e` and see if there's a difference.. try cooper's answer too- see if `e` has any different params that can be used to delineate. – TheMaster Jul 18 '20 at 06:51
  • If all that fails, try [getUserAgent](https://developers.google.com/apps-script/reference/html/html-service#getuseragent) or try showing a prompt/dialog in the browser. If there's a human, dialog should show, else it should timeout. None of it is ideal, but onChange triggering on script executions is not a documented feature. – TheMaster Jul 18 '20 at 07:02
  • Related: https://stackoverflow.com/a/62807921 – TheMaster Jul 18 '20 at 07:06
  • I think your suggestions made something. User info is hidden when service account makes an update. `AM Warning The script does not have permission to get the active user's identity.` Check it out https://i.imgur.com/xgNFXIf.png – wol Jul 18 '20 at 07:11
  • 1
    @wol Great. If you can confirm it works, add a answer. See [answer]. @Cooper was actually suggesting the same thing to you- to look into `e`. – TheMaster Jul 18 '20 at 07:22

1 Answers1

2

So, basically it is a bit a hacky way, but it works.

As @TheMaster suggested onChange event parameter e contains user node, which in case of Service Account is empty, thus could be used to distinguish change event sources.

devtools console screenshot

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
wol
  • 142
  • 1
  • 14