I'm trying to create an automated email sender using Forms & Apps Script (via Forms response spreadsheet) that can send to multiple recipients, given that the recipients are input values on Forms.

I already created the Forms and here's my current setup:

1. Email Input # 1 (short answer)
2. Details Input # 2 (short answer)
3. Details Input # 3 (short answer)
4. Details input # 4 (Checkboxes/Multiple Choices)
5. Name Input # 5 (via multiple choices, name input in Forms then mapped the corresponding email addresses in Apps Script)
6. Email input # 6 (short answer)
7. Name Input # 7 (same scenario in #5)

As for the Apps Script, I already created a working Apps Script, but my issue lies in returning the mapped emails.

Here's the code:

function sendEmail(e) {
  var responses = e.values;
  var recipientEmail = responses[1] || ''; 
  var clientName = responses[2] || ''; 
  var clientAddress = responses[3] || ''; 
  var scopeworks = responses[4] || ''; 
  var MSPemail = responses[5] || ''; 
  var KAMemail = responses[6] || ''; 
  var assignedSNE = responses[7] || ''; 

  // Build email arrays with robust handling
  var recipientEmailArray = recipientEmail.split(',').map(email => email.trim()).filter(Boolean);
  var MSPEmails = emailMapping[MSPemail] || [];
  var sneEmails = sneEmailMapping[assignedSNE] || [];
  var KAMEmailArray = KAMemail.split(',').map(email => email.trim()).filter(Boolean);

  // Combine TO emails (including KAM)
  var toRecipientsArray = [...recipientEmailArray, ...MSPEmails, ...sneEmails, ...KAMEmailArray].filter(Boolean);
  var toRecipients = toRecipientsArray.join(', ');

  // Combine CC emails
  var ccRecipientsArray = [...specificCcEmail].filter(Boolean);
  var ccRecipients = ccRecipientsArray.join(', ');

  // Debugging logs
  Logger.log('Recipient Email: ' + JSON.stringify(recipientEmailArray));
  Logger.log('MSP Emails: ' + JSON.stringify(MSPEmails));
  Logger.log('SNE Emails: ' + JSON.stringify(sneEmails));
  Logger.log('KAM Emails: ' + JSON.stringify(KAMEmailArray));
  Logger.log('TO Recipients: ' + toRecipients);
  Logger.log('CC Recipients: ' + ccRecipients);

  if (toRecipientsArray.length === 0) {
    Logger.log('Error: No valid TO recipients found.');
    return;
  }

  // Email subject and body
  var subject = "Test Email"
  var emailBody = `
    Random email message

  // Send email
  MailApp.sendEmail({
    to: toRecipients,
    cc: ccRecipients,
    subject: subject,
    htmlBody: emailBody
  });

  Logger.log('Email successfully sent to: ' + toRecipients);
  Logger.log('CCed: ' + ccRecipients);

This code runs but not all of the multiple assigned recipients are not included in the email. Can you help me find another way to code this?

I tried multiple scenarios & different tools for easier solutions but it always fails to deliver expected results.

I'm trying to create an automated email sender using Forms & Apps Script (via Forms response spreadsheet) that can send to multiple recipients, given that the recipients are input values on Forms.

I already created the Forms and here's my current setup:

1. Email Input # 1 (short answer)
2. Details Input # 2 (short answer)
3. Details Input # 3 (short answer)
4. Details input # 4 (Checkboxes/Multiple Choices)
5. Name Input # 5 (via multiple choices, name input in Forms then mapped the corresponding email addresses in Apps Script)
6. Email input # 6 (short answer)
7. Name Input # 7 (same scenario in #5)

As for the Apps Script, I already created a working Apps Script, but my issue lies in returning the mapped emails.

Here's the code:

function sendEmail(e) {
  var responses = e.values;
  var recipientEmail = responses[1] || ''; 
  var clientName = responses[2] || ''; 
  var clientAddress = responses[3] || ''; 
  var scopeworks = responses[4] || ''; 
  var MSPemail = responses[5] || ''; 
  var KAMemail = responses[6] || ''; 
  var assignedSNE = responses[7] || ''; 

  // Build email arrays with robust handling
  var recipientEmailArray = recipientEmail.split(',').map(email => email.trim()).filter(Boolean);
  var MSPEmails = emailMapping[MSPemail] || [];
  var sneEmails = sneEmailMapping[assignedSNE] || [];
  var KAMEmailArray = KAMemail.split(',').map(email => email.trim()).filter(Boolean);

  // Combine TO emails (including KAM)
  var toRecipientsArray = [...recipientEmailArray, ...MSPEmails, ...sneEmails, ...KAMEmailArray].filter(Boolean);
  var toRecipients = toRecipientsArray.join(', ');

  // Combine CC emails
  var ccRecipientsArray = [...specificCcEmail].filter(Boolean);
  var ccRecipients = ccRecipientsArray.join(', ');

  // Debugging logs
  Logger.log('Recipient Email: ' + JSON.stringify(recipientEmailArray));
  Logger.log('MSP Emails: ' + JSON.stringify(MSPEmails));
  Logger.log('SNE Emails: ' + JSON.stringify(sneEmails));
  Logger.log('KAM Emails: ' + JSON.stringify(KAMEmailArray));
  Logger.log('TO Recipients: ' + toRecipients);
  Logger.log('CC Recipients: ' + ccRecipients);

  if (toRecipientsArray.length === 0) {
    Logger.log('Error: No valid TO recipients found.');
    return;
  }

  // Email subject and body
  var subject = "Test Email"
  var emailBody = `
    Random email message

  // Send email
  MailApp.sendEmail({
    to: toRecipients,
    cc: ccRecipients,
    subject: subject,
    htmlBody: emailBody
  });

  Logger.log('Email successfully sent to: ' + toRecipients);
  Logger.log('CCed: ' + ccRecipients);

This code runs but not all of the multiple assigned recipients are not included in the email. Can you help me find another way to code this?

I tried multiple scenarios & different tools for easier solutions but it always fails to deliver expected results.

• google-sheets
• google-apps-script
• triggers
• google-forms

• Do you mind sharing your clear expected result? What are the requirements for it? – EL SRY Commented Nov 21, 2024 at 10:17
• Can I confirm if the email input number 1 is the main recipient for the email and the entry number 6 is the cc recipients? – Lime Husky Commented Nov 21, 2024 at 14:24
• @LimeHusky for clarifications, email input 1 , entry number 5 , email input 6 , and entry number 7 are all considered as main recipient. I have a different email for the cc recipients and it's included in the mapping. – iTumbling Commented Nov 22, 2024 at 0:50
• 1 @ELSRY The clear expected result for this project is: 1. Automated email sender to the delegated recipients: a) recipientEmail b)MSPemail c) KAMemail d) assignedSNE Of course, a template email will be sent to the recipients. The only requirement here is that the user will fill out the necessary fields in Forms. I hope this answer your question. – iTumbling Commented Nov 22, 2024 at 0:51
• 1 1) Sorry to demonstrate my ignorance here, but what is the function/command/array emailMapping; ditto emailMapping. 2) Would you please include the results of the 6 "Debugging logs" 3) Would you please edit the question to include some sample data and an example of a successful outcome. – Tedinoz Commented Nov 22, 2024 at 3:39

1 Answer 1

Send Email to multiple users

The clear expected result for this project is: 1. Automated email sender to the delegated recipients: a) recipientEmail b)MSPemail c) KAMemail d) assignedSNE Of course, a template email will be sent to the recipients. The only requirement here is that the user will fill out the necessary fields in Forms. I hope this answer your question.

I understand that this is your expected result. However, with your provided code, I can't seem to get the other parts of it. Therefore, I have crafted a solution by tweaking your current code. I believe that this will meet your expected result. but if not, you can use this as a reference to finish your project.

Given that you want the user to have multiple emails with the comma as delimeter within a single input field, and the Scope field is a checkbox. you can try this.

Modified code:

function sendEmail(e) {
  var responses = e.values;
  var recipientEmail = responses[1]; 
  var clientName = responses[2]; 
  var clientAddress = responses[3]; 
  var scopeworks = responses[4]; 
  var MSPemail = responses[5]; 
  var KAMemail = responses[6]; 
  var assignedSNE = responses[7]; 
  
  var recipientEmailArray = recipientEmail.split(',').map(email => email.trim());
  var scopeworks = scopeworks.split(',').map(scope => scope.trim()).join(', ');

  var MSPEmailArray = MSPemail.split(',').map(email => email.trim());
  var KAMEmailArray = KAMemail.split(',').map(email => email.trim());
  var SNEmailArray = assignedSNE.split(',').map(email => email.trim());

  var toRecipients = recipientEmailArray.concat(MSPEmailArray, KAMEmailArray, SNEmailArray);

  var ccRecipients = "[email protected], [email protected], [email protected]";
  var subject = "Test Email";
  var emailBody = "Random email message";

  MailApp.sendEmail({
    to: toRecipients.join(','),
    cc: ccRecipients,
    subject: subject,
    htmlBody: emailBody
  });
}

Please add a OnFormSubmit() trigger to execute the code any time the user submits the form. You can check the reference below to know how to add a trigger.

This is how the trigger should look like:

Sample Output:

Reference:

1. How to add an installable trigger
2. onFormSubmit()
3. join()
4. concat()