Sending Emails from different accounts with Google

Question

I have generated the codes with app script in order to send emails automatically if users changed some values in google spreadsheet, which is working fine.

Now I am sharing my google spreadsheet with the app script to two more person (e.g: PersonA, PersonB). Thus, I want whoever changes anything on google spreadsheet will be sending an email under his/her own account. E.g: [All of us needs to edit within the google spreadsheet itself, without going to the "current web app url", (thanks Cameron Roberts for the clarification] if PersonA changes anything, then an email will be sent out under PersonA's account, and an email will be sent out under PersonB's account if PersonB changes anything.

I did something based on the advice from user2970721 and Cameron Roberts. I adjusted "Deploy web App" as "User accessing the web app". e.g:

I also asked PersonA & PersonB to do the same under their accounts and made sure they have triggered the script at least once. e.g:

My issue is that after I have done all these mentioned above, no matter who changes anything on google spreadsheet, emails were always sent out from PersonB's account (my best guess is that I messed up something and PersonB was the last one who triggered the script).

Does the "Project version" need to be different for me, PersonA, and PersonB, or anything else I need to change? Any help would be greatly appreciated!

Answer 1

First, Your project versions do not need to be different for each user.

I'm assuming you are using the On Change event, rather than a web-app. If that's the case, you should disable the WebApp entirely, as it's not needed.

When PersonA creates an On Change trigger, that will be triggered anytime any user (eg PersonA or PersonB) edits the spreadsheet. When it is triggered the code will execute as PersonA , because PersonA created the trigger. As a result, the email will be sent from PersonA's account.

For the scenario you describe, where both PersonA and PersonB have created OnChange triggers and authorised the script. I would expect emails to be sent from both accounts that have created triggers.

To send only one email, from the account that did the edit, I think you would need to do a check to determine if the user who did the edit matches the user under who's authority the script is running. I've never done this before and the docs don't really make it clear if it's possible.

Try checking the User object (Eg e.user) included with the Change event, and see if that email address is reflecting the different users making the edits.

If it is, you can compare it with the effective user and send the email if they match.

https://developers.google.com/apps-script/reference/base/user

https://developers.google.com/apps-script/reference/base/session#getEffectiveUser()

Finally, it might be simpler to just include the address of the person making the edit in your email subject, and just have all the emails send from one account. That way you know who made the edit, but don't need to have every user create a trigger and do all the extra checking.