Auto-publish approved files to a public folder

Northwind keeps work-in-progress files in a private drafts/ folder and moves them to a public published/ folder once they clear review. Done by hand, that move is easy to forget and easy to get wrong — a file gets moved but stays private, or gets shared but never moved, and the public folder drifts out of sync with what is actually signed off.

This script makes the queue the single source of truth. Editors tick an approved box in a sheet; the script moves each approved file into the public folder, opens up its sharing, and stamps the row so it is never processed twice. The public folder then contains exactly the approved files and nothing else.

What you’ll need

• A Publishing queue Google Sheet with a header row and these columns: fileId, approved (a checkbox or TRUE/FALSE), and publishedAt.
• A Drive folder to act as the public destination, and its folder ID.
• The files referenced by fileId must be owned by or shared with the account running the script, so it can move them and change their sharing.

The script

// The sheet that drives the queue, and the public destination folder.
const PUBLISH_QUEUE_ID = '1abcPublishQueueId';
const PUBLISHED_FOLDER_ID = '1abcPublishedFolderId';

/**
 * Moves every approved-but-unpublished file into the public folder,
 * makes it viewable by link, and records when it was published.
 */
function promoteApproved() {
  const sheet = SpreadsheetApp.openById(PUBLISH_QUEUE_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();

  // Split the header off and map column names to their indexes.
  const [header, ...rows] = values;
  if (!rows.length) {
    Logger.log('Publishing queue is empty — nothing to do.');
    return;
  }
  const col = Object.fromEntries(header.map((k, i) => [k, i]));

  const publishedFolder = DriveApp.getFolderById(PUBLISHED_FOLDER_ID);
  let moved = 0;

  rows.forEach((row, i) => {
    // Skip rows that are not approved, or already published.
    if (!row[col.approved] || row[col.publishedAt]) return;

    const file = DriveApp.getFileById(row[col.fileId]);

    // Move the file into the public folder...
    file.moveTo(publishedFolder);

    // ...and open it up so anyone with the link can view it.
    file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);

    // Stamp the row so this file is never processed again.
    values[i + 1][col.publishedAt] = new Date();
    moved++;
  });

  // One write commits every timestamp change at once.
  sheet.getDataRange().setValues(values);
  Logger.log(`Promoted ${moved} file(s) to the public folder.`);
}

How it works

1. promoteApproved opens the queue sheet and reads every row with a single getDataRange() call.
2. It splits off the header row and builds a col lookup so columns are referenced by name, not by index.
3. If there are no data rows it logs a message and stops.
4. It resolves the public destination folder once, before the loop, rather than re-fetching it for every file.
5. For each row it skips anything not approved or already carrying a publishedAt timestamp — that timestamp is the guard against moving a file twice.
6. For an eligible file it calls moveTo to relocate it into the public folder, then setSharing to make it viewable by anyone with the link.
7. It writes the current time into the in-memory copy of the row, then commits all timestamp changes with one setValues call after the loop.

Example run

The Publishing queue sheet before a run:

fileId	approved	publishedAt
1fileBrandKit	TRUE
1fileDraftSpec	FALSE
1fileCaseStudy	TRUE	2025-10-30 11:15

After a run, only the first row is touched — 1fileDraftSpec is not approved and 1fileCaseStudy already has a timestamp:

fileId	approved	publishedAt
1fileBrandKit	TRUE	2025-11-01 08:00
1fileDraftSpec	FALSE
1fileCaseStudy	TRUE	2025-10-30 11:15

1fileBrandKit now lives in published/ and is viewable by anyone with the link.

Trigger it

Run this on a schedule so approvals are promoted without anyone watching:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add a trigger for promoteApproved, Time-driven, Hour timer, every hour. An approved file then moves to the public folder within the hour.

Watch out for

• moveTo relocates the file rather than copying it — the original leaves drafts/. If you need the draft kept, copy it first and queue the copy.
• ANYONE_WITH_LINK keeps files link-only, not search-indexed. Switch to DriveApp.Access.ANYONE only if you genuinely want them publicly discoverable.
• The script never moves a file back. If something is unapproved after publishing, you must move it and reset its sharing by hand.
• The publishedAt timestamp is the only thing preventing reprocessing. If a user clears that cell, the file is moved and reshared again on the next run.
• Moving and resharing files counts against Drive quotas. Keep each run to a reasonable batch rather than thousands of files at once.