Cross-Sheet Reference Lookup

Enrich data by looking up values from another sheet (like VLOOKUP).

User Story

You have two sheets: - Orders sheet: Customer orders with customer ID and order amount - Customers sheet: Master list of customers with ID, name, and region

You need to:

• Look up customer details (name, region) based on customer ID from the orders
• Add the customer name and region to each order row
• Handle missing customer IDs gracefully (skip or mark as "Unknown")
• Validate all required lookup values exist before processing

With gaslamp

JavaScript

function cookbook_crossSheetLookup() {
  // Step 1: Define schemas for both data sources
  const orderSchema = {
    customer_id: gaslamp.FlameGuards.isNumber,
    amount:      gaslamp.FlameGuards.isNumber,
  };

  const customerSchema = {
    id:     gaslamp.FlameGuards.isNumber,
    name:   gaslamp.FlameGuards.isString,
    region: gaslamp.FlameGuards.isString,
  };

  const workbook = SpreadsheetApp.getActiveSpreadsheet();

  // Step 2: Load and validate orders
  const ordersSheet = workbook.getSheetByName('Orders');
  const ordersDF = gaslamp.BareFrame.fromSheet(ordersSheet);
  const { passed: validOrders, failed: invalidOrders } = gaslamp.FlameFrame.from(ordersDF, orderSchema);

  if (invalidOrders.length > 0) {
    console.error('Invalid orders found:');
    console.error(invalidOrders.display());
    return;
  }

  // Step 3: Load and validate customers (reference data)
  const customersSheet = workbook.getSheetByName('Customers');
  const customersDF = gaslamp.BareFrame.fromSheet(customersSheet);
  const { passed: validCustomers, failed: invalidCustomers } = gaslamp.FlameFrame.from(customersDF, customerSchema);

  if (invalidCustomers.length > 0) {
    console.error('Invalid customers found:');
    console.error(invalidCustomers.display());
    return;
  }

  // Step 4: Create lookup map from customer ID to details
  // Convert customers to an object map for O(1) lookup
  const customerMap = {};
  validCustomers.toRows().forEach((row) => {
    const customerId = row.get('id');
    customerMap[customerId] = {
      name:   row.get('name'),
      region: row.get('region'),
    };
  });

  // Step 5: Enrich orders with customer details via lookup
  const enriched = validOrders.withColumn('customer_name', (row) => {
    const customerId = row.get('customer_id');
    return customerMap[customerId]?.name || 'Unknown';
  }).withColumn('region', (row) => {
    const customerId = row.get('customer_id');
    return customerMap[customerId]?.region || 'Unknown';
  });

  // Step 6: Identify orders with missing customer references
  const hasCustomer = gaslamp.Expression.col('customer_name').ne('Unknown');
  const validEnriched = enriched.filter(hasCustomer.toFunction());
  const missingCustomers = enriched.filter(
    hasCustomer.not().toFunction()
  );

  console.log(`Enriched ${validEnriched.length} orders with customer details`);
  if (missingCustomers.length > 0) {
    console.warn(`${missingCustomers.length} orders have unknown customers:`, missingCustomers.display());
  }

  // Step 7: Write enriched data to output sheet
  const outputSheet = workbook.getSheetByName('Enriched Orders')
    ?? workbook.insertSheet('Enriched Orders');

  validEnriched.toSheet(outputSheet);
  console.log('Enriched orders exported');
}

Key gaslamp features used:

• Dual schema validation — validate both source and reference data independently
• .toRows() and .forEach() — convert DataFrame to rows for building lookup maps
• Object notation ?. (optional chaining) — safe access to lookup results with fallback
• .withColumn() chaining — add multiple lookup columns sequentially
• Expression negation — filter for missing references with .not()
• Graceful fallback — mark missing references as "Unknown" instead of failing

Without gaslamp

JavaScript

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

  // Step 1: Load and validate orders
  const ordersSheet = workbook.getSheetByName('Orders');
  const ordersRange = ordersSheet.getDataRange();
  const ordersValues = ordersRange.getValues();
  const ordersHeaders = ordersValues[0];
  const ordersRows = ordersValues.slice(1);

  const orderCustomerIdIdx = ordersHeaders.indexOf('customer_id');
  const orderAmountIdx = ordersHeaders.indexOf('amount');

  const validOrders = [];
  const invalidOrders = [];
  for (const row of ordersRows) {
    if (typeof row[orderCustomerIdIdx] !== 'number' || typeof row[orderAmountIdx] !== 'number') {
      invalidOrders.push(row);
      continue;
    }
    validOrders.push(row);
  }

  if (invalidOrders.length > 0) {
    console.error('Invalid orders found:', invalidOrders);
    return;
  }

  // Step 2: Load and validate customers
  const customersSheet = workbook.getSheetByName('Customers');
  const customersRange = customersSheet.getDataRange();
  const customersValues = customersRange.getValues();
  const customersHeaders = customersValues[0];
  const customersRows = customersValues.slice(1);

  const customerIdIdx = customersHeaders.indexOf('id');
  const customerNameIdx = customersHeaders.indexOf('name');
  const customerRegionIdx = customersHeaders.indexOf('region');

  const validCustomers = [];
  const invalidCustomers = [];
  for (const row of customersRows) {
    if (typeof row[customerIdIdx] !== 'number' || typeof row[customerNameIdx] !== 'string' || typeof row[customerRegionIdx] !== 'string') {
      invalidCustomers.push(row);
      continue;
    }
    validCustomers.push(row);
  }

  if (invalidCustomers.length > 0) {
    console.error('Invalid customers found:', invalidCustomers);
    return;
  }

  // Step 3: Manually build lookup map from customers
  const customerMap = {};
  for (const row of validCustomers) {
    const customerId = row[customerIdIdx];
    customerMap[customerId] = {
      name:   row[customerNameIdx],
      region: row[customerRegionIdx],
    };
  }

  // Step 4: Manually enrich orders with lookups
  const enriched = [];
  const missingCustomers = [];
  for (const row of validOrders) {
    const customerId = row[orderCustomerIdIdx];
    const customer = customerMap[customerId];

    const customerName = customer ? customer.name : 'Unknown';
    const region = customer ? customer.region : 'Unknown';

    enriched.push([...row, customerName, region]);

    if (!customer) {
      missingCustomers.push(row);
    }
  }

  // Step 5: Filter out rows with missing customers
  const validEnriched = enriched.filter((row) => row[row.length - 2] !== 'Unknown');

  console.log(`Enriched ${validEnriched.length} orders with customer details`);
  if (missingCustomers.length > 0) {
    console.warn(`${missingCustomers.length} orders have unknown customers`);
  }

  // Step 6: Write enriched data to output sheet
  const outputSheet = workbook.getSheetByName('Enriched Orders')
    ?? workbook.insertSheet('Enriched Orders');

  if (validEnriched.length > 0) {
    // Must manually add headers with new lookup columns
    const outputHeaders = [...ordersHeaders, 'customer_name', 'region'];
    outputSheet.getRange(1, 1, 1, outputHeaders.length).setValues([outputHeaders]);

    // Write enriched rows
    outputSheet.getRange(2, 1, validEnriched.length, validEnriched[0].length)
      .setValues(validEnriched);
  }

  console.log('Enriched orders exported');
}

Without gaslamp, you need to:

• Manually load and validate both source and reference data with separate loops
• Manually index headers for both sheets and track column positions
• Manually type-check both datasets with nested conditions
• Manually build lookup map by iterating through reference data
• Manually enrich data with nested ternary operators or if-else for each lookup column
• Manually track which rows had missing references separately
• Manually construct headers by spreading original headers + new lookup column names
• Manually filter results by checking the added columns
• Manually calculate range dimensions for writing multi-source data

---

Key Techniques

• Validate both sources: Always validate source and reference data independently before lookup
• Build lookup maps: Convert reference data to objects for O(1) lookup instead of nested loops
• Safe defaults: Use optional chaining ?. with fallback values for missing references
• Track missing references: Keep separate DataFrame of unmatched rows for auditing
• Filter after enrichment: Remove unmatched rows in post-processing, not during lookup
• Chainable enrichment: Use .withColumn() to add multiple lookup columns sequentially

See Select & Drop to remove intermediate lookup columns if needed after enrichment.