Build a watched-folder processing pipeline

Northwind has half a dozen “drop boxes” in Drive — one for supplier invoices, one for raw photoshoot exports, one for contractor timesheets — and the same manual loop repeats in each. Someone uploads a file, someone else opens it, renames it, logs it in a sheet, drops a copy somewhere, then pings the team. By the time the third person gets involved, two files have been forgotten.

This script turns that loop into a pipeline. It watches one folder, and on a schedule it picks up every file added since the last run, passes each file through a process function, then stores the run time so the next pass picks up exactly where this one left off. Replace the body of process and you’ve got a folder that quietly logs, routes, or transforms anything dropped into it — without doubles, without misses.

What you’ll need

• The Drive folder ID you want to watch. Share it with anyone who needs to drop files in; the script reads it via DriveApp.
• A Google Sheet to log activity. The default process function writes one row per file: timestamp, name, MIME type, URL.
• Permission to set time-driven triggers (your own account is fine; a shared service account is better for production).

The script

// The folder being watched. Anything dropped here is picked up next run.
const WATCH_FOLDER_ID = '1abcWatchedFolderId';

// The sheet that records every processed file.
const LOG_SHEET_ID = '1abcLogSheetId';

// Script Properties key for the cursor — the last time we ran.
const CURSOR_KEY = 'LAST_RUN';

/**
 * Pick up every file added since the last run, process each one, then
 * advance the cursor. Safe to run on a 5-minute trigger — repeats won't
 * reprocess the same file because the cursor only moves on success.
 */
function processNewFiles() {
  const props = PropertiesService.getScriptProperties();
  const lastRun = new Date(props.getProperty(CURSOR_KEY) || 0);
  const now = new Date();

  const files = DriveApp.getFolderById(WATCH_FOLDER_ID).getFiles();
  let processed = 0;
  let errors = 0;

  while (files.hasNext()) {
    const f = files.next();
    // Drive returns files in no particular order — filter by created date.
    if (f.getDateCreated() <= lastRun) continue;

    try {
      processFile(f);
      processed++;
    } catch (e) {
      // Don't poison the cursor if one file blows up — log and continue.
      errors++;
      Logger.log('Failed on ' + f.getName() + ': ' + e.message);
    }
  }

  // Only advance the cursor when the run completes. If the script
  // crashes outright, the next run picks up from the same lastRun.
  props.setProperty(CURSOR_KEY, now.toISOString());
  Logger.log('Processed ' + processed + ' file(s), ' + errors + ' error(s).');
}

/**
 * The work done on each new file. Swap this body for whatever your
 * pipeline needs — convert formats, move into archive folders, send a
 * notification, kick off a doc generator.
 *
 * @param {GoogleAppsScript.Drive.File} file - The newly added file.
 */
function processFile(file) {
  // Default behaviour: append a row to the log sheet.
  SpreadsheetApp.openById(LOG_SHEET_ID).getSheets()[0]
    .appendRow([
      new Date(),
      file.getName(),
      file.getMimeType(),
      file.getUrl(),
    ]);
}

/**
 * One-off helper: reset the cursor so the next run reprocesses every
 * file in the folder. Useful for backfills or after a code change.
 */
function resetCursor() {
  PropertiesService.getScriptProperties().deleteProperty(CURSOR_KEY);
  Logger.log('Cursor cleared — next run will process every file.');
}

How it works

1. processNewFiles reads the cursor — the timestamp of the last successful run — out of Script Properties. On the very first run, the property is missing and the cursor defaults to the Unix epoch, so every file in the folder gets processed once.
2. It records now at the top of the function. That timestamp is what we commit to the cursor at the end, so any files dropped during the run are still picked up next time — the cursor never skips ahead of work that has actually finished.
3. The file iterator walks the watched folder. Drive returns files in no guaranteed order, so we don’t rely on order — we compare each file’s creation date against the cursor and skip anything older.
4. processFile is the swappable step. The default writes to a log sheet, but the real value of the script is that you replace its body: convert a PDF to a Doc, move the file into a dated archive folder, fire off a Slack webhook, kick off a Gmail draft. The framing around it stays identical.
5. Errors on a single file are caught and logged, but don’t roll back the cursor. That’s deliberate — one bad upload shouldn’t stop the next ten from being processed. If the script crashes outright, the cursor isn’t advanced and the next run retries everything from lastRun.
6. resetCursor is the manual override. Delete the property and the next run treats every file as new — handy after a code change that should apply to historic uploads, or when you’ve just enabled the watcher on an existing folder.

Example run

Suppose the watched folder is empty at 09:00 and the cursor is set to 08:55. Between 09:00 and 09:05, two files arrive:

Time uploaded	File
09:01	invoice-april.pdf
09:04	photos-may.zip

At 09:05 the trigger fires. processNewFiles:

1. Reads lastRun = 08:55 and now = 09:05.
2. Walks the folder, finds both files have dateCreated > 08:55, processes each one — the log sheet gains two rows.
3. Writes 09:05 back as the new cursor.

At 09:10, nothing new has been uploaded. The trigger fires, the iterator finds no files newer than 09:05, no rows are added, and the cursor moves to 09:10. No duplicate log entries; no missed files.

Trigger it

Set it on a clock:

1. In the Apps Script editor, open Triggers (clock icon on the left).
2. Add a time-driven trigger for processNewFiles — every 5 minutes is a sensible default for an active folder, hourly for a slow one.
3. Drop a test file into the watched folder, wait for the next tick, and confirm the log sheet shows a new row.

If you ever need to reprocess everything — say you’ve changed what processFile does — run resetCursor once, then the next scheduled tick will treat the whole folder as new.

Watch out for

• The cursor is dateCreated, not dateModified. Editing an existing file doesn’t trigger a reprocess — only newly uploaded files do. If you want to react to edits too, switch to getLastUpdated() and accept that every edit will fire the pipeline.
• The default folder iterator only sees the top level. To watch a tree, recurse into subfolders inside the loop, or use the Drive Advanced Service with a q filter on modifiedTime.
• The six-minute execution limit will bite if a single run has to process hundreds of files. Process in batches, or move heavy work into a separate trigger that consumes a queue this script writes to.
• One bad file shouldn’t break the pipeline. The try/catch around processFile is essential — without it, one rogue upload could permanently stall the cursor.
• The cursor only advances after a successful run. If you change the script in a way that always errors before reaching the setProperty call, the same files will keep being retried — fix the code or call resetCursor to start fresh.