Trying to set a column as email sent so I dont keep resending emails again and again
In your script, how about the following modification?
Modified script:
function sendEmails() {
const sheet = SpreadsheetApp.getActiveSheet();
const dataRange = sheet.getRange("A2:E6");
const data = dataRange.getValues();
const rangeList = data.reduce(function (ar, [recipient, , emailAddress, status, done], i) {
if (status == 'approved' && done != 'sent') {
const greeting = 'Dear ' + recipient + ',\n'
const statusMessage = 'Your request is ' + status;
const greatJobMessage = 'Thanks for playing! :)';
const message = [greeting, statusMessage, greatJobMessage].join('\n');
const subject = 'Request ' + status;
MailApp.sendEmail(emailAddress, subject, message);
ar.push("E" + (i + 2));
}
return ar;
}, []);
if (rangeList.length > 0) {
sheet.getRangeList(rangeList).setValue('sent');
}
}
- In this modification, when the values of columns "D" and "E" are
approved
and notsent
, an email is sent. And, when the email was sent,sent
is put to the column "E". For this, I used the rangeList. By this, a value can be put to the split cells.
References:
- getRangeList(a1Notations)
- setValue(value) of Class RangeList
- reduce()
Added:
From your following replying,
Works for this sheet but I need to set columns for email, Status etc at will because my form is long then 26 columns and will keep changing
In this case, how about the following sample script?
Sample script:
function sendEmails() {
// Please modify the colum numbers of the following variables for your actual situation.
const recipientCol = 1; // This is the column A
const emailAddressCol = 3; // This is the column C
const statusCol = 4; // This is the column D
const doneCol = 5; // This is the column E
const sheet = SpreadsheetApp.getActiveSheet();
const dataRange = sheet.getRange("A2:E6");
const data = dataRange.getValues();
const rangeList = data.reduce(function (ar, r, i) {
const recipient = r[recipientCol - 1];
const emailAddress = r[emailAddressCol - 1];
const status = r[statusCol - 1];
const done = r[doneCol - 1];
if (status == 'approved' && done != 'sent') {
const greeting = 'Dear ' + recipient + ',\n'
const statusMessage = 'Your request is ' + status;
const greatJobMessage = 'Thanks for playing! :)';
const message = [greeting, statusMessage, greatJobMessage].join('\n');
const subject = 'Request ' + status;
MailApp.sendEmail(emailAddress, subject, message);
ar.push("E" + (i + 2));
}
return ar;
}, []);
if (rangeList.length > 0) {
sheet.getRangeList(rangeList).setValue('sent');
}
}