Multi-Sheet Merge

Combine data from different sheets into one consolidated view.

User Story

Your organization collects customer feedback through Google Forms from different regions (North, South, East, West). Each region's form responses are saved to a separate sheet with the same structure (id, name, feedback). The region field must match one of the valid regions.

You need to:

• Validate each region's data independently (type and region constraints)
• Merge all valid data into a single consolidated sheet
• Track which region each row came from (for auditing)
• Skip any regions with data quality issues

With gaslamp

JavaScript

function cookbook_mergeSheets() {
  // Step 1: Define valid regions and schemas
  const validRegions = ['North', 'South', 'East', 'West'];

  // Input schema validates each region's form data
  const inputSchema = {
    id:   gaslamp.FlameGuards.isNumber,
    name: gaslamp.FlameGuards.isString,
  };

  // Output schema includes region validation with enum constraint
  const outputSchema = {
    id:     gaslamp.FlameGuards.isNumber,
    name:   gaslamp.FlameGuards.isString,
    region: gaslamp.FlameWright.enumOf(validRegions),
  };

  const workbook = SpreadsheetApp.getActiveSpreadsheet();
  const sheets   = workbook.getSheets();

  // Step 2: Load all sheets and add region metadata
  let merged = null;
  for (const sheet of sheets) {
    if (sheet.getName() === 'Combined Data') continue;

    const df = gaslamp.BareFrame.fromSheet(sheet);

    // Step 3: Validate input data against input schema
    const { passed, failed } = gaslamp.FlameFrame.from(df, inputSchema);

    // Skip sheets with validation errors
    if (failed.length > 0) {
      console.warn(`Skipping "${sheet.getName()}": ${failed.length} validation error(s)`);
      console.error(failed.display());
      console.error('→ Fix these rows in the sheet and run again');
      continue;
    }

    // Add region metadata after input validation
    const withRegion = passed.withColumn('region', () => sheet.getName());

    // Concatenate validated data
    merged = merged === null ? withRegion : merged.concat(withRegion);
  }

  if (merged === null) {
    console.warn('No valid data found');
    return;
  }

  // Step 4: Validate output data against output schema (including region enum constraint)
  const { passed: validated, failed: invalid } = gaslamp.FlameFrame.from(merged, outputSchema);

  if (invalid.length > 0) {
    console.error('Output validation failed for ' + invalid.length + ' row(s):');
    console.error(invalid.display());
    console.error('→ Ensure all region values are one of: ' + validRegions.join(', '));
    return;
  }

  // Step 5: Write the combined data to a new sheet
  const combinedSheet = workbook.getSheetByName('Combined Data')
    ?? workbook.insertSheet('Combined Data');

  console.log(`Combined ${validated.length} rows from ${sheets.length - 1} sheets`);
  console.log(validated.display());

  validated.toSheet(combinedSheet);
  console.log('Combined data exported');
}

Key gaslamp features used:

• Input and output schemas — separate schemas for validation at each stage
• Schema validation with FlameFrame.from() — validate input per sheet, then validate merged result
• .withColumn() for metadata — add region tracking after input validation
• .concat() for merging — directly concatenate validated DataFrames
• .toSheet() — write combined data to Google Sheet

Without gaslamp

JavaScript

function cookbook_mergeSheets_vanilla() {
  const workbook = SpreadsheetApp.getActiveSpreadsheet();
  const sheets   = workbook.getSheets();

  // Step 1: Define valid regions and accumulate valid rows from all sheets
  const validRegions = ['North', 'South', 'East', 'West'];
  const allRows = [];

  for (const sheet of sheets) {
    if (sheet.getName() === 'Combined Data') continue;

    // Step 2: Fetch data from this sheet
    const range = sheet.getDataRange();
    const values = range.getValues();
    const headers = values[0];
    const rows = values.slice(1);

    // Find column indexes
    const idIdx = headers.indexOf('id');
    const nameIdx = headers.indexOf('name');

    // Step 3: Validate and collect rows from this sheet
    const passed = [];
    const failed = [];
    for (const row of rows) {
      // Manual type checking (no schema framework)
      if (typeof row[idIdx] !== 'number' || typeof row[nameIdx] !== 'string') {
        failed.push(row);
        continue;
      }
      passed.push(row);
    }

    // Skip sheets with validation errors
    if (failed.length > 0) {
      console.warn(`Skipping "${sheet.getName()}": ${failed.length} validation error(s)`);
      continue;
    }

    // Step 4: Add region column and accumulate rows with region enum validation
    for (const row of passed) {
      const sheetName = sheet.getName();
      // Validate region value against enum
      if (!validRegions.includes(sheetName)) {
        console.warn(`Skipping row from "${sheetName}": invalid region`);
        continue;
      }
      allRows.push([...row, sheetName]);
    }
  }

  // Step 5: Check if we collected any valid data
  if (allRows.length === 0) {
    console.warn('No valid data found');
    return;
  }

  // Step 6: Write headers and data to the combined sheet
  const combinedSheet = workbook.getSheetByName('Combined Data')
    ?? workbook.insertSheet('Combined Data');

  // Add headers with region column
  const headers = ['id', 'name', 'region'];
  combinedSheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // Add all accumulated rows (batch write for efficiency)
  combinedSheet.getRange(2, 1, allRows.length, allRows[0].length)
    .setValues(allRows);

  console.log(`Combined ${allRows.length} rows from ${sheets.length - 1} sheets`);
}

Without gaslamp, you need to:

• Manually fetch and index headers for each sheet
• Manually type-check each row and separate passed/failed
• Manually skip sheets with validation errors
• Manually define and validate enum constraints (e.g., validRegions array and .includes() checks)
• Manually validate region values before appending them to rows
• Manually append region column to each row (array spread syntax)
• Manually accumulate rows in a 2D array
• Manually construct headers and calculate range dimensions for setValues()

---

Key Techniques

• Input validation first: Validate each sheet independently to catch errors early
• Add metadata after validation: Use .withColumn() to tag rows with region after input validation passes
• Merge with .concat(): Directly concatenate validated DataFrames without intermediate arrays
• Output validation second: Validate the combined result to ensure data integrity
• Handle errors gracefully: Log which sheets were skipped and any output validation failures

See Select & Drop to remove the region column in downstream processing if needed.