Google表格根据单元格值发送一次电子邮件 [英] Google Sheets email once based on cell values

问题描述

这个脚本可以工作，除了发送多封电子邮件外，这个脚本检查F列的任何大于0的值，然后发送一个email.Column F是我手动输入数据的地方。试图让它只发送一封电子邮件，并在H列中输入发送电子邮件。然后检查H列是否有已发送电子邮件，如果不是，请再次发送电子邮件。

这是我的工作表的样子

https://docs.google .com / spreadsheets / d / 1o5jKMDECIFozrwAQx6EDvPL1NkoMQ1nbpkd6AdS9ULk / edit？usp = sharing

  function sendEmails（）{
 var sSheet = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（Form Responses）; //获取表单Form Responses
 var data = sSheet.getRange（2，1，sSheet.getLastRow（），sSheet.getLastColumn（））。getValues（）; //获取表的值，不包含头部
 var lastRow = sSheet.getLastRow（）; //获取输入数据的最后一行
 
 var yourColumn =H; / /设置你的列
 var yourFunction =（Email Sent）; //你想要的任何函数或公式或数据，只是不要忘记使用lastRow做行号
 sSheet.getRange（ yourColumn + LASTROW）.setValue（yourFunction中）; 
 
 for（var i = 0; i< data.length; i ++）{//对于标签的每个元素
 
 var row = data [i]; 
 
 if（row [5]> 0）{//如果这个人至少有1个未完成的报告（E列）
 
 var body =美好的一天 +行[0] +你有+行[5] +内部审计报告未完成。 
 GmailApp.sendEmail（行[1]，杰出内部审计报告，正文）; //发送邮件以提醒
 
 
 
 $ b  

 函数sendEmailsToTechs（）{
 var ss = SpreadsheetApp.getActive（）; 
 var sh = ss.getSheetByName（Form Responses）; 
 // var sh = ss.getActiveSheet（）; 
 var rg = sh.getDataRange（）; 
 var data = rg.getValues（）; 
 var hRange = sh.getRange（2,8，sh.getLastRow（），1）; 
 var hValues = hRange.getValues（）; 
 for（var i = 1; i< data.length; i ++）{
 var row = data [i]; 
 if（row [5]&& row [7]！='Email Sent'）{
 var body =美好的一天+ row [0] +you have+ row [ 5] +内部审计报告未完成。 
 GmailApp.sendEmail（行[1]，杰出内部审计报告，正文）; 
 //Logger.log（body）; 
 hValues [i-1] [0] ='发送邮件'; 
} 
} 
 hRange.setValues（hValues）; //将这些值一次放入电子表格中。 
} 
  

This script works, except it sends multiple emails, This script check Column F for any value above 0 then sends an email.Column F is where i manually enter data. Trying to get it to only send one email and put a "Email Sent" in column H. Then check if column H has a "Email Sent" if so don't send email again.

Here Is what my sheet looks like

https://docs.google.com/spreadsheets/d/1o5jKMDECIFozrwAQx6EDvPL1NkoMQ1nbpkd6AdS9ULk/edit?usp=sharing

function sendEmails() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses"); //get the sheet "Form Responses"
  var data = sSheet.getRange(2, 1, sSheet.getLastRow(),sSheet.getLastColumn()).getValues(); //get the values of your table, without the header
 var lastRow = sSheet.getLastRow();//gets the last row of entered data

 var yourColumn = "H";//set your column
 var yourFunction = ("Email Sent");//whatever function or formula or data you want,just don't forget to use lastRow do do the row number
  sSheet.getRange(yourColumn+lastRow).setValue(yourFunction);

  for (var i = 0; i < data.length; i++) { // For each element of your tab

var row = data[i];

if(row[5] > 0) {  // If the person have got at least 1 report outstanding (column E)

    var body = "Good day "+ row[0] + " you have " + row[5] + " Internal Audit reports outstanding.";
    GmailApp.sendEmail(row[1],"Outstanding Internal Audit Reports", body); // Sending a mail to alert

  }
 } 
}

解决方案

Try This:

function sendEmailsToTechs() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Form Responses");
  //var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var data=rg.getValues();
  var hRange=sh.getRange(2,8,sh.getLastRow(),1);
  var hValues=hRange.getValues();
  for (var i=1;i<data.length;i++){
    var row = data[i];
    if(row[5] && row[7]!='Email Sent') {
      var body = "Good day "+ row[0] + " you have " + row[5] + " Internal Audit reports outstanding.";
      GmailApp.sendEmail(row[1],"Outstanding Internal Audit Reports", body); 
      //Logger.log(body);
      hValues[i-1][0]='Email Sent';  
    }
  }
  hRange.setValues(hValues);//Puts the values into the spreadsheet all at one time. 
}

这篇关于Google表格根据单元格值发送一次电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！