CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Pramod
Pramod

Reputation: 11

Trigger an email from Google Sheet when a condition is met

I have a program in Apps Script & The purpose is to send an email alert for multiple recipients from Google Sheet when a condition is met. The email body should contain an HTML table.

The Google Sheet has data in 3 columns (A, B & C). Email should trigger when the following condition is met: Column C > 10

As per the below program, I am getting the alert in table format, But I am unable to add this condition to trigger an email. Recipients should not receive the email if the above condition is not met.

function sendMail(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Status")
 var data = sh.getRange("A1:C20").getValues();
  //var htmltable =[];

var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="2" style="width:100%;table-layout:fixed;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:3px solid #ccc;font-weight:normal;color:Black;background-color:white;text-align:center;text-decoration:grid;font-style:Times;'
var htmltable = '<table ' + TABLEFORMAT +' ">';

for (row = 0; row<data.length; row++){

htmltable += '<tr>';

for (col = 0 ;col<data[row].length; col++){
  if (data[row][col] === "" || 0) {htmltable += '<td>' + '' + '</td>';} 
  else
    if (row === 0)  {
      htmltable += '<th>' + data[row][col] + '</th>';
    }

  else {htmltable += '<td>' + data[row][col] + '</td>';}
}

     htmltable += '</tr>';
}

     htmltable += '</table>';
     Logger.log(data);
     Logger.log(htmltable);

      var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange("A1:A20");
    var emailAddress = emailRange.getValues();
GmailApp.sendEmail(emailAddress, 'Order Status Update','' ,{htmlBody: htmltable});
}

Link to a sample Google Sheet: https://docs.google.com/spreadsheets/d/1NpA3kTJFnUFRJ4CTNeSwJNEfSGAezchEuwfMi6ZGQoc/edit?usp=sharing

Upvotes: 1

Views: 523

Answers (1)

Tanaike
Tanaike

Reputation: 201358

I believe your goal is as follows.

  • You want to send an email including all rows that the value of column "C" is more than 10.

In this case, how about the following modification?

From:

var data = sh.getRange("A1:C20").getValues();

To:

var data = sh.getRange("A1:C20").getValues().filter((r, i) => i == 0 || (i > 0 && r[2] > 10));
if (data.length == 1) return;
  • By this modification, an email including all rows that the value of column "C" is more than 10 is sent. And, when there are no rows where the value of column "C" is more than 10, no email is sent.

Reference:

Upvotes: 2

Related Questions