How to send emails with for loop in google script?

Question

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2000;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message, {noReply:true});
  }
}

Here I want to send C2, D2, E2, F2 row-wise data to A2 and C3, D3, E3, F3 row-wise data to A3 and the loop should run for all the rows in the sheet.

I have a script send the common subject body to everyone.

But this script is not helping to resolve my issue.

Could someone please help me with sending data in the mentioned pattern

In the best case, you're probably getting empty emails, as you're only considering the first two columns when creating your variable data. Your message variable then contains the content of column B, which is empty. Can you describe further what exactly the content of the emails should be based on your example spreadsheet? — Benoît Wéry
– Benoît Wéry, Commented Nov 6, 2019 at 17:47
Hello, did your issue get solved? Remember that if you want to mark your question as solved you should accept whatever answer provided a solution to your problem. If that's not the case and your issue is not solved, consider explaining why that's not the case so that this community can help you. — Iamblichus
– Iamblichus, Commented Nov 11, 2019 at 8:19
Below provided resolution helped me modify the script according to my concern. Thanks for the help. I will try replying asap from here onwards. — beenuntz
– beenuntz, Commented Nov 12, 2019 at 5:27

Iamblichus · Accepted Answer · 2019-11-07 08:24:21Z

1

Try using this:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  // Get all values in the spreadsheet
  var data = sheet.getDataRange().getValues();
  // Loop through all rows with data:
  for(var i = 1; i < data.length; i++) {
    var row = data[i];
    var emailAddress = row[0];  // Get email address
    // Build message body (you could also do this with a loop):
    var message = row[2] + "\n" + row[3] + "\n" + row[4] + "\n" + row[5]
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message, {noReply:true});
  }
}

This code takes all values in the sheet and loops through them so you don't need to indicate the number of rows you want to loop through. You do have to indicate which row to start (in this case, the second, hence var i = 1).

Also, when building the message body, a loop could be used, but I'm not sure it's worth it. If there were many more columns it would make sense.

Also, you were trying to use for...in, which is not recommended for looping through arrays, and you were not using the right syntax.

Tell me if that works for you.

answered Nov 7, 2019 at 8:24

Iamblichus

19.5k2 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

beenuntz Over a year ago

Thanks for the help provided. I have modified the script according to my concern.

Collectives™ on Stack Overflow

How to send emails with for loop in google script?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related