'send email from currently logged in user when checkbox is TRUE

My question is an extension of this fantastic solution but am hoping to take it one step further. Whenever a user other than me marks the checkbox as TRUE, the sender of the email is always me, since:

Installable triggers always run under the account of the person who created them

Is it possible to capture the user currently logged in and make them the sender, and if so, what am I missing in my code to make that happen?

What I've tried

I believed I had found my answer, but no such luck. This still posts the timestamp to the sheet successfully, but the email sender still shows as me. Any help would be greatly appreciated.

Edit(s):

I noticed in the above solution if (activeUser === effectiveUser) { only "worked" if typed as if (activeUser !== effectiveUser) {. In my attempts at making it work, I made that edit and forgot to revert it.

function sendEmail(e){
  var sheet = e.source.getActiveSheet();
  var cell = e.range;
  var activeUser = Session.getActiveUser().getEmail();
  var effectiveUser = Session.getEffectiveUser().getEmail();
  
  if (activeUser !== effectiveUser) {

    Logger.log(cell.getColumn());
    Logger.log(cell.isChecked());
    //Check if the checkbox in column G(index 7) was checked
    if(sheet.getName() == "actionItems" && cell.getColumn() == 7 && cell.isChecked()){

      //get current row values from column A to column F
      var values = sheet.getRange(cell.getRow(),1,1,6).getDisplayValues().flat();
      Logger.log(values);
      var transmittalNumber = values[0];
      var email = values[5];
      var query = values[1];

      //create and update the email's hmtl body
      var templ = HtmlService.createTemplateFromFile('html_template');
      templ.query = query;
      var message = templ.evaluate().getContent();

      //Send email
      MailApp.sendEmail({
        to: email,
        subject: "Oatsies Action Item: "+transmittalNumber,
        htmlBody: message
      });

      //Add timestamp at column H(index 8)
      var timeZone = "GMT-7";
      var date = Utilities.formatDate(new Date(),timeZone, "yyyy-MM-dd HH:mm");
      sheet.getRange(cell.getRow(),8).setValue(date);
    }
  }
}
emailgoogle-apps-scriptgoogle-sheetstriggerstimestamp


Solution 1:[1]

From the question

Is it possible to capture the user currently logged in and make them the sender?

If you are using a free Google account (usually gmail.com account) it might be possible if you use the Gmail API and set a way for active users to authorize the access to their Gmail account to send emails. Also it might be possible if you and the user are using a Google Workspace accounts and if you are able to take advantage of company-wide delegation of authority (also might be possible if you are able to configure the user's email addresses as emails aliases of your account).

Regarding the use of Session.getActiveUser().getEmail() it will return the active user email based on a complex rules i.e. your account and active user belongs to the same Google Workspace domains.

Related

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions