CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
MM91
MM91

Reputation: 23

sendEmail function: get one email for multiple values

I need help with google script and I'm stuck on a particular issue. Basically, I want to get emails (once a day or once a week, doesn't matter - will set a trigger) when the value in column E is zero.

example

So, instead of getting three separate emails that say: "Zero purchases for lemon", "Zero purchases for strawberry" and "Zero purchases for mellon", I'd like to get one email which will say: "Zero purchases for the following fruits" and then in the body of the email I'd have them listed

lemon strawberry mellon

So far all I have is this code which works fine, but I'm missing a line which will merge all the emails into one:

function sendEmail() {
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var startRow = 2
  var numRows = 99
  var dataRange = sheet.getRange(startRow,1, numRows, 99)
  var data = dataRange.getValues()
  for (i in data) {
    var row = data[I];
    var emailAddress = "[email protected]";
    var message = "zero links for" + " "+ row [0];
    var subject = "zero links update";
    if(row[4] <1){
    MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

Upvotes: 0

Views: 146

Answers (1)

Joan L
Joan L

Reputation: 74

Proposal

You should filter the purchases table first and then send an email with the list of fruits not being sold.

The following code should do the trick:

function sendEmail() {
  const s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = s.getRange("A2:E8").getValues();
  const fruits = data.filter(function(row){return row[4]===0}).map(function(row){return row[0]});
  MailApp.sendEmail("[email protected]", "Zero purchases for the following fruits", fruits.toString())
}

Data structure

The data array owns the information of your table of fruits. This array contains seven array objects inside. Each of these arrays stores the row data of the table, so that they are denoted in the code as row. The first position of row contains Fruit (A col) while the fourth one contains Bought Fruit (E col).

Search relevant information

The data array is filtered using filter which returns a new array containing only the rows where Bought Fruit is 0 (return row[4]===0). Then, the returned array is reduced to a list of fruits using the map function, which allows to hold the fruit name (return row[0]).

Send an email

At this point, you obtain the fruits array. Next, this array is converted to a string through toString() function which is the body argument of MailApp.sendEmail().

Result

Finally, note that when you execute sentEmail() only one email is sent. The result looks as follows:

enter image description here

Upvotes: 1

Related Questions