How to send emails based on data filled in a form?

Question

function ifstatement() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("Product Form");
  const ws2 = ss.getSheetByName("Email details");

  var Avals = ws.getRange("c1:c").getValues();
  var lr = Avals.filter(String).length;

  Logger.log(lr);

  var recipient = ws.getRange(lr,2).getValue();
  var sub = ws.getRange(lr,4).getValue();
  var mailTemp = ws.getRange(lr,5).getValue();
  var impfield1 = ws.getRange(lr,6).getValue();
  var impfield2 = ws.getRange(lr,8).getValue();
  var impfield3 = ws.getRange(lr,10).getValue();
  var fieldvalue1 = ws.getRange(lr,7).getValue();
  var fieldvalue2 = ws.getRange(lr,9).getValue();
  var fieldvalue3 = ws.getRange(lr,11).getValue();
  var heading = ws.getRange(lr,12).getValue();
  var subheading = ws.getRange(lr,13).getValue();
  var body = ws.getRange(lr,14).getValue();
  var footer = ws.getRange(lr,15).getValue();
  var attach = ws.getRange(lr,17).getValue();
  var tomail = ws.getRange(lr,16).getValue();
  var file1 = attach.split(",").map(url => DriveApp.getFileById(url.trim().split("=")[1]).getBlob());

  var AvalsWealth = ws2.getRange("b1:b").getValues();
  var AvalsInsurance = ws2.getRange("c1:c").getValues();
  var AvalsTeam = ws2.getRange("e1:e").getValues();
  var lrWealth = AvalsWealth.filter(String).length;
  var lrInsurance = AvalsInsurance.filter(String).length;
  var lrTeam = AvalsTeam.filter(String).length;
  
  Logger.log(mailTemp);
  Logger.log(heading);

  if(tomail=="Wealth RMs"){
    var bccmail = ws2.getRange(2,2,lrWealth).getValues().toString();
  } else if(tomail=="Self"){
    var bccmail = recipient;
  } else if(tomail=="Insurance RMs"){
    var bccmail = ws2.getRange(2,3,lrInsurance).getValues().toString();
  } else if(tomail=="All India"){
    var bccmail = ws2.getRange(2,4,lrTeam).getValues().toString();
  }

  Logger.log(bccmail)

  if(mailTemp=="HTML1"){
    const htmlTemplate = HtmlService.createTemplateFromFile("HTML1");
    htmlTemplate.heading = heading;
    htmlTemplate.subheading = subheading;
    htmlTemplate.body = body;
    htmlTemplate.footer = footer;
    htmlTemplate.impfield1 = impfield1;
    htmlTemplate.impfield2 = impfield2;
    htmlTemplate.impfield3 = impfield3;
    htmlTemplate.fieldvalue1 = fieldvalue1;
    htmlTemplate.fieldvalue2 = fieldvalue2;
    htmlTemplate.fieldvalue3 = fieldvalue3;

    const htmlforemail = htmlTemplate.evaluate().getContent();

    GmailApp.sendEmail('',
    ""+ sub + "",
    '',
    {htmlBody: htmlforemail,
    bcc: bccmail,
    attachments: file1}
    ) } else if(mailTemp=="HTML2"){
          const htmlTemplate = HtmlService.createTemplateFromFile("HTML2");
          htmlTemplate.heading = heading;
          htmlTemplate.subheading = subheading;
          htmlTemplate.body = body;
          htmlTemplate.footer = footer;
          htmlTemplate.impfield1 = impfield1;
          htmlTemplate.impfield2 = impfield2;
          htmlTemplate.impfield3 = impfield3;
          htmlTemplate.fieldvalue1 = fieldvalue1;
          htmlTemplate.fieldvalue2 = fieldvalue2;
          htmlTemplate.fieldvalue3 = fieldvalue3;

          const htmlforemail = htmlTemplate.evaluate().getContent();

          GmailApp.sendEmail('',
          ""+ sub + "",
          '',
          {htmlBody: htmlforemail,
          bcc: bccmail,
          attachments: file1}
          ) } else if(mailTemp=="HTML3"){
            const htmlTemplate = HtmlService.createTemplateFromFile("AUM Annual awards");
            htmlTemplate.heading = heading;
            htmlTemplate.subheading = subheading;
            htmlTemplate.body = body;
            htmlTemplate.footer = footer;
            htmlTemplate.impfield1 = impfield1;
            htmlTemplate.impfield2 = impfield2;
            htmlTemplate.impfield3 = impfield3;
            htmlTemplate.fieldvalue1 = fieldvalue1;
            htmlTemplate.fieldvalue2 = fieldvalue2;
            htmlTemplate.fieldvalue3 = fieldvalue3;

            const htmlforemail = htmlTemplate.evaluate().getContent();

            GmailApp.sendEmail('',
            ""+ sub + "",
            '',
            {htmlBody: htmlforemail,
            bcc: bccmail}
            ) } else if(mailTemp=="HTML4"){
          const htmlTemplate = HtmlService.createTemplateFromFile("HTML4");
          htmlTemplate.heading = heading;
          htmlTemplate.subheading = subheading;
          htmlTemplate.body = body;
          htmlTemplate.footer = footer;
          htmlTemplate.impfield1 = impfield1;
          htmlTemplate.impfield2 = impfield2;
          htmlTemplate.impfield3 = impfield3;
          htmlTemplate.fieldvalue1 = fieldvalue1;
          htmlTemplate.fieldvalue2 = fieldvalue2;
          htmlTemplate.fieldvalue3 = fieldvalue3;

          const htmlforemail = htmlTemplate.evaluate().getContent();

          GmailApp.sendEmail('mayank.agarwal@aumcap.com',
          ""+ sub + "",
          '',
          {htmlBody: htmlforemail,
          bcc: bccmail,
          attachments: file1}
          ) }
}

I have this small setup where I have a Google Sheet, Google Appscript and Google form. The user needs to input data in a Google Form - and once the sheet (linked with Form response) receives the updated row - a script is triggered which takes the data from the row and feeds it into the sheet and sends mail to the selected set of users.

Now the problem I am facing is that I need to send this mail to approx 100-150 people, however Googel appscript does not allow me to send mail to more than 30 participants at a time.

I understand that I need an Email service for this, however, I am unable to find any good solution wherein I can store my templates and the user just fills up data fields and shoots the mail. Maybe because I am very new to this tech field so thats why.

Can anyone please guide me as to what is the setup I need to use for my purpose? Thanks!

Are you sending the emails using BCC? Also, have you tried sending them in batches (30 each call)? — Martí, Aug 02 '21 at 11:27
Yes I am sending them in BCC Sorry I didn't understand send in batches meaning? You mean using a loop? Or how can I batch them? — Mayank Agarwal, Aug 02 '21 at 11:37
I meant sending multiple emails with 30 people in each of them (probably in a loop) — Martí, Aug 02 '21 at 12:03
I was wondering if there can be a better and easier way rather than running loops. Because I already have an 2 If statements in my script (1 based on HTML selected and 2nd based on email set selected) - so it will get very complicated if I then add a loop as well. Anything simpler that you can suggest? I am also adding the code of my script so that you get an idea. — Mayank Agarwal, Aug 02 '21 at 12:06
Are you normal gmail user? There is also 100 recipients per 24h quota limit. — idfurw, Aug 02 '21 at 12:51
No I'm a Gmail business account user. I can send 300 mails a day, however I am not able to send to more than 30-35 people even if I'm sending it as the first mail of the day. — Mayank Agarwal, Aug 02 '21 at 13:52
I just realized that you are not validating any values. Have you made sure that the `bccmail` that you use when using more than 30 recipients is actually valid? It could be the case of an invalid or empty email it's preventing you to send it. Also, the way of making multiple calls to send the mails in multiple batches would be creating a function, not making a loop in every conditional branch. — Martí, Aug 03 '21 at 10:48
Yes, the values are valid, I validate it in the google sheet itself. Can you please how can I create a function for making multiple calls and create batches of mail please? — Mayank Agarwal, Aug 03 '21 at 10:57

score 0 · Answer 1 · answered Aug 10 '21 at 11:15

The first thing you need to do is to keep the BCC list as an array until it's necessary. This will allow us to make chunks out of it. This means removing the .toString() at the end of assigning bccmail.

Then, we need a custom function to send the email in batches of emails. I'm calling that function sendMassEmail.

function sendMassEmail(bccList, subject, plainBody, htmlBody, attachments) {
  for (let bcc of chunks(bccList, 30)) {
    GmailApp.sendEmail(
      '',
      subject,
      plainBody,
      {
        htmlBody,
        bcc: bcc.join(','),
        attachments,
      },
    )
  }
}

Chunks is a function that does exactly that, gets the elements of an array in chunks. My favorite implementation for this case is by using a generator.

I've kept the plain text body as it's important to generate it if you are sending it, as it is shown in most clients on the Inbox. Also, some of them can only read this ones (think smart watch).

The last argument (attachments) is optional.

Here is an example on how you would use it:

sendMassEmail(
  bccmail,
  sub.toString(),
  '', // Should probably change it
  htmlforemail,
  file1,
)

Note that it's better to use toString() than concatenating into an empty string.

References

@Mayank Agarwal Hey, did this work for you? – Martí Aug 13 '21 at 11:30 — Martí, Aug 13 '21 at 11:30

How to send emails based on data filled in a form?

1 Answers1

References