Email notification when date due expired

Question

first of all I would like to say that I am really a rookie at Apps Script.

I have found Script Code for automated Email notification when certain date is expired. I tried to alter the code for my SpreadSheet, but unfortunately it does not work properly for me:

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, 45);
  var values = range.getValues();
  var users = [];

  // loop all data per row
  values.forEach(function(row) {
    // if days left is 3
    if(row[45] <= 30) {
      // 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><h2>Reminder</h2><p>The following device/s service is due in 30 days or less:</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("norbert.jedrzejczak@kbcpl.pl", "Reminder Date Dues Services", "", {htmlBody: message, noReply: true});
  }
}

So what I would like to accomplish here is to send automated notification every week with the list of expired services.

I have a spreadsheet with 51 columns and I would like to:

Take the name of the customer (column 31)
Take the date of last service (column 41)
Take the number of days left to next service (column 44) - and take these values which are equal or less than 30

And send a message with these data.

Subject: "Reminder Date Dues Services" + Current Date (of sending each email) Body:

"Reminder! The following device/s service is due in 30 days or less:"
And below - a list of devices to be serviced - e.g. "CUSTOMER 1 - DEVICE 1 - SERIAL NUMBER 1 - DATE OF LAST SERVICE - DATE TO NEXT SERVICE"

Could you please help me out with the code? I would be grateful for your tips and recommendations.

score 0 · Answer 1 · answered Aug 07 '22 at 16:57

0

Just to get you started:

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const sr = 2;
  const vs = sh.getRange(sr,sh.getLastRow() - sr + 1, sh.getLastColumn()).getValues();
  vs.forEach((r,i) => {
    let n = r[30];
    let dt = r[40];
    let ds = r[43];
    if(ds < 30 ) {
      //complete message and email in here
    }
  })
}

answered Aug 07 '22 at 16:57

Cooper

59,616
6
23
54

Thank you for your help. However, when I put the code responsible for sending messages (as shown in my post), it did not work unfortunately. – kbc_coding Aug 14 '22 at 08:53

Email notification when date due expired

1 Answers1