CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-apps
Shaalan
Shaalan

Reputation: 1

Google Apps Script - Send Email based on value in columns

I am new with Google App Scripts and have a spreadsheet with 2 columns: https://docs.google.com/spreadsheets/d/1J3IR3y7VemlrKNsJ8u0gzaUAZy6wBb7qncPfKkab4l4/edit#gid=0

The scripts I'm using:

function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1'); // change Sheet1 to the name of your sheet
  const data = sh.getRange('A2:B'+sh.getLastRow()).getValues();
  data.forEach(r=>{
     let salesValue = r[1];  
     if (salesValue = 0){
         let name = r[0];
         let message = 'There are no sales for  ' + name;
         let subject = 'No SALES.'
         MailApp.sendEmail('[email protected]', subject, message); 
     }
  });  
}

I would like to send one email only to group of emails on a daily basis when the sales column (B) equals ZERO. Assume that the sheet is updated daily and I want to set a trigger to run the script daily.

Upvotes: 0

Views: 611

Answers (1)

punkopotamus7
punkopotamus7

Reputation: 1

Your email send is in the forEach loop so every row that is zero (change the if comparison to ==) it is sending the email. Bring that to the bottom and you'll just get one email. This might get you closer to what you're looking for:

function sendEmail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1'); // change Sheet1 to the name of your sheet
  const data = sh.getRange('A2:B'+sh.getLastRow()).getValues();
  let zeroSales = []
  data.forEach(r=>{
     let salesValue = r[1];  
     if (salesValue == 0){
         let name = r[0];
         zeroSales.push(name)
     }
  });
  if (zeroSales.length > 0) {
    let subject = 'No SALES.'
    let message = 'There are no sales for ' + zeroSales.join(', ')
    MailApp.sendEmail('[email protected]', subject, message); 
  }
}

Upvotes: 0

Related Questions