email - 需要用于根据 Google 表格单元格值发送电子邮件的脚本代码

Question

我正在尝试编写一个脚本，只要 Google 表格电子表格上的单元格包含该人的姓名，该脚本就会向该人生成一封电子邮件。它被构建为一个任务跟踪器。基本上，有五个人可以获得任务。当该名称输入到该项目的单元格中时（我的作业名称单元格在 C 列中），然后我想向他们发送电子邮件。我在同一文档的单独表格中有电子邮件地址。一旦成功，我就知道如何设置触发器。我编写了以下代码（请记住，还需要添加两个名称，但您会从中得到想法）。发生了四件事我不希望发生。首先，它忽略了名称值。我在单元格中输入什么信息并不重要，它会发送一封电子邮件。第二，它会向我添加到此列表中的每个人发送一封电子邮件，无论任务应该是给谁的。第三，它会针对同一任务发送几封电子邮件。我只希望在分配时发送一次电子邮件。最后，如果整行被删除（我有一个脚本可以在完成后将其移动到另一张表），它会再次生成电子邮件。我的代码如下。如果我完全偏离轨道，请告诉我如何写一些可行的东西。非常感谢！！我的代码如下。如果我完全偏离轨道，请告诉我如何写一些可行的东西。非常感谢！！我的代码如下。如果我完全偏离轨道，请告诉我如何写一些可行的东西。非常感谢！！

function sendEmail() {
  // Sends an email based on assignment
  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ongoing Projects").getRange("C"); 
  var emailAddress = emailRange.getValue();
  // Check if assignment made
  if (emailRange = 'Name here'){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Validation").getRange("D3");
    var emailAddress = emailRange.getValues();
    // Send Alert Email.
    var message = 'You have a new assignment waiting on the Content Project Management Sheet. Please visit the sheet to see your new assignment. '; // Second column
    var subject = 'New Content Development Assignment';
    MailApp.sendEmail(emailAddress, subject, message);}

     if (emailRange = 'Second Name Here'){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Validation").getRange("D2");
       var emailAddress = emailRange.getValues();
       // Send Alert Email.
    var message = 'You have a new assignment waiting on the Content Project Management Sheet. Please visit the sheet to see your new assignment. '; // Second column
    var subject = 'New Content Development Assignment';
       MailApp.sendEmail(emailAddress, subject, message);}

       if (emailRange = 'Third Name Here'){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Validation").getRange("D4");
         var emailAddress = emailRange.getValues();}

    // Send Alert Email.
    var message = 'You have a new assignment waiting on the Content Project Management Sheet. Please visit the sheet to see your new assignment. '; // Second column
    var subject = 'New Content Development Assignment';
    MailApp.sendEmail(emailAddress, subject, message);
    }

标签： emailgoogle-apps-scriptgoogle-sheetscell

Answer 1

您尚未使用固定单元格范围

var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ongoing 
 Projects").getRange("C");

需要是

var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Ongoing 
 Projects").getRange("C3"); 

条件检查也需要 ==

if (emailRange = 'Name here'){

应该

if (emailRange == 'Name here'){