Remote Ops

Send One Daily Task Reminder from Google Sheets Without Repeat Emails

How can I send one daily email with tomorrow's unfinished Google Sheets tasks without sending repeat reminders?

A locked Apps Script emails tomorrow's unfinished tasks together and records a sent marker for the recipient and due date. A daily trigger runs it. The lock and marker reduce ordinary overlaps and reruns, but cannot guarantee exactly-once delivery.

What you need

  • Google Sheets spreadsheet with a Tasks tab and Task, Due, Status columns
  • Actual date values in column B and United States locale for the sample
  • Apps Script project with SHEET_ID and REMINDER_TO Script Properties
  • Spreadsheet access and authorization for spreadsheet and email permissions

How this script runs

  1. A daily trigger starts the script
  2. It takes a lock so runs can't overlap
  3. It checks the sent marker for tomorrow's date
  4. It collects unfinished tasks due tomorrow
  5. It sends one combined email
  6. It saves the sent marker and clears older ones

A locked Apps Script can put tomorrow’s unfinished Google Sheets tasks into a single email and record a marker to limit repeat sends. The digest goes to one configured address. The lock and marker reduce duplicate sends, but they cannot guarantee exactly-once delivery.

How should the Google Sheets task table be structured?

Put Task, Due, and Status in columns A through C on a tab named Tasks.

Task Due Status
Draft remote onboarding checklist 7/15/2026 Open
Confirm team coverage 7/15/2026 In progress
Archive old meeting notes 7/15/2026 Done
Review support handoff 7/14/2026 Open
Prepare August agenda 7/16/2026 Open

For this sample, set the spreadsheet locale to United States before adding the rows. Column B must contain real date values, not text formatted to look like dates. On the sample run date, three rows are due July 15. One has a Done status, so the email contains the other two. The July 14 task is overdue; the July 16 task is not due tomorrow.

The script treats Done as complete regardless of capitalization or spaces around the word. It treats every other status, including an empty cell, as unfinished. Rows without a task name or due date are skipped. If a row has both a task name and a due value, but that value is text rather than a date, the script stops instead of sending a digest that may be incomplete.

Only one configured address receives the reminder. The script does not send separate messages to task owners. “Tomorrow” is the next calendar date in the spreadsheet’s time zone, including weekends. To send each owner a separate message, add a recipient field and save a separate delivery record for each recipient. This combined digest does not alert owners individually.

How do you install the Google Sheets reminder script?

Create an Apps Script project and save the spreadsheet ID and recipient address in Script Properties. The Properties Service guide explains the service used to keep this configuration out of the source code.

  1. Prepare the spreadsheet. Create a Tasks tab and fill it with the table above. Open File > Settings and set the locale and time zone.
  2. Open Apps Script. Choose Extensions > Apps Script. Replace the starter code with the script below.
  3. Add Script Properties. In Project Settings, add SHEET_ID and set its value to the spreadsheet address text between /d/ and /edit. Add REMINDER_TO with one plain email address. You do not need an API key. Keep configuration and any future secrets out of the source code. Project editors can read properties, so restrict editor access.
  4. Match time zones. Set the Apps Script project time zone to the spreadsheet’s time zone. The spreadsheet time zone is used for date comparisons, while the project time zone determines the trigger schedule.
  5. Save and authorize. Choose sendTomorrowTaskReminder and click Run, then approve the spreadsheet and email permissions. If tasks qualify, a manual run sends a real email. Enter the intended recipient before running it.
function sendTomorrowTaskReminder() {
  const lock = LockService.getScriptLock();

  if (!lock.tryLock(30000)) {
    console.log("Skipped: another reminder run holds the lock.");
    return;
  }

  try {
    const properties = PropertiesService.getScriptProperties();
    const sheetId = (properties.getProperty("SHEET_ID") || "").trim();
    const recipient = (properties.getProperty("REMINDER_TO") || "")
      .trim()
      .toLowerCase();

    if (!sheetId) {
      throw new Error("Set SHEET_ID in Script Properties.");
    }

    if (!/^[^\s@,;<>]+@[^\s@,;<>]+\.[^\s@,;<>]+$/.test(recipient)) {
      throw new Error("Set REMINDER_TO to one plain email address.");
    }

    const spreadsheet = SpreadsheetApp.openById(sheetId);
    const sheet = spreadsheet.getSheetByName("Tasks");

    if (!sheet) {
      throw new Error('Create a sheet named "Tasks".');
    }

    const timeZone = spreadsheet.getSpreadsheetTimeZone();
    const today = Utilities.formatDate(
      new Date(),
      timeZone,
      "yyyy-MM-dd"
    );
    const dueDate = nextCalendarDate_(today);
    const sentKey = "sent:" + recipient + ":" + dueDate;

    if (properties.getProperty(sentKey) !== null) {
      console.log("Skipped: reminder already marked sent for " + dueDate);
      return;
    }

    const rows = sheet.getDataRange().getValues();
    const expectedHeaders = ["Task", "Due", "Status"];
    const validHeaders = expectedHeaders.every(
      (header, index) => String(rows[0][index] || "").trim() === header
    );

    if (!validHeaders) {
      throw new Error("Use Task, Due, Status as headers in A1:C1.");
    }

    const tasks = [];

    for (let index = 1; index < rows.length; index++) {
      const task = String(rows[index][0] ?? "").trim();
      const due = rows[index][1];
      const status = String(rows[index][2] ?? "").trim();

      if (!task || due === "" || due == null) {
        continue;
      }

      if (status.toLowerCase() === "done") {
        continue;
      }

      if (!(due instanceof Date) || Number.isNaN(due.getTime())) {
        throw new Error(
          "Row " + (index + 1) +
          ": Due must be a real Google Sheets date."
        );
      }

      const rowDate = Utilities.formatDate(due, timeZone, "yyyy-MM-dd");

      if (rowDate === dueDate) {
        tasks.push("- " + task + " [" + (status || "Unspecified") + "]");
      }
    }

    if (tasks.length === 0) {
      console.log("No unfinished tasks due " + dueDate);
      return;
    }

    const subject =
      "Task reminder for " + dueDate +
      " (" + tasks.length + " unfinished)";
    const body = [
      "Unfinished tasks due " + dueDate + ":",
      "",
      ...tasks,
      "",
      "Source sheet: Tasks"
    ].join("\n");

    MailApp.sendEmail({
      to: recipient,
      subject: subject,
      body: body
    });

    properties.setProperty(sentKey, new Date().toISOString());
    // Remove markers for past dates so they don't pile up in Script Properties.
    properties.getKeys()
      .filter(key => key.startsWith("sent:") && key.slice(-10) < today)
      .forEach(key => properties.deleteProperty(key));
    console.log("Sent reminder for " + dueDate);
  } finally {
    lock.releaseLock();
  }
}

function nextCalendarDate_(dateKey) {
  const [year, month, day] = dateKey.split("-").map(Number);
  const nextDate = new Date(Date.UTC(year, month - 1, day + 1));
  return Utilities.formatDate(nextDate, "UTC", "yyyy-MM-dd");
}

The helper finds the next calendar date rather than adding 24 hours, which avoids daylight saving shifts. Before sending, the script checks unfinished rows that have both a task and a due value. If any such due cell contains text, the run stops. Fix those cells before trying again. The Properties Service guide describes the key-value storage behind getScriptProperties, which this script also uses for its sent markers.

How do you schedule one daily task reminder?

Add one time-driven trigger for sendTomorrowTaskReminder and choose an afternoon window that comes after the usual task updates.

  1. Create the trigger. In Apps Script, open Triggers > Add Trigger. Choose sendTomorrowTaskReminder, Time-driven, Day timer, and an afternoon window.
  2. Remove duplicate triggers. Leave one daily trigger in place. Additional triggers cause additional executions, even with duplicate protection enabled.
  3. Set the cutoff. Pick a window after task updates and away from midnight. Apps Script runs the trigger sometime within its chosen window, not at an exact minute.

The account that creates the trigger supplies its authorization and must retain access to the spreadsheet. A run after midnight calculates a different tomorrow, so select a window that remains within one calendar date. If calendar events are part of the workflow, compare this method with syncing Google Sheets task deadlines to a Google Calendar. The remote operations guides cover other ways to make handoffs and recurring work visible.

What email does the sample produce, and what happens on reruns?

On July 14, 2026, using the spreadsheet’s time zone, the script sends two unfinished tasks due July 15 if there is no matching sent marker.

Subject:

Task reminder for 2026-07-15 (2 unfinished)

Plain-text body:

Unfinished tasks due 2026-07-15:

- Draft remote onboarding checklist [Open]
- Confirm team coverage [In progress]

Source sheet: Tasks

The Done row is left out, along with the tasks due July 14 and July 16. Of the three rows due July 15, one is marked Done, leaving two unfinished tasks in the message.

Execution condition Result
Qualifying tasks, no marker Send one digest; save timestamp marker
Matching recipient-and-date marker Skip, even if tasks changed
No qualifying tasks No email or marker; later runs may send

The marker key combines the recipient address, after trimming spaces and changing it to lowercase, with the target due date. If the marker is present, later runs skip sending even if qualifying tasks are added or existing rows change. When no tasks qualify, the script sends no email and saves no marker. A later run can send if matching tasks are added. Treat the digest as a daily snapshot, not a notification that updates itself.

For this example, the script creates one subject line containing the task count and a plain-text message with one line for each matching task. It calls MailApp.sendEmail once for the combined content, outside the task loop. The MailApp reference documents the email method and its parameters.

How do the lock and sent marker prevent repeat emails?

The lock stops overlapping executions in the same project, and the marker skips another send to the same recipient for the same due date. Neither one can guarantee exactly-once delivery.

The script asks for a script lock and waits up to 30 seconds to get it. If the lock is still unavailable, execution stops. The LockService reference explains script locks and how they are acquired. After securing the lock, the script looks for a sent marker before reading the sheet and preparing the email.

The script writes the marker after the email submission call. Since those are separate operations, the email could be submitted successfully and execution could fail before the marker is saved. A later run could then send another copy. Writing the marker before submission creates the reverse risk: a failed send could leave a marker that prevents a retry. I would keep the marker after the send and accept that tradeoff.

The Apps Script project owns its lock and properties. A copied project has separate properties and a separate lock, so another active copy can send its own email. Keep one project responsible for this workflow. For individual reminders, add a recipient to the task data and record delivery separately for each recipient. To review task ownership another way, see counting open tasks by owner with Google Sheets QUERY.

How do you fix date errors, skipped reminders, and duplicate emails?

Fix date or status issues, check for a matching marker, and make sure only one project is sending reminders.

An error such as Row 3: Due must be a real Google Sheets date. means the run stopped before sending an email. Remove a leading apostrophe from the affected cell in column B, re-enter the date using the spreadsheet locale, and select Format > Number > Date. Changing how the cell is displayed does not turn existing text into a date. Fix every row reported by the script before running it again.

If a task marked Completed or Closed appears in the email, change its status to Done. The script recognizes only Done as complete. A data-validation dropdown in column C can help keep status entries consistent; include Done among the completion values.

If the logs say the reminder is already marked sent, a marker exists for that recipient and date. Usually, leave the daily snapshot as it is. If you choose to send a replacement, delete only the matching sent:recipient:YYYY-MM-DD Script Property in Project Settings, then run the script again. Removing the marker allows another email; it does not recall the first one. The properties guide describes the service that stores these values.

If duplicate reminders arrive, disable the triggers in other projects that send them and keep one project active. If an execution failed around the time the email was sent or the marker was saved, review the execution record and confirm whether the earlier email went out before trying again. Do not delete markers in bulk, since that may allow another copy to be sent.

This setup is a poor fit for workflows that require separate owner reminders, task lists too long to fit usefully in an email, or strict duplicate-proof delivery. Apps Script cannot combine email submission and marker storage in one atomic transaction. For those needs, use a durable job system with an auditable delivery ledger and an email service that supports idempotent send requests.

Sources

Esc