Log new-row creator’s email in Column A, all accounts. Script:
function logIt(e){ var s=e.source.getActiveSheet(); if(s.getName()!='LogSheet') return; var m=Session.getEffectiveUser().getEmail(); var r=e.range.getRow(); if(!s.getRange(r,1).getValue()) s.getRange(r,1).setValue(m); }
i found using an installable onChange trigger really helped. it captured external emails better than onEdit. testing settings and ensuring proper script context fixed many issues i’ve had. give it a try if your logs still miss some user info.
Based on my experience, ensuring you capture the correct user email for every row addition requires a bit more than just a simple onEdit trigger. When I first set it up, I noticed that the trigger sometimes missed external account activities. Switching to an installable trigger and then adding extensive logging helped me monitor and verify trigger events. There were times when rows were added due to automated scripts and user actions simultaneously, which further complicated email capture. By logging trigger execution details, I could pinpoint discrepancies and adjust my code accordingly so that every user, regardless of their account type, is properly tracked.
In my experience, successfully logging the email of a user when they add a row in Google Sheets requires careful management of triggers and account types. Rather than solely relying on Session.getEffectiveUser(), it’s important to validate that the trigger is indeed capturing the correct user identity, especially when external accounts are involved. There have been instances where unexpected behavior occurred due to differences in how Google handles anonymous changes versus logged-in user actions. Building in rough error handling and conditions for cases where no valid email is returned has improved my logging accuracy significantly.
Based on personal experience, it is essential to remember that simple triggers such as onEdit(), do not include authorization details for external accounts. A viable solution is to use an installable trigger which provides the expanded set of event properties. This ensures that the user’s email is captured correctly regardless of account type. Additionally, verifying that the trigger executes in the correct context is key. A practical workaround has been testing both Workspace and personal accounts extensively to ensure the log consistently records accurate user data.