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:
- Email Input # 1 (short answer)
- Details Input # 2 (short answer)
- Details Input # 3 (short answer)
- Details input # 4 (Checkboxes/Multiple Choices)
- Name Input # 5 (via multiple choices, name input in Forms then mapped the corresponding email addresses in Apps Script)
- Email input # 6 (short answer)
- 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:
- Email Input # 1 (short answer)
- Details Input # 2 (short answer)
- Details Input # 3 (short answer)
- Details input # 4 (Checkboxes/Multiple Choices)
- Name Input # 5 (via multiple choices, name input in Forms then mapped the corresponding email addresses in Apps Script)
- Email input # 6 (short answer)
- 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.
Share Improve this question edited Nov 23, 2024 at 14:20 Wicket 38.5k9 gold badges78 silver badges193 bronze badges asked Nov 21, 2024 at 8:01 iTumblingiTumbling 11 bronze badge 5 |1 Answer
Reset to default 0Send 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:
- How to add an installable trigger
- onFormSubmit()
- join()
- concat()
email input number 1
is the main recipient for the email and theentry number 6
is the cc recipients? – Lime Husky Commented Nov 21, 2024 at 14:24email input 1
,entry number 5
,email input 6
, andentry 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:50recipientEmail
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:511)
Sorry to demonstrate my ignorance here, but what is the function/command/arrayemailMapping
; dittoemailMapping
.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