Google Apps Script Time based trigger at custom date and time based on value in spreadsheet

Question

I am trying to create a Google apps script with the ability to schedule mails at specific times based on the value in the spreadsheet. There will be multiple date-time cells based on which the trigger would be made. I have tried adding the trigger programmatically using ScriptApp.newTrigger() but I don't get when that function the creates it should run. Any insight on how to go about this would be great.

Edit: Created the trigger when the user submits the form like this.

function saveForm(form) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test');

  const now = Utilities.formatDate(new Date(), "GMT+5:30", "yyyy-MM-dd");

  const newRow = [now, form.cid, form.custName, form.custNumber, form.goodsType, form.tonnage, form.area, form.completeAddr, `${now} - ${form.time}`, form.comments];
  sheet.appendRow(newRow); 

  const customDate = new Date(`${now}T${form.time}`);

  ScriptApp.newTrigger('dispatchMail').timeBased().at(customDate).create();


  return 'Data saved successfully';
}

Now the function that dispatches the mail

  function dispatchMail() {
       const now = Utilities.formatDate(new Date(), "GMT+5:30", "yyyy-MM-dd");

      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test');
      const data = sheet.getDataRange().getValues();

      const normalized = {
               'Mark': [],
               'Jack': [],
               'Chloe': [],
               'Robin': [],
               'Unassigned': []
      };

      for(let i=1; i<data.length; i++) {
          normalized[data[i][10]] = normalized[data[i][10]].concat([data[i]]);
      }

     //start our mail dispatch here once we have aggregated the data
  }

But now the problem is won't have any idea of whom to dispatch the Mail to. e.g.. The user submits the form with Mail Trigger scheduled for Mark at 2pm and Jack at 4pm. Now in the dispatch mail function how to determine that this mail is to dispatched to Mark or Jack. I don't see a way of passing params to the trigger function. Any way to go about this?

Answer 1

If your problem is related to identifying what should be executed when the trigger runs, you may want to change how you approach this.

Instead of creating one trigger for each message, why not have a single trigger check if messages need to be sent.

You can:

• Create a function that:
  • Filters out already sent messages
  • Find messages that have a target send time on the past
  • Sends those messages
• Create a time-based trigger to run that function.

Keep in mind that, on the best case scenario, your message will be sent at the target time, on the worst case, it will be sent on the <interval between trigger runs> time.

Also, you will need to track what has already been sent (with an extra column, or removing the rows, for example)