Search code examples
google-apps-scriptgoogle-sheetsgmail

Google Sheet Email

I need to send the messages on all the email listed in column A please help ,

var e1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sampling").getRange("A:A");
var email1 = e1.getValue();
var mes1 =  crypto1 + 'our value ' + (changedd * 100) + '  Average value ' + (Sales * 100 );
MailApp.sendEmail(email1, sub1, mes1, mes1);

only first cell in Column A get the email

Solution

  • I believe your goal as follows.

    • You want to send emails using MailApp.sendEmail by retrieving the email addresses from the column "A" of the sheet of "sampling" in the active Spreadsheet.

    Modification points:

    • In your script, the value of email1 is the cell value of the 1st row of the column "A". I understand that this is your current issue. In this case, you can retrieve the values from the column "A" using getValues().
    • And, I thought that the empty values and the duplicated values might be required to be considered.
    • About MailApp.sendEmail(email1, sub1, mes1, mes1), when sub1 is the subject of the email, please use sendEmail(recipient, subject, body). Ref Because when 4 arguments are used like MailApp.sendEmail(email1, sub1, mes1, mes1), the 2nd argument is the reply-to address. Ref Please be careful this.

    When above points are reflected to your script, it becomes as follows.

    Modified script:

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sampling");
var emails = [... new Set(sheet.getRange("A1:A" + sheet.getLastRow()).getValues().reduce((ar, [a]) => {
  if (a) ar.push(a);
  return ar;
}, []))];
var mes1 =  crypto1 + 'our value ' + (changedd * 100) + '  Average value ' + (Sales * 100 );
emails.forEach(email => MailApp.sendEmail(email, sub1, mes1));
    • In this case, it supposes that the variables of crypto1, changedd, Sales and sub1 have already been declared. Please be careful this.

    Note:

    • When you want to send the email by including the emails as cc, please modify above script as follows.

      • From

          emails.forEach(email => MailApp.sendEmail(email, sub1, mes1));

      • To

          MailApp.sendEmail({subject: sub1, body: mes1, cc: emails.join(",")});

    References: