google-apps-script - 根据单元格值自动发送电子邮件并使用以前的脚本进行调整

Question

如何将基于 D 列“今天”的电子邮件自动发送到 A 列上的电子邮件，主题为 B 列，正文为 C 列

我发现一个脚本与我的情况非常相似，但它只发送到静态电子邮件 脚本源

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");  // To only handle the trigger sheet
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    if (row[2] === "Today") {       // Trigger only if Column C is "Yes"
      var emailAddress = row[0];  // First column
      var message = row[1];       // Second column
      var subject = "Bday ==" + row[2]; // Add "Yes" although by your trigger logic it will always say yes in the email
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

是否有可能使它与我以前的脚本 链接源兼容

这个关于动态相关下拉列表的脚本

function onEdit(event) 
{
  var maxRows = false;
    
  // Change Settings:
  //--------------------------------------------------------------------------------------
  var TargetSheet = 'Main'; // name of sheet with data validation
  var LogSheet = 'Data1'; // name of sheet with data
  var NumOfLevels = 4; // number of levels of data validation
  var lcol = 2; // number of column where validation starts; A = 1, B = 2, etc.
  var lrow = 2; // number of row where validation starts
  var offsets = [1,1,1,2]; // offsets for levels
  //                   ^ means offset column #4 on one position right.  
  // var maxRows = 500; // to set the last row of validation; delete this row if not needed

  
  // =====================================================================================
    
  SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets, maxRows);
  
  // Change Settings:
  //--------------------------------------------------------------------------------------
  var TargetSheet = 'Main'; // name of sheet with data validation
  var LogSheet = 'Data2'; // name of sheet with data
  var NumOfLevels = 7; // number of levels of data validation
  var lcol = 9; // number of column where validation starts; A = 1, B = 2, etc.
  var lrow = 2; // number of row where validation starts
  var offsets = [1,1,1,1,1,1,1]; // offsets for levels
  // var maxRows = 500; // to set the last row of validation, delete this row if not needed
  // =====================================================================================  
  SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets, maxRows);

  
}



function SmartDataValidation(event, TargetSheet, LogSheet, NumOfLevels, lcol, lrow, offsets, maxRows) 

..... Etc etc

抱歉脚本太长了，我收到警告“你的帖子主要是代码”我只是在这里发布了一些，你可以在链接源上查看完整的脚本

标签： google-apps-scriptgoogle-sheets

Answer 1

您可以更改脚本以检查行并根据 D 列的值发送电子邮件：

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");  // To only handle the trigger sheet
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow()-1;   // Number of rows to process
  // Fetch the range of cells A2:D
  var dataRange = sheet.getRange(startRow, 1, numRows, 4)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    if (row[3] === "Today") {       // Trigger only if Column D is "Today"
      var emailAddress = row[0];
      var subject = row[1];
      var message = row[2];
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

要在类似于第二个脚本的触发器中使用它，您需要创建一个可安装触发器并指定函数名称。例如，如果您想每 24 小时触发一次发送：

function createTimeDrivenTriggers() {
  // Trigger every 24 hours.
  ScriptApp.newTrigger('sendEmails')
      .timeBased()
      .everyHours(24)
      .create();
}