Split a master sheet into per-owner sheets

Northwind runs every project off one master Tasks sheet — hundreds of rows, one owner column, everyone in it. That works for the project lead, but for each teammate it is noise: they have to filter the sheet down to their own name every time they open it, and a stray sort by someone else wrecks the view.

This script splits the master sheet into one spreadsheet per owner, each holding only that person’s rows plus the header. It runs on a schedule, so every Monday morning María opens Tasks — María and sees exactly her work, nothing else. The master stays the single source of truth; the per-owner sheets are read-friendly copies regenerated from it.

What you’ll need

• A master Google Sheet whose first tab has a header row, including a column titled exactly owner.
• A Drive folder to hold the generated per-owner spreadsheets — copy its ID from the folder URL.
• Edit access to that folder, so the script can create files inside it.

The script

// The master Tasks spreadsheet that everything is split from.
const MASTER_ID = '1abcMasterTasksId';

// The Drive folder the per-owner spreadsheets are written into.
const OUTPUT_FOLDER_ID = '1abcPerOwnerFolderId';

// The header that identifies the owner column.
const OWNER_HEADER = 'owner';

// What to call rows that have no owner set.
const UNASSIGNED_LABEL = 'unassigned';

/**
 * Reads the master Tasks sheet, groups its rows by owner, and writes one
 * fresh spreadsheet per owner into the output folder.
 */
function splitByOwner() {
  // 1. Read every row of the master sheet, header included.
  const [header, ...rows] = SpreadsheetApp.openById(MASTER_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  // 2. Bail out if there is nothing below the header.
  if (!rows.length) {
    Logger.log('Master sheet has no task rows — nothing to split.');
    return;
  }

  // 3. Find the owner column by its header name.
  const ownerCol = header.indexOf(OWNER_HEADER);
  if (ownerCol === -1) {
    throw new Error('No "' + OWNER_HEADER + '" column in the master sheet.');
  }

  // 4. Group the rows into one bucket per owner.
  const buckets = new Map();
  for (const row of rows) {
    const owner = row[ownerCol] || UNASSIGNED_LABEL;
    if (!buckets.has(owner)) buckets.set(owner, []);
    buckets.get(owner).push(row);
  }

  // 5. Clear out last run's files so old data never lingers.
  const folder = DriveApp.getFolderById(OUTPUT_FOLDER_ID);
  removeOldSplitFiles(folder);

  // 6. Create one spreadsheet per owner with the header and their rows.
  for (const [owner, ownerRows] of buckets) {
    const ss = SpreadsheetApp.create('Tasks — ' + owner);
    DriveApp.getFileById(ss.getId()).moveTo(folder);

    const sheet = ss.getSheets()[0];
    sheet.getRange(1, 1, 1, header.length).setValues([header]);
    sheet.getRange(2, 1, ownerRows.length, header.length).setValues(ownerRows);
  }

  Logger.log('Split ' + rows.length + ' rows into ' + buckets.size + ' sheets.');
}

/**
 * Deletes the per-owner spreadsheets from a previous run so the folder
 * never accumulates stale duplicates. Files are matched by the "Tasks — "
 * name prefix.
 *
 * @param {Folder} folder The output folder to clean.
 */
function removeOldSplitFiles(folder) {
  // Iterate every file in the folder and trash the ones from a previous run.
  const all = folder.getFiles();
  while (all.hasNext()) {
    const file = all.next();
    if (file.getName().startsWith('Tasks — ')) {
      file.setTrashed(true);
    }
  }
}

How it works

1. splitByOwner opens the master spreadsheet and reads its first tab in one call, peeling off the header with array destructuring.
2. If there are no rows below the header, it logs a message and stops — no empty files get created.
3. It locates the owner column by name rather than by position, so reordering columns in the master never breaks the split. A missing column throws a clear error.
4. It walks the rows once, dropping each into a Map keyed by owner. Rows with a blank owner go into an unassigned bucket.
5. removeOldSplitFiles trashes last run’s Tasks — … files so the folder does not fill up with duplicates and a removed owner’s file disappears.
6. For each owner it creates a new spreadsheet, moves it into the output folder, writes the header, and writes that owner’s rows beneath it.

Example run

The master Tasks sheet:

task	owner	due
Draft Q3 report	María	2025-10-20
Update price list	Hans	2025-10-18
Review supplier list	María	2025-10-25
Archive old files		2025-10-30

After a run the output folder contains three spreadsheets. Tasks — María holds:

task	owner	due
Draft Q3 report	María	2025-10-20
Review supplier list	María	2025-10-25

Tasks — Hans holds the price-list row, and Tasks — unassigned holds the archive row.

Trigger it

This is a regenerate-from-scratch job, so run it on a schedule that suits the team’s rhythm — Monday morning works well:

1. In the Apps Script editor open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose splitByOwner, event source Time-driven, type Week timer, and pick the day and hour.
4. Save and approve the authorisation prompt.

Watch out for

• The script regenerates the per-owner files every run. Anything a teammate types directly into their copy is overwritten — these are read-only views, and edits must go back into the master.
• Owners are grouped by the exact text in the owner column. “Maria” and “María” become two separate files, and so do “hans” and “Hans”. Keep the master’s owner values consistent.
• A new spreadsheet is created on every run. Without removeOldSplitFiles the folder would fill with duplicates; keep that cleanup step in place.
• setValues writes the whole grid at once, which is fast, but a master sheet with tens of thousands of rows can still approach the script time limit. If it does, split the run across owners in batches.
• The per-owner files inherit only the values, not the formatting, formulas, or data validation of the master sheet.