I’m trying to send an email to a person who filled out an approval google form after a decision has been made. The email must be sent after we change a value in the corresponding sheet to ‘Goedgekeurd’ (= approved) or ‘Afgekeurd’ (= rejected) in a certain column.
The script needs to get the email address from the same row as the changed value.
After running the script, I do not receive an email.
See the script below:
<code>function onEdit(e) {
try {var editedColumn = e.range.getColumn();var editedSheet = e.source.getActiveSheet();
// Checks if the edit is made in the right sheet and right column (column J)
if (editedSheet.getName() == "SheetName" && editedColumn == 10) {
var editedRow = e.range.getRow();
var editedValue = e.value;
var recipientEmail = editedSheet.getRange(editedRow, 2).getValue();
// Checks if the email address in the same row as the changed value isn't empty
if (!recipientEmail) {
Logger.log("E-mailadres is leeg op rij " + editedRow);
return;
}
// Makes an email depending on the changed value
var subject, body;
if (editedValue == "Goedgekeurd") {
subject = "Goedkeuringsbericht";
body = "Uw aanvraag is goedgekeurd.";
} else if (editedValue == "Afgekeurd") {
subject = "Afkeuringsbericht";
body = "Uw aanvraag is afgewezen.";
} else {
return;
}
// Sends the email to the email address found in the sheet
MailApp.sendEmail(recipientEmail, subject, body);
}
</code>
<code>function onEdit(e) {
try {var editedColumn = e.range.getColumn();var editedSheet = e.source.getActiveSheet();
// Checks if the edit is made in the right sheet and right column (column J)
if (editedSheet.getName() == "SheetName" && editedColumn == 10) {
var editedRow = e.range.getRow();
var editedValue = e.value;
var recipientEmail = editedSheet.getRange(editedRow, 2).getValue();
// Checks if the email address in the same row as the changed value isn't empty
if (!recipientEmail) {
Logger.log("E-mailadres is leeg op rij " + editedRow);
return;
}
// Makes an email depending on the changed value
var subject, body;
if (editedValue == "Goedgekeurd") {
subject = "Goedkeuringsbericht";
body = "Uw aanvraag is goedgekeurd.";
} else if (editedValue == "Afgekeurd") {
subject = "Afkeuringsbericht";
body = "Uw aanvraag is afgewezen.";
} else {
return;
}
// Sends the email to the email address found in the sheet
MailApp.sendEmail(recipientEmail, subject, body);
}
</code>
function onEdit(e) {
try {var editedColumn = e.range.getColumn();var editedSheet = e.source.getActiveSheet();
// Checks if the edit is made in the right sheet and right column (column J)
if (editedSheet.getName() == "SheetName" && editedColumn == 10) {
var editedRow = e.range.getRow();
var editedValue = e.value;
var recipientEmail = editedSheet.getRange(editedRow, 2).getValue();
// Checks if the email address in the same row as the changed value isn't empty
if (!recipientEmail) {
Logger.log("E-mailadres is leeg op rij " + editedRow);
return;
}
// Makes an email depending on the changed value
var subject, body;
if (editedValue == "Goedgekeurd") {
subject = "Goedkeuringsbericht";
body = "Uw aanvraag is goedgekeurd.";
} else if (editedValue == "Afgekeurd") {
subject = "Afkeuringsbericht";
body = "Uw aanvraag is afgewezen.";
} else {
return;
}
// Sends the email to the email address found in the sheet
MailApp.sendEmail(recipientEmail, subject, body);
}
<code>} catch (error) {Logger.log("Fout: " + error.message);}}
</code>
<code>} catch (error) {Logger.log("Fout: " + error.message);}}
</code>
} catch (error) {Logger.log("Fout: " + error.message);}}
New contributor
Niels Kempeneers is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.