Watch a Drive folder and enqueue new files

TL;DR — A time-driven job that lists files added to a Drive folder since the last checkpoint, appends one queue row per file (id, name, link, status = pending), and moves the cursor forward safely — plus a soft NitroGAS note for listDriveFilesModifiedSince.

Clients drop invoices into a shared “Inbox” folder. Somebody is supposed to notice, open each one, and log it. Some weeks that works. Some weeks three files sit there until the client emails to ask.

Apps Script can’t get a push notification when a file lands in a folder (not without a lot of plumbing). It can look every 15 minutes and write down what’s new. That’s enough for almost every bootstrapper intake flow.

TL;DR

Time-driven trigger every 10–15 minutes runs watchInbox
List files updated since the saved cursor with listDriveFilesModifiedSince
Append one queue row per new file: file id, name, link, status = pending
Skip file ids already in the queue; move the cursor forward only after the rows are written
A separate worker handles the pending rows (claim → work → done)

This guide covers the watcher, the part that turns “a file appeared” into a row. What happens to that row next is the job-queue pattern from Treat a sheet like a job queue. We won’t repeat it here.

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

Why a queue row, not “process it right away”?

You could OCR, rename and file each document inside the watcher. Don’t. Keeping the watcher tiny means:

It finishes in seconds, every time, even when 40 files land at once
A failure processing one file doesn’t stop the others from being noticed
Ops can see the backlog: a Queue tab with pending rows is its own status page
Re-running the worker never re-scans Drive

What not to do

Don’t rely on “files created today”; a run just after midnight misses late files from yesterday
Don’t store the cursor as “now” at the start of the run; files uploaded mid-run fall through the gap
Don’t trust the cursor alone to prevent duplicates; renames and edits bump lastUpdated, so check file ids too
Don’t let two runs overlap; use a lock and skip if busy

The queue tab

Status starts as pending. The worker flips it to working, then done or error.

The watcher

var INBOX_CURSOR_KEY = 'inboxWatcherCursor';

function watchInbox() {
  // Another run still going? Skip; the next trigger will catch up.
  var lock = tryLockOrSkip('script', 1000);
  if (!lock) return;

  try {
    var props = PropertiesService.getScriptProperties();
    var folderId = props.getProperty('inboxFolderId');
    if (!folderId) throw new Error('Set Script Property inboxFolderId');

    var cursor = props.getProperty(INBOX_CURSOR_KEY); // ISO string, or null on first run
    var files = listDriveFilesModifiedSince(folderId, cursor); // oldest first
    if (!files.length) return;

    var queue = SpreadsheetApp.getActive().getSheetByName('Queue');
    var known = knownFileIds_(queue);
    var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
    var now = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd HH:mm');

    var rows = files
      .filter(function (f) { return !known[f.id]; })  // renamed/edited files come back; skip them
      .map(function (f) { return [f.id, f.name, f.url, 'pending', now, '']; });

    if (rows.length) {
      queue.getRange(queue.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
      SpreadsheetApp.flush();
    }

    // Move the cursor only after the rows are safely written
    var newest = files[files.length - 1].lastUpdated;
    props.setProperty(INBOX_CURSOR_KEY, newest.toISOString());
    console.log('Enqueued %s new file(s); cursor → %s', rows.length, newest.toISOString());
  } finally {
    lock.releaseLock();
  }
}

function knownFileIds_(queue) {
  var last = queue.getLastRow();
  var map = {};
  if (last < 2) return map;
  queue.getRange(2, 1, last - 1, 1).getValues().forEach(function (r) {
    if (r[0]) map[String(r[0])] = true;
  });
  return map;
}

The helpers are in the catalog:

listDriveFilesModifiedSince returns { id, name, url, mimeType, lastUpdated, size } oldest first, strictly after the cursor
tryLockOrSkip skips the run if the last one is still going

Want the cursor to carry more than a timestamp (counts, last file name)? readWriteCheckpoint stores a { cursor, updatedAt, … } record instead of a bare string.

Why the cursor is “newest lastUpdated,” not “now”

If you save new Date() as the cursor, a file uploaded while the run was listing could have an earlier lastUpdated than your cursor and never show up. Saving the newest lastUpdated you actually saw means the next run starts exactly where this one’s evidence ends. The strictly-greater check keeps the last file from being enqueued twice.

Install the trigger once

function installInboxWatcher() {
  deleteTriggersByHandler('watchInbox'); // no doppelgängers
  ScriptApp.newTrigger('watchInbox').timeBased().everyMinutes(15).create();
}

deleteTriggersByHandler makes this setup safe to re-run.

First run: backfill or start fresh?

With no cursor, everything in the folder gets enqueued. That’s usually right for a new intake folder. If the folder already holds 2,000 old files, set the cursor to “now” once by hand (props.setProperty(INBOX_CURSOR_KEY, new Date().toISOString())) and only new arrivals get queued.

Filter what you enqueue

Only care about PDFs and images?

var files = listDriveFilesModifiedSince(folderId, cursor, {
  mimeType: [MimeType.PDF, MimeType.PNG, MimeType.JPEG]
});

Keep the cursor logic the same. Skipped types just never become rows.

Failure modes worth naming

Trigger owner can’t see the folder: the list comes back empty and nothing looks wrong. Share the folder with the account that owns the trigger, and log the count every run.
Files moved out after enqueue: the worker should treat “file not found” as a clean error row, not a crash.
Clock skew / same-second uploads: the id check catches duplicates the cursor misses.
Big bursts: 300 files in one run is fine for the watcher (one setValues). The worker is where you batch.
Subfolders: recursive: true works, but deep trees are slow. Prefer a flat intake folder.

Soft Co-Pilot note

NitroGAS keeps listDriveFilesModifiedSince, tryLockOrSkip and deleteTriggersByHandler a search away in the editor. Describe your folder and queue columns and Co-Pilot can draft the watcher and its installer. You still share the folder and watch the first few runs. Free core; Co-Pilot is a pass you grab when you want the draft.

Closing checklist

Folder id in Script Properties; trigger owner can see the folder
Cursor = newest lastUpdated seen, saved after rows are written
File ids de-duplicated against the queue
Lock around the watcher; one trigger installed by a re-runnable setup
Worker handles pending rows separately

Happy Coding!