I have a macro set up to send an email to a general mailbox that I would like to add the ability to specify the recipient of the email (being the email of the estimator named in the dropdown box). The estimator is chosen from a dropdown data validation box that is on a different page.
Google Apps Script does not have the ability to run multiple installable triggers of the same kind of trigger on the same sheet (i.e. you cannot run more than 1 OnEdit installable trigger on the same project). If it could handle multiple installable OnEdit triggers, I would just run each individual instance of the MailApp.sendEmail trigger on different functions with the correct information in each one. The spreadsheet is referenced correctly and rData has taken into consideration the array format.
I have very little experience so I am spinning in circles trying to fix this issue. This is what I have come to by now. (personal information has been replaced with @example.com)
function sendEmail(e){
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const opportunity = rData [0][0];
const estimator = rData [0][4];
const projectName = rData [0][1];
const date = rData [0][5];
const now = new Date().toLocaleString("en-US");
const msg = 'Hello, this is to notify you of the recent project assigned on ' + now;
const subject = opportunity + ' ' + projectName + ' has been assigned to ' + estimator + ', due on '+ date;
const body = msg;
const options = { cc: "logan@example.com"}
if (e.range.columnStart != 5 || e.value != "Matt") return;
let recipient = "matt@example.com";
Logger.log(recipient, subject, msg);
MailApp.sendEmail(recipient, subject, body, options)
}
This works for one recipient, I have not been able to add anyone past the first. I have a total of 11 estimators that I want to specify, but I have no idea if this is even possible or if google will only allow me to send the info to a single estimator.
I have tried putting the estimators in an array, adding them individually, running else if statements, creating a line for each individual, redefining recipient, using GmailApp.sendEmail, everything I can think of to make this work. Having only the experience gained through hours of googling, I an not sure what the limitations are of Apps Script.
Results when I have attempted to define multiple emails, I get Syntax error: SyntaxError: Identifier 'recipient' has already been declared line: 21 file: SendEmail.gs
function sendEmail(e){
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const opportunity = rData [0][0];
const estimator = rData [0][4];
const projectName = rData [0][1];
const date = rData [0][5];
const now = new Date().toLocaleString("en-US");
const msg = 'Hello, this is to notify you of the recent project assigned on ' + now;
const subject = opportunity + ' ' + projectName + ' has been assigned to ' + estimator + ', due on '+ date;
const body = msg;
const options = { cc: "logan@example.com"}
if (e.range.columnStart != 5 || e.value != "Matt") return;
let recipient = "matt@example.com";
if (e.range.columnStart != 5 || e.value != "Rob") return;
let recipient = 'rob@example.com';
Logger.log(recipient, subject, msg);
MailApp.sendEmail(recipient, subject, body, options)
}When attempting to modify the return value with the desired email address, no email was sent.
function sendEmail(e){
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const opportunity = rData [0][0];
const estimator = rData [0][4];
const projectName = rData [0][1];
const date = rData [0][5];
const now = new Date().toLocaleString("en-US");
const msg = 'Hello, this is to notify you of the recent project assigned on ' + now;
const subject = opportunity + ' ' + projectName + ' has been assigned to ' + estimator + ', due on '+ date;
const body = msg;
const options = { cc: "logan@example.com"}
if (e.range.columnStart != 5 || e.value != "Matt") return (recipient = "matt@example.com");
if (e.range.columnStart != 5 || e.value != "Rob") return (recipient = "rob@example.com");
Logger.log(recipient, subject, msg);
MailApp.sendEmail(recipient, subject, body, options)
}When removing the return statement and directly linking the email to the edit, an email was sent to Rob if the edit value is Matt or Rob, without differentiating between the two (where I referenced above it only sends the email to the last listed recipient.)
function sendEmail(e){
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const opportunity = rData [0][0];
const estimator = rData [0][4];
const projectName = rData [0][1];
const date = rData [0][5];
const now = new Date().toLocaleString("en-US");
const msg = 'Hello, this is to notify you of the recent project assigned on ' + now;
const subject = opportunity + ' ' + projectName + ' has been assigned to ' + estimator + ', due on '+ date;
const body = msg;
const options = { cc: "logan@example.com"}
if (e.range.columnStart != 5 || e.value != "Matt"); recipient = "matt@example.com";
if (e.range.columnStart != 5 || e.value != "Rob") ; recipient = 'rob@example.com';
Logger.log(recipient, subject, msg);
MailApp.sendEmail(recipient, subject, body, options)
}When changing the if statement to an else if statement, Syntax error: SyntaxError: Unexpected token 'else' line: 19 file: SendEmail.gs
function sendEmail(e){
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const opportunity = rData [0][0];
const estimator = rData [0][4];
const projectName = rData [0][1];
const date = rData [0][5];
const now = new Date().toLocaleString("en-US");
const msg = 'Hello, this is to notify you of the recent project assigned on ' + now;
const subject = opportunity + ' ' + projectName + ' has been assigned to ' + estimator + ', due on '+ date;
const body = msg;
const options = { cc: "logan@example.com"}
if (e.range.columnStart != 5 || e.value != "Matt"); recipient = "matt@example.com";
else if (e.range.columnStart != 5 || e.value != "Rob"); recipient = 'rob@example.com';
Logger.log(recipient, subject, msg);
MailApp.sendEmail(recipient, subject, body, options)
}I am trying to update this thread with information as I try things and get suggestions, please forgive my inexperience on questions like this. I feel like I am throwing paint at a wall and seeing what sticks.
Thank you @BlindSpots for helping me recognize the simple error of curly brackets.
"your recipient var is not part of the if statements. Format should be: if (condition) { 'code block' } see JavaScript if, else, and else if you used a one line shortcut (no curly braces) for multi-line statement."
Corrected,
function sendEmail(e){
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,11).getValues();
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const opportunity = rData [0][0];
const estimator = rData [0][4];
const projectName = rData [0][1];
const date = rData [0][5];
const now = new Date().toLocaleString("en-US");
const msg = 'Hello, this is to notify you of the recent project assigned on ' + now;
const subject = opportunity + ' ' + projectName + ' has been assigned to ' + estimator + ', due on '+ date;
const body = msg;
const options = { cc: "logan@example.com"}
if (e.range.columnStart != 5 || e.value != "Matt") {recipient = "matt@example.com"};
if (e.range.columnStart != 5 || e.value != "Rob") {recipient = 'rob@example.com'};
Logger.log(recipient, subject, msg);
MailApp.sendEmail(recipient, subject, body, options)
}I cannot believe it is that simple, but the answers consistently are.