Send automated email based on cell value changes in a range

Question

I'm working on a scoreboard automation process using Google Sheets, but I've come up with a little problem. I want an automatic e-mail to be sent to a list of emails when a cell value in a range is met. For example, when an user takes a test, if his/her scores is below 50%, it will instantly send an email to the teacher and the students. The contents of the email should be able to edit easily based on different scores. For example, if the score is below 30% > send an warning email, if the score is above 60%, send a congratulations email

Other needs:

The script should be able to send notification instantly right after the user complete the test
The script should work when other users use the file, not just the owner of the file

I've used similar Appscripts, however if I use onEdit or onChange trigger, everytime any cell change, it send an email, which is way to many, I only want the email to be sent when the test taker complete a whole test. The Link of a demo file is below, please take a look. Many thanks https://docs.google.com/spreadsheets/d/1s0IApxtJuUNbHhKRxEpFnJg_rd2_JAk-GjnSUP9VdJs/edit#gid=0

I don't know how to code so I cannot include a script here, I just think that it might be possible to use Google Appscript or some extensions.

please add your script to the spreadsheet so we can see what is wrong and correct as necessary! — Mike Steelson, Jul 28 '22 at 08:46
@MikeSteelson I don't know how to code so I cannot include a script here, I just think that it might be possible to use Google Appscript or some extensions. — Minh Uplus, Jul 28 '22 at 09:32
but you said `I've used similar Appscripts, however if I use onEdit or onChange trigger, everytime any cell change, it send an email, which is way to many`, so what is this code? — Mike Steelson, Jul 28 '22 at 10:18

score 0 · Accepted Answer · answered Jul 29 '22 at 05:55

Try (set a trigger on the onSpeEdit function when the edit happens)

function onSpeEdit(event) {
  var sh = event.source.getActiveSheet();
  var rng = event.source.getActiveRange();
  if (rng.getColumn() >= 3 && rng.getColumn() <= 11) {
    if (sh.getRange(rng.getRow(), 12).getValue() == 'Completed') {
      var d = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "MMM dd yyyy hh:mm a");
      if (sh.getRange(rng.getRow(), 13).getValue() > 0.5) {
        MailApp.sendEmail({
          to: sh.getRange(rng.getRow(), 2).getValue(),
          cc: sh.getRange('B2').getValue(),
          subject: "Congratulations",
          body: sh.getRange(rng.getRow(), 14).getValue(),
        });
        sh.getRange(rng.getRow(), 15).setValue('sent @ ' + d)
      }
      else if (sh.getRange(rng.getRow(), 13).getValue() < 0.3) {
        MailApp.sendEmail({
          to: sh.getRange(rng.getRow(), 2).getValue(),
          cc: sh.getRange('B2').getValue(),
          subject: "Warning",
          body: sh.getRange(rng.getRow(), 14).getValue(),
        });
        sh.getRange(rng.getRow(), 15).setValue('sent @ ' + d)
      }
    }
  }
}

I've made a change, however the script no longer work in the new situation. In particular, I created two new sheets (Student A and Student B) for 2 students to answer the questions. After that, I use IMPORTRANGE to import those answers to the main file (row 20&21), although the cells in column "L" changed to "Completed" just like the cells above them, the script does not send email accordingly. Please take a look, I've research and tried to change ".getvalue to .getdisplayvalue" but nothing happens https://stackoverflow.com/questions/56880458/cant-use-getvalues-with-importrange — Minh Uplus, Sep 15 '22 at 13:21

Send automated email based on cell value changes in a range

1 Answers1