How to auto send email based Column D "Today" to Email on Column A with Subject of COlumn B and Body of Columnn C

I found a script quite similar to my condition, but it only send to a static email Script Source

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

And is it possile to make it compatible with my previous script Link Source

this script about Dynamic Dependent Drop Down Lists

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

Sorry the script is so long, i got warning "your post mostly code" i just post some of it here and you can check full script onLink Source

CodePudding user response：

You can change the script as such to check the rows and send email based on value of column 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);
    }
  }
}

To use this in a trigger similar to your second script you need to create an Installable Trigger and specify the function name. For example, if you want to trigger the sending every 24 hours:

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