CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
beenuntz
beenuntz

Reputation: 85

How to send emails with for loop in google script?

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.

enter image description here But this script is not helping to resolve my issue.

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

Upvotes: 0

Views: 1660

Answers (1)

Iamblichus
Iamblichus

Reputation: 19339

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.

Upvotes: 1

Related Questions