Completed work should stop generating reminders
A teammate finished the materials yesterday, yet another “deadline approaching” email arrives this morning. It was scheduled in advance and completion never canceled it. After repeated false reminders, the team starts overlooking valid ones too.
For a small internal register, check the task’s current state when preparing the message. Skip completed or paused tasks. An open task on or after its due date may receive one automatic reminder per day under the chosen rule. This design flags unfinished work; missing automation runs are a different problem.
Download the Google Apps Script program, blank table and setup instructions, and fictional tasks and checks. Only explicitly allowed internal addresses are supported. This is not a client-chasing workflow.
Import the blank CSV or separate fictional test set. Disable conversion of text to numbers and dates in the import dialog; check that due_date remains text. Replace the reserved address with your own and allow that same address in the program.
Keep one current task state
The Tasks sheet uses exact headers: task_id, title, owner_email, due_date, status, revision. Keep them unchanged. Enter dates as plain-text YYYY-MM-DD, with the column formatted as plain text. Status is open, done, or paused.
A task identifier belongs to one piece of work and is never reused. Increase the integer revision after changing its owner or due date. The daily reminder then belongs to a specific task, date, recipient, and revision. A same-day correction may make a new reminder eligible, so review it first.
The program sends nothing before the due date. Starting on that date, it reminds daily while the task stays open. This is an illustrative operating rule rather than a universal team cadence. Keep existing in-app notifications or a weekly review if they already suffice.
Prepare a copy and inspect a dry run
You need a Google spreadsheet you own, allowed Apps Script access and permission to send internal mail. Create a new spreadsheet, add Tasks, and enter harmless fictional rows. Open Extensions → Apps Script, paste the downloaded program, and configure the spreadsheet identifier, time zone, language, and allowed internal addresses. Start with your own test address only. Default dryRun: true sends nothing.
Run runReminders and inspect planned messages in the execution log. The program validates all task rows before sending. Missing owners, invalid states, duplicate identifiers, and impossible dates stop it before mail is sent. Repair the table first. Once the preview matches, set dryRun: false and run your own test. The testing account grants spreadsheet and mail permissions.
The script checks MailApp’s remaining daily recipient quota. Insufficient quota stops sending; it must not mark unsent messages as sent. Check current Apps Script limits for the actual account.
Record a message before sending it
In ReminderLog, the script records uncertain and saves before sending, then changes that entry to sent after the mail service returns successfully. Either state prevents automatic repetition of the same reminder. A lock prevents two concurrent executions of this program.
The spreadsheet write and email send are separate operations. An interruption between them can leave an ambiguous result. An uncertain entry can therefore represent a message that was never sent. The maintainer checks receipt and execution history, then resolves it manually or removes that particular entry after confirming no send occurred. Automatically deleting all uncertain entries could cause duplicate messages.
Immediately before each message, the script validates the current register again and rechecks that task. This is still not an absolute guarantee: someone can finish it during sending. If one stale message would have an unacceptable impact, use notifications within a system with stronger state handling or retain manual review.
Add the recurring trigger after testing
In Apps Script, open Triggers (clock icon) → Add Trigger. Choose runReminders, event source Time-driven, type Day timer, and an interval such as 9–10 a.m. Save, then compare the time zone in Project Settings with timeZone in the program. Keep only one active project and one such trigger; its lock does not coordinate independent copies. Installable triggers run as their creator, and scheduled intervals do not guarantee an exact minute. In Executions, inspect the first actual scheduled run, its result and message receipt. For a maintainer handover, remove the old trigger before the new maintainer creates their own. Pause by deleting this trigger, rather than closing the browser. Name someone to monitor failures and take over if the account owner leaves.
Check an overdue open task, a second same-day run, a completed task, a future date, an owner change, and an uncertain send. Record actual receipt. Local program tests cover decision logic and logging; they do not establish delivery in your Google account.
If you are scheduling emails before task status is known, start with the forgotten internal tasks problem and the shared tracker. No AI agent is needed here: the decision uses an agreed date and checked state, rather than model interpretation.
Include script administration and exception handling in its value. At small volumes, those costs can exceed manual review. For client follow-ups, use the separate one-follow-up workflow.
Documentation checked October 10, 2026. Example tasks contain no real recipients; no team or client messages were sent while preparing this article.