Send Email With Array Formula In Column E doesn't work

user_4059078 0
Viewed 60

I have a working hours Google web app form that fills the data A:D

  • Column A - Name
  • Column B - In Time
  • Column C - Out Time
  • Column D - Hours

I've added an extra Column E - Status which changes accordingly IN - OUT with ArrayFormula function in E1 based on B and C data.

enter image description here

I want to trigger an email every time a user enters the data and if Column E status changes to "OUT".

THE ISSUE: The email doesn't trigger when the status changes to OUT in Column E, Where did I go wrong?

function sendEmail(e) {
    var thisSheet = e.source.getActiveSheet();
    if (thisSheet.getName() !== 'MAIN' || e.range.columnStart !== 5 || e.range.rowStart == 1 || e.value !== 'OUT') return;
    var body, headers = thisSheet.getRange(1, 1, 1, 5)
        .getValues()[0],
        thisRow = thisSheet.getRange(e.range.rowStart, 1, 1, 5)
        .getValues()[0],
        in = thisRow[3],
        out = thisRow[4],
        recipients = "email@email.com",
        subject = "⚫ Work Log "+in +" "+out
        body = "Work Log Record \n\n",
        i = 0;
    while (i < 5) {
        body += headers[i] +' - ' + thisRow[i] +'\n';
        i++;
    }
    
    MailApp.sendEmail(recipients, subject,  body, {name: "Company"});
}
google-apps-script arrays javascript
1 Answers

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

This applies both for simple and installable triggers. So you need a different approach. You could try

  • Sending the email as part of the Google web app form execution.
  • Creating a time-driven trigger to check the status periodically and then send the email.

Keep in mind that in is a reserved word, so you can't use it as a variable.

user_13294980
Related