Home > database >  Google App Script to trigger on cell value change with email notification
Google App Script to trigger on cell value change with email notification

Time:12-20

looking for a help to fix my script in Google spreadsheets. I want to build a function that sends an email every time a certain cell in the list is updated. Here is my code, but it doesn't work.

Looking for your help to find and fix my issue

function onEdit(e) {
  const specificSheet = "Inventory"
  const specificCell = "B1"

  let sheetCheck = (e.range.getSheet().getName() == specificSheet)
  let cellCheck = (e.range.getA1Notation() == specificCell)

  if (!(sheetCheck && cellCheck)) {
    return
  }
  else {
  sendEmail(){
  var subject = "New update";
  var emailAddress = "[email protected]";
  var message = "new update: link";
  MailApp.sendEmail(emailAddress, subject, message)
  }
}
}

CodePudding user response:

Send email on an Edit

function onMyEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() = "Inventory" && e.range.columnStart == 2 && e.range.rowStart == 1) {
    var subject = "New update";
    var emailAddress = "[email protected]";
    var message = "new update: link";
    MailApp.sendEmail(emailAddress, subject, message)
  }
}

I think this needs to be an installable trigger in order to send an email.

CodePudding user response:

Sometimes Sheet can not process complex operations related to onEdit(e), so maybe this could help:

function onEdit(e) {
  const specificSheet = "Inventory";
  const specificCell = "B1";
  var range = e.range;
  var ss = e.source; 
  var sheetName = ss.getActiveSheet().getName();
  var a1Notation = range.getA1Notation();

  if ((sheetName==specificSheet) && (a1Notation==specificCell)) {
    var props = PropertiesService.getScriptProperties();
    props.setProperty("CHECKED","TRUE");
}
}

function sendEmail(){
  var subject = "New update";
  var emailAddress = "[email protected]";
  var message = "new update: link";
  var props = PropertiesService.getScriptProperties();
  var checked = props.getProperty("CHECKED");
  if (checked == "TRUE"){

    GmailApp.sendEmail(emailAddress, subject, message);
    Utilities.sleep(3000);
    props.setProperty("CHECKED","FALSE");
   }
 }

Another thing you should do is setting sendEmail() function is activated by time trigger every minute or so...

  • Related