Pivot & Export to Multiple Sheets

Generate multiple pivot views from a single data source and export to different sheets.

User Story

You have sales transaction data with columns: date, product, region, amount. You need to:

• Validate all transaction data
• Create a pivot table: products by region (sum of amounts)
• Create another pivot table: products by month (sum of amounts)
• Export both pivot views to separate sheets
• Keep original data as reference

With gaslamp

JavaScript

function cookbook_pivotExport() {
  // Step 1: Define schema for transaction data
  const schema = {
    date:    gaslamp.FlameGuards.isString,
    product: gaslamp.FlameGuards.isString,
    region:  gaslamp.FlameGuards.isString,
    amount:  gaslamp.FlameGuards.isNumber,
  };

  const sourceSheet = SpreadsheetApp.getActiveSheet();
  const workbook    = SpreadsheetApp.getActiveSpreadsheet();

  // Step 2: Load and validate transaction data
  const df = gaslamp.BareFrame.fromSheet(sourceSheet);
  const { passed, failed } = gaslamp.FlameFrame.from(df, schema);

  if (failed.length > 0) {
    console.error(failed.length + ' validation error(s) found:');
    console.error(failed.display());
    return;
  }

  console.log(`Loaded ${passed.length} valid transactions`);

  // Step 3: Create Pivot 1 — Products by Region
  const pivotByRegion = passed
    .groupBy(['product', 'region'])
    .sum(['amount']);

  console.log('Pivot 1 (Products × Region):');
  console.log(pivotByRegion.display());

  const regionSheet = workbook.getSheetByName('Pivot - By Region')
    ?? workbook.insertSheet('Pivot - By Region');
  pivotByRegion.toSheet(regionSheet);

  // Step 4: Add month column for time-based pivot
  const withMonth = passed.withColumn('month', (row) => {
    const date = new Date(row.get('date'));
    return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM');
  });

  // Step 5: Create Pivot 2 — Products by Month
  const pivotByMonth = withMonth
    .groupBy(['product', 'month'])
    .sum(['amount']);

  console.log('Pivot 2 (Products × Month):');
  console.log(pivotByMonth.display());

  const monthSheet = workbook.getSheetByName('Pivot - By Month')
    ?? workbook.insertSheet('Pivot - By Month');
  pivotByMonth.toSheet(monthSheet);

  // Step 6: Create summary statistics
  const summary = passed
    .groupBy([])
    .sum(['amount']);

  const totalAmount = summary.toRows()[0].get('amount');
  console.log(`Total sales: ${totalAmount}`);

  // Step 7: Export pivot summaries to a dashboard sheet
  const uniqueProductCount = passed.groupBy(['product']).length;

  const summaryData = gaslamp.BareFrame.fromArrays([
    ['Metric', 'Value'],
    ['Total Transactions', passed.length],
    ['Total Sales Amount', totalAmount],
    ['Unique Products', uniqueProductCount],
  ]);

  const dashboardSheet = workbook.getSheetByName('Dashboard')
    ?? workbook.insertSheet('Dashboard');
  summaryData.toSheet(dashboardSheet);

  console.log('All pivots and dashboard exported');
}

Key gaslamp features used:

• Schema validation with FlameFrame.from() — validate all data before pivoting
• .groupBy() with multiple dimensions — products × region, products × month
• .sum() on grouped data — aggregate amounts per group
• Reusable DataFrame — generate multiple pivots from same source
• .withColumn() for computed columns — add month from date for time-based pivot
• Multiple output sheets — export different pivot views independently
• .toSheet() for each view — write each pivot to its own sheet
• Dashboard creation — combine summary statistics with .fromArrays()

Without gaslamp

JavaScript

function cookbook_pivotExport_vanilla() {
  const sourceSheet = SpreadsheetApp.getActiveSheet();
  const workbook    = SpreadsheetApp.getActiveSpreadsheet();

  // Step 1: Load and validate transaction data
  const range = sourceSheet.getDataRange();
  const values = range.getValues();
  const headers = values[0];
  const rows = values.slice(1);

  const dateIdx = headers.indexOf('date');
  const productIdx = headers.indexOf('product');
  const regionIdx = headers.indexOf('region');
  const amountIdx = headers.indexOf('amount');

  const validated = [];
  for (const row of rows) {
    if (typeof row[dateIdx] !== 'string' || typeof row[productIdx] !== 'string' ||
        typeof row[regionIdx] !== 'string' || typeof row[amountIdx] !== 'number') {
      console.warn('Type error in row:', row);
      continue;
    }
    validated.push(row);
  }

  if (validated.length === 0) {
    console.warn('Cannot pivot: no valid rows');
    return;
  }

  console.log(`Loaded ${validated.length} valid transactions`);

  // Step 2: Manually create pivot map for products by region
  const pivotMapRegion = {};
  const productOrder = [];
  const regionOrder = [];

  for (const row of validated) {
    const product = row[productIdx];
    const region = row[regionIdx];
    const amount = row[amountIdx];
    const key = `${product}|${region}`;

    if (!pivotMapRegion[key]) {
      pivotMapRegion[key] = { product, region, amount: 0 };
      if (!productOrder.includes(product)) productOrder.push(product);
      if (!regionOrder.includes(region)) regionOrder.push(region);
    }

    pivotMapRegion[key].amount += amount;
  }

  // Step 3: Convert pivot map to 2D array (products × regions)
  const pivotByRegion = [];
  for (const product of productOrder) {
    const row = [product];
    for (const region of regionOrder) {
      const key = `${product}|${region}`;
      row.push(pivotMapRegion[key]?.amount || 0);
    }
    pivotByRegion.push(row);
  }

  // Step 4: Write pivot by region to sheet
  const regionSheet = workbook.getSheetByName('Pivot - By Region')
    ?? workbook.insertSheet('Pivot - By Region');

  const regionHeaders = ['Product', ...regionOrder];
  regionSheet.getRange(1, 1, 1, regionHeaders.length).setValues([regionHeaders]);
  if (pivotByRegion.length > 0) {
    regionSheet.getRange(2, 1, pivotByRegion.length, pivotByRegion[0].length)
      .setValues(pivotByRegion);
  }

  // Step 5: Manually create pivot map for products by month
  const pivotMapMonth = {};
  const monthOrder = [];

  for (const row of validated) {
    const product = row[productIdx];
    const date = new Date(row[dateIdx]);
    const month = Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM');
    const amount = row[amountIdx];
    const key = `${product}|${month}`;

    if (!pivotMapMonth[key]) {
      pivotMapMonth[key] = { product, month, amount: 0 };
      if (!monthOrder.includes(month)) monthOrder.push(month);
    }

    pivotMapMonth[key].amount += amount;
  }

  // Step 6: Convert pivot map to 2D array (products × months)
  const pivotByMonth = [];
  for (const product of productOrder) {
    const row = [product];
    for (const month of monthOrder) {
      const key = `${product}|${month}`;
      row.push(pivotMapMonth[key]?.amount || 0);
    }
    pivotByMonth.push(row);
  }

  // Step 7: Write pivot by month to sheet
  const monthSheet = workbook.getSheetByName('Pivot - By Month')
    ?? workbook.insertSheet('Pivot - By Month');

  const monthHeaders = ['Product', ...monthOrder];
  monthSheet.getRange(1, 1, 1, monthHeaders.length).setValues([monthHeaders]);
  if (pivotByMonth.length > 0) {
    monthSheet.getRange(2, 1, pivotByMonth.length, pivotByMonth[0].length)
      .setValues(pivotByMonth);
  }

  // Step 8: Calculate summary statistics
  let totalAmount = 0;
  for (const row of validated) {
    totalAmount += row[amountIdx];
  }

  // Step 9: Write dashboard
  const dashboardSheet = workbook.getSheetByName('Dashboard')
    ?? workbook.insertSheet('Dashboard');

  const dashboardHeaders = ['Metric', 'Value'];
  const dashboardData = [
    ['Total Transactions', validated.length],
    ['Total Sales Amount', totalAmount],
  ];

  dashboardSheet.getRange(1, 1, 1, 2).setValues([dashboardHeaders]);
  dashboardSheet.getRange(2, 1, dashboardData.length, 2).setValues(dashboardData);

  console.log('All pivots and dashboard exported');
}

Without gaslamp, you need to:

• Manually validate types for each row
• Manually create separate pivot maps for each dimension combination
• Manually track row and column order to preserve insertion order
• Manually iterate through validated data multiple times (once per pivot)
• Manually reconstruct 2D arrays from pivot maps with nested loops
• Manually construct headers by spreading computed dimensions
• Manually handle missing pivot cells with optional chaining and defaults
• Duplicate dimension collection logic (product order, region order, month order)
• Manually calculate summary statistics with reduce-like loops
• Write headers and data separately for each output sheet
• No schema validation on results

---

Key Techniques

• Validate before pivoting: Always check data types before generating pivots
• Reuse source data: Generate multiple pivot views from the same validated DataFrame
• Computed columns: Add derived columns (month, year) before pivoting
• Multi-dimensional grouping: Group by multiple columns to create cross-tabulations
• Separate output sheets: Export each pivot to its own sheet for clarity
• Summary dashboard: Create a dashboard sheet with key metrics alongside pivots
• Preserve order: Use .groupBy() which preserves insertion order for dimensions

See groupBy Guide for more aggregation patterns and pivot Guide for advanced pivot operations.