Trigger script on non manual change/edit to cell - google script

Question

I'm using the following script to send an email every time a "No" changes to "Yes" in a particular column.

However this only happens if the cell is manually changed to "Yes", typed into a cell. And triggers based on the on edit UI trigger.

But the cell updates based on a formula that references to another sheet where it takes the "No" and "Yes" from, so there is no manual editing/updating on the cell in this sheet.

How can I get it to send the email without any manual change to cells, only on the change from "No" to "Yes"

Any help would be appreciated.

function sendNotification(e){
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var ss = s.getSheetByName("Order Details")
  if(e.range.getColumn()==3 && e.value=='Yes'){
  var cell = ss.getActiveCell();
  var row = cell.getRow();

  var ordernumber = ss.getRange(row,4).getValue();  //Column D
  var sku = [{}]; sku = ordernumber.toString().split("-")

 var sizewidth = ss.getRange(row,5).getValue();  //Column E
 var sizeheight = ss.getRange(row,6).getValue();  //Column F
 var qty = ss.getRange(row,8).getValue();    //Column H
 var country = ss.getRange(row,10).getValue();  //Column J
 var tube = ss.getRange(row,9).getValue();   //Column I
 var paintingimage = ss.getRange(row,7).getValue();  //Column G
 var orderlink = ('http://testly/Km345TS');



 MailApp.sendEmail({
  to: "[email protected]",
  subject:  country + " New Order No. " + ordernumber, // note the spaces between the quotes...
  //attachment: [file,blob],
  htmlBody: "Hello XYZ,  <br><br>"+
   "Please find order details below. <br><br>"+
    sku[1] + "<br><br>" +
   "Size - Width: " + sizewidth + " x " + "Height: " + sizeheight + "<br><br>"+
   "Quantity - " + qty + "<br><br>"+
   "- It needs to be tube rolled"+ "<br>" +
   "- Shipment to " + country + "<br>" +
   "- Order image is " + paintingimage + "<br><br>" +
   "Please fill in cost and delivery details at this link " + orderlink + "<br><br>" +
   "The order is for a customer from " + country + "<br><br>" +
   "Thanking you, <br>" +
   "ABC",            
                   })
 }
}

Update: Solution - a big thank you to Ron.

function sendNotification2(){
  var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var data = sSheet.getRange(2, 1, sSheet.getLastRow(), sSheet.getLastColumn()).getValues(); //get the values of your table, without the header
  var EMAIL_SENT = 'EMAIL_SENT';

  for (var i = 0; i < data.length; i++) {
    var row = data[i];

    var send = row[16];  //Column Q
    var emailSent = row[17];   //Column R
    var ordernumber = row[4];  //Column E
    var country = row[10];  //Column K
    var orderlink = ('http:/testly/Khghgy');
    var shipaddress = row[18];  //Column S

 if (send == "Yes"){
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates

 MailApp.sendEmail({
   to:  "[email protected]",
  subject:  country + " Ship Order No. " + ordernumber, // note the spaces between the quotes...
  htmlBody: "Hello XYZ,  <br><br>"+

   "Thanking you, <br>" +
   "ABC",            
    })
  sSheet.getRange(i+2, 18).setValue(EMAIL_SENT);
   }
  }
 }
}

I've run a test and it also doesn't trigger when the value of the cell is updated with importRange(). I've check the web and found this, some triggers " aren't triggered on automatic changes like does that occur to automatic recalculation of import functions and functions like NOW." You can use installable triggers to check the changes of the sheets. — Mr.Rebot
– Mr.Rebot, Commented Sep 3, 2018 at 2:09

Ron Kloberdanz · Accepted Answer · 2018-08-31 02:45:29Z

1

If you want to have the email triggered on the edit change, you could create a separate function that watches for the edit on the other sheet, and then call the above function to send the email.

Something like:

function onEdit(){
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var ss = s.getSheetByName("Other Sheet Name");
  if(e.range.getColumn()==3 && e.value=='Yes'){  //change this if the data setup is different on the other sheet
    sendNotification();
  }
}

The issue is that this setup will then send an email to every 'Yes' on the email sheet. This can be rectified using an 'Email_Sent' indicator (you can look that up - lots of examples available).

**Another option, as I mentioned in the last question, would be to have the sendNotification function triggered every minute, 5 minutes, 10 minutes, or ??? This won't provide immediate emails, but it would be nearly so.

answered Aug 31, 2018 at 2:45

Ron Kloberdanz

4423 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user2240778 Over a year ago

Thanks Ron, I'd prefer the second option, a time trigger, I've tried it, but for some reason it doesn't work.

user2240778 Over a year ago

Here you go - docs.google.com/spreadsheets/d/…

Ron Kloberdanz Over a year ago

Did you try setting up a time-based trigger? There weren't any active in the script editor. I set up one to run every 5 min.

user2240778 Over a year ago

I had since they weren't working I removed them cause I was get a ton of error notifications

Ron Kloberdanz Over a year ago

OK. As I'm thinking about this, it's occurring to me that the time-based trigger may not work for this situation either. It will have the same problem as the onEdit, because your sendNotification function is looking for specific cells, and if the wrong cell is active, you would be getting no email or email for the wrong item. It may be better to just use the 'Email_Sent' code. That will prevent duplicates/errors.

|

Collectives™ on Stack Overflow

Trigger script on non manual change/edit to cell - google script

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related