CODEWITHSUNDEEP
google-apps-script
Wimpie Hattingh
Wimpie Hattingh

Reputation: 43

Sending emails based on cell values

I am trying to send weekly emails to managers with outstanding reports. The code should just take into account the value of cells in column "E" when sending reminders. The code I am using is pasted together from various sources on the internet, it executes without any errors but it does not send any emails even when the conditions have been met. I would greatly appreciate any help.

Data being used is present on the "Area Managers" sheet.

And this is my attempt at the code:

function sendEmails() {

var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Area Managers");
var range = sSheet.getDataRange();  
var column = 4;
var cValues = Number(sSheet.getRange(2, column, sSheet.getLastRow()).getValue()); 

  var startRow = 2;
  var numRows = sSheet.getLastRow()-1;
  var dataRange = sSheet.getRange(startRow, 1, numRows, 2)
  var data = dataRange.getValues();

  for (i in data) {
    var row = data[i];

    if(row[5] >= 0) {  
   range = range.offset(1, 0, range.getNumRows()-1);
   range.getValues().forEach( function( recipient, key, data ) {
    var body = "Good day "+ recipient[0] + " you have " + recipient[4] + " Internal Audit reports outstanding.";
    GmailApp.sendEmail(recipient[0] +"<"+recipient[1]+">","Outstanding Internal Audit Reports", body);
   })}}; 
}

Upvotes: 4

Views: 6535

Answers (1)

Pierre-Marie Richard
Pierre-Marie Richard

Reputation: 2004

Your code is a bit messy. You declare some variable that you don't use.Here is a simply solution of your problem:

function sendEmails() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Area Managers"); //get the sheet "Area Managers"
  var data = sSheet.getRange(2, 1, sSheet.getLastRow(), sSheet.getLastColumn()).getValues(); //get the values of your table, without the header

  for (var i = 0; i < data.length; i++) { // For each element of your tab

    var row = data[i];

    if(row[5] > 0) {  // If the person have got at least 1 report outstanding (column E)

        var body = "Good day "+ row[0] + " you have " + row[5] + " Internal Audit reports outstanding.";
        GmailApp.sendEmail(row[1],"Outstanding Internal Audit Reports", body); // Sending a mail to alert

    }
  } 
}

The code is comment so you can understand the process

PS: You should be careful about the quota with the GmailApp mail sending (see here), you may be blocked if you send too many mail

Upvotes: 3

Related Questions