Search code examples
google-apps-scriptgoogle-sheetsspreadsheetgoogle-developer-tools

App Script - Send Email based on days left to date - Can't make script send it X days left to date

I am using this spreadsheet Here

I am trying to send an email to a specific recipient "[email protected]" when there are 3 days left for a specific date. Each row represents a person and I want the email sent to send me an email for each person whose date of offboarding - date of today is exactly 3 days.

This is my output: Email received

This is the script i am using:

function Offboarding_Reminder() {
  // get the spreadsheet object
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // set the first sheet as active
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  // fetch this sheet
  var sheet = spreadsheet.getActiveSheet();

  // figure out what the last row is
var lastRow = sheet.getLastRow();
 
// the rows are indexed starting at 1, and the first row
// is the headers, so start with row 
var startRow = 2;

// grab column 11 (the 'days left' column) 
var range = sheet.getRange(2,12,lastRow-startRow+1,1 );
var numRows = range.getNumRows();
var days_left_values = range.getValues();
 
// Now, grab the user name column
range = sheet.getRange(2, 1, lastRow-startRow+1, 1);
var reminder_name = range.getValues();

var warning_count = 0;
var msg = "";
 
// Loop over the days left values
for (var i = 0; i < numRows - 1; i++) {
  var days_left = days_left_values[i][0];
  if(days_left == 3) {
    // if it's exactly 3, do something with the data.
    var user_name = reminder_name[0][i];
     
   msg ="Reminder:"+reminder_name+" offboarding is due in "+days_left+" days.\n";
      warning_count++;
  }
}
if(warning_count) {
    MailApp.sendEmail("[email protected]","Reminder Offboarding",msg)
  }
   
};

Two things I don't understand:

  1. Why is my sent email giving me all the user emails instead of just the ones that have days_left == 3?
  2. To automate this script, once it works, I should just add a trigger to the appscript?

Thanks for the help!

Solution

  • I actually revised your code since it wasn't optimized. It had a lot of redundant and unnecessary lines.

    Here are the changes:

    • fetched the range from user column to days_left column once, not separately
    • created array to store users matching the condition
    • formatted the email to look better.

    Code:

    function offboardingReminder() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // set active sheet to first sheet
  spreadsheet.setActiveSheet(spreadsheet.getSheets()[0]);
  var sheet = spreadsheet.getActiveSheet();
  // figure out what the last row is
  var lastRow = sheet.getLastRow();
  var startRow = 2;
  // grab all data from user to days left
  var range = sheet.getRange(startRow, 1, lastRow - startRow + 1, 12);
  var values = range.getValues();
  var users = [];

  // loop all data per row
  values.forEach(function(row) {
    // if days left is 3
    if(row[11] == 3) {
      // add user if 3 days left
      users.push(row[0]);
    }
  });

  // if users has elements
  if(users) {
    // Formatted the message as html to look nicer
    var message = "<html><body><h1>Reminder!!!</h1><p>The following user/s offboarding is due in 3 days:</p>";
    // created bulleted list for list of users
    var emails = "<ul>";
    users.forEach(function(user){
      emails = emails + "<li>" + user + "</li>";
    });
    emails += "</ul>";
    message = message + emails + "</body></html>";
    MailApp.sendEmail("[email protected]", "Reminder Offboarding", "", {htmlBody: message, noReply: true});
  }
}

    Sample data:

    sample data

    Sample output:

    output

    Note:

    • You can calculate the days left in script instead, but if it is already needed in sheets, then reusing it would be fine.
    • I populated the days left column via formula =DAYS(C2,TODAY()) for row 2 (dragged for other rows)