1

I am using this spreadsheet Here

I am trying to send an email to a specific recipient "email@myemail.com" 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@myemail.com","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!

Manel Veloso
  • 13
  • 4

1 Answers1

2

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@myemail.com", "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)
NightEye
  • 10,634
  • 2
  • 5
  • 24
  • 1
    Cool! What a wizard! That solved it for me! Thanks so much for the much needed improvements!! – Manel Veloso Mar 05 '21 at 18:23
  • Hi @ManelVeloso , I'm glad that it worked out for you. If we answered your question, please click the accept button. By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. stackoverflow.com/help/accepted-answer – NightEye Mar 05 '21 at 18:29
  • Yep it did! Thank you :) I don't see the accept button next to the answer, could it be because i'm a new user? – Manel Veloso Mar 06 '21 at 01:37
  • 1
    With the new changes you've made I got a little overwhelmed :D – Manel Veloso Mar 06 '21 at 01:39