HELP for Automated Email Reminders
Norland Amar
Good day, I am going to make an automated email reminder with the following steps:
1. Enter data in Google Forms (name, email, project date, start date)
2. Data is linked in Google Sheets. I have two sheets, drrep_response which is the default sheet in which the data entered in GForms will be inserted with timestamp. The second one is emailreminders with the following columns:
Column A - Email (queried from drrep_response)
Column B - Name (queried)
Column C - Subject (format for subject of email)
Column D - Project Name (queried)
Column E - Start Date (queried)
Column F - Date of Response (queried from the timestamp from drrep_response)
Column G - Message (format of email body)
Column H - One Week Reminder (Date one week after date of response)
Column I - Status (on edit, can be inserted DONE or PENDING/Blank)
3. I want to get the rows of all data that was entered in the current day, that is why I also have the date of response in the GSheets. In a separate trigger (like 11 pm every day), I want a trigger that make those emails of all data entered today (via a for loop) to receive an email reminder on the date of the one week reminder. It repeats everyday.
4. I can edit the GSheet in Column I / Status in which I can enter DONE or PENDING. If a specific row has DONE, or edited to have DONE, the reminder from that specific email will stop. However, if the status in that specific row is still blank or PENDING, the reminder will not stop and change from reminding in 1 week to reminding every 2 days. Once the specific row is DONE, stop the reminder for that specific email.
Now I am baffled with steps 3 and 4, as I tried to use a for loop and for each loop but the code is not working properly. Is there something that can be changed or replaced in the code? Sorry if it's too long. Thank you for the help.
CODE:
// Other things to note: Make sure that when a specific cell has DONE, its specific row will have the trigger activated to them to stop, so no more reminders on that email address. But if it's still PENDING, continue the trigger for 2 days, until the status column has edited it from DONE from its specific row.
function sendEmail() {
var activesh = SpreadsheetApp.getActiveSpreadsheet();
var sheet = activesh.getSheetByName('emailreminder');
var e = 0;
var last = getLastRowSpecial(e);
var today = new Date();
// Get the range of new rows that were added in the current day
var newRowsRange = sheet.getRange("A2:I" + last + 1).getValues().filter(function(row) {
return row[5] && row[5].getDate() == today.getDate() && row[5].getMonth() == today.getMonth() && row[5]. getFullYear() == today.getFullYear();
});
// ****THIS CODE IS NOT WORKING PROPERLY. ONCE FIXED, IF POSSIBLE, IT CAN BE IN A SEPARATE FUNCTION (lines 14 - 40)****
newRowsRange.forEach(function(row) {
var status = sheet.getRange("A2:I" + last).getValues().filter(function(row) {
return row[8]; // Returns every row with either DONE or PENDING
});
// Now, for each row, if that specific row has "DONE", it will stop reminding as it is done. Else, it will continue and instead of triggering in a week, it will trigger every two days until that specific row is "DONE".
status.forEach(function(row) {
var e = 0;
var last = getLastRowSpecial(e);
if (row[8] == "DONE") {
stop();
return;
} else {
var emailAddress = row[0];
var subject = row[2];
var message = row[6];
MailApp.sendEmail(emailAddress, subject, message);
everytwo();
return;
}
});
});
}
// This function identifies the last row of the spreadsheet that is blank, so it makes the last row the blank row which is next to the recently added row/data (GOOD CODE)
function getLastRowSpecial(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const sheet = ss.getSheetByName("emailreminder");
const lastRow = sheet.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() - 1;
console.log(lastRow);
return lastRow
}
// This function is for stopping triggers when testing. Ignore this code.
function stop() {
ScriptApp.getProjectTriggers().forEach(function(trigger) {
if (trigger.getHandlerFunction() == "sendEmail") {
ScriptApp.deleteTrigger(trigger);
}
});
}
// This triggers sends a reminder to an email every 2 days to a specific email that is still pending after a week.
function twoDaysReminder() {
ScriptApp.newTrigger("sendEmail")
.timeBased()
.everyDays(2)
.create();
}
// This trigger sends a reminder for all emails in one week.
function oneWeekReminder() {
ScriptApp.newTrigger("sendEmail")
.timeBased()
.everyWeeks(1)
.create();
}
Nerio Villalobos
To fix this, you could use the Array.find method to find the corresponding row in the status array that matches the current newRowsRange row, and then check the status column of that row. Here's an updated version of the sendEmail function that should work:
var activesh = SpreadsheetApp.getActiveSpreadsheet();
var sheet = activesh.getSheetByName('emailreminder');
var e = 0;
var last = getLastRowSpecial(e);
var today = new Date();
// Get the range of new rows that were added in the current day
var newRowsRange = sheet.getRange("A2:I" + last + 1).getValues().filter(function(row) {
return row[5] && row[5].getDate() == today.getDate() && row[5].getMonth() == today.getMonth() && row[5].getFullYear() == today.getFullYear();
});
var statusRow = sheet.getRange("A2:I" + last).getValues().find(function(statusRow) {
return statusRow[0] == row[0] && statusRow[1] == row[1];
});
if (statusRow && statusRow[8] == "DONE") {
return; // stop reminding as it is done
} else {
var emailAddress = row[0];
var subject = row[2];
var message = row[6];
MailApp.sendEmail(emailAddress, subject, message);
// set the one week reminder date to two days from now
var twoDaysReminderDate = new Date(today.getTime() + 2 * 24 * 60 * 60 * 1000);
sheet.getRange(statusRow.getRow(), 8).setValue(twoDaysReminderDate);
} else {
// add a new row to the status sheet with the two days reminder date
var twoDaysReminderDate = new Date(today.getTime() + 2 * 24 * 60 * 60 * 1000);
var statusSheet = activesh.getSheetByName('drrep_response');
var statusLastRow = getLastRowSpecial(e, statusSheet);
var statusNewRow = [emailAddress, row[1], subject, row[3], row[4], row[5], message, twoDaysReminderDate, ""];
statusSheet.getRange(statusLastRow + 1, 1, 1, statusNewRow.length).setValues([statusNewRow]);
}
}
});
}
Instead of using the status variable, I added a statusRow variable inside the newRowsRange.forEach loop that finds the corresponding row in the emailreminders sheet that matches the current newRowsRange row using the email and name columns. If the status column of this row is "DONE", it skips the reminder.
If the statusRow variable is defined but the status column is not "DONE", it sets the one week reminder date to two days from now and updates the corresponding
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/66c4a582-2854-4341-8371-399c09161429n%40googlegroups.com.