Capture the User Email on onEdit trigger

[Sameer Goyal]

Currently i am using Session.getActiveUser().getEmail();
to capture the user email but as documentation says i cannot get it without the user's authorization, 
But how to get the user authorization
I want to capture the user email  onEdit. I am also okay to get permission from user so that i can log it

[Ed Robinson]

Hi Sameer,

this will work on your own account OK. When you deploy your app to someone else, they'll need to grant permission for your app to read the email address.

In your app's appscript.json file, add the OAuth Scope "https://www.googleapis.com/auth/userinfo.email"

This indicates your app wants to read the user's email, and they will be prompted to approve this, the first time they use your app.

This Google article shows how to edit the file 

[Sameer Goyal]

Thanks Ed Robinson for reply

But the problem is i want to capture the user's email every time they made changes in the sheet so i set up a trigger using onEdit. But i am not able to capture the email due to strict policy. I am also okay to implement a way to ask for their authorization from user. and i am open for suggestions if there are alternatives available. I am not developing as a app i just want to log my sheet

[Ed Robinson]

Hi Sameer,

Sorry for not understanding your question. I'm guessing this is the situation:

1. The spreadsheet is "shared". It can be accessed by many people, all of them have permissions to edit cells

2.  Everyone who edits a cell is part of the same company (they are all authenticated and part of the same Google organization)

3. When someone makes an edit, you'd like to record the info of who made the edit, and what they changed (maybe this is written as a log on another sheet)

Let me know if this captures the issue you're having. 

[Sameer Goyal]

Yes, Ed you are right 

But the sheet do not only belong to single company but to multiple even there are chances a user with gmail account may appear.
and when someone will make any change i want to log on other tab. And i want to keep this logging tab protected

[Ed Robinson]

Ok. Final question: do you share the sheet with "Anyone with the link can edit", or is the sharing "Restricted"?

[Sameer Goyal]

Restricted

[Ed Robinson]

Hi Sameer, 

I believe when the onEdit trigger fires, the email address is either:

- Email address (if you're the owner of the spreadsheet)

- Blank (in all other cases)

In my testing, the missing email is not just an issue due to strict policy, it occurs in all cases unless the user is the owner of the spreadsheet. Google Apps Script allows you to get a person's email if they perform an obvious action - like clicking a button that runs a macro, but not if it is a non-obvious action - like opening a sheet or editing a cell

----

There is a second problem: the onEdit event receives incomplete information about what changes the user made. For example if they delete a row - the event doesn't fire. If they or copy/paste a range of data, the event fires but doesn't receive the data

----

Your workarounds depend on exactly what info you want to capture. If it is a form submission, there are plenty of options (like Google Forms) that post into a sheet.

If you're looking at tracking free-edits to a sheet, I can't see how to do that reliably.

Ed

[Sameer Goyal]

Thank you so much Ed for your time