How to send email to only selected emails from google sheet?

Question

new to google script, I have a google script with a button on the sheet which sends out emails from a google sheet, it works fine.

however I want to send emails to those emails that I checkmark manually, I have a column (F) with a checkmark, so I want to checkmark emails then send emails to those emails that have been check-marked only.

I stuck in the completion of the coding for the checkmark part, I appreciate your help. I have commented out my 2 lines of code for filtering the checkmarks.

the code check for send email quote as well as writes timestamp and email sent note every time email has been sent.

Thanks.

these are columns, one header: A1: timestamp B1: email address C1: Name D1: Email Sent? E1: Data emaill sent F1: check box

function sendEmails(){           
var sheet = SpreadsheetApp.openById("mySheetid");
var ss = sheet.getSheetByName("Emails");  
var lr = ss.getLastRow();

var EMAIL_SENT = 'Email has been sent';
var messageSubject = 
 SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BodyTextSheet").getRange(2, 1).getValue();
var messageText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SubjectSheet").getRange(2, 2).getValue();
 
var quotaLeft = MailApp.getRemainingDailyQuota();
            if ((lr-1) > quotaLeft){
Browser.msgBox("you have " + quotaLeft + "left and you're trying to send " + (lr-1) + "emails. Emails were not sent.");
} else{
        for (var i = 2; i<=lr; i++){
        var currentEmail = ss.getRange(i, 2).getValue();
        var currentName = ss.getRange(i, 3).getValue();
        var messageBody = messageText.replace("{name}",currentName);
          

       //var selected = ss.getRange("F2:F" + ss.getLastRow()).getValues();
       //data = selected.filter(function(r){ return r[6] == true});     
          
       MailApp.sendEmail(currentEmail, messageSubject, messageBody); 
          
       var EmailSent  = ss.getRange(i, 4).setValue(EMAIL_SENT);
       var TimeEmailSent = ss.getRange(i, 5).setValue(new Date());
       SpreadsheetApp.flush();   
            
       }
      }
    }

RemcoE33 RemcoE33 · Accepted Answer · 2021-05-09T20:22:12

This worked out when i tested it. In your code you have BodyTextSheet with the var: messageSubject and viseversa... This is a bit confusing. So you need to change those ranges in this code.

function sendEmails() {
  const ss = SpreadsheetApp.openById("mySheetid");
  const emailSheet = ss.getSheetByName("Emails");
  const bodyText = ss.getSheetByName("BodyTextSheet").getRange(2, 1).getValue()
  const subjectText = ss.getSheetByName("SubjectSheet").getRange(2, 2).getValue()
  const emails = emailSheet.getDataRange().getValues();

  const filterdRows = [];

  for (i = 0; i < emails.length; i++) {
    const row = emails[i]
    if (row[5] == true) {
      row.unshift(i+1)
      filterdRows.push(row)
    }
  }

  const quotaLeft = MailApp.getRemainingDailyQuota();

  if (filterdRows.length > quotaLeft) {
    Browser.msgBox(`you have ${quotaLeft} left and you're trying to send ${filterdRows.length} emails. Emails were not sent.`);
  } else {
      filterdRows.forEach(email => {
        const messageBody = bodyText.replace("{name}", email[3]);
        MailApp.sendEmail(email[2], subjectText, messageBody);
        emailSheet.getRange(email[0], 4, 1, 2).setValues([['Email has been sent', new Date()]])
      })
      SpreadsheetApp.flush();
  }
}

So i made some modifications:

const emails: Get all the emails at once (much quicker)
Loop over the data and check if column F (arrays are 0 indexed so column 5) is true
If is true then add i+1 (rownumber) to the beginning of the array
Then forEach filtertEmail send the mail and set set text and date in the right row (in one call) That is why i pushed the rownumber in the beginning.

How to send email to only selected emails from google sheet?

1 Answers