0

I have a google sheet that for the past 18 months has worked fine, inputting a username in a particular row/column when a corresponding cell is edited by using something basically like:

function onEdit(e) {
     (Session.getActiveUser().getEmail());
     ....
}

Lately, this has stopped working, probably as we are not on a GSuite service (although I'm wondering why it ever worked now).

I've tried disabling the V8 runtime, tried an installable onEdit trigger, tried to use Drive API scripts to get the username (adding Drive API in "Advanced Google Services"), some old workarounds (that no longer work), revoked permissions and allowed again, but no luck.

It seems a bit odd that it was working (Stopped maybe start of Oct 2021), and that I can click a cell and check the edit history, but can't get a username data written into a cell for security reasons.

There are several older posts on this but no current solutions, so apologies for digging this up again, but I'm wondering if there is solution?

Update following Kos' help: (Re)Adding a trigger seems to fix the permissions issue, provided that it is Runtime V8 AND the trigger is added per each user, the email address is then correctly logged. However, as there are now several onEdit triggers they each conflict and this email address entry is quickly deleted. The Edit History of the cell shows the "other" users delete the email address immediately.

Another Update: I got around this conflict by adding a Lock on the contents of the onEdit, see https://stackoverflow.com/a/68191989/2441916

function onEdit(e) {
  var lock = LockService.getDocumentLock();
  lock.waitLock(20000);
  // DO SOMETHING
  SpreadsheetApp.flush();
  lock.releaseLock();
}

Key takeaways:

  • Each user must have an onEdit trigger installed (and authorised). (if non-business suite)
  • Must be Runtime V8
  • Content Lock on onEdit required (see linked post)
klous1
  • 79
  • 8
  • Check executions history, is there any failed one? – Kos Oct 18 '21 at 18:53
  • Yes, this one: You do not have permission to call SpreadsheetApp.getActiveSheet. Required permissions: (https://www.googleapis.com/auth/spreadsheets.currentonly || https://www.googleapis.com/auth/spreadsheets) at onEdit(UserName:3) – klous1 Oct 19 '21 at 06:41
  • 1
    Does this answer your question? [Google app script trigger not working](https://stackoverflow.com/questions/28701382/google-app-script-trigger-not-working) – Kos Oct 19 '21 at 07:12
  • @Kos Thanks, but not the same issue. That is for a trigger not firing. Mine is firing, just not working for anyone who isn't the author, due to security/permissions/authority. – klous1 Oct 19 '21 at 08:47
  • 1
    That's one of a possible reasons, permissions may be lost by apps script, recreating trigger should help. – Kos Oct 19 '21 at 08:53
  • I see...and I think that's got it. I actually added the trigger yesterday but it didn't work. I see now that it must be runtime V8 to work (I changed it yesterday trying to fix). I also had to add the trigger for each user (I don't recall doing that previously). For one user the trigger runs and immediately deletes the entry....I assume that is an unrelated problem and will investigate. I'll see if I can mark this question as duplicate or if you make an answer I can mark up. Thanks for the help. – klous1 Oct 19 '21 at 10:01
  • 1
    Reference the other issue, entering and deleting of user emails; This is actually the various user's triggers conflicting with each other, one user's trigger correctly writes the entry and other user's trigger (incorrectly) deletes it. Without each user having a trigger they don't get necessary permissions to function. – klous1 Oct 19 '21 at 12:26

0 Answers0