Send emails using auto script

user_15223550 0
Viewed 50

I am trying to get Gsheets to send to multiple emails. It is gathering information from A2 & A3 only. How do I fix this?

function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('Sheet1');
var n=sheet1.getLastRow();
for (var i = 2; i < n+1 ; i++ ) {
var emailAddress = sheet1.getRange(i,1).getValue();
var subject = sheet1.getRange(i,2).getValue();
var message = sheet1.getRange(i,3).getValue();
MailApp.sendEmail(emailAddress, subject, message);
}

}
google-apps-script google-sheets
1 Answers

Explanation:

Based on your question, I understand that you have fixed subject and message and you want to iterate over a list of emails.

  • You said A2 & A3 but then in your code for the emails you iterate over column A so it does not make sense to also have the subject and the message in the same column that you have the emails.

Solution:

Assuming the following sheet:

enter image description here

here is how you can send emails based on column A with subject and message based on cells B2 and B3 respectively:

function sendEmail() {
  const ss = SpreadsheetApp.getActive()
  const sheet1=ss.getSheetByName('Sheet1');
  const subject = sheet1.getRange('B2').getValue(); // B2
  const message = sheet1.getRange('B3').getValue(); // B3
  const n=sheet1.getLastRow();
  const emails = sheet1.getRange('A2:A'+n).getValues().flat();
  emails.forEach(emailAddress=>{                 
      MailApp.sendEmail(emailAddress, subject, message);           
  });     
}

If you have a different data structure, let me know and I will modify the answer accordingly.

user_112252910
Related