Send spreadsheet deadline reminders with Make without daily duplicates

Define the reminder policy

Choose an explicit policy: today and tomorrow are eligible, overdue work is handled separately, and each deadline version gets one notification. A daily-until-complete reminder is a different job. If the team chooses that policy, its reminder key needs to include the intended reminder day. Write the rule in terms the operator can check against a row.

Prepare stable IDs and reminder state

Use task_id, title, due_date, status, reminder_key and reminded_at. Prefer unambiguous dates such as 2026-11-02 and a controlled status vocabulary. An example key is TASK-001|2026-11-02. This separates task identity from the deadline version. Do not use the row’s current position as identity if someone might sort, insert or move rows.

Align the schedule and date interpretation

Set a daily schedule, for example 09:00 in the operator’s business time zone. Interpret the deadline and “today” in that same zone. Use the team’s actual zone rather than copying a Japanese example blindly. Test a date boundary and, where relevant, daylight-saving changes. A reminder at the correct clock time can still select the wrong date if parsing uses another zone.

Search existing tasks and filter eligible records

Search the existing open tasks and verify that the result limit covers the required set. Parse due_date as a date, then select values from the start of today up to but excluding the start of the day after tomorrow. Isolate missing or invalid dates for review. A new-row trigger alone cannot detect a deadline approaching on a row created last week.

Send the reminder and save the deadline key

Only notify rows whose calculated key differs from the stored reminder_key. Post the task ID, title and deadline, then save the new key and time after success. A changed deadline can become eligible again. If people edit the sheet concurrently, consider checking that the source values remain unchanged before updating; a simplistic row overwrite can erase a person’s latest correction.

Test the date boundaries and repeated run

Prepare tasks due today, tomorrow, the day after tomorrow, in the past and a completed task. Verify that only the intended open items are selected. Repeat the same run to check reminder state, then change a deadline and verify the chosen policy. If timestamps are used instead of dates, include values immediately on both sides of the boundary.

Common question: what resets a reminder?

In this example, changing the deadline changes the key and permits another reminder. If a reassignment should notify the new owner too, the policy may need an owner identifier. Reopening completed work is another separate decision. List the changes that justify a fresh reminder before finalizing the key format.

A later policy change also needs to account for existing keys. Resetting every row to unnotified can create a sudden burst of messages. Limit the migration to intended tasks and inspect a sample before enabling the new rule across the full sheet.

Watch delivery failures and search coverage

A failed state update after a successful post can make a task appear unnotified. Inspect delivery evidence before retrying. Also monitor coverage: a search limit that omits later rows can produce missing reminders without an execution error. Compare open-task counts with retrieved records and introduce pagination or a more suitable data store before the sheet outgrows the simple workflow.

• How to estimate Make credits: polling, rows and retries
• Prevent duplicate writes in Make with stable keys and recovery states
• Handle Make errors: execution history, incomplete runs and safe retries