CODEWITHSUNDEEP
google-apps-script
JPadilla_1990
JPadilla_1990

Reputation: 35

Auto-Generated Email Based on Cell Values

I am having trouble with my code and I'm not sure what's missing. I've built code a few times before but I am by no means an expert.

I am trying to send automatic email reminders when a cell value equals 30. My code successfully runs but it doesn't generate an email at all. What am I doing wrong?

I'm not sure what else to try, I've searched up how to make this happen but it isn't making sense to me on how exactly to make this happen.

function sendEmail(){
var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
var Dept = sSheet.getRange(1, 1, sSheet.getLastRow(), 1);
var PDName = sSheet.getRange(1, 3, sSheet.getLastRow(), 3);
var HRPname = sSheet.getRange(1, 26, sSheet.getLastRow(), 26);
var HRPemail = sSheet.getRange(1, 27, sSheet.getLastRow(), 27);
var AnniversaryDate = sSheet.getRange(1, 12, sSheet.getLastRow(), 12);
var DaysUntilAnni = sSheet.getRange(1, 13, sSheet.getLastRow(), 13);


if (DaysUntilAnni == 30){
  var subject = "Upcoming Postdoc Anniversary: " + PDName + " - " + AnniversaryDate;  
  var message = "Hi " + HRPname + "," + "\n\n" + "The Postdoc appointment for "+PDName+" in "+Dept+" has an appointment end date and/or anniversary date coming up on "+AnniversaryDate+". Please review this postdoc appointment and" +
                 "\n\n" + "take necessary action to extend their appointment and/or update level change based on their anniversary date." +
                 "\n\n" + "Please update the BEST Postdoc Tracker no later than " +AnniversaryDate+ " once the actions have been completed."

                 "\n\n" + "You will continue to receive weekly reminders regarding this action until the appointment end date or anniversary date has been updated in the BEST Postdoc Tracker.";     
  MailApp.sendEmail(HRPemail, subject, message);
 }  
}

My code should run daily and it should send emails to the appropriate people as listed in the google sheet.

Upvotes: 1

Views: 100

Answers (1)

Cooper
Cooper

Reputation: 64062

In This line var HRPemail = sSheet.getRange(1, 27, sSheet.getLastRow(), 27); you have defined a range that begins on row 1, column 27, it is sSheet.getLastRow() rows and it has 27 columns and you never actually ran either the getValue() or the getValues() method on it. So there is no data in HRPemail. It's simply a range object which is just a collection of cells.

I suspect that you have a spreadsheet with several rows of data. Right?

So after chatting with you I believe that you will have a little more success with this script.

function sendEmail(){
  var sSheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  var srg=sSheet.getRange(1,1,sSheet.getLastRow(),28);
  var sData=srg.getValues();
  for(var i=0;i<sData.length;i++) {
    var Dept = sData[i][0];//columns begin at 1 arrays begin at zero
    var PDName = sData[i][2];
    var HRPname = sData[i][25];
    var HRPemail = sData[i][2];
    var AnniversaryDate = sData[i][11];
    var DaysUntilAnni = sData[i][12];
    var Sent=sData[i][27];
    if (DaysUntilAnni == 30 && !Sent){
      var subject = "Upcoming Postdoc Anniversary: " + PDName + " - " + AnniversaryDate;  
      var message = "Hi " + HRPname + "," + "\n\n" + "The Postdoc appointment for "+PDName+" in "+Dept+" has an appointment end date and/or anniversary date coming up on "+AnniversaryDate+". Please review this postdoc appointment and" +
        "\n\n" + "take necessary action to extend their appointment and/or update level change based on their anniversary date." +
          "\n\n" + "Please update the BEST Postdoc Tracker no later than " +AnniversaryDate+ " once the actions have been completed."

          "\n\n" + "You will continue to receive weekly reminders regarding this action until the appointment end date or anniversary date has been updated in the BEST Postdoc Tracker.";     
      MailApp.sendEmail(HRPemail, subject, message);
      //i+1 is the row number assuming no headers
      sSheet.getRange(i+1,27).setValue('Sent');//if column 28 has data in it then an email will not be sent again on the next time through the loop.
    }  
  }
}

I haven't debugged it but it should come closer to what you want. Look it over and ask questions. If you have headers in your spreadsheet then we need to take that into account so let me know.

Upvotes: 1

Related Questions