在非手动更改/编辑单元格时触发脚本-Google脚本 [英] Trigger script on non manual change/edit to cell - google script

问题描述

每次使用"否"更改为特定列中的"是"时，我都会使用以下脚本发送电子邮件.

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

但是，只有在将单元格手动更改为"是"并键入单元格时，才会发生这种情况.并基于on edit UI触发器进行触发.

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: "[e-mail]",
  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:  "[e-mail]",
  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);
   }
  }
 }
} 

推荐答案

如果您希望在修改更改时触发电子邮件，则可以创建一个单独的函数来监视另一张工作表上的修改，然后调用以上功能发送电子邮件.

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.

类似:

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();
  }
}

问题在于，此设置随后将向电子邮件表上的每个是"发送一封电子邮件.可以使用'Email_Sent'指示符对此进行纠正(您可以查找一下-许多可用示例).

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.

这篇关于在非手动更改/编辑单元格时触发脚本-Google脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！