Consolidate contributor sheets into a master

Northwind runs three regions, and each one keeps its own Hours spreadsheet so the regional leads can edit freely without tripping over each other. That works right up until billing day, when someone has to copy three sheets into one, add a column to say which region a row came from, and hope nothing was missed.

This script does that merge on a schedule. It reads every spreadsheet in a shared folder, stamps each row with its region, and writes the combined result to a master Hours sheet. Run it nightly and the master is always current — the regions never touch it, and billing always pulls from one place.

What you’ll need

• A Drive folder (for example Region sheets/) containing one spreadsheet per region. The script reads every spreadsheet in that folder, so keep it tidy.
• A naming convention where the region is the first word of each file name — North Hours, South Hours, West Hours. The script uses that first word as the region label.
• A separate master spreadsheet for the merged output. Its first tab will be cleared and rewritten on every run.
• Each regional sheet should use the same column layout, with a header in row 1.

The script

// The Drive folder holding one spreadsheet per region.
const REGION_FOLDER_ID = '1abcRegionsFolderId';

// The master spreadsheet that receives the merged rows.
const MASTER_SHEET_ID = '1abcMasterHoursId';

/**
 * Reads every spreadsheet in the region folder, tags each row with its
 * region, and writes the combined result to the master sheet.
 */
function consolidateRegions() {
  const folder = DriveApp.getFolderById(REGION_FOLDER_ID);
  const files = folder.getFiles();

  const allRows = [];
  let header = null;

  // 1. Walk every spreadsheet in the folder.
  while (files.hasNext()) {
    const file = files.next();
    const sheet = SpreadsheetApp.openById(file.getId()).getSheets()[0];
    const data = sheet.getDataRange().getValues();

    // Skip a file that has only a header row, or nothing at all.
    if (data.length < 2) continue;

    // 2. Capture the header once, prefixed with a "region" column.
    if (!header) header = ['region', ...data[0]];

    // 3. The region is the first word of the file name.
    const region = file.getName().split(' ')[0];

    // 4. Tag every data row with its region and collect it.
    data.slice(1).forEach((row) => allRows.push([region, ...row]));
  }

  // Nothing to merge — leave the master untouched and stop.
  if (!header) {
    Logger.log('No regional data found — master left as-is.');
    return;
  }

  // 5. Rebuild the master's first tab from scratch.
  const master = SpreadsheetApp.openById(MASTER_SHEET_ID).getSheets()[0];
  master.clear();
  master.getRange(1, 1, 1, header.length).setValues([header]);
  if (allRows.length) {
    master.getRange(2, 1, allRows.length, header.length).setValues(allRows);
  }
  Logger.log('Merged ' + allRows.length + ' rows into the master sheet.');
}

How it works

1. consolidateRegions opens the region folder and iterates over every file in it, opening each as a spreadsheet and reading the first tab in full.
2. A file with fewer than two rows (header only, or empty) is skipped, so a half-set-up regional sheet does not break the run.
3. The header is captured from the first file that has data, prefixed with a new region column. Every later file is assumed to share that layout.
4. The region label is the first word of the file name — North Hours becomes North. Each data row is tagged with that label and pushed onto allRows.
5. If no file contributed any data, the script logs a message and leaves the master alone — better than wiping it to a bare header.
6. Otherwise it clears the master’s first tab and rewrites it: the header, then every collected row in one setValues call.

Example run

The Region sheets/ folder contains North Hours and South Hours. The North Hours sheet holds:

date	person	hours
2025-10-01	Sam	7.5
2025-10-02	Sam	8

And South Hours holds:

date	person	hours
2025-10-01	Lee	6

After a run, the master Hours sheet holds every row, each tagged with its region:

region	date	person	hours
North	2025-10-01	Sam	7.5
North	2025-10-02	Sam	8
South	2025-10-01	Lee	6

That is the single table billing works from — no copy-paste, no guessing which region a row belongs to.

Trigger it

Run the merge overnight so the master is fresh each morning:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add Trigger → choose consolidateRegions, event source Time-driven, type Day timer, and pick the 11pm to midnight slot.
3. Approve the authorisation prompt — the script needs both Sheets and Drive access.

Watch out for

• The master is fully rebuilt every run. Do not enter anything by hand on its first tab — notes, formulas, extra columns — because the clear wipes it. Put any manual work on a second tab.
• Every spreadsheet in the folder is read, including ones dropped there by mistake. Keep the folder dedicated to regional sheets, or the merge will pick up unrelated files.
• The script trusts the first file’s header. If one region renames or reorders its columns, those rows will land under the wrong headings without any error. Agree the column layout up front and keep it fixed.
• The region label is the first word of the file name. Rename North Hours to Northern Region Hours and the label silently becomes Northern. Keep the naming convention consistent.
• A very large number of regional sheets can push the run past the Apps Script execution limit, since each file is opened separately. For a long folder, split the work or merge in batches.