Dynamic Column Mapping¶

Transform data from different source schemas into a unified format.

User Story¶

You receive customer data from multiple systems with different column names:

System A: cust_id, cust_name, cust_email
System B: customer_id, customer_name, email_address
System C: id, name, email

You need to:

Validate each source independently with its own schema
Map different source column names to a unified target schema
Combine data from all sources into a single standardized format
Handle missing columns gracefully (with defaults)
Export unified data for downstream processing

With gaslamp¶

JavaScript

function cookbook_dynamicColumnMapping() {
  const workbook = SpreadsheetApp.getActiveSpreadsheet();

  // Step 1: Define source schemas (different column names per source)
  const sourceSchemaA = {
    cust_id:   gaslamp.FlameGuards.isNumber,
    cust_name: gaslamp.FlameGuards.isString,
    cust_email: gaslamp.FlameGuards.isString,
  };

  const sourceSchemaB = {
    customer_id:   gaslamp.FlameGuards.isNumber,
    customer_name: gaslamp.FlameGuards.isString,
    email_address: gaslamp.FlameGuards.isString,
  };

  const sourceSchemaC = {
    id:    gaslamp.FlameGuards.isNumber,
    name:  gaslamp.FlameGuards.isString,
    email: gaslamp.FlameGuards.isString,
  };

  // Step 2: Define target unified schema
  const targetSchema = {
    id:    gaslamp.FlameGuards.isNumber,
    name:  gaslamp.FlameGuards.isString,
    email: gaslamp.FlameGuards.isString,
  };

  // Step 3: Create mapping functions for each source
  const mapSourceA = (row) => {
    return {
      id:    row.get('cust_id'),
      name:  row.get('cust_name'),
      email: row.get('cust_email'),
    };
  };

  const mapSourceB = (row) => {
    return {
      id:    row.get('customer_id'),
      name:  row.get('customer_name'),
      email: row.get('email_address'),
    };
  };

  const mapSourceC = (row) => {
    return {
      id:    row.get('id'),
      name:  row.get('name'),
      email: row.get('email'),
    };
  };

  // Step 4: Helper function to load, validate, and map a source
  const processSource = (sheetName, schema, mapFunction) => {
    const sheet = workbook.getSheetByName(sheetName);
    const df = gaslamp.BareFrame.fromSheet(sheet);
    const { passed, failed } = gaslamp.FlameFrame.from(df, schema);

    if (failed.length > 0) {
      console.warn(`${sheetName}: ${failed.length} invalid row(s)`);
      console.error(failed.display());
    }

    const mapped = passed.toRows().map(mapFunction);
    console.log(`Mapped ${mapped.length} rows from ${sheetName}`);
    return mapped;
  };

  // Step 5: Load, validate, and map each source
  const allMapped = [];
  allMapped.push(...processSource('Source A', sourceSchemaA, mapSourceA));
  allMapped.push(...processSource('Source B', sourceSchemaB, mapSourceB));
  allMapped.push(...processSource('Source C', sourceSchemaC, mapSourceC));

  // Step 6: Convert mapped objects to BareFrame using target schema
  if (allMapped.length === 0) {
    console.warn('No valid data to map');
    return;
  }

  // Create 2D array from mapped objects for BareFrame construction
  const headers = ['id', 'name', 'email'];
  const rows = allMapped.map((obj) => [obj.id, obj.name, obj.email]);
  const unified = gaslamp.BareFrame.fromArrays([headers, ...rows]);

  // Step 7: Validate unified data against target schema
  const { passed: validUnified, failed: invalidUnified } = gaslamp.FlameFrame.from(unified, targetSchema);

  if (invalidUnified.length > 0) {
    console.error('Validation failed on unified data:');
    console.error(invalidUnified.display());
    return;
  }

  console.log(`Unified ${validUnified.length} records from all sources`);

  // Step 8: Export unified data
  const outputSheet = workbook.getSheetByName('Unified Data')
    ?? workbook.insertSheet('Unified Data');

  validUnified.toSheet(outputSheet);
  console.log('Unified data exported');
}

Key gaslamp features used:

Multiple source schemas — define validation independently per source
Independent validation — validate each source with its own schema before mapping
.toRows() conversion — get row objects for mapping logic
Custom mapping functions — convert source columns to target columns declaratively
.fromArrays() — construct unified DataFrame from mapped objects
Target schema validation — validate combined result against unified schema
Graceful error handling — log invalid rows per source, continue with valid data
Schema-driven transformation — mapping functions are simple, schema defines contracts

Without gaslamp¶

JavaScript

function cookbook_dynamicColumnMapping_vanilla() {
  const workbook = SpreadsheetApp.getActiveSpreadsheet();

  // Step 1: Helper function to load, validate, and map a source
  const processSource = (sheetName, headerIndexes, mapFunction) => {
    const sheet = workbook.getSheetByName(sheetName);
    const values = sheet.getDataRange().getValues();
    const headers = values[0];
    const rows = values.slice(1);

    // Find column indexes using provided header names
    const indexes = {};
    for (const [key, headerName] of Object.entries(headerIndexes)) {
      indexes[key] = headers.indexOf(headerName);
    }

    // Validate and map rows
    const mapped = [];
    for (const row of rows) {
      if (mapFunction(row, indexes) !== null) {
        mapped.push(mapFunction(row, indexes));
      } else {
        console.warn(`Invalid row in ${sheetName}:`, row);
      }
    }

    console.log(`Mapped ${mapped.length} rows from ${sheetName}`);
    return mapped;
  };

  // Step 2: Load, validate, and map Source A
  const mappedA = processSource(
    'Source A',
    { id: 'cust_id', name: 'cust_name', email: 'cust_email' },
    (row, idx) => {
      if (typeof row[idx.id] !== 'number' || typeof row[idx.name] !== 'string' || typeof row[idx.email] !== 'string') {
        return null;
      }
      return [row[idx.id], row[idx.name], row[idx.email]];
    }
  );

  // Step 3: Load, validate, and map Source B
  const mappedB = processSource(
    'Source B',
    { id: 'customer_id', name: 'customer_name', email: 'email_address' },
    (row, idx) => {
      if (typeof row[idx.id] !== 'number' || typeof row[idx.name] !== 'string' || typeof row[idx.email] !== 'string') {
        return null;
      }
      return [row[idx.id], row[idx.name], row[idx.email]];
    }
  );

  // Step 4: Load, validate, and map Source C
  const mappedC = processSource(
    'Source C',
    { id: 'id', name: 'name', email: 'email' },
    (row, idx) => {
      if (typeof row[idx.id] !== 'number' || typeof row[idx.name] !== 'string' || typeof row[idx.email] !== 'string') {
        return null;
      }
      return [row[idx.id], row[idx.name], row[idx.email]];
    }
  );

  // Step 5: Combine all mapped data
  const unified = [...mappedA, ...mappedB, ...mappedC];

  if (unified.length === 0) {
    console.warn('No valid data to map');
    return;
  }

  console.log(`Unified ${unified.length} records from all sources`);

  // Step 6: Write unified data to output sheet
  const outputSheet = workbook.getSheetByName('Unified Data')
    ?? workbook.insertSheet('Unified Data');

  const unifiedHeaders = ['id', 'name', 'email'];
  outputSheet.getRange(1, 1, 1, unifiedHeaders.length).setValues([unifiedHeaders]);
  outputSheet.getRange(2, 1, unified.length, unifiedHeaders.length).setValues(unified);

  console.log('Unified data exported');
}

Without gaslamp, you need to:

Manually load each source sheet and extract headers/rows
Manually find column indexes for each source (different indexes per source)
Manually validate types for each source with repetitive type-check loops
Manually map each source to unified columns with custom array construction
Repeat validation and mapping logic for each source (no reusability)
Manually concatenate mapped arrays from multiple sources
Manually construct unified headers and write to output sheet
No validation of the final unified result
No declarative mapping — mapping logic is scattered in .map() callbacks

Key Techniques¶

Schema per source: Define independent validation schemas for each source format
Mapping functions: Create declarative mapper functions that convert source rows to target format
Separate validation: Always validate each source before mapping
Graceful continuation: Log validation errors per source but continue processing valid rows
Unified schema: Define a target schema and validate the combined result
Object-based transformation: Use .toRows() to get row objects, map to objects, then reconstruct
Clear error reporting: Report which source had issues and how many invalid rows

See Select & Drop to customize output columns, and Filter Guide for more advanced transformation patterns.