A one-shot reminder trigger that deletes itself

TL;DR — Schedule a reminder for a specific date and time with a one-time Apps Script trigger, pass it the data it needs, do the work, then delete only that trigger by id, so fired triggers don't pile up. Plus a soft NitroGAS note for createOneShotTriggerAt.

“Remind the client 48 hours before their slot.” “Chase the approver on Thursday at 9 if it’s still Pending.” These aren’t nightly jobs. They’re one specific moment, once.

You could run a trigger every hour that scans for due reminders. That works, and for hundreds of reminders it’s the right call. For a few a week, a one-shot trigger is simpler: schedule it for the exact time, let it fire once, and have it clean up after itself.

TL;DR

createOneShotTriggerAt('sendReminder', when) creates a .at(date) trigger and returns its unique id
Save what the reminder needs (row, email, message) in Script Properties under that id
In the handler, read e.triggerUid, load the payload, do the work
In finally, delete that trigger by id and its payload, and nothing else
Keep an eye on the per-script trigger limit (20 per user); use a scanner for high volume

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

Why “deletes itself” matters

A fired .at() trigger won’t run again, but it can linger in the Triggers list. Each one counts toward your limit of 20 triggers per user per script. Schedule a reminder per row for a month and one day newTrigger throws: This script has too many triggers.

And no, deleteTriggersByHandler isn’t the cleanup. It deletes every trigger for sendReminder, including the five other reminders you scheduled for next week. Delete by id, and only the trigger that just fired goes away.

What not to do

Don’t delete by handler name inside the handler; you’ll cancel other pending reminders
Don’t cram data into the function name or a global; triggers can’t take arguments, so store a payload keyed by trigger id
Don’t schedule in the past; .at() with a past date either fires almost immediately or never, depending on timing. Validate first
Don’t promise to-the-minute timing; Apps Script can fire a few minutes late

Workflow

1. Decide the moment (a Date in the sheet's timezone)
2. createOneShotTriggerAt('sendReminder', when) → triggerId
3. Save payload under 'reminder:' + triggerId in Script Properties
4. Optionally write triggerId to the row (so it can be cancelled)
5. Handler: load payload by e.triggerUid → work → finally delete trigger + payload

Schedule it

/**
 * Schedule a reminder for one row at a specific time.
 * @param {number} row     sheet row (1-based)
 * @param {Date} when
 */
function scheduleReminder(row, when) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Bookings');
  var email = sheet.getRange(row, 3).getValue();   // C = Email
  var slot = sheet.getRange(row, 4).getDisplayValue(); // D = Slot (as shown)

  var triggerId = createOneShotTriggerAt('sendReminder', when);

  // Triggers can't take arguments, so store what the run needs, keyed by trigger id
  PropertiesService.getScriptProperties().setProperty(
    'reminder:' + triggerId,
    JSON.stringify({ row: row, email: email, slot: slot })
  );

  // Keep the id on the row so ops (or a Cancel menu) can find it
  sheet.getRange(row, 7).setValue(triggerId); // G = ReminderTriggerId
  return triggerId;
}

createOneShotTriggerAt refuses past dates, invalid dates, and handler names that don’t exist. All three otherwise fail silently at fire time.

The handler cleans up after itself

function sendReminder(e) {
  var id = e && e.triggerUid; // time-driven events include the id of the trigger that fired
  var props = PropertiesService.getScriptProperties();
  var key = 'reminder:' + id;

  try {
    var raw = props.getProperty(key);
    if (!raw) {
      console.warn('No payload for trigger ' + id + ' (cancelled?)');
      return;
    }
    var p = JSON.parse(raw);

    // Re-check the sheet: maybe the booking was cancelled since we scheduled this
    var sheet = SpreadsheetApp.getActive().getSheetByName('Bookings');
    var status = sheet.getRange(p.row, 5).getValue(); // E = Status
    if (status === 'Cancelled') return;

    GmailApp.sendEmail(p.email, 'Reminder: your slot ' + p.slot,
      'Hi! Quick reminder that your slot is ' + p.slot + '. Reply here if you need to move it.');
    sheet.getRange(p.row, 8).setValue('Reminded'); // H = ReminderStatus
  } finally {
    // Only this trigger, only this payload, whether the work succeeded or not
    if (id) deleteTriggerById(id);
    props.deleteProperty(key);
  }
}

deleteTriggerById ships with the snippet. It loops getProjectTriggers() and deletes the one whose getUniqueId() matches.

Two details matter here:

Re-check the row at fire time. The world changes between scheduling and firing. A 10-second check saves an awkward email.
Clean up in finally. If the email throws, you still don’t want a zombie trigger. Log or flag the failure on the row instead.

Cancel a reminder

function cancelReminderForActiveRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getActiveRange().getRow();
  var id = String(sheet.getRange(row, 7).getValue() || '');
  if (!id) {
    SpreadsheetApp.getActive().toast('No reminder scheduled on this row', 'Reminders', 5);
    return;
  }
  deleteTriggerById(id);
  PropertiesService.getScriptProperties().deleteProperty('reminder:' + id);
  sheet.getRange(row, 7, 1, 2).setValues([['', 'Cancelled']]);
}

When to use a scanner instead

One-shot triggers are great for a handful of future moments. Switch to a single recurring trigger that scans a “Due at” column when:

You’d have more than ~10 reminders pending at once (the 20-trigger ceiling is per user, per script, shared with your other triggers)
Reminders are created by many people (each person’s triggers count separately, and are harder to see)
You need a full audit trail. A “Due at / Sent at” column is easier to read than the Triggers panel

If you go the scanner route, set up your triggers once so you don’t end up with duplicates.

Failure modes worth naming

“Too many triggers”: you hit the ceiling. Clean up fired triggers by id, and switch to a scanner for volume.
Row moved or deleted before firing: the payload stores a row number. For sorted sheets, store a stable id and look the row up at fire time.
Created by a teammate: the trigger belongs to whoever scheduled it and runs as them. They also need Gmail authorization.
Timezone mix-ups: build when from the sheet’s values (Date cells are already in the sheet’s timezone), not from strings.
Handler renamed: existing triggers still point at the old name and fail. Keep handler names stable once reminders are scheduled.

Soft Co-Pilot note

NitroGAS keeps createOneShotTriggerAt and deleteTriggerById a search away in the editor. Describe it as “remind the booker 48 hours before the Slot column,” and Co-Pilot can draft the scheduler, the payload, and a handler that cleans up after itself. You still test with a reminder five minutes out. Free core; Co-Pilot is a pass you grab when you want the draft.

Closing checklist

when validated (future, real Date) before creating the trigger
Payload stored under the trigger id; trigger id written to the row
Handler re-checks the row before acting
finally deletes only that trigger (by id) and its payload
Scanner plan ready if reminders grow past a handful

Happy Coding!