App Script help to send email on value change

[MoPR Helpdesk]

Hi everyone,

I am trying for script that can send email automatically on change of value by formula and to set the time stamp in another column. Below is script

function sendEmailOnTrue() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var range = sheet.getRange("A1:AG"); // Modify the range to fit your needs

  var displayedValues = range.getDisplayValues();

  var formulas = range.getFormulas();

  for (var i = 0; i < formulas.length; i++) {

    var rowDisplayed = displayedValues[i];

    var rowFormulas = formulas[i];

    var condition = rowDisplayed[25] === "TRUE"; // Cell with TRUE/FALSE value in column 26

    var subject = rowDisplayed[1]; // Cell with email subject

    var body = rowDisplayed[24]; // Cell with email body

    var recipients = rowDisplayed[3]; // Cell with email recipients

    if (condition && recipients !== "") {

      var recipientEmails = recipients.split(",");

      for (var j = 0; j < recipientEmails.length; j++) {

        var recipientEmail = recipientEmails[j].trim();

        if (recipientEmail !== "") {

          MailApp.sendEmail({

            to: recipientEmail,

            subject: subject || "(No subject)",

            body: body

          });

        }

      }

      var timestampCell = sheet.getRange(i + 2, 32); // Cell to hold the timestamp in column AF (offset 31)

      timestampCell.setValue(new Date());

    }

  }

}

But this script neither sending email nor the time stamp. Any one please help me out?

[Hacı Bayram TAĞ]

Did you set a trigger for this?

 

Windows için Posta ile gönderildi

 

Kimden: MoPR Helpdesk
Gönderilme: 22 Haziran 2023 Perşembe 12:27
Kime: Google Apps Script Community
Konu: [Apps-Script] App Script help to send email on value change

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9212ac49-cfca-4431-a25b-78fd6b8494fan%40googlegroups.com.

 

[cbmserv...@gmail.com]

If this script does everything you want done and you just want it to run when a change is done, then just create a trigger manually and set it to run this function when the spreadsheet is edited.

 

Put your cursor on the left side of the script editor screen, you will see Trigger tab show, click it and then on bottom right, click add trigger, then from that window, select your function, then select the on edit condition to trigger your function. That should do it.

--