AppSScript Code

Copy Code

function scheduleDripEmails() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var dataRange = sheet1.getDataRange();
  var data = dataRange.getValues();
  var numRows = data.length;


  for (var i = 1; i < numRows; i++) {
    var recipientName = data[i][0];
    var recipientEmail = data[i][1];
    var scheduleTime = new Date(data[i][2]);
    var emailSubject = sheet2.getRange("A1").getValue();
    var emailBody = sheet2.getRange("A2").getValue();


    // Create a time-driven trigger for this row
    ScriptApp.newTrigger("sendDripEmail")
      .timeBased()
      .at(scheduleTime)
      .create();


    sheet1.getRange(i + 1, 4).setValue("Scheduled");
    sheet1.getRange(i + 1, 5).setValue(scheduleTime);
    Logger.log("Scheduled email to " + recipientName + " (" + recipientEmail + ") for " + scheduleTime);
  }
}


function sendDripEmail() {
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var dataRange = sheet1.getDataRange();
  var data = dataRange.getValues();
  var numRows = data.length;


  for (var i = 1; i < numRows; i++) {
    var recipientName = data[i][0];
    var recipientEmail = data[i][1];
    var scheduleTime = new Date(data[i][2]);
    var currentTime = new Date();


    // Send the email if the current time matches the schedule time
    if (scheduleTime <= currentTime) {
      var emailSubject = sheet2.getRange("A1").getValue().replace("", recipientName);
      var emailBody = sheet2.getRange("A2").getValue().replace("", recipientName);


      try {
        GmailApp.sendEmail(recipientEmail, emailSubject, emailBody);
        sheet1.getRange(i + 1, 4).setValue("Sent");
        sheet1.getRange(i + 1, 5).setValue(new Date());
        Logger.log("Sent email to " + recipientName + " (" + recipientEmail + ")");
      } catch (e) {
        sheet1.getRange(i + 1, 4).setValue("Failed");
        sheet1.getRange(i + 1, 5).setValue(new Date());
        Logger.log("Failed to send email to " + recipientName + " (" + recipientEmail + ")");
      }


      // Clear the schedule time for this row so the email is not sent again
      sheet1.getRange(i + 1, 3).clearContent();
    }
  }
}