Spin up a client folder structure on intake

Every Northwind client gets the same Drive layout: a folder named for the client, with design, dev, contracts, invoices, and shared inside it. Built by hand, it is a minute of clicking per client — and the minute someone skips it, files start landing loose in My Drive and never get found again.

This script makes the folders the moment a client is added. It scans the Clients sheet for any row without a folderId, creates the full folder set, and writes the new folder’s ID back into the row. That ID is both the link to the client’s space and the marker that says “already done”, so the script is safe to run as often as you like.

What you’ll need

• A Clients Google Sheet with a header row and these columns: name (the client name, used as the folder name) and folderId (left blank — the script fills it).
• A root folder in Drive to hold all client folders.
• The IDs of the Clients sheet and the root folder for the two config values at the top of the script.

The script

// The Drive folder that holds every client folder.
const CLIENTS_ROOT = '1abcClientsRootId';

// The Clients sheet.
const CLIENTS_SHEET_ID = '1abcClientsId';

// The standard subfolders created inside every new client folder.
const SUBFOLDERS = ['design', 'dev', 'contracts', 'invoices', 'shared'];

/**
 * Finds every client row without a folderId, builds the standard
 * folder set, and writes the new folder's ID back to the row.
 */
function scaffoldNewClients() {
  const sheet = SpreadsheetApp.openById(CLIENTS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet, splitting the header off the data rows.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;

  if (!rows.length) {
    Logger.log('No clients in the sheet — nothing to do.');
    return;
  }

  // 2. Map column names to indexes so column order does not matter.
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  // 3. Resolve the root folder once and reuse it for every client.
  const root = DriveApp.getFolderById(CLIENTS_ROOT);
  let created = 0;

  // 4. Walk every row. Skip any client that already has a folderId.
  rows.forEach((row, i) => {
    if (row[col.folderId]) return;

    // 5. Create the client folder, then the standard subfolders.
    const folder = root.createFolder(row[col.name]);
    for (const sub of SUBFOLDERS) folder.createFolder(sub);

    // 6. Write the new folder ID back to the matching row in `values`
    //    (i + 1 accounts for the header row).
    values[i + 1][col.folderId] = folder.getId();
    created++;
  });

  // 7. Write the whole grid back in one call.
  sheet.getDataRange().setValues(values);
  Logger.log('Created folders for ' + created + ' new client(s).');
}

How it works

1. scaffoldNewClients opens the Clients spreadsheet and reads the entire grid, splitting row 1 off as the header.
2. If there are no data rows, it logs a message and stops.
3. It builds a col lookup from header text to column index, so the script keeps working even if the columns are reordered.
4. It resolves the root folder once, then walks every row and skips any client that already has a value in folderId — that is how rows already processed are left alone.
5. For each new client it creates a folder named after the client, then loops SUBFOLDERS to create the five standard subfolders inside it.
6. It writes the new folder’s ID back into that row’s folderId cell, which stops the next run from creating the folders again.
7. After the loop it saves the whole grid in a single setValues call.

Example run

The Clients sheet before a run:

name	folderId
Acme Co	1Xy…aB7
Bluefin Ltd
Cedar Studio

Acme Co already has a folderId, so it is skipped. Bluefin and Cedar each get a full folder set in Drive, and their IDs are written back:

name	folderId
Acme Co	1Xy…aB7
Bluefin Ltd	1Qr…nM2
Cedar Studio	1Lp…vK9

In Drive, under the clients root:

Bluefin Ltd/
├── design/
├── dev/
├── contracts/
├── invoices/
└── shared/
Cedar Studio/
├── design/
├── ... (same five subfolders)

The log reads Created folders for 2 new client(s).

Trigger it

Run it on a timer so a folder set appears soon after a client is added:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose scaffoldNewClients, event source Time-driven, type Hour timer, every hour.

You can also run it by hand from the editor straight after adding a client — the folderId guard means running it again never creates duplicates.

Watch out for

• The folderId column is the guard. If it is cleared or the column is renamed, the script no longer recognises processed clients and creates a second set of folders.
• Duplicate client names create separate folders. Drive happily allows two folders with the same name, so keep the name column unique — or the team will not know which “Acme Co” folder is the real one.
• An empty name cell creates a folder with no name. Add a check, or make name a required field in the sheet.
• The script does not set sharing. New folders inherit the root folder’s permissions; if clients need access to their shared subfolder, grant that separately.
• Renaming a client in the sheet later does not rename the Drive folder — the link is the folderId, not the name. Rename the folder by hand if needed.