AUTOMATED REPLIER QUESTIONS

15 views
Skip to first unread message

Norland Amar

unread,
Mar 30, 2023, 4:10:55 AM3/30/23
to Google Apps Script Community
Good day, I'm currently making an automated replier in which recipients will enter data through Google Form, then it will be linked to
Google Sheets. Then a trigger will run in which the recipient will be reminded via email a week after they responded. They can reply "DONE" if they are
done and the reminder trigger will stop. However, if they are still not done, the trigger will continue, but instead of a week, it is now on every two days.
Then, if they replied "DONE", the every two day reminder trigger will then stop. I just have a couple of questions about those, since in some aspects I am confused.

1.) Is it possible to run the same trigger multiple times for every data entered during that day?
For example:
I have a trigger remindInOneWeek() that emails the recently added email in a week.
In Google Forms, I entered the data/email a...@gmail.com. On form submit, it will go to the row in Google Sheet, then the trigger will run.
Then I went on and entered another data/email d...@gmail.com. On form submit, go to next row in Sheets,the trigger runs, but I wanted to have the trigger from a...@gmail.com to still run as I want it to received an email in a week, just like d...@gmail.com. Then when I add other data, so that means that
when I entered another email like g...@gmail.com, the triggers of a...@gmail.com & d...@gmail.com or any previously added data will still run, until the week comes. Is that even possible and is there a code for it? Is there like a way to do that in AppsScript or any addons if possible?
2.) How to have a trigger that sends an email in a week, then it changes to every two days after receiving the email reminder for the week?
I was able to make a trigger but only for every week. I also want this trigger to run multiple times just like the first question's trigger. The trigger will stop if the recipient replies with "DONE".

My code for reference:
// (Addition) I need this trigger to still run for the specific email address that was previously added
function sendEmail() {
  var activesh = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activesh.getSheetByName('emailreminder');
  var dataValues = sheet.getDataRange().getValues();
  var e = 0;
  var last = getLastRowSpecial(e);
  console.log(last);
  
  var email = dataValues[last][0]; // Column A (Email Address)
  console.log(email);
  var subject = dataValues[last][1]; // Column B (Subject)
  console.log(subject);
  var now = new Date(); // Date today
  console.log(now);
  var oneWeek = new Date(dataValues[last][7]); // Column H (After 7 days)
  console.log(oneWeek);

 if (now <= oneWeek) { // If today is one week after the response, remind the corresponding email address.
    var message = dataValues[last][5]; // Column F (Message)
    MailApp.sendEmail(emailsubjectmessage); }
 else { // For testing only. Instead of a deadline, I want this trigger to stop
 // if recipient replied "DONE" instead.
    ScriptApp.getProjectTriggers().forEach(function(trigger) {
      if (trigger.getHandlerFunction() == "sendEmail") {
        ScriptApp.deleteTrigger(trigger);
      }
    });
  }

// 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
function getLastRowSpecial(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = ss.getSheetByName("emailreminder");
  const lastRow = sheet.getRange(11).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() - 1;
  console.log(lastRow);
  return lastRow
}

// This function creates a trigger that runs sendEmail. This trigger is timebased.
// The thing is I also want to make this so that after a week, it will also run every two days until the
// recipient replies "DONE". Also applies to other email address that was previously added.
function createTrigger() {
  ScriptApp.newTrigger("sendEmail")
  .timeBased()
  .everyWeeks(1)
  .create();

Thank you very much.

cbmserv...@gmail.com

unread,
Mar 30, 2023, 1:37:44 PM3/30/23
to google-apps-sc...@googlegroups.com

No you should not re-trigger on every data entry. What you should do is when the time-based trigger runs for the reminders, then do a for loop and go through all your reminders and send them all out in one shot.

--
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/ecba5e61-1f43-4387-ac5f-a5e093afb812n%40googlegroups.com.

Reply all
Reply to author
Forward
0 new messages