Build a recurring file-delivery system

Northwind sends the same handful of files to the same clients on the same cadence — a weekly performance report, a monthly invoice pack, a refreshed asset bundle. It is the kind of job that is easy until someone is on leave, and then a client is left wondering where their report went.

This script turns that delivery into a schedule. A Deliveries sheet lists each client, the source file to copy, the folder it lands in, and how often. A nightly run checks what is due, drops a dated copy into the client’s folder, and stamps the row so it does not deliver the same file twice. The client sees a fresh file appear without anyone lifting a finger.

What you’ll need

• A Deliveries sheet with a header row and these columns: client, targetFolderId (the client’s Drive folder), sourceFileId (the file to copy each cycle), cadence (weekly, monthly, or anything else for daily), and lastDelivered.
• The script’s account must have edit access to every source file and every target folder — shared drives count, as long as access is granted.
• Nothing else. The script writes back to the same sheet it reads.

The script

// The spreadsheet that lists every recurring delivery.
const DELIVERIES_SHEET_ID = '1abcDeliveriesId';

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

/**
 * Reads the Deliveries sheet, copies any file that is due into its
 * client folder with a dated name, and stamps the row so it will not
 * deliver again until the next cycle.
 */
function recurringDeliveries() {
  const sheet = SpreadsheetApp.openById(DELIVERIES_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 the sheet has no delivery rows.
  if (!rows.length) {
    Logger.log('No deliveries configured — nothing to do.');
    return;
  }

  const today = new Date();
  let delivered = 0;

  // 3. Walk every row and deliver the ones that are due.
  rows.forEach((row, i) => {
    // Treat a blank or invalid lastDelivered as "never delivered".
    const last = row[col.lastDelivered] instanceof Date
      ? row[col.lastDelivered]
      : new Date(0);

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

    // 4. Copy the source file into the client folder with a date stamp.
    const source = DriveApp.getFileById(row[col.sourceFileId]);
    const stamp = Utilities.formatDate(today, 'GMT', 'yyyy-MM-dd');
    source.makeCopy(
      `${source.getName()} — ${stamp}`,
      DriveApp.getFolderById(row[col.targetFolderId])
    );

    // 5. Stamp the row in memory so it will not re-deliver this cycle.
    values[i + 1][col.lastDelivered] = today;
    delivered++;
  });

  // 6. Write the updated dates back in one call.
  if (delivered) {
    sheet.getDataRange().setValues(values);
  }
  Logger.log(`Delivered ${delivered} file(s).`);
}

/**
 * Decides whether a delivery is due, based on its cadence and how long
 * ago it last ran. Anything other than weekly/monthly is treated as daily.
 */
function isDue(cadence, last, today) {
  const days = (today - last) / MS_PER_DAY;
  if (cadence === 'weekly') return days >= 7;
  if (cadence === 'monthly') return days >= 28;
  return days >= 1;
}

How it works

1. recurringDeliveries opens the Deliveries sheet and reads every row in one call with getDataRange.
2. It peels off the header row and builds a col lookup, so the rest of the code refers to columns by name rather than fragile numeric indexes.
3. If there are no data rows, it logs a message and stops.
4. For each row it works out when the file was last delivered — a blank cell becomes new Date(0), which is always “due”.
5. isDue compares the gap in days against the cadence. Weekly needs a seven-day gap, monthly needs twenty-eight, and everything else is daily.
6. When a row is due, it copies the source file into the client folder, appending the run date to the name so each cycle’s file is distinct.
7. It updates lastDelivered in the in-memory copy of the sheet, then writes the whole grid back once at the end — far faster than a write per row.

Example run

The Deliveries sheet looks like this before a Monday run:

client	targetFolderId	sourceFileId	cadence	lastDelivered
Acme Co	1folderAcme	1fileWeekly	weekly	2025-12-01
Beltran Ltd	1folderBel	1fileMonthly	monthly	2025-12-08

On 2025-12-15 the Acme row is fourteen days old, so it is due; the Beltran row is only seven days old, so it is skipped. After the run, Acme’s folder contains a new file named Weekly report — 2025-12-15, and the sheet reads:

client	…	cadence	lastDelivered
Acme Co	…	weekly	2025-12-15
Beltran Ltd	…	monthly	2025-12-08

Trigger it

Run this once a day so deliveries land on the correct date without you watching the calendar:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for recurringDeliveries, time-driven, Day timer, set to an early-morning hour such as 5am–6am.
3. Save. From then on the script checks every morning and delivers whatever is due.

Watch out for

• The cadence is gap-based, not calendar-based. A weekly delivery fires seven days after the last one, so if a run is missed the next one simply happens late rather than being lost.
• makeCopy duplicates the file as it exists at run time. If the source file is still being edited when the trigger fires, the client gets a half-finished copy. Freeze or finalise source files before the delivery hour.
• Copies accumulate. Each cycle adds a dated file to the client folder — over a year that is fifty-plus files. Add a clean-up pass, or deliver into a dated subfolder if it gets noisy.
• Drive has a daily file-creation quota. A handful of deliveries is well within it, but a sheet with hundreds of rows copying large files could hit the limit — stagger them across cadences if so.
• The script copies single files, not folders. To deliver a whole folder of assets, copy its contents in a loop or zip them first.