Build a recurring-task generator

Some Northwind tasks come back on a clockwork schedule — empty the studio recycling every Friday, reconcile petty cash on the first of the month, post the daily stand-up notes. They are too routine to plan, but they still need to land on someone’s list, and a task that lives only in someone’s head is a task that eventually gets forgotten.

This script keeps a Recurring sheet of those repeating jobs and, once a day, copies any that are due into the live Tasks sheet as fresh open rows. The team works one task list; the recurring ones simply reappear on schedule without anyone re-typing them.

What you’ll need

• A Recurring sheet with a header row and these columns: title, assignee, cadence (daily, weekly, or monthly), and lastGenerated.
• A Tasks sheet where new rows are appended, with columns in the order title, assignee, status, createdAt.
• Both can be separate spreadsheets or two tabs — the script opens each by ID.

The script

// The spreadsheet holding the recurring-task definitions.
const RECURRING_SHEET_ID = '1abcRecurringId';

// The spreadsheet where generated tasks are appended.
const TASKS_SHEET_ID = '1abcTasksId';

// Milliseconds in a day — used to turn a date gap into a day count.
const MS_PER_DAY = 86400000;

/**
 * Reads the Recurring sheet, appends an open task for every definition
 * that is due today, and stamps each one so it will not generate twice.
 */
function generateRecurringTasks() {
  const sheet = SpreadsheetApp.openById(RECURRING_SHEET_ID).getSheets()[0];
  const tasks = SpreadsheetApp.openById(TASKS_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();

  // 1. Split the header from the data and map column names to indexes.
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  // 2. Bail out early if there are no recurring definitions.
  if (!rows.length) {
    Logger.log('No recurring tasks configured — nothing to do.');
    return;
  }

  // Normalise "today" to midnight so date comparisons are clean.
  const today = new Date();
  today.setHours(0, 0, 0, 0);

  // 3. Collect every task that is due into one batch.
  const fresh = [];
  rows.forEach((row, i) => {
    // A blank or invalid lastGenerated counts as "never generated".
    const last = row[col.lastGenerated] instanceof Date
      ? row[col.lastGenerated]
      : new Date(0);

    if (!isDue(row[col.cadence], last, today)) return;

    // New task: title, assignee, status, created date.
    fresh.push([row[col.title], row[col.assignee], 'open', today]);

    // Stamp the definition in memory so it will not fire again this cycle.
    values[i + 1][col.lastGenerated] = today;
  });

  // 4. Append the whole batch and write the stamps back in one pass each.
  if (fresh.length) {
    tasks.getRange(tasks.getLastRow() + 1, 1, fresh.length, 4)
      .setValues(fresh);
    sheet.getDataRange().setValues(values);
  }
  Logger.log(`Generated ${fresh.length} task(s).`);
}

/**
 * Decides whether a recurring task is due. Daily needs a one-day gap,
 * weekly a seven-day gap, and monthly only fires on the 1st of the month.
 */
function isDue(cadence, last, today) {
  const days = (today - last) / MS_PER_DAY;
  if (cadence === 'daily') return days >= 1;
  if (cadence === 'weekly') return days >= 7;
  if (cadence === 'monthly') return today.getDate() === 1 && days >= 28;
  return false;
}

How it works

1. generateRecurringTasks opens both spreadsheets and reads the entire Recurring sheet in one call.
2. It peels off the header and builds a col lookup so columns are referred to by name, then stops early if there are no definitions.
3. today is normalised to midnight, so a definition generated earlier today is not counted as a day old.
4. For each row it reads lastGenerated — a blank cell becomes new Date(0), which always counts as due.
5. isDue applies the cadence rule: daily and weekly use a simple day gap, while monthly fires only on the 1st, so a monthly task lands once per calendar month rather than every 28 days.
6. Due tasks are pushed into a fresh array and the definition is stamped in the in-memory grid.
7. At the end it appends the whole fresh batch below the last row of Tasks and writes the updated stamps back — two bulk writes instead of many.

Example run

The Recurring sheet on a Monday, 6am run on 2026-01-12:

title	assignee	cadence	lastGenerated
Empty studio recycling	Sam	weekly	2026-01-05
Post stand-up notes	Priya	daily	2026-01-11
Reconcile petty cash	Lee	monthly	2025-12-01

The recycling row is seven days old and the stand-up row is one day old, so both are due; petty cash is skipped because it is not the 1st. Two new rows appear in Tasks:

title	assignee	status	createdAt
Empty studio recycling	Sam	open	2026-01-12
Post stand-up notes	Priya	open	2026-01-12

Trigger it

Run this once a day, early, so the day’s tasks are waiting when the team arrives:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for generateRecurringTasks, time-driven, Day timer, set to the 6am–7am slot.
3. Save. Each morning the script tops up the Tasks sheet with whatever is due.

Watch out for

• The cadence is gap-based for daily and weekly tasks. If a daily run is missed, the next run still generates only one task — it does not back-fill the skipped day.
• The monthly rule depends on the trigger actually running on the 1st. If the 1st is missed entirely, that month’s task will not appear — relax the rule to today.getDate() <= 3 if you want a small grace window.
• Generated tasks are plain rows. There is no de-duplication against tasks that are already open, so a slow assignee can accumulate several copies of the same weekly job.
• The Tasks sheet column order is assumed to be title, assignee, status, createdAt. If the live sheet differs, the appended rows will land in the wrong columns.
• Running the function manually mid-cycle will generate tasks early and stamp the definitions, pushing the next genuine cycle later.